ODBC Setup for CSV Import with ODBC LC Connector

Mindwatering Incorporated

Author: Tripp W Black

Created: 10/27/2009 at 06:59 PM

 

Category:
Notes Developer Tips
LSX (LotusScript Extensions/Connectors)

Issue:
Want to use CSV files for import of data using LSX LC ODBC Connector.

ODBC Setup on Windows:
Configure a System DSN in the ODBC Database Administrator.

Note:
A Text ODBC connection is to a folder:
- The folder is loosely the "database".
- The table is a specific file.
The select would be something like "select * from mycsvfile.csv".

The name and password are still passed into the LX Connection variables/properties, just enter anything (e.g. myid, and mypassword). Seems to crash if left nothing.


1. Start --> Programs --> Administrative Tools --> Data Sources (ODBC)
2. In the ODBC Database Administrator, choose the second tab, System DSN
3. Click the "Add" button.
4. In the driver selection options list, Choose the "Microsoft Text Driver (*txt, *csv)" option. Click Finish.
5. In the new ODBC Text Setup window:
In the Data Source Name field, enter a name to be the "database" name (e.g. CSVImport).
In the Description field, enter a description to help you remember what you entered.
Deselect the "Use current folder" check box and "Select Directory" button to browse to the folder containing the CSV file(s) to import.
6. Click the Options>> button, click the "Define format..." button to map the commas to fields.
Note: If you have a header/title row, then this will be easy as the column headers become the "field" names. Otherwise skip the next few steps and add your own field names for each comma's value. These instructions will assume a header/title row exists.
7. Click the "Column Name Header" check box if unchecked.
8. Click the "Guess" button. In the window/area left of this button, the header names will be displayed with a guess of the data type and length.
9. Highlight each field and update the Type, Name, and Length as needed.
10. Long field names will cause a "invalid field" error when the import runs. Therefore, if you have "fields" longer than say 12 characters, truncate them. Update your field mappings back in your LotusScript LCField map. Verify the data types are compatible.


previous page