In Excel, Is there an option to prevent quotes from being used on text strings when you export a sheet to TSV?
Asked
Active
Viewed 9,686 times
1
-
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
6 Answers
3
If you use the .prn format "Formatted Text (Space Delimited)" for saving text in excel then no quotes are added.
zork
- 31
2
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.
Lance Roberts
- 8,653
Ƭᴇcʜιᴇ007
- 112,807
-
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
-
-
2Also, 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
-
I would consider a TSV library buggy that decided to strip off quotes in one cell out of numerous ones without quotes. If it was consistent quotes on every cell, that'd be different. – jep Jun 25 '19 at 14:10
1
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 like notepad, atom, sublime or textwrangler. Voila 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.
DanJGer
- 111
-
As of 2023, even copying and pasting into a text editor adds the quotation marks, sadly. – pdtcaskey Nov 11 '23 at 18:16
1
@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 office.microsoft.com website.
Nicolas C.
- 175
0
Open TSV file(with double quotes) in notepad++
- Replace
""by" - Replace
\t"by\t - Replace
"\tby\t
and you are done.... Original answer here Saving to CSV file always adds quotation marks in OpenOffice
Triven
- 111
-
will not work. if your first/last column contains a cell with quotes it will miss the first quotes. If you suggest to just do another run with
^"and"$: wont work either if the cell's text starts/ends with a"– masgo Oct 01 '19 at 15:05 -
With Wildcards enabled, replace
"*\r\n"*with\r\n, then""with". Still a pain having to take additional manual action, but will work. – pdtcaskey Nov 11 '23 at 18:19
0
Use Open Office Calc, Save as csv file and check the "Edit filter settings" checkbox. When the filter settings box comes up, clear the Text delimiter field.
peter
- 1