Saving xls in Pipe delimited format

Suman · Post by **Suman** » Tue Jul 24, 2007 12:35 pm

Hi,
I am reading an xls file as .csv file. But, the file is having ',' as a valid value for some fields. I tried to change the ',' in the file to "|" but not able to do so because replace ',' with '|' is not working. I want to save the file in '|' delimited format. Is there any other way I can achieve it. My intention was to change the ',' to '|' then save as .csv and then change the '|' to ',' again. There is problem with saving as tab delimted also as I am not able to read the file in sequential file stage. The values are getting shifted to other column.

Regards,
Suman

DSguru2B · Post by **DSguru2B** » Tue Jul 24, 2007 1:41 pm

Specify that the quote character as " (double quotes). Save it that way so that the comma within the data will not be considered a delimiter.
You can save the file as tab delimited and then read with delimiter as 009. That should work.

Suman · Post by **Suman** » Tue Jul 24, 2007 8:56 pm

DSguru2B wrote:Specify that the quote character as " (double quotes). Save it that way so that the comma within the data will not be considered a delimiter.
You can save the file as tab delimited and then read with delimiter as 009. That should work.

Tab delimited is not working as file is having space.Is there any way to change ',' into some other special characer in xls. There is no option to specify 'double quote'

Suman

ArndW · Post by **ArndW** » Tue Jul 24, 2007 10:07 pm

If you do a "save as csv" in Excel you will get a comma separated file, and any columns that contain embedded commas will be double-quoted; so you will get a well-formed CSV file. You can read that in DataStage without a problem.

Could you explain

...I tried to change the ',' in the file to "|" but not able to do so because replace ',' with '|' is not working...

and perhaps we can come to the root of the problems you are seeing.

Suman · Post by **Suman** » Thu Jul 26, 2007 3:41 am

ArndW wrote:If you do a "save as csv" in Excel you will get a comma separated file, and any columns that contain embedded commas will be double-quoted; so you will get a well-formed CSV file. You can read that in DataStage without a problem.

Could you explain
...I tried to change the ',' in the file to "|" but not able to do so because replace ',' with '|' is not working...
and perhaps we can come to the root of the problems you are seeing.

I have got the solution by changing the ',' to '|' in regional settings-> number tab ->list separator in control panel and now if I am saving it as ',' delimited it is taking '|' as delimiter.
In excel there are values and formulas.If I am converting the excel to only values then if there is @ value in some field at the first position then the ',' in that field value is not replaced by '|'.The @ is taken as formula.So,I am not able to replace all ',' to '|'. This is now not required to save the file as '|' delimited.

Regards,
Suman