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.
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 (
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:
|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.
|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
A sample connection string for the above
users.csv might look like:
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 about 1 month ago