How to convert Excel files to CSV (comma delimited) and CSV UTF-8

The article explorers quick and efficient ways to export data from Excel to CSV keeping all special characters and foreign symbols intact. The methods work for all versions of Excel, from 365 to 2007.

Comma separated values (CSV) is a widely used format that stores tabular data (numbers and text) as plain text. Its popularity and viability are due to the fact that CSV files are supported by many different applications and systems at least as an alternative import/export format. The CSV format allows users to glance at the file and immediately diagnose the problems with data, change the delimiter, text qualifier, etc. All this is possible because a CSV document is plain text and an average user or even a novice can easily understand it without any learning curve.

CSV formats supported by Excel

Microsoft Excel allows saving a file in a few different CSV formats, and you may be curious to know the differences between them.

In essence, each CSV format saves data as comma-separated values but performs encoding in a slightly different way. For example, Mac uses a single carriage return (<CR>) represented by \r for a line break, while Windows uses a combination of carriage return and line feed (<CRLF>) represented by \r\n.

To correctly export data to other programs, Excel lets you choose the CSV formatting that best matches the program's expectations.

Here are the CSV options available in Excel 365. In your version, the list may look a little different.
CSV formats supported by Excel

CSV (comma delimited). This format saves data in a comma-separated text file that can be used in another Windows program or another version of the Windows operating system.

CSV (Macintosh). This format saves a workbook as a comma-separated file for use on the Mac operating system.

CSV (MS-DOS). Saves as a comma-separated document for use on the MS-DOS operating system.

CSV UTF-8 (comma delimited). It is Unicode Transformation Format 8-bit encoding that supports many special characters, including hieroglyphs and accented characters, and is backward compatible with ASCII. This format is recommended for files that contain any non-ASCII characters since the classic CSV format destroys them.

Besides CSV, there is one more format that may come in extremely handy for communicating with other programs.

Unicode Text (*.txt). This is a computing industry standard supported by almost all current operating systems including Windows, Macintosh, Linux and Solaris Unix. It can handle characters of almost all modern languages and some ancient ones.

Note. By strict definition, the CSV format implies separating values with commas. In reality, you may come across many other delimiters, a semicolon and tab being most common.

How to convert Excel file to CSV

When Excel data is to be transferred to some other application such as the Outlook Address book or Access database, the easiest way is to save your worksheet as a .csv file, and then import that file to another program.

To save an Excel file (.xlsx or .xls) in the CSV format, here are the steps you need to follow:

  1. In your workbook, switch to the target worksheet as only the active sheet will be converted.
  2. On the File tab, click Save As. Or press the F12 key to open the Save As dialog.
    Switch to the File tab and click Save As.

  3. In the Save As dialog box, pick the desired CSV format from the Save as type drop-down menu, On Windows, you'd choose either CSV (Comma delimited) or CSV UTF-8.
  4. Pick the destination folder and hit Save.
    Saving an Excel worksheet as a CSV (Comma delimited) file

In case your worksheet has any formatting, formulas, charts, shapes or other objects, you will be informed that some features in your workbook might be lost if you save it as CSV (Comma delimited). If that is Okay, click Yes to complete the conversion without the unsupported features.
Some features in your workbook might be lost if you save it as CSV.

Export Excel to CSV without destroying special characters

If your spreadsheet contains some special symbols, smart quotes or long dashes (e.g. inherited from a Word document), foreign characters (tildes, accents, etc.) or hieroglyphs, the method described above won't work.

The point is the saving to the CSV (comma delimited) format distorts any characters other than ASCII (American Standard Code for Information Interchange).

To keep non-ASCII characters undamaged, a document should be saved to a format that uses a Unicode character encoding. There exist two Unicode encoding forms: 8-bit (UTF-8) and 16-bit (UTF-16).

Before we move to the exporting steps, let us point out the key features of each encoding, so you can choose the format right for a particular case.

UTF-8 is a more compact encoding since it uses 1 to 4 bytes for each symbol. Generally, this format is recommended if ASCII characters are most prevalent in your file because most such characters are stored in one byte each. Another advantage is that a UTF-8 file containing only ASCII characters has absolutely the same encoding as an ASCII file.

UTF-16 uses 2 to 4 bytes to encode each symbol. However, a UTF-16 file does not always require more storage than UTF-8. For example, Japanese characters take 3 to 4 bytes in UTF-8 and 2 to 4 bytes in UTF-16. So, you may want to use UTF-16 if your data contains any Asian characters, including Japanese, Chinese or Korean. A noticeable disadvantage of this format is that it's not fully compatible with ASCII files and requires some Unicode-aware programs to display them. Please keep that in mind if you are going to import the resulting document somewhere outside of Excel.

