26

I am working on a project that uses a CSV file supplied by the client to populate a database. The client is to create the spreadsheet, then save it as a CSV to be uploaded, which is then parsed into a database.

The problem is, whenever the client saves the excel spreadsheet as an MS-DOS .csv file, many of the special characters get converted to question marks "?" (symbols such as ' " / ). However, if we then open the CSV file and manually replace each ? with the right character it works fine.

The problem is that the data file is HUGE, and we can't reasonably do this, so I was hoping there was a way to save it. We have tried exporting as Unicode and ASCII to no avail. We have also tried uploading to google docs, and re-saving, however, it also breaks those characters.

3
  • 3
    If you are on excel 2007, in the File save as dialog there is just left of the save button a Tools drop down.If you choose weboption and on the tab Encoding you can select a specific encoding. Try if that is of any help
    – rene
    Aug 24, 2012 at 21:43
  • What is an example of that text?
    – enderland
    Aug 24, 2012 at 21:58
  • 1
    The problem is not reproducible. Make sure that the data is actually what you describe and not some characters that cannot be represented in the MS-DOS encoding. Aug 24, 2012 at 22:12

15 Answers 15

14

Ensure you are choosing to save as a CSV (Comma Delimited) and not a CSV (MS-DOS), as DOS doesn't support UTF-8 characters.

8

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.

1
  • 1
    This doesn't work for me. I still get ? in csv file
    – thanos.a
    May 6, 2016 at 8:27
4

A possible workaround is to save it as Unicode Text (2007 has it, not sure about previous editions), which saves it as a tab-separated text file.

This file preserved my unicode characters (in my case I was working with asian characters) while producing some sort of delimited text file which you can then run through external tools to convert to a csv if necessary.

My input did not have tabs embedded within each cell, however, and I am not sure how that would be handled.

4

Using Excel For Microsoft 365 on Windows.

I found 4 CSV options scattered around the save file options. For me it was the 5th option on the dropdown.

  • CSV UTF-8 (Comma-delimited) (*.csv)
  • CSV (Comma-delimited) (*.csv)
  • CSV (Macintosh) (*.csv)
  • CSV (MS-DOS) (*.csv)

You want to make sure you select the UTF-8 option, this fixed it for me.

1
  • Kudos! This worked for me!
    – NikSp
    Aug 19, 2023 at 7:11
3

I've been having this issue for a while now, and finally dedicated some time to figure it out! I was able to (seemingly) fix the issue by saving as "Windows Comma Separated (.csv)". I tried it from a .xlsx and a .xls, both converted over to a .csv just fine. Hope this helps - let me know if any issues pop up with this method. I'll report back if I see anything over the next few weeks.

1
  • 2
    Welcome to Super User. Glad to see you jump right in to answer questions.
    – CharlieRB
    Jan 14, 2016 at 16:53
3

Here's what works for me:

  1. Make data corrections in Excel or CSV
  2. Save file As Unicode Text
  3. Open Notepad
  4. Open the Unicode file you just saved using Notepad
  5. Use your cursor to highlight a blank area that holds a single tab 5a. Use the space between the Acquire Id and the Request Type because this holds ONE TAB!
  6. Hit Ctrl-C to copy the tab character
  7. Type Ctrl-H to open the Replace function box
  8. Click in the Find What text box and type Ctrl-V to paste the Tab
  9. Click in the Replace With Text box and type a comma
  10. Click Replace to test it one time. Confirm the tab in the file is replaced with a comma
  11. Click Replace All
  12. Click Cancel
  13. Save the file and Exit
  14. In Windows Explorer, change the file extension to .csv
2
  • 1
    This worked for me, using Notepad++.
    – ChrisB
    Apr 21, 2017 at 18:26
  • Yeah I had to export as Unicode and find and replace tabs to commas too. Sep 26, 2019 at 13:03
2

Solution:

  1. Open your CSV file in Notepad (you will notice it says ANSI), then re-save it as UTF-8 in Notepad.

  2. Then the import should work. If however, you open it again in Excel and just Save it won't work because Excel can't natively encode in UTF-8 apparently.

  3. The other option is to work with your CSV file in www.LibreOffice.org (free), which can encode correctly in UTF-8 (I haven't tried this myself though).

I ran into a similar issue with importing a csv of products with fractions into WooCommerce, which was getting rejected because of "?" error characters. This is because the CSV file was not encoded in UTF-8. HOWEVER, even after saving the CSV file in Excel with UTF-8 encoding it still didn't work.

After scratching through multiple forums, it appears that although Excel gives the option to encode as UTF-8 - when SAVING AS A CSV, Excel automatically converts it back to ANSI, even though you select UTF-8.

1

Found the best solution ever: http://woshka.com/blog/microsoft/microsoft-excel/solve-the-problem-saving-excel-csv-format-with-utf-8-unicode-encoding.html

From link:

1-Click on the start menu

2-Select control panel

3-Find Regional and Language options on classic mode or type its name on the search bar on just top right of the control panel window

4-Click on advanced tab and click locales

5-Click on Persian or Arabic or your desired UTF-08 encoding program that you’d like to save with Excel into CSV

1
  • woshka.com is no longer a valid URL, it's a parked domain at the moment. Mar 14, 2018 at 3:21
1

for the ™ I found a solution. In the .xlsx file, replace all "™" with "&tr-ade;". remove the - from the replace with. Save the file as .csv and all is done. wish it works for you.

1
  1. Download and install Unicode CSV Addin for excel from https://github.com/jaimonmathew/Unicode-CSV-Addin
  2. Save the csv from the new "Unicode CSV" menu as shown in picture below.

enter image description here

2
  • 1
    Would you post a link to this add-in please? Oct 2, 2019 at 12:54
  • I have updated the answer to include the github page. There you can find the download page
    – thanos.a
    Oct 9, 2019 at 15:37
0

Even i faced the issue with special characters while downloading the japanese terms in .csv format.However when i saved the .csv file in Text format(Tab delimited) the japanese characters where populating perfectly. Then i just copied data from text file and pasted in a spreadsheet. Worked well!!!

Thanks, Vaishakh

1
  • This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post.
    – Ramhound
    Jul 21, 2015 at 14:59
0

On a Mac computer, this is what worked for me.

On excel choose save as and then from the drop-down choose windows comma separated (CSV).

It just works!!!

0

Save as unicode Rename unicode file as .csv (https://www.youtube.com/watch?v=1VP8__shxTg)

1
  • Welcome to Super User! Whilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference.
    – bertieb
    May 30, 2018 at 14:50
0

Saving as CSV (Comma Delimited) as Techie007 mentioned above will properly capture most international characters; however, I was still having problems a few to include Ł, ć, and ń (there may be others).

I was a little surprised to see that the EXCEL VBA function ASC returns the ascii code associated with L, c, and n respectively for these international characters. My somewhat unsatisfying approach was to write a short macro to convert all characters before saving out to a .csv. [Note: ASC for a vast majority of common international characters such as é or á will work just fine]

0

Saving the csv file in ANSI encoding using nodepad solved the issue for me (worked for latin and spanish special characters as well)

1
  • What notepad has to do here. It's an Excel question.
    – Toto
    Aug 3, 2022 at 10:24

You must log in to answer this question.