Two differnt delimitters in one file

[jyothisdasms]

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

[Sreenivasulu]

1.First job with one delimeter
2.Second job with another delimiter
3.Append the two files

[jyothisdasms]

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

[chulett]

Pick one and then "manually" build the section of the file with the other delimiter as a single varchar field.

[DSguru2B]

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.

[ray.wurlod]

You could even do that within one job. Just ignore the word "file" in what DSGuru2B suggested.

[svhari76]

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.

[chulett]

... create, not read.

[jyothisdasms]

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 ...

Thanks for the reply DSguru2B,

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

[DSguru2B]

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.

[chulett]

... much like I posted.

[jyothisdasms]

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. ...

Hi,

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