Suppose we have a csv file with contents as below which is
used has a source file.
C1,C2,C3,C4
100,200,A B,300
200,400,X,Y,299 ---> it should be 4 values but by mistake
300,600,C D,566 with a comma in between X & Y,its 5.
My target takes only 4 columns, How to take care of the 2
record as above without rejecting it and loading in the target.
Hope u guys have understood my scenerio
- 4 Answers
- 7832 Views
- IBM, I also Faced
- E-Mail Answers
Answer Posted / abhinav
I am not sure if the solution I am going to post will
actually work but it is worth a shot:
1. Don't define a delimiter so that the records are read
as '100,200,A B,300' and '200,400,X,Y,299' and so on i.e.
as a single record. This is to ensure that all the records
pass the source-qualifier.
2. Now you have all the records including the faulty one in
SQ. Next, you put an expression where you use REPLACECHAR
to remove all the commas from all the records. Now ALL your
records will of the same length.
3. Next you can use SUBSTR to read the first 3 numbers to
get the first column, next 3 to get the second column and
so on and so forth to get proper data in proper columns.
My explanation might not be clear but I have a hunch that
this might work.
| Is This Answer Correct ? | 5 Yes | 4 No |
Post New Answer View All Answers
How to create Target definition for flat files?
What is the difference between stop and abort in informatica
Explain how to import oracle sequence into informatica?
How do you load first and last records into target table?
Explain the features of connected and unconnected lookup.
What is meant by lookup transformation?
is it possible to index the data in cache of lkp transformation ?
Where can we find the throughput option in informatica?
I have a flat file, want to reverse the contents of the flat file
What is the different lookup cache(s)?
Which files are created during the session rums by informatics server?
How can you increase the performance in joiner transformation?
Which are the different editions of informatica powercenter that are available?
in u flatfile some of fact records are missed then u load the diminision records are not if u load diminsion records what about fact table records
What is InformaticaPowerCenter?
