12

I have a tab separated value file that I want to edit in Excel. When Excel saves the file it modifies any column with quotes. It puts quotes around the whole column, and then double any quotes within the content of the column ( " -> "" ).

I don't want quotes around my columns and I don't want my quotes within columns escaped. The program that reads this file is not expecting quotes around the columns. Is there anyway to save or export the file without these added quotes?

wilson
  • 5,222
BlueWaldo
  • 315

3 Answers3

21

To save Excel file into tab separated file (without quotes added):

  1. In Excel, press CtrlA to select all
  2. Press CtrlC to copy
  3. Open Notepad, press CtrlV to paste
  4. Press CtrlS to save and it is done
wilson
  • 5,222
3

Find & Replace this: (")
With a null value: ()
Manually.

Then,

Find & Replace 'All' of this ("")
With this: (")

wilson
  • 5,222
NginUS
  • 410
0

There is no possibility for Excel to do this. I had the same problem and ended up writing a sed script to convert it. (sed is also available for windows)

sed "s/^\"//g;s/\"$//g;s/\t\"/\t/g;s/\"\t/\t/g;s/\"\"/\"/g" -i your-file.txt

It basically consists of multiple search&replace tasks s/search/replace/g (g=global=replace all). First, it searches for all surrounding quotes and finally replaces all double quotes with a single quote.

masgo
  • 2,324