Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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 '12 at 21:43
    
What is an example of that text? –  enderland Aug 24 '12 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. –  Jukka K. Korpela Aug 24 '12 at 22:12
add comment

migrated from stackoverflow.com Aug 25 '12 at 16:15

This question came from our site for professional and enthusiast programmers.

4 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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 Cnrl-C to copy the tab character
  7. Type Cnlr-H to open the Replace function box
  8. Click in the Find What text box and type Cnlr-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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.