INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Excel Unicode CSV Issue

Excel Unicode CSV Issue

(OP)
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.

RE: Excel Unicode CSV Issue

Wow. It is like you read my mind. I am trying to do the exact same thing (except I am not using CR). I was wondering if you found a solution to this.

Thanks,
Billy

RE: Excel Unicode CSV Issue

(OP)
Sorry absolutely no luck.  I posted here as well as three other sights - no response on any.  I also searched the Internet for at least two hours and didn't find a solution.  We eventually forced the receiving application to upgrade versions - to one that accepts Excel input.

RE: Excel Unicode CSV Issue

Issues like this can be traced (usually) to one of two things.

1. You don't have a font installed to correctly display the Unicode character values.

2. You're specifying the wrong character set encoding when you save/open the file.

I suspect #2 in this case. When you use Excel to save as Unicode Text, it is probably saving it as UTF-16 encoding, an encoding which Notepad automatically recognizes.  But it appears that Excel doesn't put commas in there like you want when saving as Unicode Text.

I would go ahead and take a few rows and manually convert the tabs to commas, and then make sure that CR can read it.  If that works, then all you have to do is write a small program to programatically do this conversion, and run it in the middle of your export process.

Chip H.

____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

RE: Excel Unicode CSV Issue

I tested CSV on my computer (XP with Office XP) and CSV is not supporting chinese characters.  I can type them into CSV but after I save and reopen it is converted to jibberish.  It is not even restoring when I change the font in the cells to a chinese or unicode font.

I know you would rather not have a step on top of the excel work you are asking users to do but I think you are stuck going there unless someone knows how to support international fonts in CSV.

RE: Excel Unicode CSV Issue

FYI I thought I would also mention the following test results:  MSXP w/officexp and East Asian language support activated.

1.) saving from XLS containing chinese characters to CSV = chinese becomes jibbersih in CSV file.

2.) type new chinese characters dirrectly to CSV file.  Characters display correctly at first but once saved and re-oppend CSV displays them as jibberish.

3.) open the CSV with notepad.  Notepad display's jibberish from data in step 1, but display's correctly for data in step 2.

Conclussions:

1.) Microsoft Excel can not properly convert unicode fonts from XLS to CSV files. (I looked for advanced options to handle this and found none, closest option was under web options while saving there is a font menu but it had no impact on test results)

2.) CSV format can not properly display unicode fonts

RE: Excel Unicode CSV Issue

CSV is a formatting style -- comma separated values.  The values may or may not be Unicode.

You might want to use a hex editor to examine the files that you're producing, and not use Notepad.  If you see the Unicode BOM characters, then you know that Notepad is lying to you.

I like www.hexedit.com

Chip H.

____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close