Once you've decided on the format, the below instructions will walk you through the process.

How to convert Excel to CSV UTF-8

Suppose you have a worksheet with some foreign characters, Japanese names in our case:
Source data

Depending on the Excel version you are using, it may take 3 to 5 steps to convert this file to CSV keeping all special characters.

Export to CSV UTF-8 in Excel 2016 - 365

In Excel 2016 and later versions, you can save a file in the CSV format with UTF-8 encoding directly:

  1. In the target worksheet, click File > Save As or press the F12 key.
  2. In the Save As dialog box, select CSV UTF-8 (comma delimited) (*.csv) from the Save as type drop down.
  3. Click the Save button. Done!
    Exporting a sheet to CSV UTF-8 in Excel 2016 - 365

Convert to CSV UTF-8 in Excel 2013 - 2007

As older Excel versions do not support the UTF-8 encoding, you'll need to save your document in the Unicode Text format first, and then convert it to UTF-8.

To export an Excel file to CSV and preserve special characters, follow these steps:

  1. In your worksheet, click File > Save As or press F12.
  2. In the Save As dialog box, choose Unicode Text (*.txt) from the Save as type drop-down menu, and click Save.
    Save your Excel workbook as Unicode Text (*.txt) file.

  3. Open the txt document using your preferred text editor, for example Notepad.
    Note. Some simple text editors do not fully support all Unicode characters, therefore certain characters may be displayed as boxes. In most cases this won't affect the resulting file, so you can simply ignore this or use a more advanced text editor such as Notepad++.
  4. Since the txt file is tab-delimited while we aim for a comma-separated file, replace the tabs with commas. Here's how:
    • Select any tab character, right click it and choose Copy from the context menu, or press the Ctrl + C key combination.
      Copy the tab character in a txt document.

    • Press Ctrl + H to open the Replace dialog box and paste the copied tab (Ctrl + V) in the Find what field. After you've done this, the cursor will move rightwards indicating that the tab is pasted. Type a comma in the Replace with field and click Replace All.

      If you want a semicolon-delimited CSV, then replace tabs with semicolons.
      Replace all tabs with commas.

      If all done right, your resulting txt file should look similar to this:
      A comma separated text file

  5. In Notepad, click File > Save As and do three important changes:
    • In the File name box, change the .txt extension to .csv.
    • In the Save as type box, pick All files (*.*).
    • In the Encoding drop-down menu, select UTF-8 with BOM.

    When done, click the Save button.
    Change the encoding to UTF-8 with BOM.

Tips and notes:

  • The byte order mark (BOM) is a sequence of bytes at the start of a text stream that indicates Unicode encoding of a text document. In case of UTF-8 with BOM, the sequence 0xEF,0xBB,0xBF signals the reading program that UTF-8 encoding is used in the file. The Unicode standard permits but does not require the BOM in UTF-8. However, it is often crucial for correct UTF-8 recognition in Excel, especially when converting from Asian languages.
  • If your text editor does not allow changing the file extension, you can do that in Windows Explorer.

Now, you can open the CSV file in Excel and make sure all data is rendered correctly:
Excel sheet converted to CSV UTF-8

Note. If your file is intended for use in another application where the UTF-8 format is a must, do not make any edits nor save the file in Excel as this may cause encoding problems. If some data does not appear right in Excel, open the file in Notepad and fix the data there. Remember to save the file in the UTF-8 with BOM format again.

How to convert Excel file to CSV UTF-16

Exporting to CSV UTF-16 is done very much the same way as to CSV UTF-8:

  1. Save the workbook in the Unicode Text (*.txt) file format.
  2. Open the .txt document in a text editor such as Notepad and replace all tabs with commas.
  3. Change the file extension to .csv, make sure encoding is set to UTF-16 LE, and save the file.
    Converting Excel file to CSV UTF-16

Convert multiple Excel sheets to CSV

As already mentioned, Excel's Save As command is only able to convert an active worksheet. But what if your workbook contains a lot of different sheets, and you wish to turn them all into separate csv files? The only alternative suggested by Microsoft is saving each sheet under a different file name, which does not sound very inspiring, huh?
CSV does not support workbooks with multiple sheets.

So, is there a quick way to save multiple Excel sheets as CSV at once? Yes, it can be done with VBA.

The below code converts all worksheets in the current workbook to individual CSV files, one for each sheet. The file names are created from the workbook and sheet names (WorkbookName_SheetName.csv) and saved to the same folder as the original document.

