1

I need to open a CSV file with UTF-8 encoding through Excel VBA. I found for this purpose code snippet in the - Is it possible to force Excel recognize UTF-8 CSV files automatically?. This code snippet did not work for me. Many non-ASCII symbols become unreadable.

I also found other solution -Import text with foreign characters, which solved this problem, but that code is much longer. Maybe someone knows why the code below doesn’t work?

Workbooks.OpenText Filename:=filetoopen, Origin:=65001, DataType:=xlDelimited, Comma:=True
Community
  • 1
  • 1
Sharunas Bielskis
  • 1,033
  • 1
  • 16
  • 25
  • Is 65001 the correct code page? Is the only problem with [Import text with foreign characters](http://stackoverflow.com/questions/32416841/import-text-with-foreign-characters/32417169#32417169) the length of the code? Did you try the answer that suggests inserting a UTF8 BOM from [this answer](http://stackoverflow.com/a/11399444/4088852) to your first link? I see a bunch of proposed solutions, and it looks like you tried one of them. – Comintern Dec 07 '16 at 15:56
  • I didn't find Excel VBA example for this BOM. As I understand it is for csv file creation in other programing languages. In my case one csv file I create from xml with C# and I do encoding things correctly and got csv file with correct all utf-8 symbols. I can attach some additional information in my C# program to this csv file, but problem is that I need operate in Excel VBA with some csv files which are downloaded from other systems. – Sharunas Bielskis Dec 07 '16 at 16:07
  • That goes back to the question of whether you know the correct code page. If you do, you can either open the csv file as a binary and insert a BOM if there isn't on there or specify it with `Origin:=CorrectCodePage`. If you *don't* know the code page, Excel's guess is as good as anyone's. – Comintern Dec 07 '16 at 16:15
  • It is more complicated than solution - Import text with foreign characters -which I implemented and it works. – Sharunas Bielskis Dec 07 '16 at 16:38
  • 2
    I'm not sure how you think "text with foreign characters" gets stored in a .csv file, but the only complicated part is figuring out the code page if you don't know it. I'd trust Excel's best guess over anything that anyone is going to come up with in VBA. So is the question "How do I detect the code page of a random .csv file" or "How do I open a .csv file in a known code page"? – Comintern Dec 07 '16 at 16:43
  • One file I created in C# and use this code: StreamWriter sw = new streamWriter(@"D:\FileFolder\FileName.csv",false,Encoding.UTF8); as I understand BOM must be written into created file. – Sharunas Bielskis Dec 07 '16 at 16:54

1 Answers1

1

Workbooks.OpenText with code-page 65001 does read UTF-8-with-BOM files correctly (unless you have newlines inside cells).

You should triple-check your file has BOM. For instance, the code you shared in comments is said to not output the BOM marker:

StreamWriter sw = new streamWriter(@"D:\FileFolder\FileName.csv",false,Encoding.UTF8);

(I know this is very old question, but it does come up in Google searches and adds to confusion around Excel's support of CSV.)

Nickolay
  • 31,095
  • 13
  • 107
  • 185