up vote 0 down vote favorite
share [g+] share [fb]

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

link|improve this question
91% accept rate
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? – techie007 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
feedback

2 Answers

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.

link|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. :) – techie007 Sep 15 '11 at 19:38
2  
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
feedback
up vote 0 down vote

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

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.