-
Daniel Vivier
- PowerBuilder
- Sunday, 2 May 2021 09:40 PM UTC
I need to read some data from tab-separated text files with a row of column headers, where I know that certain column names will always be there, but don't know whether they will always be in the same order, or what other columns might be there. So loading them into a pre-built DataStore didn't seem like it would work.
Of course I could just read the file directly, and split each line into an array, at tabs. Search in the first row for the desired column names, and note their indexes in the array. Then access those columns at those array indexes in the subsequent rows.
But wouldn't it be nice to be able to use DataStores more directly? So here's my solution, using the Microsoft Text Driver ODBC driver. (This is actually for a bunch of files in one directory, exported from another application.)
First, create a Transaction object sqlText, and set it up and connect like this:
sqlText.DBMS = "ODBC"
sqlText.DBPARM = "ConnectString='Driver={Microsoft Text Driver (*.txt; *.csv)};&
Dbq=DriveAndDirectory\;&
Extensions=asc,csv,tab,txt'"
CONNECT USING sqlText;
You will replace DriveAndDirectory with the actual directory the files are in. Then create a SQL statement for reading the desired columns from one of the tables in that directory, and create a runtime datastore for it the normal way, using SyntaxFromSql and Create using that syntax.
Then you can just SetTransObject using the transaction object on the datastore, and Retrieve!
However, there's one big issue: this will not work unless you have a schema.ini file with at least minimal contents in the directory where the data files are. Specs for that file are at https://docs.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver?view=sql-server-ver15
You can actually have the ODBC Administrator application create a working version of the schema.ini file for you by creating a System Data Source using that Microsoft Text Driver on that directory. However, while that can (optionally) put in column definitions for the tables, they will always assume that all columns are char(255). So you may want to hand-edit that based on your understanding of the data - at least for the columns you are interested in!
I hope this might be helpful to others with a similar requirement.
Find Questions by Tag
Helpful?
If a reply or comment is helpful for you, please don’t hesitate to click the Helpful button. This action is further confirmation of their invaluable contribution to the Appeon Community.