Sequential file Loading 5Million Rows of Size 544 MB.

sh.bangash · Post by **sh.bangash** » Wed Sep 05, 2007 6:50 am

Hi All,
I tried to transform a sequential file of sizze 544 MB into another sequential file with different delimiter character; but the transformation returns after transforming only first row of the data file; can you please guide as what could be reason for it?

Regards,
Shahid.

chulett · Post by **chulett** » Wed Sep 05, 2007 7:09 am

Not with that much information to work with.

Would need a better idea of your job design and what errors or warnings you received. Can you do a 'View Data' on the input file?

sh.bangash · Post by **sh.bangash** » Wed Sep 05, 2007 7:22 am

Well, thats one of the files that I have to load daily to our Data Warehouse staging area.

Yes I tried to view the data; it first gives a warning and then shows only the first row of the data.

Job design is pretty simple; I used sequential file stage and transsfomer stage to write the same data to a another sequential file with different separator say '|' sign.

Would really appreciate the insight and elaborated description along with any idea on how to do it in some more suitable way.
Regards,

chulett · Post by **chulett** » Wed Sep 05, 2007 7:36 am

Your job design is fine, but I'd guess your metadata is wrong. I'm forced to guess because all you've told us is you "get a warning". It would be really swell if you'd share the actual warning with us.

And take that warning and try to figure out what is wrong with the way you've defined the file in the Sequential File stage.

gateleys · Post by **gateleys** » Wed Sep 05, 2007 7:47 am

chulett wrote:Your job design is fine, but I'd guess your metadata is wrong.

Or, your metadata does not synch with the data in your input file because of some corrupt format. Example, a string column with a comma is not enclosed in quotes.

As Craig suggested, its something to do with metadata, and I am certain the warning that you get (with 'View Data') is regarding missing column.

sh.bangash · Post by **sh.bangash** » Thu Sep 06, 2007 12:26 am

Hi,
Thank you for the valueable inputs.

This time I made it more simple by just dropping a sequential file stage and linking it to a transformer stage. I imported the table definition from the same source file which contains the actual data. it imported the definition perfectly, and showed the data in the lower grid that actually shows the source data, I scrolled it left right and could see the data in the file.

When I pressed 'View Data' button it showed me error message which says "DSBrowser...Sequential_File_0.DSLink03: Read_Delimited()- row 1, too many columns in record".
When I press 'Ok' button it shows me only first row of the data in the data brower grid.

Now, I could not understand what it actually means as there are 32 columns in the data file per record.

Thanks and Regards,
Shahid.

hamzaqk · Post by **hamzaqk** » Thu Sep 06, 2007 3:02 am

it means that the meta data you have defined for the sequential file does not match with the original file.

check if the file path is correct and you are loading the correct file.
also check if the metadata is for the file you are referring too.

these are usually the two causes for it.

sh.bangash · Post by **sh.bangash** » Thu Sep 06, 2007 3:46 am

Hi,

refferint to last thread details; I made sure that I used same file to import meta data definition into table definitions; then I used same file as source for the sequential file.
If the issue was with metadata, I wonder why ADS is displaying me only one record from the file after giving the error message.
I have manually browsed the file and verifeid that there are no broken or extra columns records. It is a fixed 26 columns file with '|' as separator.

Moreover, while importing the table definitions; it not only shows me contents of the file but allow me to scroll column by column and row by row.

Gurus, I need your expert openions and tricks here to resolve the issue.

Regards,

chulett · Post by **chulett** » Thu Sep 06, 2007 7:48 am

sh.bangash wrote:there are 32 columns in the data file per record

sh.bangash wrote:It is a fixed 26 columns file with '|' as separator

Which one is correct? And if it has a field separator then it's not a 'fixed' file but rather a delimited one.

There's no trick here. You simply have to manually verify the metadata you are using matches the actual file - number of columns, quote character used, field separator, etc. Right now it doesn't.

sh.bangash · Post by **sh.bangash** » Thu Sep 06, 2007 8:18 am

Hi,
sorry for mentioning 26 columns its acutally 32 and yes it not a fixed column instead its a '|' separated 32 columns file.

I verified the metadata, it exactly matches the column definition for the data file.

But, I did found the source of this error, while browsing the actual data, I recalled that I had ftped this file from Linux system where it is created daily, and assumed that ftp will automatically replace the LF charcter with CR + LF combination for the Windows machine that I am using as source.

I selected the Unix option on file format page of properties for data link and it allowed me to browse the data in file.

I did same for the previousely mentioned job and it completed successfully.

Thank you all experts for the tips and guidance that you provided.

Regards,

chulett · Post by **chulett** » Thu Sep 06, 2007 9:29 am

ASCII transfer = convert record terminators, BINARY = leave alone and transfer unchanged.

ray.wurlod · Post by **ray.wurlod** » Thu Sep 06, 2007 3:31 pm

There's only one "trick" with metadata - get it right.