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
Saving xls in Pipe delimited format
Moderators: chulett, rschirm, roy
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.
You can save the file as tab delimited and then read with delimiter as 009. That should work.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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'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.
Suman
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
Could you explain
and perhaps we can come to the root of the problems you are seeing....I tried to change the ',' in the file to "|" but not able to do so because replace ',' with '|' is not working...
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.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 explainand perhaps we can come to the root of the problems you are seeing....I tried to change the ',' in the file to "|" but not able to do so because replace ',' with '|' is not working...
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