Sub ConvertMultipleCSV()
	Dim ws As Worksheet
	Dim path As String

	Application.ScreenUpdating = False
	path = ActiveWorkbook.path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)

	For Each ws In Worksheets
		ws.Copy
		ActiveWorkbook.SaveAs Filename:=path & "_" & ws.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False
		ActiveWorkbook.Close False
	Next

	Application.ScreenUpdating = True
End Sub

Please keep in mind that the above code saves sheets in the CSV format. If there are any non-ASCII characters in your data, then you need to convert to UTF-8 CSV. This can be done by changing the file format from xlCSV to xlCSVUTF8. That is, you replace FileFormat:=xlCSV with FileFormat:=xlCSVUTF8.

Also, remember that CSV UTF-8 conversions are possible in Excel 2016 and higher.

The following guidelines will help you with adding the macro to your workbook: How to insert and run VBA code in Excel.

Apart from the methods described above, there exist a handful of other ways to convert Excel sheets to CVS. Below, I will share a couple of my favorite ones.

Excel to CSV via Google Spreadsheets

The use of Google Spreadsheets for .xlsx to .csv conversions seems a very simple workaround:

  1. In Google Spreadsheet, click File > Import.
    Import your Excel file to Google Spreadsheets

  2. Click Upload and drag-and-drop the file or select from your computer, and then click Import data.
    Upload the file and import data.

    Tip. If you have a relatively small dataset, it may be easier to copy/paste it directly in the spreadsheet.
  3. Go to the File menu > Download > Comma-separated values (.csv, current sheet).
    Download a Google spreadsheets as a comma-separated values .csv file.

Open the downloaded file in some text editor to make sure all the data is exported right.

Note. If your original Excel sheet contains special characters, the resulting CSV file may not display the characters correctly when opened in Excel, though it looks perfect in many other spreadsheet programs.

Save .xlsx to .xls and then convert to .csv

This method of converting Excel to CSV hardly needs any further explanations because the heading says it all :)

I came across this solution on some forum, cannot remember which exactly. To be honest, this method has never worked for me, but many users reported that special characters, which got lost when saving .xlsx directly to .csv, are preserved if to save a .xlsx file to .xls first, and and then save .xls as .csv as explained in How to convert Excel to CSV.

Anyway, you can try this method of exporting Excel to CSV on your side and if it works, this can be a real time-saver.

Convert Excel to CSV using OpenOffice

OpenOffice is an open-source suite of six applications. One of them is a spreadsheet app named Calc, which is really good at exporting spreadsheet data to the CSV format. In fact, it provides more options (encodings, delimiters, etc.) than Microsoft Excel and Google Sheets combined.

To convert your Excel file to CSV, follow these steps:

  1. Open your Excel document with OpenOffice Calc.
  2. Click File > Save as… and choose Text CSV (.csv) from the Save as type drop-down menu.
  3. Next, you will be asked to define encoding and delimiters. If your goal is the CVS format that correctly handles special characters, then choose:
    • Unicode (UTF-8) for Character set.
    • Comma for Field delimiter. If you need a semicolon-delimited csv file, then select semicolon (;) or whatever delimiter you want.
    • Quotation mark for Text delimiter.

    To complete the conversion, click OK.
    Convert an Excel file to CSV using OpenOffice.

It would be really nice if Excel provided similar options to perform fast and painless CSV conversions, wouldn't it?

These are the ways of converting Excel to CSV I am aware of. If you know other more efficient methods, please do share in comments. Thank you for reading!

You may also be interested in

