16

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
Improve this question
4
  • 1
    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, 2011 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, 2011 at 16:31
  • Exporting Access to Excel is a trivial step. Formatting your text is the number one priority.
    – surfasb
    Aug 5, 2011 at 16:50
  • 4
    Can not install access on the computer that I need to use to do this.
    – Unreason
    Aug 5, 2011 at 23:47

6 Answers 6

Reset to default
4

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.

Improve this answer
1
  • I also have a CSV file in UTF-8, and Excel can either parse the text of this file correctly, XOR parse the columns correctly, but not both. Reading the file in LibreOffice and exporting as an Excel xlsx file was the only way I could find to import it into Excel ungarbled.
    – mikebridge
    Jun 27, 2018 at 18:25
3

What you ask is not possible. The only real solution is for Microsoft to either:

  • create a setting that allows the user to specify what the default encoding of CSV is when OPENING them
  • fix the bug in the Text Import Wizard so that it correctly handles multiline values

However there are some workarounds.

My favourite:

  1. Open your csv in a text editor that understands the encoding (e.g. sublime, notepad++, etc.)
  2. Open a new Excel work book
  3. Copy the entire content of the csv, and paste into cell A1
  4. Excel automatically does the right thing (encoding + newlines)

Other workarounds are less elegant and rely on manipulating the file:

To use the Text Import Wizard you would have to remove all the new line characters from your CSV file. If you created the file programatically you could edit it to omit the newline characters, if you received it from elsewhere it would be trivial to write a python script to strip the newlines characters out.

To use the standard method of just opening the file (e.g. by double click) you should transcode it to whatever the default encoding is that Excel prefers. If you can control the creation of the file (or ask the creator to create it with the desired encoding), then it's easy. Otherwise a python solution is trivial, but again more work

(HINT: you can find out which encoding Excel is expecting by opening the Text Import Wizard and seeing what the preselected option is)

Ultimately it depends on how often you are receiving / creating these files, the best workaround to deal with this issue would be to create the files in the default encoding Excel expects, so you can just double click to open.

Relevant post on stack overflow: https://stackoverflow.com/questions/2668678/importing-csv-with-line-breaks-in-excel-2007

Improve this answer
2

Another trick is to import via a query. Try selecting Data > New Query > From File > From CSV. Either create a new tab or find a spot to let it populate. One advantage with this method is you can do some formatting, slicing, and dicing on the front end.

The query tool has the feature to ignore line feeds in quotes; which the normal "from text" import seems to lack. Go figure, it's Microsoft.

Improve this answer
1
  • This is the solution: uses only Excel and no need to do anything on the data beforehand.
    – Indigo
    Oct 16, 2022 at 8:46
0

try to encode the file in Notepad. Just open the CSV file in Notepad and click on "Save as" in the file top menu. Then in the Save as dialog box choose UTF-8 and save the file. Now you can open the file with Excel with UTF-8 encoding. Best regards, Lukas

Improve this answer
0
  1. Open both your CSV file and a new Excel file next to each other.
  2. Copy all the content in the CSV cells (Ctrl+A then Ctrl+C).
  3. Move to the new excel file; paste the content into the empty Excel file (Ctrl+V).
Improve this answer
1
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    May 11, 2022 at 11:59
-1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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