CSV file with embedded quotes and commas
Posted: Thu Jun 05, 2014 11:49 pm
I have looked around on google and this forum, as well as the documentation, and experimenting for myself, and not found a solution. If there is no DataStage native solution, I would like confirmation that that is the case. I do not want to see a command-line scripting solution to this problem.
It seems like the Datastage parallel job file input step cannot handle general Excel CSV files. For instance, the below.
30-40 ABC+C,TV,MXN,0.08026,160701,,,SPACE,"20""",,,Cable / Satellite,,"97,253.20","7,805.13",,0,"97,253.20","7,805.13",2,1/6/14,1/12/14,January,"24,313.30","24,313.30","1,951.28","1,951.28",2014
Delimiter for field "Burst_Spend_NNMC_USD" not found; input: {2 0 1 4}, at offset: 368
There are commas embedded in the fields, and also double quotes. Setting the file as double-quoted will fail on the embedded quotes, setting it as not double-quoted will fail on the embedded commas. How can I read a line like this in correctly?
It seems like the Datastage parallel job file input step cannot handle general Excel CSV files. For instance, the below.
30-40 ABC+C,TV,MXN,0.08026,160701,,,SPACE,"20""",,,Cable / Satellite,,"97,253.20","7,805.13",,0,"97,253.20","7,805.13",2,1/6/14,1/12/14,January,"24,313.30","24,313.30","1,951.28","1,951.28",2014
Delimiter for field "Burst_Spend_NNMC_USD" not found; input: {2 0 1 4}, at offset: 368
There are commas embedded in the fields, and also double quotes. Setting the file as double-quoted will fail on the embedded quotes, setting it as not double-quoted will fail on the embedded commas. How can I read a line like this in correctly?