Is it possible to force Excel recognize UTF-8 CSV files automatically

Question

I m developing a part of an application that s responsible for exporting some data into CSV files  The application always uses UTF-8 because of its multilingual nature at all levels  But opening such CSV files  containing e g  diacritics  cyrillic letters  Greek letters  in Excel does not achieve the expected results showing something like                     And I don t know how to force Excel understand that the open CSV file is encoded in UTF-8  I also tried specifying UTF-8 BOM EF BB BF  but Excel ignores that   Is there any workaround   P S  Which tools may potentially behave like Excel does     UPDATE  I have to say that I ve confused the community with the formulation of the question  When I was asking this question  I asked for a way of opening a UTF-8 CSV file in Excel without any problems for a user  in a fluent and transparent way  However  I used a wrong formulation asking for doing it automatically  That is very confusing and it clashes with VBA macro automation  There are two answers for this questions that I appreciate the most  the very first answer by Alex https   stackoverflow com a 6002338 166589  and I ve accepted this answer  and the second one by Mark https   stackoverflow com a 6488070 166589 that have appeared a little later  From the usability point of view  Excel seemed to have lack of a good user-friendly UTF-8 CSV support  so I consider both answers are correct  and I have accepted Alex s answer first because it really stated that Excel was not able to do that transparently  That is what I confused with automatically here  Mark s answer promotes a more complicated way for more advanced users to achieve the expected result  Both answers are great  but Alex s one fits my not clearly specified question a little better     UPDATE 2  Five months later after the last edit  I ve noticed that Alex s answer has disappeared for some reason  I really hope it wasn t a technical issue and I hope there is no more discussion on which answer is greater now  So I m accepting Mark s answer as the best one

User · Accepted Answer

Alex is correct  but as you have to export to csv  you can give the users this advice when opening the csv files    Save the exported file as a csv Open Excel Import the data using Data-- Import External Data --  Import Data Select the file type of  csv  and browse to your file  In the import wizard change the File Origin to  65001 UTF   or choose correct language character identifier   Change the Delimiter to comma Select where to import to and Finish   This way the special characters should show correctly

User · Answer

If you want to make it fully automatic  one click  or to load automatically into Excel from say a web page  but can t generate proper Excel files  then I would suggest looking at SYLK format as an alternative   OK it is not as simple as CSV but it is text based and very easy to implement and it supports UTF-8 with no issues    I wrote a PHP class that receives the data and outputs a SYLK file which will open directly in Excel by just clicking the file  or will auto-launch Excel if you write the file to a web page with the correct mime type  You can even add formatting  like bold  format numbers in particular ways etc  and change column sizes  or auto size columns to the text in the columns and all in all the code is probably not more than about 100 lines   It is dead easy to reverse engineer SYLK by creating a simple spreadsheet and saving as SYLK and then reading it with a text editor   The first block are headers and standard number formats that you will recognise  which you just regurgitate in every file you create   then the data is simply an X Y coordinate and a value

User · Answer

hi i m using ruby on rails for csv generation  In our application we plan to go for the multi language I18n  and we faced an issue while viewing I18n content in the CSV file of windows excel   Was fine with Linux  Ubuntu  and mac   We identified that windows excel need to be imported the data again to view the actual data  While import we will get more options to choose character set   But this can   t be educated for each and every user  so solution we looking for is to open just by double click    Then we identified the way of showing data by open mode and bom in windows excel with the help of aghuddleston gist  Added at reference   Example I18n content  In Mac and Linux  Swedish   F  rnamn  English   First name  In Windows  Swedish   F    rnamn English   First name  def user information report report file path  user id      user   User find user id      I18n locale   user current lang     open mode    w  UTF-16LE UTF-8      bom     xEF xBB xBF      body user  open mode  bom   end  def headers     headers              ID    SDN ID           I18n t  sys first name    I18n t  sys last name    I18n t  sys dob            I18n t  sys gender    I18n t  sys email    I18n t  sys address            I18n t  sys city    I18n t  sys state    I18n t  sys zip            I18n t  sys phone number           end  def body tenant  open mode  bom     File open report file path  open mode  do  f        csv file   CSV generate col sep    t   do  csv          csv  lt  lt  headers         tenant patients find each batch size  10  do  patient            csv  lt  lt                   patient id  patient patientid                patient first name  patient last name     patient dob                     translate gender patient gender     patient email     patient address 1 to s    patient address 2 to s                     patient city       patient state        patient zip                     patient phone number                       end       end       f write bom       f write csv file      end   end   Important things to note here is open mode and bom  open mode    w  UTF-16LE UTF-8   bom     xEF xBB xBF   Before writing the CSV insert BOM  f write bom  f write csv file   Windows and Mac  File can be opened directly by double clicking   Linux  ubuntu   While opening a file ask for the separator options -  choose    TAB

