Columns are not loading properly from Flat File (Pipe Delimiter)

[Former Member]

Hi All,

I have a Flat File (Pipe Delimiter) contains 28 Columns.

Input File contains 200000 Records. All records loaded properly except one record.

For that record, first 6 columns are not loaded. 7th column from Flat file started populating into 1st Column in Template Table (Target Table) and So On.

Note:

    1) For Previous record, Last 6 columns are NULLs.

    2) If I load this one record Individually, its being loaded correctly

Could you please let me know how to load this row properly along with huge file.

Regards.

[Former Member]

Can we say it is Data Issue.

But the record which is prior to failed record is loaded successfully and these two records are similar in format.

I have asked my client to generate Excel format. They said, they can not generate Excel format.

Please do let me know if you have any other alternatives.

Regards.

[Former Member]

Do you have access to DB? If so can you try loading directly to DB instead of DS? Check if thats throwing an error?

[Former Member]

Hi Arun,

My Client machine do not allow other websites. So I can not attach the screen shot.

I am sorry for that.

Regards.

[Former Member]

Hi Arun,

I tried to load same data through Excel. All data loaded with out any Issues. I am still breaking my head whats wrong with Flat File.

Regards.

[Former Member]

Hi,

Can you attach the records that are throwing the error? I need some records before row number <15386> and some after.

Arun

[Former Member]

I think this is regarding your earlier post.

When you are loading from flat files, these are bound to happen unless the file is formatted well. The error you have is a delimiter issue because of NULL values in the 6th column or there is no 7th column for that record. When you run DS job it will throw an error with the row number. To fix this do the following -

1) Open flat file in excel or Notepad++

2) Go to row number which is displayed in DS.

3) Keep your cursor on the 6th column and see whats going on.

4) Either fix yourself manually or inform the guys who are generating flat file for you.

[Former Member]

Hi,

There is nothing wrong in data.

I think its due to No of records.

I deleted error record and tried to process the data. Still Issue exists.

I deleted another 10 records. Still Issue exists.

I deleted all records from that error record. Now all records processed.

[Former Member]

Its definitely not due to no of records, 200,000 is not a big number. How is your data type defined? All varchars?Decimals?. I would suggest to convert all the field types to varchars and load it.

[Former Member]

Hi,

I have converted all the fields to VARCHAR255. But still Issue exists.

Error is "A row delimiter was seen for row number <15386> while processing column number <24> in file"

I have examined the file and could not able to find any data Issue.

My Flat file contains 27 Columns. Last 12 columns are storing currency. First 3 columns storing Korean Names.

Regards.

[former_member198401]

Can you ask the team to sent the flat file again

It might be tedious to find the issue withe the data in it

Regards

Arun Sasi

[former_member187605]

Keep all output fields as varchar and set Adaptable Schema to Yes. Your job won't run into error and you'll be able to examine the contents of the culprit record in output. That might shed a light on your issue.