Two differnt delimitters in one file
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 33
- Joined: Wed May 19, 2010 12:15 am
- Location: Pune
Two differnt delimitters in one file
Hi everyone,
I have a requirement in which we needed the output file in the following format
"OPEN,20101027,160000,CUST_KEY_1,2007-05-05,Open,Y,P,,,,,2007-07-25,NON-EXEMPT CUST. VALID W-9 ,,,,,,,,,|NOT|TANNER|N||||SCHAPMAN|HOME TELEPHONE|5135348500^|NOT|EMAIL|EMAIL|NOT|1234||^|NOT|DRIVERS LICENSEOH|2012-12-28,,,,,,"
Now is it possible to make a file with two different delimitters?
Which stage we have to use for this?
Thanks in Advance
I have a requirement in which we needed the output file in the following format
"OPEN,20101027,160000,CUST_KEY_1,2007-05-05,Open,Y,P,,,,,2007-07-25,NON-EXEMPT CUST. VALID W-9 ,,,,,,,,,|NOT|TANNER|N||||SCHAPMAN|HOME TELEPHONE|5135348500^|NOT|EMAIL|EMAIL|NOT|1234||^|NOT|DRIVERS LICENSEOH|2012-12-28,,,,,,"
Now is it possible to make a file with two different delimitters?
Which stage we have to use for this?
Thanks in Advance
" Dream like you will live forever, live like you will die today."
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
-
- Participant
- Posts: 33
- Joined: Wed May 19, 2010 12:15 am
- Location: Pune
Thanks for your reply.But if i am doing that i will get output as
Ab,cd,ef,gh
Jk,lk,mk,op
de,ki,po,lk
Ab2|cd2|ef2
Jk2|lk2|mk2
de2|ki2|po2
But i need the output as
Ab,cd,ef,gh Ab2|cd2|ef2
Jk,lk,mk,op Jk2|lk2|mk2
de,ki,po,lk de2|ki2|po2
Thanks
Jyothisdas
Ab,cd,ef,gh
Jk,lk,mk,op
de,ki,po,lk
Ab2|cd2|ef2
Jk2|lk2|mk2
de2|ki2|po2
But i need the output as
Ab,cd,ef,gh Ab2|cd2|ef2
Jk,lk,mk,op Jk2|lk2|mk2
de,ki,po,lk de2|ki2|po2
Thanks
Jyothisdas
" Dream like you will live forever, live like you will die today."
One job creates two files, one with comma and the second with pipe; with an extra column that has a running number. Use a second job to join the two files on the running number you created in the first job and create the final file that will give you your desired result.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Ok Do like this.
Procedure 1:
1)Job1 - read the file with comma(,) delimitor, in the transformer pick all the fields except | separated column(remember all pipe separated field will be read into single column here, so make sure to give enough length for this field) write these field to one file -file1.
In the same job from transformer use second link to write to another file-file2.
2) Job 2 - read the file 2 with pipe separated and read the data and write to file (file3)with comma separated
3) Job 3 - use file1,file 3 as source and read each record in single line and concatinate them in transformer.(don't forget to concat extra pipe between these 2 records )Write to another file(file4)
your order of the fields may be different but you got the file with single delimitor.
May be this is long procedure, but should work.
Procedure 2:
1)read all comma separated columns so that you will get all pipe sepaarted strign in 1 field and use Convert function replace the | with comma on that field and rewrite all the fields back to a file.
Procedure 1:
1)Job1 - read the file with comma(,) delimitor, in the transformer pick all the fields except | separated column(remember all pipe separated field will be read into single column here, so make sure to give enough length for this field) write these field to one file -file1.
In the same job from transformer use second link to write to another file-file2.
2) Job 2 - read the file 2 with pipe separated and read the data and write to file (file3)with comma separated
3) Job 3 - use file1,file 3 as source and read each record in single line and concatinate them in transformer.(don't forget to concat extra pipe between these 2 records )Write to another file(file4)
your order of the fields may be different but you got the file with single delimitor.
May be this is long procedure, but should work.
Procedure 2:
1)read all comma separated columns so that you will get all pipe sepaarted strign in 1 field and use Convert function replace the | with comma on that field and rewrite all the fields back to a file.
Hari
-
- Participant
- Posts: 33
- Joined: Wed May 19, 2010 12:15 am
- Location: Pune
Thanks for the reply DSguru2B,DSguru2B wrote:One job creates two files, one with comma and the second with pipe; with an extra column that has a running number. Use a second job to join the two files on the running number you created in the firs ...
But i am unable to see the full message.
I assume that what you are trying to say that to join the files based on the common key.But after that we have to give a target file where we can mention only one delimiter.So i think in output we will get a file with only one delimiter.Correct me if i am wrong.
Thanks
" Dream like you will live forever, live like you will die today."
-
- Participant
- Posts: 33
- Joined: Wed May 19, 2010 12:15 am
- Location: Pune
Hi,DSguru2B wrote:Yes on the common key. Yes on the output for a single delimiter (comma). But your nth column will be the entire row from the second file which is pipe delimited. ...
Thanks for the reply.I think you are trying to say,we have to add all the fields from second file into last field and concatenate those fields with a pipe in between.
Please correct me if i am wrong
" Dream like you will live forever, live like you will die today."