Schema Definition for Fixed Width Files

[dsusr]

Hello everyone,

We are profiling the Fixed Width Ascii files but even after defining the schema in the QETXT.INI Profile Stage is only reading the first field and rest other fields are takes as null.

Sample QETXT.INI file

[Defined Tables]
customers.txt=CUSTOMER
[CUSTOMER]
FILE=customers.txt
FLN=1
TT=Fixed
Charset=ANSI
DS=
FIELD1=CUSTOMER_ID,NUMERIC,3,0,3,0,
FIELD2=COUNTRY_CODE,NUMERIC,3,0,3,0,
FIELD3=CUSTOMER_NAME,VARCHAR,25,0,25,0,
FIELD4=VENDOR_NAME,VARCHAR,20,0,20,0,


customers.txt

+12+01Sample Customer Name 1 Vendor Name 1
+13+01Sample Customer Name 2 Vendor Name 2
+14+02Sample Customer Name 3 Vendor Name 3
+15+02Sample Customer Name 4 Vendor Name 4


Setup is Profile Stage on HP-UX and Files also on HP-UX. While testing the ODBC DSN for text file we ran the SQL query

SELECT * from CUSTOMER;

The output is

CUSTOMER_ID COUNTRY_CODE CUSTOMER_NAME VENDOR_NAME
12 NULL NULL NULL
13 NULL NULL NULL
14 NULL NULL NULL
15 NULL NULL NULL

I am not able to figure out whether there is some problem in the schema file or there is a problem with the Text File driver.

Can someone please guide me on this

Regards,
dsusr

[dsusr]

Problem has got resolved. The QETXT.INI for fixed width was getting created incorrectly using the Define tab of the Text driver on Windows.

For the above data file the QETXT.INI should have to be in the following format:-

[Defined Tables]
customer.txt=CUSTOMER
[CUSTOMER]
FILE=customer.txt
FLN=0
TT=FIXED
Charset=ANSI
DS=
FIELD1=CUSTOMER_ID,NUMERIC,3,0,3,0,
FIELD2=COUNTRY_CODE,NUMERIC,3,0,3,3,
FIELD3=CUSTOMER_NAME,VARCHAR,0,0,23,6,
FIELD4=VENDOR_NAME,VARCHAR,0,0,20,29,

Regards,
dsusr

[dsusr]

Although I have marked this topic resolved but I am re-opening this topic because I am facing a new issue with the Fixed-width file.

In my earlier scenario i was having the files which are fixed width but with end of line character after every record but now I am getting fixed width files without any end of line delimiter.

If i am trying to read the file using Text DSN it is just reading the first record.

Can someone please guide me to resolve this issue.

[ray.wurlod]

It's my understanding that line terminators are required. You'll need to pre-process your file - perhaps using DataStage - to add them.

[lstsaur]

Your file definition for "CUSTOMER" should be coded as
FLN=1
TT=Comma

[dsusr]

Your file definition for "CUSTOMER" should be coded as
FLN=1
TT=Comma

lstasaur,

lstsaur,

My file is Fixed length and not comma separated so I can't put 'TT=Comma'.

In regard to FLN it means whether the first line contain the column names or not. Value 1 indicates that the first line contain column names and 0 means vice versa.

Regards,
dsusr

[dsusr]

It's my understanding that line terminators are required. You'll need to pre-process your file - perhaps using DataStage - to add them. ...

Hi Ray,

You are aboslutely right. I have gone through the manuals of Data Direct drivers and have found the following information regarding Fixed Length files:-

'No character separates column values. Instead, values start at the same position and have the same length in each line. The values appear in
fixed columns if you display the file. Each line is a separate record.'

The above clearly states the kind of Fixed Length files which the Text driver can process.

Thanks everyone
dsusr

[dsusr]

The problem has been resolved, we can have the following option in the schema:-

TT=Stream

The above will work for all fixed length files which dont have any end of line delimiter.

TT=Fixed --- this option will work for the fixed length files with end of line delimeter.

[ray.wurlod]

Excellent research. Thank you for posting the answer.