Excel to CSV with UTF8 encoding

Question

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 intact

User · Accepted Answer

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.

NOTE: Google Sheets does have limitations when importing. See here.

NOTE: Be careful of sensitive data with Google Sheets.

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.

User · Answer

I used the following solution  Mac Exel 2008   file   Save-as and then under format use MS DOS Comma Separated   csv   Worked perfect

User · Answer

I have also came across the same problem but there is an easy solution for this     Open your xlsx file in Excel 2016 or higher  In  Save As  choose this option    CSV UTF-8 Comma Delimited   csv     It works perfectly and a csv file is generated which can be imported in any software  I imported this csv file in my SQLITE database and it works perfectly with all unicode characters intact

User · Answer

I needed to automate this process on my Mac  I originally tried using catdoc xls2csv as suggested by mpowered  but xls2csv had trouble detecting the original encoding of the document and not all documents were the same  What I ended up doing was setting the default webpage output encoding to be UTF-8 and then providing the files to Apple s Automator  applying the Convert Format of Excel Files action to convert to Web Page  HTML   Then using PHP  DOMDocument and XPath  I queried the documents and formatted them to CSV   This is the PHP script  process php     lt  php  pi   pathinfo  argv 1     file    pi  dirname            pi  filename       csv    fp   fopen  file  w      doc   new DOMDocument   doc- gt loadHTMLFile  argv 1     xpath   new DOMXPath  doc    table       foreach  xpath- gt query    tr   as  row         r           foreach  xpath- gt query  td   row  as  col             r     trim  col- gt textContent             fputcsv  fp   r     fclose  fp     gt    And this is the shell command I used to convert the HTML documents to csv   find   -name    htm    xargs -I   php   process php      This is a really  really roundabout way of doing this  but it was the most reliable method that I found

User · Answer

You can do this on a modern Windows machine without third party software  This method is reliable and it will handle data that includes quoted commas  quoted tab characters  CJK characters  etc   1  Save from Excel  In Excel  save the data to file txt using the type Unicode Text    txt    2  Start PowerShell  Run powershell from the Start menu   3  Load the file in PowerShell   data   Import-Csv C  path to file txt -Delimiter   t  -Encoding BigEndianUnicode   4  Save the data as CSV   data   Export-Csv file csv -Encoding UTF8 -NoTypeInformation

User · Answer

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

User · Answer

Excel typically saves a csv file as ANSI encoding instead of utf8    One option to correct the file is to use Notepad or Notepad      Open the  csv with Notepad or Notepad     Copy the contents to your computer clipboard  Delete the contents from the file  Change the encoding of the file to utf8  Paste the contents back from the clipboard  Save the file

User · Answer

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

User · Answer

On a Mac   From Excel save as CSV file  Open the CSV file in TextWrangler  it   s free  and use  save as   In the save dialog choose Unicode  UTF-8   Done   I guess you can do this with TextEdit also - if you play with the open and save settings  Try Open files  auto  Save files  UTF-8

User · Answer

And for those who have sublime text  save with encoding utf-16 LE with BOM should do it  -

User · Answer

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

User · Answer

Under Excel 2016 and up  including Office 365   there is a CSV option dedicated to the UTF-8 format   In Office 365  do Save As  where previously one might have chosen CSV  Comma Delimited   now one of the file types you can save as is CSV UTF-8  Comma delimited     csv

User · Answer

I have written a small Python script that can export worksheets in UTF-8   You just have to provide the Excel file as first parameter followed by the sheets that you would like to export  If you do not provide the sheets  the script will export all worksheets that are present in the Excel file      usr bin env python    export data sheets from xlsx to csv  from openpyxl import load workbook import csv from os import sys  reload sys  sys setdefaultencoding  utf-8    def get all sheets excel file       sheets          workbook   load workbook excel file use iterators True data only True      all worksheets   workbook get sheet names       for worksheet name in all worksheets          sheets append worksheet name      return sheets  def csv from excel excel file  sheets       workbook   load workbook excel file use iterators True data only True      for worksheet name in sheets          print  Export     worksheet name                    try              worksheet   workbook get sheet by name worksheet name          except KeyError              print  Could not find     worksheet name              sys exit 1           your csv file   open    join  worksheet name   csv      wb           wr   csv writer your csv file  quoting csv QUOTE ALL          for row in worksheet iter rows                lrow                  for cell in row                  lrow append cell value              wr writerow lrow          print       done       your csv file close    if not 2  lt   len sys argv   lt   3      print  Call with     sys argv 0       lt xlxs file gt   comma separated list of sheets to export        sys exit 1  else      sheets          if len sys argv     3          sheets   list sys argv 2  split           else          sheets   get all sheets sys argv 1       assert sheets    None and len sheets   gt  0      csv from excel sys argv 1   sheets