User · Answer

The UTF-8 Byte-order marker will clue Excel 2007  in to the fact that you re using UTF-8   See this SO post    In case anybody is having the same issues I was   NET s UTF8 encoding class does not output a byte-order marker in a GetBytes   call  You need to use streams  or use a workaround  to get the BOM to output

User · Answer

Just for help users interested on opening the file on Excel that achieve this thread like me   I have used the wizard below and it worked fine for me  importing an UTF-8 file  Not transparent  but useful if you already have the file    Open Microsoft Excel 2007  Click on the Data menu bar option  Click on the From Text icon  Navigate to the location of the file that you want to import   Click on the filename and then click on the Import button   The Text Import Wizard - Step 1 or 3 window will now appear on the screen  Choose the file type that best describes your data - Delimited or Fixed Width  Choose 65001  Unicode  UTF-8  from the drop-down list that appears next to File origin  Click on the Next button to display the Text Import Wizard - Step 2 or 3 window  Place a checkmark next to the delimiter that was used in the file you wish to import into Microsoft Excel 2007   The Data preview window will show you how your data will appear based on the delimiter that you chose  Click on the Next button to display the Text Import Wizard - Step 3 of 3  Choose the appropriate data format for each column of data that you want to import   You also have the option to not import one or more columns of data if you want  Click on the Finish button to finish importing your data into Microsoft Excel 2007    Source  https   www itg ias edu content how-import-csv-file-uses-utf-8-character-encoding-0

User · Answer

This is my working solution   vbFILEOPEN    your utf8 file csv  Workbooks OpenText Filename  vbFILEOPEN  DataType  xlDelimited  Semicolon  True  Local  True  Origin  65001   The key is Origin  65001

User · Answer

Download  amp  install LibreOffice Calc Open the csv file of your choice in LibreOffice Calc  Thank the heavens that an import text wizard shows up       select your delimiter and character encoding options Select the resulting data in Calc and copy paste to Excel

User · Answer

In php you just prepend  bom to your  csv string    bom   sprintf    c c c   239  187  191      EF BB BF file put contents   file name   bom    csv string      Tested with MS Excel 2016  php 7 2 4

User · Answer

Old question but heck  the simplest solution is    Open CSV in Notepad Save As -  select the right encoding Open the new file

User · Answer

Yes it is possible   When writing the stream creating the csv  the first thing to do is this   myStream Write Encoding UTF8 GetPreamble    0  Encoding UTF8 GetPreamble   Length

User · Answer

This is not accurately addressing the question but since i stumbled across this and the above solutions didn t work for me or had requirements i couldn t meet  here is another way to add the BOM when you have access to vim   vim -e -s   set bomb set encoding utf-8 wq  filename csv

User · Answer

A truly amazing list of answers  but since one pretty good one is still missing  I ll mention it here  open the csv file with google sheets and save it back to your local computer as an excel file   In contrast to Microsoft  Google has managed to support UTF-8 csv files so it just works to open the file there  And the export to excel format also just works  So even though this may not be the preferred solution for all  it is pretty fail safe and the number of clicks is not as high as it may sound  especially when you re already logged into google anyway

User · Answer

I have had the same issue in the past  how to produce files that Excel can read  and other tools can also read   I was using TSV rather than CSV  but the same problem with encodings came up   I failed to find any way to get Excel to recognize UTF-8 automatically  and I was not willing able to inflict on the consumers of the files complicated instructions how to open them  So I encoded them as UTF-16le  with a BOM  instead of UTF-8  Twice the size  but Excel can recognize the encoding  And they compress well  so the size rarely  but sadly not never  matters

User · Answer

I tried everything I could find on this thread and similar  nothing worked fully  However  importing to google sheets and simply downloading as csv worked like a charm  Try it out if you come to my frustration point

User · Answer

