Column Import Stage - Carriage return in free-text field

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
brock125
Premium Member
Premium Member
Posts: 14
Joined: Wed Dec 14, 2011 9:03 am

Column Import Stage - Carriage return in free-text field

Post by brock125 »

I'm at a loss trying to find a solution for my problem. Below is a simple illustration of my job. There are multiple Column Imports/Transformers/Data Sets coming off the first transformer but I only showed the direct path for a single one. This job reads messages from the MQ and routes them to their appropriate path within the first transformer. The problem I'm having is within the Column Import stage. The data is ~ delimited and has double quotes around text fields. There is a free-text field included as one of the fields. The problem I'm am encountering is when there is a carriage return within the free form text field. The Column Import stage treats this carriage return as a field delimiter (I think) and gives the following warning message: "Trailing quote for field "FREE_TEXT_FIELD" not found". The job aborts after that. Does anybody know if there is a setting in the Format section on the Output tab in the Column Import stage that I can set to fix this issue? What about the "Edit column meta data" option in the Column section on the Output tab of the Column Import stage? Does anybody know if there is something that can be done in the "Field level" or "String type" properties? I have included the current settings from the Format section of the Output tab within the Column Import stage below.

Format section, Output tab settings:
- Record level
- Final delimiter = end

-Field defaults
- Delimiter = ~
- Quote = double


Parallel Job:
WebSphere MQ Connector --> Transformer --> Column Import --> Transformer --> Data Set
rkashyap
Premium Member
Premium Member
Posts: 532
Joined: Fri Dec 02, 2011 12:02 pm
Location: Richmond VA

Post by rkashyap »

Carriage return => Char(13). Can you try running following derivation on FREE_TEXT_FIELD in the transformer.

Code: Select all

Ereplace(in_rec.FREE_TEXT_FIELD, Char(13) ," ",0,1)
brock125
Premium Member
Premium Member
Posts: 14
Joined: Wed Dec 14, 2011 9:03 am

Post by brock125 »

Thanks for that suggestion. I'm guessing that would work but I'm not sure if we can modify the data in any way. I have a feeling that will be considered a no-no. I'll give it a shot though.
Post Reply