Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In Excel, Is there an option to prevent quotes from being used on text strings when you export a sheet to TSV?

share|improve this question
By default when saving as a Tab Delimited text file (or a CSV), it doesn't add quotes, at least not in 2007. Are there quotes already around in the fields you are trying to save? Which version of Excel are you using? How are you "exporting" it? – Ƭᴇcʜιᴇ007 Sep 15 '11 at 19:08
I'm using Excel 2010, and using the "Save As Text" to export the sheet. – monksy Sep 15 '11 at 19:18
up vote 1 down vote accepted

If your data contains double quotes, commas or new-line characters, then Excel will wrap that field in double quotes in the text file (and double up any existing double quotes).

Check out this Wikipedia entry on the "Basic rules" for CSV files (which Excel seems to follow).

Unfortunately Excel saves it like that, and there's not much you can do about that.

You may have to write a VBA macro to strip them on save (if possible), or write a program to strip them after the fact, or perhaps use a text editor and search and replace them with "null", or an implementation of grep or alike.

share|improve this answer
I realize that its trying to keep the data clean, but for my application I'm not going to to use that data in a spreadsheet anymore. [I'm going to do text transformations] – monksy Sep 15 '11 at 19:32
See my answer again, I extended it. :) – Ƭᴇcʜιᴇ007 Sep 15 '11 at 19:38
Also, what are you writing your program in? Most decent CSV(TSV) handling libraries are smart enough to deal with the quotes. – EBGreen Sep 15 '11 at 19:41

If you use the .prn format "Formatted Text (Space Delimited)" for saving text in excel then no quotes are added.

share|improve this answer

@zork Concerning the .prn format, I initially thought it would indeed be my solution but there is a problem... the line limitation of 240 characters. If you only write lines shorter than that, it's ok, but otherwise, you are stuck.

See mention of that limit on website.

share|improve this answer
This should be a comment and not another answer. – wbeard52 Oct 6 '15 at 17:30

TL;DR In Excel (Mac or PC, pretty much any version) Highlight all the cells in the sheet (ctrl-a or command-a) copy them and then paste in a text only editor line notepad, atom, sublime or textwrangler. Viola NO quotes around fields and a TSV!

More detail: Excel can be a pain when working with anything that has to be processed with other programs but this trick gets the data out in as clean a TSV as possible.

TSV format doesn't need the same level of escaping and quoting as CSV. I use a simple model of escaping tabs, newlines, carriage returns with \t, \n, \r Anything else is left as-is including quotes and extended/multibyte characters. UTF8 works well for this.

The characters that Excel and Word use for quotes can vary a lot since they tend to use the Windows 1252 character set, so if you have those or other "windows" type characters you should convert to unicode (preferably UTF8) for greater compatibility if you are making files for alternate platforms.

share|improve this answer

You must log in to answer this question.

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