This article details Solver’s robust integration to import data from flat file sources, including CSV and Excel files.
Introduction
Solver’s file import connector allows users to create integrations and load data from multiple worksheets and workbooks directly from Excel into the Solver Cloud or Private Host solution. The file import connector is also available through the Gateway client. To access the connector in Gateway, you will need to download and install the latest version of Gateway from the Marketplace. This article will detail the file import utility, Excel vs. CSV comparison, file layout recommendations, and common error messages along with troubleshooting steps that users may encounter when working with a flat file.
Supported Features
- Support for Excel and .CSV files (xls, .xlsx, .csv.).
- Support for Excel workbooks that have data in multiple worksheets.
- Can include multiple files in a single integration.
- Macro-enabled files (.xlsm) and password-protected files are supported.
When to use File Import Connector over direct ERP
- One-time imports from legacy data sources where there is no requirement to set up an automated integration (Gateway can automate file import integrations).
- Proof of concept situations where data has been supplied as file(s).
- Data sources that naturally output data to files and provide infrequent updates.
Excel & CSV Comparison
| Excel Advantages | CSV Advantages |
|---|---|
| You may already have the data in a Excel workbook | You may already have the data in an Excel workbook |
| Can leverage formulas, copy/paste, and other Excel features | Can offer better performance for large data sets, i.e. >200k rows |
| Can store multiple datasets as tabs in a single workbook |
Configuration
This article assumes familiarity with the Solver Cloud Data Warehouse and basic configuration steps including creating a new module and loading data. The below steps will focus on file layouts and ideas about what a file should contain and how to organize that information.
Solver Support does not assist in the creation of the Excel or CSV file.
File Layout Recommendations
- Keep dimension and fact (module) data on separate worksheets.
- Each worksheet should consist of one header row that has column labels, and only rows of data underneath the header row.
Tips: You can use a numeric prefix in each sheet (or file) name to determine the order in which worksheets are displayed in our import utility.
Dimension Table Sources
The worksheets should contain columns for the following:
- A unique business or natural key is typically the source for the Code column.
- Description, if applicable.
- Other dimension attributes as desired (groups, rollups, etc.).
Fact (Module) Data Sources
Module data is typically transactional data, each transaction in the file must be unique to ensure a successful integration. Below are recommendations on what fields should be included in the module data load.
- Transaction ID or Record ID, if providing transaction-level detail.
- Dimension business/natural keys (i.e. Account #, Department #, Customer ID).
- Period dimension: date value formatted as YYYYMMDD.
- Numeric values (i.e. Account Balance, Debit Amount, Credit Amount).
- Text comments or descriptors, as desired.
When Creating Tasks within Integrations:
- Be sure to add tasks that update dimension tables first, then modules.
- This avoids any issues with referential integrity since modules contain references to dimensions.
- You can use a numeric prefix in each sheet (or file) name to determine the order in which they appear in the selection box.
Current Limitations
There are a few known issues we are working through for the next release:
- Special characters in the Excel file name are not yet supported.
- An error will occur if there is a CSV and Excel file with the same name imported to the same integration.
- Currently, Solver has a 100MB limit for flat files.