Using Text File Driver (ODBC) with RPG2SQL - Support

Using Text File Driver (ODBC) with RPG2SQL

From Support

Jump to: navigation, search

Contents

[edit]

Why Use the Text File Driver?

Much information is stored in delimited or fixed-length text files, such as CSV (comma-separated variable) or tab-delimited files. Usually these can be handled using Excel functions. But sometimes the data is not consistent from row to row, or Excel is too "helpful" and interprets data as dates or times when they should be plain text. E.g., 10:09 will be treated as a time by Excel, 89:09 will not.

The MS Text File Driver (ODBC) driver can be used to process this kind of information. This article shows one way to use it. It also presents some of the difficulties with using the Text File Driver.

[edit]

Necessary Things

In order 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. This file can be created manually by using any text editor, by using the ODBC wizard, or programmatically. We will use the ODBC wizard to start out, then make some additional edits as needed.

[edit]

Creating a DSN for Text Files

In XP, at least, go to Start->Settings->Control Panel->Administrative Tools->Data Sources (ODBC) or Start->Settings->->Administrative Tools->Data Sources (ODBC). Then follow the steps presented in the following windows:

ODBC Data Source Administrator
ODBC Data Source Administrator
Select the System DSN tab and click Add...
Select the System DSN tab and click Add...
Create New Data Source
Create New Data Source
Select the Microsoft Text Driver (*.txt; *.csv) and click Finish
Select the Microsoft Text Driver (*.txt; *.csv) and click Finish
ODBC Text Setup
ODBC Text Setup
Enter a Data Source Name, a Description, and uncheck Use Current Directory; then click Select Directory...
Enter a Data Source Name, a Description, and uncheck Use Current Directory; then click Select Directory...
Select Directory
Select Directory
In the Folders list, double-click the directory you want; then click OK
In the Folders list, double-click the directory you want; then click OK
Click Options>>
Click Options>>
ODBC Text Setup with options; click Define Format...
ODBC Text Setup with options; click Define Format...
Define Text Format
Define Text Format
Select nameaddr.csv
Select nameaddr.csv
Check Column Name Header and click Guess; keep clicking OK until you are back to the ODBC Data Source Administrator
Check Column Name Header and click Guess; keep clicking OK until you are back to the ODBC Data Source Administrator
Open schema.ini in a text editor (TextPad in this case); there will be entries for all files in the selected directory
Open schema.ini in a text editor (TextPad in this case); there will be entries for all files in the selected directory
Delete everything except the [nameaddr.csv] entry; change the data types--in this case, everything is character with appropriate lengths; then save schema.ini
Delete everything except the [nameaddr.csv] entry; change the data types--in this case, everything is character with appropriate lengths; then save schema.ini
[edit]

The RPG2SQL connection string 

Rtn = SQL_DBOpenConn(SQL_Socket : 'DSN=NameAddr;');

There are other options that can be used in the connection string—see Setting Options Programmatically for the Text File Driver

[edit]

The RPG2SQL SELECT Statement 

Rtn = SQL_RunSQLSel(SQL_Socket: 'SELECT * FROM nameaddr.csv');
It seems that, when you use a default directory, you should not qualify the file name.


Now you can continue with the rest of your RPG2SQL code, as for any other SQL resource.

[edit]

Other Resources

Retrieved from "http://wiki.rjssoftware.com/wiki/index.php/Using_Text_File_Driver_%28ODBC%29_with_RPG2SQL"
Personal tools