A lot information is stored in delimited or fixed-length text files, such as CSV (comma-separated variable) or tab-delimited files. Usually Microsoft Excel functions can handle these. But sometimes the data is not consistent from row to row, or Excel misinterprets data as dates or times when they should be plain text. For example: Excel treats 10:09 as a time, but not 89:09.
You can use Microsoft Text File Driver (ODBC) to process this kind of information. This article shows you one way to use it and also some of the difficulties with using Text File Driver.
Creating a DSN for Text Files
To use the Text File Driver with a file, there has to be an information file in the same directory as the text file. The name is always schema.ini. You can create this file can manually with any text editor, with the ODBC wizard, or programmatically. This article shows you how to use the ODBC wizard to start out and then make additional edits, as needed.
- Go to Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC).
ODBC Data Source Administator opens.
- On the System DSN tab, click Add.
Create a New Data Source displays.
- Choose Microsoft Text Driver (*.txt; *.csv).
- Click Finish.
ODBC Text Setup displays.
- In ODBC Text Setup:
- Type a Data Source Name.
- Type a Description.
- Uncheck Use Current Directory.
- Click Select Directory.
- In Folders, double-click the directory you want.
- Click OK.
- Back in ODBC Text Setup, click Options>>.
ODBC Text Setup expands to display more options.
- Click Define Format.
Define Text Format displays.
- Choose nameaddr.csv.
- Check Column Name Header and click Guess.
A list of columns displays.
- Click OK as many times as you need to until you are back to ODBC Data Source Administrator.
- Open schema.ini in a text editor such as NotePad or TextPad.
Entries for all files in the selected directory display.
- Edit schema.ini as follows:
- Delete everything except the [nameaddr.csv] entry.
- Change the data types.
For example: Make everything a character with appropriate lengths.
- Save schema.ini.
RPG2SQL Connection String
Rtn = SQL_DBOpenConn(SQL_Socket : 'DSN=NameAddr;');
RPG2SQL SELECT Statement
Rtn = SQL_RunSQLSel(SQL_Socket: 'SELECT * FROM nameaddr.csv');
Note: When you use a default directory, do not qualify the file name.
Now you can continue with the rest of your RPG2SQL code, as with any other SQL resource.
Other Resources
