Saving a tab separated file in Excel without quotes

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?

BlueWaldo

Posted 2010-11-02T03:45:57.723

Reputation: 265

Answers

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

In Excel, press CtrlA to select all
Press CtrlC to copy
Open Notepad, press CtrlV to paste
Press CtrlS to save and it is done

wilson

Posted 2010-11-02T03:45:57.723

Reputation: 4 113

Bloody genius! Works! Saved my bacon. Wilson, how in the world did you learn this?? – Doug Null – 2018-01-05T18:48:58.030

Whoops. Even using this method, Excel still erroneously inserts " at the beginning of cell in column-A if row follows a group. – Doug Null – 2018-01-05T19:22:24.703

@DougNull what does it mean by "row follows a group"? – wilson – 2018-01-08T04:13:10.370

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 – 2019-07-26T10:40:25.700

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

Then,

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

NginUS

Posted 2010-11-02T03:45:57.723

Reputation: 414

1If the Excel file is long, there will be a lot of human work. – wilson – 2010-11-02T06:45:22.850

3One 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 – 2010-11-02T16:04:43.057

@jhamu, nice improvement to reduce human work :) – wilson – 2010-11-03T01:41:11.207

This will end in a catastrophy if your data already contains " for example item descriptions like 24" LCD – masgo – 2019-10-01T14:57:55.713

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

Posted 2010-11-02T03:45:57.723

Reputation: 1 541