CSV Data Connector
It is possible to use a CSV file, accessible to the SmartIQ application instance, as a source of data for use within an SmartIQ project.
Key Concepts
CSV (Comma-Separated Variable) is a plain-text data format consisting of rows of data separated by newlines. Each row may contain one or more "columns" of data separated by a delimiter. The comma (ASCII 44, ,) is conventionally used as a delimiter but another character may be used if the data fields are anticipated to contain commas.
1. Creating a CSV file
A CSV file can be created with any plain-text editor (such as Microsoft Notepad). A simple example file is shown below, consisting of three data columns (id, name, and registration date) and four rows of data. The initial row (the "header row") is optional and can be used to specify the data column names within the file - otherwise they must be specified in the Data Source connection string.
One or more non-data rows may proceed or follow the data (e.g. to specify the date the file was generated, or mark the end of the file explicitly).
id,name,registration date
1,Bob Smith,2007-05-04
2,Jane Fletcher,2009-02-17
3,Angela Baxter,2012-04-17
4,Michael Cooper,2013-01-21
2. Creating a CSV Data Source in SmartIQ Manage
Navigate to SmartIQ Manage and click on Data Sources on the left-hand menu.
- Click "New Data Source"
- Select the Connection Type as "CSV"
- Define a connection string according to the parameters below
- Click the "Test Connection" button to confirm that SmartIQ is able to establish a connection with the specified CSV file
- Click "Save"
CSV Connection String Parameters
The CSV Data Source requires a connection string, which may consist of the following parameters:
| Field | Description | Required |
|---|---|---|
| csv | The path to the CSV file (on the SmartIQ application server) | Yes |
| hdr | A "0" or "1" indicating whether the fields names can be retrieved from the first row of the CSV file. | No |
| fields | Names of the fields in the order they appear in the text file, comma delimited. | When the field names can't be retrieved from the CSV file itself. |
| widths | Column-widths if the text file is a fixed width format. Use -1 for a variable width last column. | No |
| delimiter | The delimiter to use, if different from the default (comma). | No |
| skipheaderrows | If the text file contains non-data header rows that need to be skipped. State the number of rows to ignore from the top of the CSV file. | No |
| skipfooterrows | If the text file contains non-data footer rows that need to be skipped. State the number of rows to ignore from the bottom of the CSV file. | No |
| ignorequotes | A "1" will indicate that quotes in the file are not escape characters for text containing delimiters but part of the actual text themselves eg: GROUP|PS00255|"Find a Flat" Service | No |
A sample connection string for the above users.csv might look like:
csv=c:\temp\users.csv;hdr=1
Good Practice
It is recommended to enable "Allow Connection Export" as it will include the connection string in exported projects which use it, allowing the connection to be exported to a different environment.
3. Creating a CSV Data Object in SmartIQ Manage
After saving the CSV Data Source in Manage, click on the "Data Objects" button. This will give a list of defined Data Objects for the Data Source. By default a newly-created Data Source has no Data Objects.
- Click on "New Data Object" and provide:
- Object Type: "File"
- Data Object Name / Definition: Users
- Display Name: Users
- Add all filter fields using the "Add All >>" button, or select filter fields as appropriate using the "Add >>" button
- Click the "Save" button
Updated over 2 years ago