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.
- Open your csv in a text editor that understands the encoding (e.g.
sublime, notepad++, etc.)
- Open a new Excel work book
- Copy the entire content of the csv, and paste into cell A1
- 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: