Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 76 down vote favorite
10

I have an Excel file that has some Spanish characters (tildes, etc.) that I need to convert to a CSV file to use as an import file. However, when I do Save As CSV it mangles the "special" Spanish characters that aren't ASCII characters. It also seems to do this with the left and right quotes and long dashes that appear to be coming from the original user creating the Excel file in Mac.

Since CSV is just a text file I'm sure it can handle a UTF8 encoding, so I'm guessing it is an Excel limitation, but I'm looking for a way to get from Excel to CSV and keep the non-Ascii characters in tact.

share|improve this question
6  
I saw that question before I posted but it is about already having a UTF8 CSV and opening it in Excel not the other way around. – Jeff T Nov 19 '10 at 6:36

20 Answers

up vote 65 down vote accepted

A simple workaround is to use Google Spreadsheet. Paste (values only if you have complex formulas) or import the sheet then download CSV. I just tried a few characters and it works rather well.

EDIT: Another alternative - basically they use VB macro or addins to force the save as UTF8. I have not tried any of these solutions but they sound reasonable.

share|improve this answer
Thanks. I already have a Google Docs account so this was easy to do. – Jeff T Nov 19 '10 at 7:10
Thanks! It's a simple solution, far better that struggling with Excel. – charlax Jan 10 '12 at 22:17
I tried Notepad++ first and that worked only in some cases. This worked brilliantly. – GotDibbs Aug 23 '12 at 22:07
Added another possibly solution (untested) without having to use Google Spreadsheet. – nevets1219 Aug 24 '12 at 17:52
Perhaps Joel Slotsky (former PM of Excel) could send them his decade-old post on The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets? – Indolering May 23 at 5:56
up vote 16 down vote

I've found OpenOffice's spreadsheet application, Calc, (www.openoffice.org) is really good at handling CSV data. In the "Save As..." dialog, click "Format Options" to get different encodings for CSV.

Hope this helps!

share|improve this answer
I'm sure this works, I just don't happen to have OpenOffice already so Google Docs was easier in my situation. but thanks for the suggestion – Jeff T Nov 19 '10 at 7:11
2  
OpenOffice Calc has more and better options when opening and saving "CSV" files (field separators, encoding etc) than both Google Docs and Excel. Also, Google Docs currently suffers from a limit of 400,000 cells per spreadsheet, which OpenOffice Calc does not. – Christian Davén Sep 20 '12 at 12:43
up vote 6 down vote

I know this is an old question but I happened to come upon this question while struggling with the same issues as the OP.

Not having found any of the offered solutions a viable option, I set out to discover if there is a way to do this just using Excel.

Fortunately, I have found that the lost character issue only happens (in my case) when saving from xlsx format to csv format. I tried saving the xlsx file to xls first, then to csv. It actually worked.

Please give it a try and see if it works for you. Good luck.

share|improve this answer
Weird... but it works! – SomaMan Mar 4 at 15:00
Doesn't work for me. Which version of excel is that? – axrwkr Mar 12 at 17:11
Great, worked for me too! – Marko D Apr 13 at 13:50
up vote 5 down vote

"nevets1219" is right about Google docs, however if you simply "import" the file it often does not convert it to UTF-8.

But if you import the CSV into an existing Google spreadsheet it does convert to UTF-8.

Here's a recipe:

  • On the main Docs (or Drive) screen click the "Create" button and choose "Spreadsheet"
  • From the "File" menu choose "Import"
  • Click "Choose File"
  • Choose "Replace spreadsheet"
  • Choose whichever character you are using as a Separator
  • Click "Import"
  • From the "File" menu choose "Download as" -> CSV (current sheet)

The resulting file will be in UTF-8

share|improve this answer
up vote 4 down vote

Another one I've found useful: "Numbers" allows encoding-settings when saving as CSV.

share|improve this answer
up vote 3 down vote

I used the following solution: Mac Exel 2008 > file > Save-as and then under format use MS_DOS Comma Separated (.csv). Worked perfect.

share|improve this answer
1  
The question was specifically about UTF-8. – Olivier 'Ölbaum' Scherler Jul 24 '12 at 6:48
up vote 3 down vote

You can use iconv command under Unix (also available on Windows as libiconv).

After saving as CSV under Excel in the command line put:

iconv -f cp1250 -t utf-8 file-encoded-cp1250.csv > file-encoded-utf8.csv

(remember to replace cp1250 with your encoding).

Works fast and great for big files like post codes database, which cannot be imported to GoogleDocs (400.000 cells limit).

share|improve this answer
up vote 2 down vote

open .csv fine with notepad++. if you see your encoding is good (you see all characters as they should be) press encoding , then convert to ANSI else - find out what is your current encoding

share|improve this answer
that worked for me.. had problem with greek characters when exportedmysql db as csv and imported it to excel.. – nikolas May 24 at 9:47
up vote 2 down vote

another solution is to open the file by winword and save it as txt and then reopen it by excel and it will work ISA

share|improve this answer
up vote 2 down vote

