Data Handling on DS 9.1 on RHEL 6.2

cbs_bas_ccit · Post by **cbs_bas_ccit** » Tue May 20, 2014 3:51 am

In the recent days, we have hit an issue related to the user including double-quotes in the data causing us issues. Data was shared by user as below:

Val: "Test, 123"

When the Datastage extracts the data from a table to a CSV file, the value gets converted as:

Val: ""Test, 123"" [Includes another double-quote as the data is extracted from a Text Column]

I can handle the above scenario by introducing a temporary change change using Oracle Replace command, which produces output as below:

Val: "Test, 123" [Stripping the double-quote shared by user]

We are looking for a permanent fix on the same and have got 2 questions:

1. Can DS handle control chars automatically ?
2. Can DS change the delim from ; to a non-printable character, as an example 

Can you let us know your views.

ArndW · Post by **ArndW** » Tue May 20, 2014 6:17 am

When reading from a sequential file source or writing to a sequential file, you have the option to specify whether strings are quoted. It would seem that you have this turned on but don't have quoted strings, so the solution would be to modify your sequential file stage where you are reading from the .csv file to tell it that strings are indeed quoted, and DataStage will automatically strip the outer quotes.

chulett · Post by **chulett** » Tue May 20, 2014 7:07 am

I'm not sure I'm seeing a problem. Does the data in the table actually include the double-quotes as shown? If so, in Oracle's world the proper way to write that out is by adding a double-quote to each one as you are seeing. That's the only way they'll be preserved as part of the data on the way back in.

I'm also curious how your two questions relate to this quoting issue. What 'control characters' are you asking about? And you have full control over the delimiter used from what I recall. You would use the numeric equivalent when declaring it in that case.

qt_ky · Post by **qt_ky** » Tue May 20, 2014 7:26 am

How many times can I say double quotes in a sentence? It is normal to have double double quotes when data values already contain double quotes or are already surrounded by double quotes and you want to double quote the values. Go into Excel and enter some test data with double quotes, save it as a CSV file, then open the file using any text editor. It behaves in the same way.

chulett · Post by **chulett** » Tue May 20, 2014 7:30 am

Exactly exactly.

cbs_bas_ccit · Post by **cbs_bas_ccit** » Fri May 23, 2014 12:26 am

Thanks everyone... we have managed to fix it in server jobs.. but while migrating server to PX this is still a issue? Any suggestions on PX?

ray.wurlod · Post by **ray.wurlod** » Fri May 23, 2014 12:44 am

Use a server Sequential File stage in a server Shared Container in a parallel job.