Excel: comma in tab-delimited csv file

Question

I have a tab-delimited csv file:

"a" "b"
"c,d" "e"

Excel renders the second row until comma and the rest of the row is not rendered. I enclose each cell in double-quotes but it doesn't help. Is there a way to fix it?

• Run CMD: Welcome to SuperUser. It might be help the volunteers here if you include information such as your Excel version and the import settings you tried without success.
• Hennes: Also, what is your Text qualifier set to ?
• Dirk Reichel: import from text -> check tabstop -> uncheck comma -> finish

Answer 1

I copied your two demo lines to a text file and opened that in excel 2013:

Setting used:

• Delimited (not fixed width)
• Seperator is space.
• Text qualifier set to "

First step Text qualifier

I get a properly imported set of four elements, as shown below:

Excel source file and import results

---

Some wrapper script like below might work.

(Attempt to add a line to the cvs file with the SEP indicator and then to start it with excel. Might work. Might because I have no experience with batch files and this is created with the help of some googling and some WAGs).

echo off
echo Creating a file with sep
echo "SEP= " > %tmp%\Sepfile.txt
copy %tmp%\Sepfile.txt + %1 %tmp\%1
start "" "C:\Program Files (x86)\Microsoft Office\Office14\excel.exe" "%tmp\%1"

• Dirk Reichel: as my comment -> this is for importing... but the csv will be auto-seperated when opening via double click... having an option which can be set to "open all csv with *** seperator" would be helpfull :/
• Hennes: There are three ways to auto that: 1) I set it in regional options (via control panel). This will be set for all applications. 2) Add a line to all cvs files with SEP= (but that requires modifying all files or writing a wrapper script. 3) or IIRC excel has an override local settings somewhere. I will need to look that up.
• Dirk Reichel: trying to get nasty... doing it the right way you could change the open-command for csv-files to directly throw the file from command-prompt to excel having excel "think" there is the sep= in the first line... while it is possible, i stopped impoving my skills that way to long ago... so i need to give it a goodbye here :(
• Hennes: It might be possible, but that is also beyond my skills. A workaround by copying the file (which I do not even attempt to cleanup aferwards) is ugly. But possible just within writing skills. Still, it has a lot of WildAssGuesses in it. I might try it on a windows PC once I get home.

Answer 2

make the first line

sep= 

after the = is a tabstop (you simply cant see here) ^^;

you also can use sep=; to change the seperator to ; (or whatever seperator you want to use)