Easy way to do it: download open office (here), load the spreadsheet and open the excel file (.xls or .xlsx). Then just save it as a text CSV file and a window opens asking to keep the current format or to save as a .ODF format. select "keep the current format" and in the new window select the option that works better for you, according with the language that your file is been written on. For Spanish language select Western Europe (Windows-1252/ WinLatin 1) and the file works just fine. If you select Unicode (UTF-8), it is not going to work with the spanish characters.

share|improve this answer
up vote 1 down vote

I was not able to find a VBA solution for this problem on Mac Excel. There simply seemed to be no way to output UTF-8 text.

So I finally had to give up on VBA, bit the bullet, and learned AppleScript. It wasn't nearly as bad as I had thought.

Solution is described here: http://talesoftech.blogspot.com/2011/05/excel-on-mac-goodbye-vba-hello.html

share|improve this answer
up vote 1 down vote

Encoding -> Convert to Ansi will encode it in ANSI/UNICODE. Utf8 is a subset of Unicode. Perhaps in ANSI will be encoded correctly, but here we are talking about UTF8, @SequenceDigitale.

There are faster ways, like exporting as csv ( comma delimited ) and then, opening that csv with Notepad++ ( free ), then Encoding > Convert to UTF8. But only if you have to do this once per file. If you need to change and export fequently, then the best is LibreOffice or GDocs solution.

share|improve this answer
Welcome so SO! You may edit your own answers, so there is no need to answer twice - you can always add to your old answer. – malenkiy_scot Jun 7 '12 at 8:33
up vote 1 down vote

Another way is to open the UTF-8 CSV file in Notepad where it will be displayed correctly. Then replace all the "," with tabs. Paste all of this into a new excel file.

share|improve this answer
up vote 1 down vote

Assuming an Windows environment, save and work with the file as usual in Excel but then open up the saved Excel file in Gnome Gnumeric (free). Save Gnome Gnumeric's spreadsheet as CSV which - for me anyway - saves it as UTF-8 CSV.

share|improve this answer
up vote 1 down vote

Microsoft Excel has an option to export spreadsheet using Unicode encoding. See following screenshot.

enter image description here

share|improve this answer
6  
"Unicode Text" saves in UTF-16 LE (Little Endian), not in UTF-8 as asked by the OP. – ax. Jul 18 '12 at 7:22
Yes, but it is the best way to get Unicode support for your x-separated-values in Excel. I've had all sorts of issues trying to get Excel to play ball with UTF-8! Read More – mcNux Apr 24 at 16:15
up vote 1 down vote

  1. Save the Excel sheet as "Unicode Text (.txt)". The good news is that all the international characters are in UTF16 (note, not in UTF8). However, the new "*.txt" file is TAB delimited, not comma delimited, and therefore is not a true CSV.

  2. (optional) Unless you can use a TAB delimited file for import, use your favorite text editor and replace the TAB characters with commas ",".

  3. Import your *.txt file in the target application. Make sure it can accept UTF16 format.

I use this procedure to import data from Excel to Moodle.

share|improve this answer
up vote 0 down vote

A second option to "nevets1219" is to open your CSV file in Notepad++ and do a convertion to ANSI.

Choose in the top menu : Encoding -> Convert to Ansi

share|improve this answer
No idea why you were downvoted. Notepad++ did it for me. Can't store my file in Google Spreadsheet as it is confidential. – Zane Mar 28 at 17:55
up vote 0 down vote

  1. Save xls file (Excel file) as Unicode text=>file will be saved in text format (.txt)

  2. Change format from .txt to .csv (rename the file from XYX.txt to XYX.csv

share|improve this answer
up vote -1 down vote

You can save excel as unicode text, it is tab-delimited.

share|improve this answer
Does not answer question. – Superbest Oct 6 '12 at 20:54
up vote -4 down vote

In the latest version of Excel (2010) on Windows you can now do it. When saving the file, hit the 'tools' menu and choose 'encoding.'

enter image description here

share|improve this answer
9  
Yes, the option is there, but it doesn't help: the file is still saved in the wrong encoding (at least if you want UTF-8). – romkyns Dec 20 '11 at 0:45
1  
Confirmed that the Web Options setting doesn't apply to CSV exports in Excel 2010. Please downvote this answer, it is incorrect. – Russell Davis Feb 3 '12 at 21:33
The encoding tab option says "save this document as:", so it should apply to the document being saved, for any text-based format chosen. While it's good to note that the feature is broken, the answer itself is not incorrect. – Triynko Feb 7 '12 at 18:11
1  
it is not helpful if the feature is broken – Richard Apr 3 '12 at 13:52
1  
If you save a xlsx file as a csv file (with UTF-8 set in de Tools/Web Options/Encoding) is NOT saving it as utf-8. – Roger Apr 23 '12 at 13:32
show 1 more comment

protected by Community♦ Mar 14 at 20:06

This question is protected to prevent "thanks!", "me too!", or spam answers by new users. To answer it, you must have earned at least 10 reputation on this site.

Not the answer you're looking for? Browse other questions tagged or ask your own question.