Excel Unicode CSV Issue

[baycolor]

Hi,

I'm using Microsoft Office Excel 2003 and Crystal Reports XI.

I've developed a Crystal Report that connects via ODBC to a database using Session Character Set equal to UTF8 (some of the data pulled back is Japanese). When I run the report all Japanese data displays correctly). Font used in the report is Arial Unicode MS.

When I save the report output as Microsoft Excel format and open the report in Excel the Japanese data also displays correctly.

Problem is I need to feed this data into an application in .csv format (required by that app).

Doing a Save As from Excel to .csv format doesn't work. If I open the .csv file in Notepad I get question marks in place of all my Japanese data. If I save the file as type Unicode Text (.txt) and then open the file in Notepad all the data displays correctly and is tab delimited. Some success but not what I need.

1)
Writing a program to replace the tabs in the .txt with commas would probably work but I have end users that will be running this report, saving it to Excel, hopefully saving out of Excel as .csv and then need to upload it into the end application that requires .csv. Don't want to make it a multi-step process beyond Excel.

2)
I know the Excel data can be loaded into Access and then saved out to a .csv that supports Unicode but again don't want to make this a multi-step process beyond Excel.

3)
Crystal Reports has an option to save to a .CSV but I am having other CR issues that are preventing me from testing that right now - and I don't have a lot of faith in that anyway. Have to believe there is a way to do this from Excel.

Anyone know if there is a way to get this data out of Excel correctly in .csv format?

Thanks in advance.

 

[fkegley]

What about a macro that does a Find and Replace, it could for instance find all the tabs and replace with ,s. Then it export the worksheet to a text file.

Frank kegley

 

[baycolor]

Not sure I understand...

My data is in Excel in a sheet in columns and rows. The values in some cells are latin characters and the values in other cells are non-latin. No tabs exist in any of the cells at this point.

I need to use File->Save As to save the data in the sheet as a comma delimited (.csv) file with the non-latin data supported in the resulting .csv output file.

Exel File->Save As has an option to save to a Unicode Text (.txt) tab delimited file which supports the non-latin characters in the resulting output file.

I can't believe there isn't a way to support the non-latin characters when doing File->Save As and creating a .csv comma delimited file.

Thanks