1

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?

Improve this question
3
  • 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.
    – Run CMD
    Dec 14, 2015 at 8:39
  • Also, what is your Text qualifier set to ?
    – Hennes
    Dec 14, 2015 at 8:41
  • import from text -> check tabstop -> uncheck comma -> finish
    – Dirk Reichel
    Dec 14, 2015 at 8:43

2 Answers 2

Reset to default
2

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"
Improve this answer
4
  • 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 :/
    – Dirk Reichel
    Dec 14, 2015 at 8:57
  • 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.
    – Hennes
    Dec 14, 2015 at 9:00
  • 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 :(
    – Dirk Reichel
    Dec 14, 2015 at 10:48
  • 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.
    – Hennes
    Dec 14, 2015 at 10:52
0

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)

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .