8

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
  • 4,902
  • 2
  • 22
  • 39
BlueWaldo
  • 275
  • 1
  • 3
  • 7

3 Answers3

16

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
  • 4,902
  • 2
  • 22
  • 39
  • Bloody genius! Works! Saved my bacon. Wilson, how in the world did you learn this?? – Doug Null Jan 05 '18 at 18:48
  • Whoops. Even using this method, Excel still erroneously inserts " at the beginning of cell in column-A if row follows a group. – Doug Null Jan 05 '18 at 19:22
  • @DougNull what does it mean by "row follows a group"? – wilson Jan 08 '18 at 04:13
  • If you manually do like this, you might end up with a bad encoding and when you read the same file in python you might get error like 'utf-8 codec can't decode byte.....' – Rajarshee Mitra Jul 26 '19 at 10:40
3

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

Then,

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

wilson
  • 4,902
  • 2
  • 22
  • 39
NginUS
  • 410
  • 2
  • 8
  • 1
    If the Excel file is long, there will be a lot of human work. – wilson Nov 02 '10 at 06:45
  • 3
    One option can be to replace ("") to a character which won't be there in the tab delimited file like (~) or (^), then to replace " to () and then finally (~) or (^) to ("). – jhamu Nov 02 '10 at 16:04
  • @jhamu, nice improvement to reduce human work :) – wilson Nov 03 '10 at 01:41
  • This will end in a catastrophy if your data already contains `"` for example item descriptions like 24" LCD – masgo Oct 01 '19 at 14:57
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,194
  • 1
  • 16
  • 32