User · Answer

Using Notepad   This will fix the corrupted CSV file saved by Excel and re-save it in the proper encoding   Export CSV from Excel Load into Notepad   Fix encoding Save  Excel saves in CP-1252   Windows-1252  Open the CSV file in Notepad    Select Encoding  gt  Character Sets  gt  Western European  gt  Windows-1252  Then Encoding  gt  Convert to UTF-8 File  gt  Save  First tell Notepad   the encoding  then convert  Some of these other answers are converting without setting the proper encoding first  mangling the file even more  They would turn what should be     into    If your character does not fit into CP-1252 then it was already lost when it was saved as CSV  Use another answer for that

User · Answer

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

User · Answer

I have the same problem and come across this add in    and it works perfectly fine in excel 2013 beside excel 2007 and 2010 which it is mention for

User · Answer

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

User · Answer

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

User · Answer

Another one I ve found useful   Numbers  allows encoding-settings when saving as CSV

User · Answer

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    optional  Unless you can use a TAB delimited file for import  use your favorite text editor and replace the TAB characters with commas      Import your   txt file in the target application  Make sure it can accept UTF16 format    If UTF-16 has been properly implemented with support for non-BMP code points  that you can convert a UTF-16 file to UTF-8 without losing information  I leave it to you to find your favourite method of doing so   I use this procedure to import data from Excel to Moodle

User · Answer

What about using Powershell   Get-Content  C  my csv    Out-File  C  my utf8 csv  -Encoding UTF8

User · Answer

As funny as it may seem  the easiest way I found to save my 180MB spreadsheet into a UTF8 CSV file was to select the cells into Excel  copy them and to paste the content of the clipboard into SublimeText

User · Answer

Save Dialog   Tools Button   Web Options   Encoding Tab

User · Answer

The only  easy way  of doing this is as follows  First  realize that there is a difference between what is displayed and what is kept hidden in the Excel  csv file    Open an Excel file where you have the info   xls   xlsx  In Excel  choose  CSV  Comma Delimited     csv  as the file type and save as that type  In NOTEPAD  found under  Programs  and then Accessories in Start menu   open the saved  csv file in Notepad Then choose -  Save As    and at the bottom of the  save as  box  there is a select box labelled as  Encoding   Select UTF-8  do NOT use ANSI or you lose all accents etc   After selecting UTF-8  then save the file to a slightly different file name from the original    This file is in UTF-8 and retains all characters and accents and can be imported  for example  into MySQL and other database programs   This answer is taken from this forum

User · Answer

Save xls file  Excel file  as Unicode text  file will be saved in text format   txt  Change format from  txt to  csv  rename the file from XYX txt to XYX csv

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

For those looking for an entirely programmatic  or at least server-side  solution  I ve had great success using catdoc s xls2csv tool   Install catdoc   apt-get install catdoc   Do the conversion   xls2csv -d utf-8 file xls  gt  file-utf-8 csv    This is blazing fast   Note that it s important that you include the -d utf-8 flag  otherwise it will encode the output in the default cp1252 encoding  and you run the risk of losing information   Note that xls2csv also only works with  xls files  it does not work with  xlsx files

User · Answer

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

User · Answer

Came across the same problem and googled out this post  None of the above worked for me  At last I converted my Unicode  xls to  xml  choose Save as     XML Spreadsheet 2003  and it produced the correct character  Then I wrote code to parse the xml and extracted content for my use

User · Answer

Easiest way  No need Open office and google docs   Save your file as  Unicode text file   now you have an unicode text file open it with  notepad  and  Save as  it with selecting  utf-8  or other code page that you want rename file extension from  txt  to  csv   This will result in a tab-delimited UTF-8 csv file  If you want a comma-delimited file  open the csv file you just renamed and replace all tabs with commas  To do this in Notepad on Win 10  simply select one tab field then click Ctrl H  In the window that opens  type a comma   in the  Replace with  field then click  Replace All   Save your file  The result will be a comma-delimited UTF-8 csv file    Don t open it with MS-Office anyway    Now you have a tab delimited CSV file  Or  a comma-delimited one if you applied step number 5

User · Answer

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  gt  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

User · Answer

I ve found OpenOffice s spreadsheet application  Calc  is really good at handling CSV data    In the  Save As     dialog  click  Format Options  to get different encodings for CSV  LibreOffice works the same way AFAIK

User · Answer

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

[excel] Excel to CSV with UTF8 encoding

Examples related to excel

Examples related to encoding

Examples related to csv

Examples related to utf-8