File Data in Table Column

highpoint · Post by **highpoint** » Thu Jun 02, 2011 10:54 am

Hi,

Trying to Accomplish : To load a complete sequential file into one column of table.

For this i am using Folder stage to read the file, load the entire file in to the "RECORD" column of the
sequential file.

I have used the option of Preserve CRLF as "NO". Since i want this as one column going into one column of table.

I was able to load it in table. But all the records are appended to each other and is not in readable format.

When i see data in table. Between each record there is a small 'p' kind of thing.

I would like to put back the new line characters in place of small 'p'.

when i do octal dump i see a ascii value of "376". Dont know what it represents.

i would like to convert 'p' to newline character. i think new line character is char(13).

Would appreciate help on accomplishing proper formatted data of file in table column.

chulett · Post by **chulett** » Thu Jun 02, 2011 1:06 pm

What happens when you set 'Preserve' to yes?

chulett · Post by **chulett** » Thu Jun 02, 2011 1:11 pm

Also note I changed your Job Type from Parallel to Server as there is no Folder stage on the PX side. I also deleted your duplicate post in the PX forum, please don't post the same question multiple times.

highpoint · Post by **highpoint** » Thu Jun 02, 2011 2:49 pm

chulett wrote:What happens when you set 'Preserve' to yes?

When i set preserve to Yes. The next job reading this file thinks as multiple rows because the column has new line character in it.

Also my job creating the file is server job. But the job reading the created file is parallel, so earlier i posted this in both the forums.As issue may be resolved by tweaking any one of these 2 jobs.

highpoint · Post by **highpoint** » Thu Jun 02, 2011 5:49 pm

Gurus help is appreciated on this.

chulett · Post by **chulett** » Thu Jun 02, 2011 7:49 pm

Patience, Grasshopper. And learn to use the Reply to topic link rather than quoting everything every time for no reason.

chulett · Post by **chulett** » Fri Jun 03, 2011 7:26 am

So... why two jobs? I made the silly assumption that you were doing everything all at once (hence my suggestion), don't really see the point of reading the file using the Folder stage only to write it back out again to yet another sequential file.

Any particular reason your first job can't do the loading as well? If you really need this in two parts, simply use Convert() to change the offending character to whatever you like just before you load it.

highpoint · Post by **highpoint** » Fri Jun 03, 2011 7:59 am

I can use the convert() function. But i don't know what is the offending character.
on Unix when i do octal dumb "od -bc" for that character i get a value of 376.

Help is appreciated to convert this offending character to new line character.

chulett · Post by **chulett** » Fri Jun 03, 2011 8:08 am

While Ray, being a Founding Father and all, may just know what the character is mere mortals like you and I need to figure it out... and it seems you already have. Your problem is you are dumping the value in octal, convert it to or dump it in decimal and then use that value along with Char() when you Convert.

ray.wurlod · Post by **ray.wurlod** » Fri Jun 03, 2011 3:57 pm

Octal 376 is decimal 254. This is the "field mark" character which can be referenced via the Char() function but, to avoid recalculating the Char() function unnecessarily, also through the @FM system variable. This is the character to which line terminators are converted when reading.

highpoint · Post by **highpoint** » Sun Jun 05, 2011 12:42 pm

So, what i understand is

if i use

Convert (@FM,char(13),column_name) --> Recommended
Convert (char(254), char(13), column_name) --> Not recommened

I shall be able to convert "Field Mark" to New Line Character.

I dont have access to Datastage right now.

Please correct me if i am missing something.

And also will i be able to use @FM in a parallel job Transform?

ray.wurlod · Post by **ray.wurlod** » Sun Jun 05, 2011 3:56 pm

Even that is sub-optimal, because you re-evaluate Char(13) for every record. Better would be to evaluate Char(13) (and, possibly, Char(254)) when initializing stage variables, and not to derive those stage variables further. Then Convert(svFieldMark, svNewLine, InLink.TheString) to effect the conversion.