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?

  • 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 '15 at 8:39
  • Also, what is your Text qualifier set to ? – Hennes Dec 14 '15 at 8:41
  • import from text -> check tabstop -> uncheck comma -> finish – Dirk Reichel Dec 14 '15 at 8:43
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"
  • 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 '15 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 '15 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 '15 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 '15 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)

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.