1

There is an option in Excel 2016 to export as a UTF-8 comma delimited CSV file. I don't want commas between fields- i want tabs.

I save the file as a tab-delimited txt file. In the Save As dialogue, I go to "Tools" then "Web Options" then "Encoding" and choose UTF-8. Then I open it with Sublime Text 3- the IDE I use to write python scripts to manipulate the file (It's too big to open in Notepad).

It's a large file with around 200,000 Russian sentences along with translations. The other text looks fine, but the Russian characters all show up as ???? ????. Completely useless.

The comma delimited CSV works perfectly. Is there just no way to successfully save a tab-delimited UTF-8 CSV file in Excel?

4
  • My guess would be that those weboptions only apply to actual web formats and/or whatever way you're using to save them as tab delimited falls back to pure ASCII encoding. What steps are you using to save/export each format?
    – Seth
    Nov 29, 2016 at 7:28
  • Well, I have been looking around the Web all night. The Web Options thing seems to have been the standard solution for the past decade or so.
    – Deersfeet
    Nov 29, 2016 at 9:36
  • Did you check the Encoding used for the file that has the ??? in it? If you can't open the file, how did you check for the missing Russian characters? Did you consider replacing the commas with a tab through Sublime?
    – Seth
    Nov 29, 2016 at 11:46
  • @Seth: The problem is the sentences (which are the fields of this foreign language CSV) contain commas.
    – Deersfeet
    Dec 13, 2016 at 0:13

2 Answers 2

0

I had never heard of this 'Web options' feature before, unfortunately, at the time of writing, as Deersfeet also confirms, this feature seems broken in Office 2016. I've also been testing it, and can reproduce this issue.

As a workaround, you can use the method I've always used, because I generally want much more control over my export settings...

  • Open MS Access, import your data from Excel
  • In Access, export your data. Access has much more advanced settings to export to flat-text files (encapsulation, separator, line-endings, ...)

The answer to your specific question

Is there just no way to successfully save a tab-delimited UTF-8 CSV file in Excel?

seems to be: No. Not untill MS fixes this.
You could open a bug report, but i've grown frustrated of doing so, as MS doesn't seem to care about community feedback...

2
  • Thanks for this answer. I have just started to use OpenOffice Calc. Calc is annoying because the cell widths are set to fit the text, and I want to see all the data at once in a preview-type width. It'll have to do for now. I will try your Access idea.
    – Deersfeet
    Dec 12, 2016 at 23:57
  • Excel has always been broken with CSV and unicode. TAB+UTF16 = happy. COMMA+UTF8 = happy. Do not ever cross the streams. (combinations work fine in other Office apps, such as Libra)
    – Brain2000
    Sep 15 at 14:53
0

I believe the closest to what you are looking for is "Save as type: Unicode text". It gives text file delimited with tabs and supports Russian characters, although it's not utf-8 but unicode.

2
  • Unicode is not an encoding, it's a standard. UTF-8 is an encoding that conforms to the Unicode standard. The option "Save as type: Unicode text" creates a text file with the Unicode complient encoding UCS-2 Little Endian, with BOM header.
    – Wouter
    Dec 12, 2016 at 13:46
  • 1
    @Wouter : Thanks for this clarification. I was wondering if the Endianness messed with Sublime Text somehow. And knowing it's not UTF-8 also makes the situation more clear.
    – Deersfeet
    Dec 12, 2016 at 23:58

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.