QETXT.INI File Format for a Delimited File

[binay]

Hello,

I'm using Information Analyzer to profile columns. Now, I'm using a "|" delimted file as input and defining the QETXT.INI file as follows:

[Defined Tables]
table1.dat=TABLE1
[TABLE1]
FILE=table1.dat
FLN=0
TT=Comma
Charset=ANSI
DC=|
FIELD1=ADDRESS1,VARCHAR,90,0,90,0,
FIELD2=CITY,VARCHAR,25,0,25,0,


This is what I'm assumming in the QETXT.INI File format:

table1.dat = Flat File Name
TABLE1 = Table to be created in Information Analyzer
FLN = 0 means that first line of the flat file doesn't contain column names
TT = I'm not very sure about this (Assuming that the field specifics like field name, field type, precision, scale, length, offset are separated by commans). For example, FIELD1=ADDRESS1,VARCHAR,90,0,90,0,

DC = Delimiter character in the flat file
FIELD1 and FIELD2 are the field names in the table

When I use this file to do the run the column analysis and view the results, I mostly get all the records in the first column itself. As a result, I can see the all the field values, delimiter character etc in the first field itself.

I'm pretty sure that it has something to do with my understanding of the QETXT.INI file and the way I'm defining it.

Can someone please help me understand its format so that I can correct my file and re-run the column analysis? Thanks a lot in advance for your help.

Binay

[sjfearnside]

Here is some info I found.

Define the QETXT.INI file as follows:

Create a [Defined Tables] section and list all of the tables you are defining. Specify the text file name (in either upper or lowercase, depending on the file) followed by the name you want to give the table, for example:
emptext.txt=EMP

Table names can be up to 32 characters in length and cannot be the same as another defined table in the database. This name is returned by SQLTables. By default, it is the file name without its extension.

For each table listed in the [Defined Tables] section, you must specify the text file (FILE=), the table type (TT=), whether the first line of the file contains column names (FLN=), and the delimiter character (DC=).
Specify the text file name. For example:
FILE=emptext.txt

To define the table type, specify how the fields are separated (comma, tab, fixed, or character). For example:
TT=COMMA

If the table type is CHARACTER, specify the delimiter character. The value can be any printable character except single and double quotes. For example, if the fields are separated by comma:
DC=,

Specify whether the first line of the file contains column names, using 1 for yes and 0 for no. For example:
FLN=0

Define the fields in the table, beginning with FIELD1. For each field, specify the field name, field type, precision, scale, length, offset (for fixed tables), and date/time mask. See "Date Masks" for information about masks.
Separate the values with commas. For example, to define two fields:

FIELD1=EMP_ID,VARCHAR,6,0,6,0,

FIELD2=HIRE_DATE,DATE,10,0,10,0,m/d/yy

Save the file as QETXT.INI. The driver looks for this file in the directory specified by the Database attribute in odbc.ini, or in the current directory.

Also I received this from my support provider:

Here are the instructions for creating the file

1. Launch ODBC Data Sources Administrator
2. If you have already defined a System DSN for the connection, select it and click on configure,
otherwise, define a System DSN for using the Text file driver.
3. Ensure all information is correct on the General tab
4. Click the Advanced tab, and then click Define...
5. Browse to the file and select it, and then click on Open
6. You will see the definition

Hope this helps.

[binay]

Thanks a lot!

But the format you gave me, isn't it precisely for a fixed width file? I'm also, using the same format, something like this:

Defined Tables]
table1.dat=TABLE1
[TABLE1]
FILE=table1.dat
FLN=0
TT=Comma
Charset=ANSI
DC=|
FIELD1=ADDRESS1,VARCHAR,90,0,90,0,
FIELD2=CITY,VARCHAR,25,0,25,0,


But the flat file table1.dat is a pipe delimited ("|") file. I'm not sure it is required to define the precision and offset of a delimited file. Infact, I was trying the same way (similar format) you gave me, but it doesn't seem to work. Do you know any possible way to define the QETXT.INI file for a delimited file? Thanks again for your help.

[sjfearnside]

My guess would be the TT definition is not right. Your fields are separated by a | (pipe) and not a comma. Try TT=Character and DC =|.

[binay]

Thanks a lot! I changed it to Character and it worked fine. Thanks again....

[binay]

Thanks a lot! I changed it to Character and it worked fine. Thanks again....

[wendyxu_2009]

Here is some info I found.

Define the QETXT.INI file as follows:


Define the fields in the table, beginning with FIELD1. For each field, specify the field name, field type, precision, scale, length, offset (for fixed tables), and date/time mask. See "Date Masks" for information about masks.
Separate the values with commas. For example, to define two fields:

FIELD1=EMP_ID,VARCHAR,6,0,6,0,

FIELD2=HIRE_DATE,DATE,10,0,10,0,m/d/yy

Save the file as QETXT.INI. The driver looks for this file in the directory specified by the Database attribute in odbc.ini, or in the current directory.

Dose anyone have problems changing the column name? I used the wizard to import metadata from flat files, everything works fine if I leave the column name as default (e.g. 'column1') and I can run column analysis on the file with no problem. But I lost data once change the column name. Does anyone know what the problem is?

[wendyxu_2009]

Here is some info I found.

Define the QETXT.INI file as follows:


Define the fields in the table, beginning with FIELD1. For each field, specify the field name, field type, precision, scale, length, offset (for fixed tables), and date/time mask. See "Date Masks" for information about masks.
Separate the values with commas. For example, to define two fields:

FIELD1=EMP_ID,VARCHAR,6,0,6,0,

FIELD2=HIRE_DATE,DATE,10,0,10,0,m/d/yy

Save the file as QETXT.INI. The driver looks for this file in the directory specified by the Database attribute in odbc.ini, or in the current directory.

Dose anyone have problems changing the column name? I used the wizard to import metadata from flat files, everything works fine if I leave the column name as default (e.g. 'column1') and I can run column analysis on the file with no problem. But I lost data once change the column name. Does anyone know what the problem is?