sequential stage read issue with double quotes used as data

abhik05 · Post by **abhik05** » Thu May 17, 2012 2:27 am

We are reading txt files using sequential stage with Quote property set as 'double'.
But we are getting few rejections for data records like :
7724,"4957","""VENTILATION"" PNEUMONITIS",132,"Lung disease due to external agents",,"","Diagnosis","","".

Here rejects is occurring when there are double quotes used as data, ie "VENTILATION" PNEUMONITIS. To get the data loaded like "VENTILATION" PNEUMONITIS, the data file needs to include extra double quotes, """VENTILATION"" PNEUMONITIS". The first and last " are used to enclose the data field. the 2nd and 3rd is provided to so that the double quote is read as data not as enclose code. The same case for 4th and 5th double quote.

Please help.

Thanks
Abhik.

vamsi.4a6 · Post by **vamsi.4a6** » Thu May 17, 2012 2:47 am

May i know what is the data it is showing when i select view data in Datastage for sequential file stage for field value-"VENTILATION" PNEUMONITIS

abhik05 · Post by **abhik05** » Thu May 17, 2012 2:55 am

vamsi.4a6 wrote:May i know what is the data it is showing when i select view data in Datastage for sequential file stage for field value-"VENTILATION" PNEUMONITIS

this record is getting rejected while being read from the sequential file stage itself.In view data we are not able to see this record.Let me tell u also this a comma delimited file and double quote has been selected for string datatype read.

vamsi.4a6 · Post by **vamsi.4a6** » Thu May 17, 2012 3:04 am

Try with Quote-none

abhik05 · Post by **abhik05** » Thu May 17, 2012 3:34 am

vamsi.4a6 wrote:Try with Quote-none

it will not work as without quote property.in that case all the records are getting rejected.
Also all the fields are defined as nullable here and '' is in the file to be treated as NULL.That we used mention as 'null field value' also.

ray.wurlod · Post by **ray.wurlod** » Thu May 17, 2012 3:42 am

This is not something that the parallel Sequential File stage does well.
Read the entire line as a single VarChar field, then use a Transformer stage both to parse and to handle the double quote characters appropriately.

This is a task that can much more easily be handled by a server Sequential File stage.