Transforming a delimited file into a non delimited file

jshurak · Post by **jshurak** » Fri Jul 14, 2006 6:16 am

I need to take a delimited file with four columns and transform it into a nondelimited, one field file. Any ideas?

loveojha2 · Post by **loveojha2** » Fri Jul 14, 2006 6:20 am

Read the Delimited Sequential File.
Concate the columns in the transformer using : (colon) operator and write into the one Field on the target file. Pretty Straight, right?
Or you have anything else.

DSguru2B · Post by **DSguru2B** » Fri Jul 14, 2006 6:30 am

Or Read your file and load it to another sequential file with delimiter and quote character set to 000. Just a straight load.

kumar_s · Post by **kumar_s** » Fri Jul 14, 2006 6:30 am

Give the output delimiter as none (000).

chulett · Post by **chulett** » Fri Jul 14, 2006 7:03 am

A 'nondelimited one field file' could also be generated as a 'Fixed Width' file simply by checking that option. Same thing. You'll still need to set the quote character to '000'.

Or as noted, specifically concatenate all fields into one big one.

DSguru2B · Post by **DSguru2B** » Fri Jul 14, 2006 7:15 am

A non delimited one field file, yes.
A fixed width file, i am not too sure about that.
If thats what the OP wants then he has to make sure that if a field is defined as length 10 and an incoming value has only 8 bytes, he needs to cat 2 extra spaces to it to make it a truely fixed width file. A little bit more effor for a fixed width file than just setting the delimiter to '000'

chulett · Post by **chulett** » Fri Jul 14, 2006 7:30 am

DSguru2B wrote:A non delimited one field file, yes.
A fixed width file, i am not too sure about that.
If thats what the OP wants then he has to make sure that if a field is defined as length 10 and an incoming value has only 8 bytes, he needs to cat 2 extra spaces to it to make it a truely fixed width file. A little bit more effor for a fixed width file than just setting the delimiter to '000'

No, not necessarily... a fixed width file can 'fix' things itself just fine. Using 'char' for the character types helps tremendously, for example...

Give it a shot before you pass judgement, it can be as simple as checking the option.

DSguru2B · Post by **DSguru2B** » Fri Jul 14, 2006 7:41 am

Actually you are right. Sorry, i am still learning

Thats why i said "I am not too sure"

chulett · Post by **chulett** » Fri Jul 14, 2006 7:47 am

It really depends on what the OP needs. If the fields can be strung together willy-nilly, each piece picking up where the previous piece ends, then all this 'concatenation' advice is fine.

If the fields need to be of a certain consistant size, starting and ending in consistant positions with no delimiters between them, that is by definition a fixed width file. And if you build one by padding and concatenating the fields yourself, you are taking the wrong approach.

DSguru2B · Post by **DSguru2B** » Fri Jul 14, 2006 7:50 am

Very true. I, just wasnt sure, if datastage padds spaces to honor the length on its own just by specifying the sql type as 'char'. Thats why my concern to do it explicitly.
But as i told you before, your officially my mentor.

Learn something new from you every day.

kcbland · Post by **kcbland** » Fri Jul 14, 2006 8:04 am

You can specify how to pad, either globally or on an individual field level. Some you may want space filled, others zero filled. It's all in there...