Issue with sequential file stage

[suneyes]

A very good day to you all..

I have a sequential file as an input stage which reads a comma delimited file.
I am facing problem while exporting a field in that file.
I have a record layout in the below mentioned way.

AAAAAAAAAAAA,BBBBBBBBBBBB,"G , G 'E L', S 'E AL",DDDDDDDDDDDDDDD,EEEEEEEEE

My idea is to import the following record as


COLUMN1-AAAAAAAAAAAA,
COLUMN2-BBBBBBBBBBBB,
COLUMN3-G , G 'E L', S 'E AL,
COLUMN4-DDDDDDDDDDDDDDD,
COLUMN5-EEEEEEEEE


But currently my job is exporting the above record into the below layout


COLUMN1-AAAAAAAAAAAA,
COLUMN2-BBBBBBBBBBBB,
COLUMN3-G ,
COLUMN4-
COLUMN5-G 'E L', S 'E AL,DDDDDDDDDDDDDDD,EEEEEEEEE


I currently configured my sequential file stage with the following settings..

Delimiter=comma
Quote=double
Field Delimiter=None


Can you please help to get over this situation?

[qt_ky]

Is it always true that column3 is surrounded by double quotes, does not contain any double quotes within it, and all the other columns are not surrounded by double quotes?

[suneyes]

Yes Eric. Thank you

[kandyshandy]

Double quote your 3rd column in your input file and read it. See if that works!

[suneyes]

Double quote your 3rd column in your input file and read it. See if that works!

But kandy..its already double quoted in the input file..iam I getting it wrong...

[kandyshandy]

am I getting it wrong...

No.. I don't have access to the tool to check it out.

[ray.wurlod]

The parallel Sequential File stage is notoriously bad at handling quoted strings containing field delimiters.

Create a server Shared Container containing a server Sequential File stage and use this Shared Container in your parallel job.

[qt_ky]

The parallel Sequential File stage is notoriously bad at handling quoted strings containing field delimiters.

Seems like a bug. Do you know if anyone has ever opened a PMR on it and what the response was?

It is extra effort but it can be done in a parallel job by using Field() functions in a Transfomer stage that follows the Sequential File stage. Change the file settings to have a single column that's wide enough, such as VarChar(200), and set Delimiter=none and Quote=none. Then to separate each column, use one or more Field() functions.

Specify the double quote as the delimiter for the inner-most Field() function. If you look at the sample input row with " as the delimiter, you should see 3 subtrings:

occurrence 1: AAAAAAAAAAAA,BBBBBBBBBBBB,
delimiter: "
occurrence 2: G , G 'E L', S 'E AL
delimiter: "
occurrence 3: ,DDDDDDDDDDDDDDD,EEEEEEEEE

Use an outer Field() function to derive the rest (columns 1, 2, 4, 5) based on comma as the delimiter. Example to get column2:

Code: Select all

Field(Field(in.RECORD, '"', 1), ',', 2)

[SURA]

I tried the same with your sample data and it works fine, but in windows.

Ensure that

Fianl Delimiter = End / whatever
Delimiter = comma
Quote = double

DS User

[suneyes]

The parallel Sequential File stage is notoriously bad at handling quoted strings containing field delimiters.

Create a server Shared Container containing a server Sequential File stage and use this ...

Thanks Ray..

I have used a sever container stage with a server sequential stage in it and it works fine with me.

I expect this would have been fixed in the later versions of datastage.


As SURA said,This is working fine in Windows as well.

Thank you all for your response.I am marking this thread as resolved.

[qt_ky]

I have mentioned the issue to one contact at IBM but have not opened any PMR on it. I would be curious if anyone else has already opened a PMR or knows of an enhancement request existing???