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 have a csv file (comma delimited and quoted). When csv file is opened directly from explorer excel correctly interprets the cells that are mutliline, but it messes up the character encoding (utf-8).

Therefore I have to use import function (Data/Get External Data/From Text). However, when I use import text function in excel (where I can set file encoding explicitly) it interprets the newline as start of the new row instead of putting multiline text into a single cell and breaks the file layout.

Can I somehow overcome the situation by either

  • forcing the explorer open command to use 65001: Unicode (UTF-8) encoding
  • forcing the Text Import Wizard to ignore quoted line breaks as record delimiters
share|improve this question
    
Have you tried importing into Access? Access give more options into importing text than Excel, making it a more favorable tool for CSV files. –  surfasb Aug 5 '11 at 16:28
    
@surfasb, I will try, but ultimately that is not an option. I am looking for a solution from with existing excel functionality. –  Unreason Aug 5 '11 at 16:31
    
Exporting Access to Excel is a trivial step. Formatting your text is the number one priority. –  surfasb Aug 5 '11 at 16:50
2  
Can not install access on the computer that I need to use to do this. –  Unreason Aug 5 '11 at 23:47
add comment

2 Answers

I guess I should post this as an answer instead.

Have you tried importing into Access? Access give more options into importing text than Excel, making it a more favorable tool for CSV files.

@surfasb, I will try, but ultimately that is not an option. I am looking for a solution from with existing excel functionality.

Exporting Access to Excel is a trivial step. Formatting your text is the number one priority.

share|improve this answer
add comment

Use LibreOffice to open the file, then save in desired format: I had exactly the same problem you described when trying to use Excel 2010 to read UTF-8 MySQL data with multi-line Japanese text in some fields exported as quoted CSV with \r\n used for end-of-record (tried \r and \n also with no difference in Excel's behaviour). LibreOffice 4.1.3 imported the CSV file correctly, and I could save it in Excel 2010 xlsx format and thereafter open the xlsx file correctly in Excel.

share|improve this answer
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.