Import from fixed length file

dxk9 · Post by **dxk9** » Sun Aug 30, 2009 11:59 pm

Hi,

I have a fixed lenght file(without delimiter), I want to load it into a table/file after some transformation. Can anybody tel me if there is a way to import fixed lenght or should I convert the file manually to delimited one and use it. I checked it in sequential file stage, but it says it requires a delimiter

Regards,
Divya

ArndW · Post by **ArndW** » Mon Aug 31, 2009 1:17 am

The sequential file stage can read and write fixed width files with or without delimiters. Just specify record length of "fixed" type file and delete the delimiter property.

dxk9 · Post by **dxk9** » Mon Aug 31, 2009 4:24 am

Thanks ArndW!!

I tried the settings you proposed, but I get the following error when I try to view the data.

<Sequential_File_0> Unable to initialize the importer.
<Sequential_File_0> "record_length=fixed" (no length given) and record field format is variable-length. The first variable-length field is "REC_TYPE".

"REC_TYPE" is my 1st field name.

Regards,
Divya

ArndW · Post by **ArndW** » Mon Aug 31, 2009 4:27 am

What data type is "REC_TYPE" - I would wager it is "VarChar" - which isn't a fixed length.

siauchun84 · Post by **siauchun84** » Mon Aug 31, 2009 10:06 am

You should use the datatype "Char" for all the columns that you defined fixed length to read you file and the length of each column must refer to the exact length of the records.

dxk9 · Post by **dxk9** » Wed Sep 02, 2009 10:07 pm

I changed all fields to Char of specific length as per the file. But still I get the same error.

<Sequential_File_0> Unable to initialize the importer.
<Sequential_File_0> "record_length=fixed" (no length given) and record field format is variable-length. The first variable-length field is "REC_TYPE".

Regards,
Divya

ray.wurlod · Post by **ray.wurlod** » Thu Sep 03, 2009 12:58 am

Have you specified a length property for every field?

dxk9 · Post by **dxk9** » Thu Sep 03, 2009 2:36 am

Yes.. I specified the length for all the fields.

Regards,
Divya

laknar · Post by **laknar** » Thu Sep 03, 2009 3:50 am

Go to the columns tab and right click the column properties go to edit row specify the field length for each column. you can give the start position. after setting the property run the job again

dxk9 · Post by **dxk9** » Thu Sep 03, 2009 4:18 am

It works!! Thanks all for your timely help!!!

Now I have another issue

I have a file containing different row types. Each row type has different number of columns of fixed length.

Eg:
Header
AAA field1(4) field2(11) field3(6) field4(7) field5(14)
BBB field1(9) field2(1) field3(16) field4(6) field5(4) field6(10) field7(11) field8(6) field9(2) field10(14)
CCC field1(7) field2(23)
-----------------
-------------
Footer

I need to import this entire file and filter each row type to specific destination.

I first imported the entire row as one single field and then used different transformers to send it to different targets. I used string functions in each transformer depending on the target to get that row type separately. But this becomes very complex when there are hundreds of fields in a row type.

Is there any better way to do this??

Regards,
Divya

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Sep 03, 2009 4:42 am

Isn't that what Complex Flat File stage is for ?

dxk9 · Post by **dxk9** » Thu Sep 03, 2009 5:05 am

I havn't used complex flat file before. I tried using that with some online guidance, but its not working. I did not get much info on dsxchange also about the same. So I started exploring with sequential stage itself.

Regards,
Divya

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Sep 03, 2009 5:27 am

Do you need data from all record formats or only selected ?

dxk9 · Post by **dxk9** » Thu Sep 03, 2009 5:29 am

Selected ones..

Regards,
Divya

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Sep 03, 2009 7:49 am

In that case, you can use sequential file with 'grep' as filter.