As I posted on http   thinkinginsoftware blogspot com 2017 12 correctly-generate-csv-that-excel-can html   Tell the software developer in charge of generating the CSV to correct it  As a quick workaround you can use gsed to insert the UTF-8 BOM at the beginning of the string   gsed -i  1s     xef xbb xbf      xef xbb xbf   file csv   This command inserts the UTF-4 BOM if not present  Therefore it is an idempotent command  Now you should be able to double click the file and open it in Excel

User · Answer

Working solution for office 365   save in UTF-16  no LE  BE  use separator  t   Code in PHP   header     c  slo    vytvoreno    e  cr               fileName    excel365 csv    fp   fopen  fileName   w    fputcsv  fp   header    t    fclose  fp     handle   fopen  fileName   r     contents   fread  handle  filesize  fileName     contents   iconv  UTF-8    UTF-16    contents   fclose  handle     handle   fopen  fileName   w    fwrite  handle   contents   fclose  handle

User · Answer

I faced the same problem a few days ago  and could not find any solution because I cannot use the import from csv feature because it makes everything to be styled as string   My solution was to first open the file with notpad   and change the encode to ASCII  Then just opened the file in excel and it worked as expected

User · Answer

Had the same problems with PHP-generated CSV files   Excel ignored the BOM when the Separator was defined via  sep   n  at the beginning of the content  but of course after the BOM    So adding a BOM    xEF xBB xBF   at the beginning of the content and setting the semicolon as separator via fputcsv  fh   data array        does the trick

User · Answer

It is incredible that there are so many answers but none answers the question        When I was asking this question  I asked for a way of opening a UTF-8   CSV file in Excel without any problems for a user        The answer marked as the accepted answer with 200  up-votes is useless for me because I don t want to give my users a manual how to configure Excel  Apart from that  this manual will apply to one Excel version but other Excel versions have different menus and configuration dialogs  You would need a manual for each Excel version   So the question is how to make Excel show UTF8 data with a simple double click   Well at least in Excel 2007 this is not possible if you use CSV files because the UTF8 BOM is ignored and you will see only garbage  This is already part of the question of Lyubomyr Shaydariv          I also tried specifying UTF-8 BOM EF BB BF  but Excel ignores that     I make the same experience  Writing russian or greek data into a UTF8 CSV file with BOM results in garbage in Excel   Content of UTF8 CSV file   Colum1 Column2 Val1 Val2           T         Result in Excel 2007     A solution is to not use CSV at all  This format is implemented so stupidly by Microsoft that it depends on the region settings in control panel if comma or semicolon is used as separator  So the same CSV file may open correctly on one computer but on anther computer not   CSV  means  Comma Separated Values  but for example on a german Windows by default semicolon must be used as separator while comma does not work   Here it should be named SSV   Semicolon Separated Values  CSV files cannot be interchanged between different language versions of Windows  This is an additional problem to the UTF-8 problem   Excel exists since decades  It is a shame that Microsoft was not able to implement such a basic thing as CSV import in all these years     However  if you put the same values into a HTML file and save that file as UTF8 file with BOM with the file extension XLS you will get the correct result   Content of UTF8 XLS file    lt table gt   lt tr gt  lt td gt Colum1 lt  td gt  lt td gt Column2 lt  td gt  lt  tr gt   lt tr gt  lt td gt Val1 lt  td gt  lt td gt Val2 lt  td gt  lt  tr gt   lt tr gt  lt td gt           lt  td gt  lt td gt T       lt  td gt  lt  tr gt   lt  table gt    Result in Excel 2007     You can even use colors in HTML which Excel will show correctly    lt style gt   Head   background-color gray  color white     Red    color red     lt  style gt   lt table border 1 gt   lt tr gt  lt td class Head gt Colum1 lt  td gt  lt td class Head gt Column2 lt  td gt  lt  tr gt   lt tr gt  lt td gt Val1 lt  td gt  lt td gt Val2 lt  td gt  lt  tr gt   lt tr gt  lt td class Red gt           lt  td gt  lt td class Red gt T       lt  td gt  lt  tr gt   lt  table gt    Result in Excel 2007     In this case only the table itself has a black border and lines  If you want ALL cells to display gridlines this is also possible in HTML    lt html xmlns x  urn schemas-microsoft-com office excel  gt       lt head gt           lt meta http-equiv  content-type  content  text plain  charset UTF-8   gt           lt xml gt               lt x ExcelWorkbook gt                   lt x ExcelWorksheets gt                       lt x ExcelWorksheet gt                           lt x Name gt MySuperSheet lt  x Name gt                           lt x WorksheetOptions gt                               lt x DisplayGridlines  gt                           lt  x WorksheetOptions gt                       lt  x ExcelWorksheet gt                   lt  x ExcelWorksheets gt               lt  x ExcelWorkbook gt           lt  xml gt       lt  head gt       lt body gt           lt table gt               lt tr gt  lt td gt Colum1 lt  td gt  lt td gt Column2 lt  td gt  lt  tr gt               lt tr gt  lt td gt Val1 lt  td gt  lt td gt Val2 lt  td gt  lt  tr gt               lt tr gt  lt td gt           lt  td gt  lt td gt T       lt  td gt  lt  tr gt           lt  table gt       lt  body gt   lt  html gt    This code even allows to specify the name of the worksheet  here  MySuperSheet    Result in Excel 2007

