7

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?

14

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
  • Bloody genius! Works! Saved my bacon. Wilson, how in the world did you learn this?? – Doug Null Jan 5 '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 5 '18 at 19:22
  • @DougNull what does it mean by "row follows a group"? – wilson Jan 8 '18 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 '19 at 10:40
3

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

Then,

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

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

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.