11

I have an Excel file with Unicode content of which some cells contain text inside double quotes, for example "text".

When I save the Excel file to a text file in Unicode format, the text which contains the double quote is saved as three double quotes, for example """text""".

There are some places where I even have text which contains comma (,). For example, text, which is converted into "text," in the Unicode file. It is adding double quotes to the text, which I guess contains special characters.

How can I avoid this?

Jan Doggen
  • 4,108
  • 10
  • 36
  • 51
karthik
  • 111
  • 1
  • 1
  • 3

4 Answers4

10

This is standard behavior (and similar to the way CSV files are saved). See the RFC 4180 – Common Format and MIME Type for Comma-Separated Values (CSV) Files:

If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote.

How does that apply to your case?

  • This means that "Text" must be saved as """Text""", the outer quotes delimiting the field, and the two other quotes are used to escape the actual quotes you used for your text field. Otherwise, "Text" would be just parsed as Text and you would lose your quotes when re-opening the file.

  • Excel chooses to quote Text, as well, because the comma is used as a delimiter in comma separated files, and not enclosing it in quotes would mean that text, is parsed as two fields when you re-open the file.

If you don't want them in your output, consider opening the resulting files in a text editor and removing all the quotes with a simple search-and-replace.

Community
  • 1
slhck
  • 223,558
  • 70
  • 607
  • 592
  • 2
    You mean that there is no other way to solve the issue other than replacing them in text editor. – karthik Oct 24 '11 at 13:01
  • Not really, since Excel has to do it for compatibility reasons. Maybe it might be possible to come up with a Macro that does a custom export, but I'm no expert on this. – slhck Oct 24 '11 at 13:09
  • Ok thanks may be i will try to replace it in the coding side. – karthik Oct 24 '11 at 13:15
  • @slhck: I am facing issue while saving Unicode as CSV in 2010 https://superuser.com/q/1210099/234380 – NJMR May 18 '17 at 05:13
4

Saving to Unicode seemed to add in quotes even when there were no quotes in my string. Here's how I got around it:

  1. Find a string that isn't in your file (I used xxx).
  2. Before exporting, Find & Replace All double quotes " with xxx
  3. Export your file to txt or csv.
  4. Open txt files and Find & Replace All quotes with nothing
  5. Find & Replace All xxx with quotes
Micah Lindström
  • 223
  • 1
  • 8
Pete
  • 41
  • 1
  • Note, use `replacement` not `'replacement'`. Replacing the double quotes with something including a single quote causes issues if it happens to be at the start of a cell. Basically, Excel sees a single quote at the start of a cell meaning to interpret it as text, so it's removed when you export to txt/csv. I found this out the hard way lol. – Micah Lindström Jan 06 '20 at 01:25
4

I had this problem too. Then, I noticed I was overlooking the Save as type: option "Formatted Text (Space delimited)". You also have to specify the .txt extension, or it will default with a .prn extension. For example, filename.txt instead of just filename. Try it. It works.

Jeff Byington
  • 41
  • 1
  • Will be space delimited instead of tab delimited, and columns seem to be limited in width to about 8 characters by default. – Josiah Yoder Jul 24 '17 at 18:19
  • This does not work if the cells contain something like `"A short sentence"`. Pete's answer below is the only one that worked for me. – Micah Lindström Jan 06 '20 at 11:00
0

Just easily save your file as space delimited.prn in excel and then rename it to .txt. I used this for a crisis of 18000 rows. ;)

Salar Lotfee
  • 1