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?

Improve this question

3 Answers 3

Reset to default
20

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
Improve this answer
5
  • Bloody genius! Works! Saved my bacon. Wilson, how in the world did you learn this??
    – Doug Null
    Jan 5, 2018 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 5, 2018 at 19:22
  • @DougNull what does it mean by "row follows a group"?
    – wilson
    Jan 8, 2018 at 4: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, 2019 at 10:40
  • So simple, and yet so beautiful! @RajarsheeMitra It's funny because I came here trying to solve that exact problem, and copying manually to notepad++ and saving the file from there did the trick!
    – Jak
    Nov 23, 2023 at 14:26
3

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

Then,

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

Improve this answer
4
  • 1
    If the Excel file is long, there will be a lot of human work.
    – wilson
    Nov 2, 2010 at 6: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 2, 2010 at 16:04
  • @jhamu, nice improvement to reduce human work :)
    – wilson
    Nov 3, 2010 at 1:41
  • This will end in a catastrophy if your data already contains " for example item descriptions like 24" LCD
    – masgo
    Oct 1, 2019 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.

Improve this answer

You must log in to answer this question.

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