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
Answers were Sorted based on User's Feedback
Answer / 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 |
Answer / dj
ou can do this by executing a pre session unix command. Replace consecutive delimiters with some Junk value and later in informatica mapping replace it with null.
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / naveen
By using replace char function we can write this
Suppose
length(replacechr(c3,'1234567890abcdefghtijklmnopqrstuvwzyz',null))
Is This Answer Correct ? | 0 Yes | 1 No |
In any project how many mappings they will use(minimum)?
When to use normalization transformation other than cobol source?
what is filelist concept in informatica
What exactly mentioned in High level design document.can somebody explain me sequentialy.
How might you approve all mappings in the archive all the while?
Source and Target are flat files, Source table is as below ID,NAME 1,X 1,X 2,Y 2,Y On Target flat file i want the data to be loaded as mentioned below ID,NAME,REPEAT 1,X,2 1,X,2 2,Y,2 2,Y,2 How to achieve this, Can i get a map structure
what is $$$$?
WHAT IS USE OF SQL OVERRIDE IN SOURCE QUALIFIER? chandumba2005@gmai.com
i have flatfile source. i want to load the maximum salary of each deptno into target. what isthe mapping flow
What is the meaning of up gradation of repository?
HAI FRIENDS THIS KISHORE FROM KUMBAKONAM. I HAVE COMPLETED MY MCA IN SASTRA UNIVERSITY WAITING FOR MY 6 TH SEM RESULT.DURING MY PROJECT I HAVE DONE INFORMATICA COURSE AS MY AREA INTEREST COURSE IN CORE MIND TECHNOLOGIES CHENNAI.I HAVE ENQUIRY ABT ALL IT PEOPLE FOR DATAWAREHOUSING THERE IS NO OPENING FOR FRESHERS. WITH SOME EXPERIENCE ANY OTHER DOMAIN TNEN ONLY U CAN GET THAT DOMAIN(DATAWAREHOUSING) JOB.BUT I AM MORE INTEREST ON THAT TO WORK ON THAT DOMAIN(INFORMATICA).WHAT TO I DO. I AM IN HELPLESS FRIENDS/EXPORTS.WAITING FOR REPLY
how can we convert a column into row in informatica? Eg: INPUT- a x Output Like: a b c b y x y z c z