File Data in Table Column
Moderators: chulett, rschirm, roy
File Data in Table Column
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.
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.
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.chulett wrote:What happens when you set 'Preserve' to yes?
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.
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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?
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?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.