User · Answer

Yes  this is possible  As previously noted by multiple users  there seems to be a problem with excel reading the correct Byte Order Mark when the file is encoded in UTF-8  With UTF-16 it does not seem to have a problem  so it is endemic to UTF-8  The solution I use for this is adding the BOM  TWICE  For this I execute the following sed command twice    sed -I  1s    xef xbb xbf     csv     where the wildcard can be replaced with any file name  However  this leads to a mutation of the sep  at the beginning of the  csv file  The  csv file will then open normally in excel  but with an extra row with  sep   in the first cell   The  sep   can also be removed in the source  csv itself  but when opening the file with VBA the delimiter should be specified   Workbooks Open name  Format  6  Delimiter       Local  True    Format 6 is the  csv format  Set Local to true  in case there are dates in the file  If Local is not set to true the dates will be Americanized  which in some cases will corrupt the  csv format

User · Answer

The bug with ignored BOM seems to be fixed for Excel 2013  I had same problem with Cyrillic letters  but adding BOM character  uFEFF did help

User · Answer

We have used this workaround      Convert CSV to UTF-16 LE Insert BOM at beginning of file Use tab as field separator

User · Answer

First save the Excel spreadsheet as Unicode text  Open the TXT file using Internet explorer and click  Save as  TXT Encoding - choose the appropriate encoding  i e  for Win Cyrillic 1251

User · Answer

This is an old question but I ve just encountered had a similar problem and the solution may help others   Had the same issue where writing out CSV text data to a file  then opening the resulting  csv in Excel shifts all the text into a single column  After having a read of the above answers I tried the following  which seems to sort the problem out   Apply an encoding of UTF-8 when you create your StreamWriter  That s it   Example   using  StreamWriter output   new StreamWriter outputFileName  false  Encoding UTF8  2  lt  lt  22               do stuff            output Close

User · Answer

You can convert  csv file to UTF-8 with BOM via Notepad      Open the file in Notepad    Go to menu Encoding Convert to UTF-8  Go to menu File Save  Close Notepad    Open the file in Excel     Worked in Microsoft Excel 2013  15 0 5093 1000  MSO  15 0 5101 1000  64-bit from Microsoft Office Professional Plus 2013 on Windows 8 1 with locale for non-Unicode programs set to  German  Germany

User · Answer

I am generating csv files from a simple C  application and had the same problem  My solution was to ensure the file is written with UTF8 encoding  like so      Use UTF8 encoding so that Excel is ok with accents and such  using  StreamWriter writer   new StreamWriter path  false  Encoding UTF8         SaveCSV writer       I originally had the following code  with which accents look fine in Notepad   but were getting mangled in Excel   using  StreamWriter writer   new StreamWriter path         SaveCSV writer       Your mileage may vary - I m using  NET 4 and Excel from Office 365

User · Answer

Simple vba macro for opening utf-8 text and csv files  Sub OpenTextFile       filetoopen   Application GetOpenFilename  Text Files    txt   csv     txt   csv      If filetoopen   Null Or filetoopen   Empty Then Exit Sub     Workbooks OpenText Filename  filetoopen       Origin  65001  DataType  xlDelimited  Comma  True  End Sub   Origin  65001  is UTF-8  Comma True  for  csv files distributed in colums  Save it in Personal xlsb to have it always available  Personalise excel toolbar adding a macro call button and open files from there  You can add more formating to the macro  like column autofit   alignment etc

[excel] Is it possible to force Excel recognize UTF-8 CSV files automatically?

Examples related to excel

Examples related to csv

Examples related to utf-8