Issue with Flat file

[nupur1811]

Hi,
I am having a unique issue with loading a flat file to informatica. I am getting the data from flat file and oracle table and joining them based on 1 field as store_ID.
The flat have more than 5000 records. Problem is after every 385 records store_id from flat file comes as null. I deleted the first 385 records to check if for all the other records Store_is is coming NULL, but again 385 records have Store_ID as not null rest all the recors have Store_IS as NULL.
Has anyone encountered this problem before? Please advise.

 

[blom0344]

You mean that your input flatfile is missing the store_id after 385 rows? Isn't that not just a question of having incomplete data?

Ties Blom

 

[ArtieChoke]

By any chance does the flat file have a blank line or a header line every once in awhile? Have you reviewed the flat file in a text editor?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[nupur1811]

There are no header or blank ;line in the file.
I am ble to preview data correctly by placing the flat file on my locaql machine

 

[blom0344]

Use a real text-editor (like ultra-edit) to scan the flatfile for ominous components. What do you use for separation sign?

Ties Blom

 

[nupur1811]

This file is fixed length file.
But my question is say the data file has blank store_ids, then they should be consistently blanks, but if i divide my file to 385 records the same data has not null value for store id.

 

[blom0344]

but if i divide my file to 385 records the same data has not null value for store id.

Can you give an example? Doesn't make any sense to me

Ties Blom

 

[nupur1811]

Say file has 1000 records and for all the records data is populated and Oracle table has match for all the records.
Then ran my mapping in verbise data mode and in the joinner i can see for 385 records store id is matching with orcla store id. Rest all the records are filtered out as the store_id from file is NULL.

But the data in the flat file is not null for any row if i open the file in TextPad.

After that I deleted first 385 rows from the file for which there was match with oracle table and was expecting all the records will be filter out in joiner as in the first pass only 385 records had the matching Store_ID but again 385 records out of 615 records had the matching records in theorcle and rest were filtered out.

Also If i run the mapping 3 times by dividing the file into three part for rows 385,385 and 230 ... all the rows are successfully passing through the joiner.

 

[rasETL]

Working with a flat file while joining it to a relational table can have problems on occasion.

Make sure the data in the flat file is sorted in proper order because there is no "Database Engine" running the flat file - that may be the issue.

Also, you could try loading the flat file to a temporary table before joining -that may correct the problem.

 

[srvu]

When you are working with fixed width flat file check for the line endings.
If your line endings are DOS line endings then add an extra column CRLF with precision 1 to your flat file source and to Source Qualifier and donn pass CRLF further.

I had a similar problem when working with fixed width flat file. And it was resolved by adding CRLF..

Adding CRLF reads records correctly for fixed width files