Defining a data source for CSV file systems
The ODBC connector can perform operations with CSV files by leveraging the "Microsoft Text Driver (*.txt; *.csv)" ODBC driver. This driver is available from Microsoft in both 32-bit and 64-bit versions. Depending on the version of the driver you have installed, use either the 64-bit or the 32-bit ODBC connector.
To target CSV files via the ODBC connector you must first configure an ODBC source using the "Microsoft Text Driver (*.txt; *.csv)" ODBC driver:
Start the ODBC data source administrator tool (odbcad32.exe).
Depending on your system, this is located at:
c:\Windows\System32\odbcad32.exe (64-bit)
c:\Windows\SysWow64\odbcad32.exe (32-bit)
Click the System DSN tab, then Add...
Select the "Microsoft Text Driver (*.txt; *.csv)", and click Finish.
Give your data source a name and description.
Deselect Use Current Directory and select the folder location of your CSV files. If a network drive is required, it must be a persistent drive mapping with the same drive letter.
Click Options > Define format... to configure the schema for each CSV file. From here you can define:
Whether the CSV contains column headers or not
The delimiter (if not a comma)
The character set (see UNICODE CSV files for UNICODE support)
The columns, data types and lengths for each CSV file
The Guess button generally gets the columns correct; however if the CSV file includes a byte order mark (BOM) then you need to manually override the columns.
Click OK to close the ODBC data source administrator tool.
The define format process above creates a schema.ini file along side the CSV files. This is a plain text file that can be edited afterwards for additional configuration. The format of the schema.ini file is:
[fileone.csv] ColNameHeader=True|False Format=CSVDelimited|Delimited(<char>) MaxScanRows=25 CharacterSet=ANSI|OEM|<CodePage> Col1=COLUMN_NAME <DataType> Width <FieldLength> Col2=COLUMN_NAME <DataType> Width <FieldLength> ... ColN=COLUMN_NAME <DataType> Width <FieldLength> [filetwo.csv] ....