70 comments to "How to convert Excel files to CSV (comma delimited) and CSV UTF-8"

  1. Shinoy Sharma says:

    Hey i tried the above approach. But when i convert it to csv extra column are added with empty values in it. Can someone help? I tried xlcsvutf8 approach also

  2. frg-tech says:

    I am searching for an easy way to just export the current sheet to csv, but keep my xlsx workbook intact. I frequently save as csv and forget to then save my workbook as xlsx and loose all of my work. Am I missing an easier way?

  3. Sylvie Gadoury says:

    I've saved an excel sheet under csv to export it but when I import it in my company's software, it adds '' in front of the first cell on each row. This causes problem.

    It wasn't doing it when I was doing the same thing with Excel 2007. This problem only started when I upgrade to Excel 2010.

    Any idea how I could get rid of this ''?

    Tks,

  4. ACHINTYA MONDAL says:

    I have 1500 Excel file , if I change one by one need 3 day for convert , I am searching at a time convert . kindly help me.

    Thanks
    Achintya Mondal

  5. babajan says:

    hii i m facing problem in creating sending , even i m giving 6 - 10 alphanumeric but not accepting wat to do?? help me

  6. Jill says:

    Hi there
    Thanks so much for this - after battling to simply 'save as csv.' the option of converting xlsx to xls to csv. helped as I was attempting to import a database of email addresses into GMAIL. What a relief! THANK YOU!!

  7. Paryana says:

    Hello,IAM facing a problem while saving the file in csv format it says some feature loss will be there and yes after loading the data in weka environment IAM only getting 1feature out of 41features...if any body can help please....???

  8. Mastaria says:

    Dear Sirs/Madam,
    When I try to save as excel to csv, point no. 5 did not appear:
    "Clicking OK in the first dialog will display a second message informing you that your worksheet may contain features unsupported by the CSV encoding. This is Okay, so simply click Yes."
    Your assist is really appreciate

  9. Antero says:

    Hello
    I have a problem when I try to convert Excel spreadsheet to CSV and cell containing text inside quotes like "Hello" converts always as """Hello""" in the CSV file. I'm running out of ideas, can you please help me to find the way to keep the data unchanged, Thanks. Antero

  10. Scott Hardwick says:

    Would love for AbleBits to create an AddOn that gave us all the ways to export CSVs including choosing delimiters and separators!

  11. BT says:

    Hello, Thanks for posting about "Save .xlsx to .xls and then convert to .csv file"! This worked very well for me! Other methods were creating csv files that were not opening in Excel in the way that I wanted.

  12. Peter Tabone says:

    If you populate two cells in Excel (.xlsx), say CELL A1 = FIRST and CELL A2 = LAST. Question:

    How do you convert that to a .csv file that looks as follows: “FIRST”;”LAST”

  13. subash says:

    how to modify this type of bulk neft format in CSV format.how to insert this type of symbol. ~ 8926~03221035555558~ICIC000000060~007701505649~11~KUMARK~CHENNAI~POWERBIKES~IDBISPR

  14. Ankit Kumar says:

    Hello ! ''
    Could someone guide me how to convert Excel ".xlsx" file to UTF-8 format so that I can upload on google analytics.

  15. Orton says:

    Openoffice Calc saves csv files as UTF-8 properly, so I saved my Excel file using Excel as an OpenDocument Spreadsheet (.osd) and opened it in Calc and then saved it as as a .csv file with UTF-8 encoding. Works well.

  16. ABU mASTER says:

    I want convert microsoftexcell file to csv as ash-star-dot(csv coma delimitted). Please help me.I am using 2007 version

  17. レム says:

    thanks for the google sheet suggestion works like a charm and the default encoding is really wonderful.

    great for csv upload to oracle java

  18. Carey says:

    Is there a way to convert an Excel file that has multiple worksheets into CSV format?

    It looks like it will only convert one sheet!

    Thanks!

  19. shraddha says:

    Hi there,
    I was trying to make changes in the EXCEL file and save as .csv file format. But the .csv file is not saving the changes I made in the excel. SO the error I am trying to rectify in excels is not getting changed in .csv

    Please let me know how to do this.

    Thanks,
    Shraddha

  20. gaby says:

    I have a xls file that contains columns with no data, how can I preserve those "spaces" while doing the replace you explained...

  21. Arv says:

    The solution to convert excel to a unicode text and then replacing the delimiter tab with comma or any other user specific delimiter should work. But what if my data is having tab in between the text? Can you suggest a way to convert those excel to UTF encoded CSV or a custom delimited file?
    Thanks.

  22. vikram kumar says:

    hi
    i am typing in Hindi in excel csv formate using Kruti Dev 040 and save.when ever open the same file then hindi text automatically converted in English.so how to solve this problem.

  23. Srini says:

    Hi,

    How to remove special character while saving xls file into csv file.
    IT Leaders �trusted service providers� with system.

    Thanks
    Srini

  24. Petra says:

    Excellent, the section "How to convert Excel to CSV UTF-8" was precisely the solution that I needed for my problem!
    Excel kept saving the csv with semicolons instead of commas, despite having changed the regional settings as had been recommended elsewhere. With this work around I did get the right csv file to import in another system.

    Thank you!

  25. Muhammad Basharat says:

    I am facing the same issue as that by Muhammad Farooq. I repeat here, when I save my Excel file as csv (Comma delimited) and reopen in Excel, again it is separate columns, instead of being in one column. I have tried many times but not successful.

    Kindly, explain what wrong I am doing.

    Much obliged in advance

  26. Muhammad Farooq says:

    Hi,
    Tricky problem : I saved my excel file into csv type. All is ok but when I reopen the csv doc the layout is still in table. No comma.
    I checked the properties : it is really a .csv
    Do you have any idea of what I missed ?
    Thank you in advance for your support.
    Best Regards, S..

  27. 421 says:

    Hi,
    Tricky problem : I saved my excel file into csv type. All is ok but when I reopen the csv doc the layout is still in table. No comma.
    I checked the properties : it is really a .csv
    Do you have any idea of what I missed ?
    Thank you in advance for your support.
    Best Regards, S..

  28. Giannis says:

    This section "How to convert Excel to CSV UTF-8" really worked for me, after many unsuccessful attempts trying almost anything, using both Excel and Calc programmes.

    Save excel file as txt with Unicode, replace, in this txt, tabs with commas and save as csv (UTF-8). CSV file now has all special characters, COOL and many thanks!

  29. Penny says:

    I am trying to convert Excel to .csv format so I can import into a database. I followed all the steps you listed. However, when I open the .csv file in Notepad to make sure the formatting is correct, I find after the last line of data there are hundreds of commas running down the left side.

    A co-worker does not have this problem when she tries it on her PC. Which makes me think there is a setting of some kind in Excel or an add-in not enabled that I need on my PC. Our IT staff are not familiar with the "inner workings" of Excel can't really help with this issue.

    Can you help? Thanks.

  30. Eric Crawford says:

    You are awesome! I've been working on this for a couple hours and banging my head against the wall but did not try to Notepad>Find and Replace thing. Worked like a charm. Also, thank you for your explanations as to why using foreign languages cannot be done the 'above way' and then you perfectly explained how to do it below. Kudos!

    Literally relieved - and my coworkers will be relieved into the future as well!

    Thank you again,

    Eric

  31. marta says:

    Really good article! Thanks!
    I followed your instructions to import contacts containing Japanese characters, however I had the following problem:

    Japanese characters appear in symbols in Outlook 2013, like this -> Iwasaki 岩崎(幸)

    I checked csv converted file opening again in Excel 2013 and Japanese characters were readable. However, not when imported to Outlook 2013.

    Could you help me in this last step?

    Thanks

  32. Seunope says:

    Wow! Great stuff, nice work.

    Is it possible to preserve mathematical formulas in an excel sheet when you transfer it to csv format. For example X raise to power 2(X superscript 2) in excel will become X2 in csv. Any idea on how to fix this..

  33. eko says:

    Hi,
    I just want save my excel 2010 as csv file and close it.
    but the converted csv file always show 000000 after the 15th character on the cell.
    How to fix it, show I have the correct data on the cell such as in xlsx and csv format. thanks...

  34. Habtamu Imiru says:

    This week I was working on some project that uses excel sheet, dbf and ASCII formated data and I was a bit challenged as how to convert data from excel 2007 to the other formats it does not directly have by itself.

    Finally, I went to GOOGLE search with the question I had and wrote my question on the search bar of google window.

    Automatically,my problems are resolved by reading the suggestions forwarded by respective experts in the subject matter and I am done with my problem and also I am comfortable using the excel 2007.

    Thank you so much and I will come to you with questions as I proceed with my project in the preceeding time.

    With Regards,
    Habtamu

      • Hardeep Kaur says:

        Hi Svetlana,

        I have been trying using double quotes to ignore the commas within the data value, but still not successful. Will be grateful if you can help me out.
        P.S. I am using office 2010 (Microsoft Excel Comma Separated Values File (.csv))

  35. avinash says:

    I have a excel where data ias maintained in 4 diffrent column for example
    Sr_NO NO_BOR TITLE BOR_S_NAME
    1 2 Mrs. JOSHI
    How do i bring 3rd and 4th column of the row to next new line while converting excel to csv.

  36. Aleks says:

    Hello,

    Sorry about being a bit off the topic, but really need help.
    I am trying to save an Excel spreadsheet to a tab delimited text file. Information in the spreadsheet contains french accent symbols. They appear alright in the spread sheet, but after saving a tab delimited file these symbols turn into question marks.
    Now the most interesting part - i have changed a setting in tools (next to save button) to unicode and unicode utf-8, but none of them changed a thing and.

    I would really appreciate if someone could help me out with this.

    Thanks,
    Alex.

  37. Phivos Constantinou says:

    Hi there,

    They send me a CSV file in which the information is separated into columns. Once I open it on my PC the information per line is displayed in one cell per line and all columns are distringuished with a comma between them (i.e. a comma delimited CSV file), although the person that created it had the information separated into columns. I do not want to use every time the function Data>Text to columns in order to bring the information back to columns.

    What is wrong with my excel 2007 that I have installed and the CSVs sent to me are only opened in this comma delimited mode?

    Regards,

Post a comment



Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)