Read CSV UTF8 correctly in excel

[YasserKhalil]

Hello everyone
In the attachment csv file, which has uncode characters.
When trying to open it in notepad++, I found everything is OK and there is no probelm. But when opening it in excel, the characters appeared incorrectly and the UTF8 doesn't work properly
Any ideas about this problem?

[HansV]

You might change the extension to .txt, open the text file and use Text to Columns:

Code: Select all

    Name "output.csv" As "output.txt"
    Workbooks.OpenText Filename:="output.txt", Origin:=65001
    Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlTextQualifierNone, Comma:=True

[YasserKhalil]

Amazing Mr. Hans
Just one point I don't need to convert it to txt. Can I do that trick without converting or even converting the txt to csv extension again???

[HansV]

Excel ignores the Origin if the extension is .csv, so we do need to change the extension.
After opening the file, it is locked, so you cannot immediately change the extension back to .csv, but you can do that when you have closed it:

Code: Select all

    Name "output.txt" As "output.csv"

[YasserKhalil]

Thanks a lot.
I tried the code like that but got unexpected function or variable at the line of assigning wb

Code: Select all

Sub Test()
    Name ThisWorkbook.Path & "\output.csv" As ThisWorkbook.Path & "\output.txt"
    Dim wb As Workbook
    Set wb = Workbooks.OpenText(FileName:=ThisWorkbook.Path & "\output.txt", Origin:=65001)
    With wb.Worksheets(1)
    .Range("A:A").TextToColumns Destination:=.Range("A1"), DataType:=xlDelimited, TextQualifier:=xlTextQualifierNone, Comma:=True
    .Columns.AutoFit
    .Parent.Close True
    End With
    Name ThisWorkbook.Path & "\output.txt" As ThisWorkbook.Path & "\output.csv"
End Sub

[HansV]

Workbooks.OpenText doesn't return anything. Use

Code: Select all

    Workbooks.OpenText FileName:=ThisWorkbook.Path & "\output.txt", Origin:=65001
    Set wb = ActiveWorkbook

[YasserKhalil]

Thanks a lot. Now the unicode problem is solved but as for the new csv has only one column....!!

[HansV]

The TextToColumns line should split it.

[YasserKhalil]

What is weird is that I put breakpoint at this line
.Parent.Close True
and found the data in four columns but after cloing the workbook and renaming it, I found that the output is only on one column !!!

[HansV]

Ah - that is because saving a text file doesn't use commas.
Note: autofitting the columns makes no sense - a .csv file doesn't have column widths.

Code: Select all

Sub Test()
    Name ThisWorkbook.Path & "\output.csv" As ThisWorkbook.Path & "\output.txt"
    Workbooks.OpenText Filename:=ThisWorkbook.Path & "\output.txt", Origin:=65001
    Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlTextQualifierNone, Comma:=True
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\output.csv", FileFormat:=xlCSV
    ActiveWorkbook.Close SaveChanges:=False
    Kill ThisWorkbook.Path & "\output.txt"
End Sub

[YasserKhalil]

Amazing. Thank you very much for your support and your patience.
You are really a legend.

[YasserKhalil]

Can you please have a look at cells B4 and B8 for example? Does these cells appear correctly for you?

[HansV]

They do, on my computer.

S0025.png

The letter ß is the German letter Ringel-S.

[YasserKhalil]

So why it doesn't appear properly on my PC???!! although it works well in notepad++ and appears well.

[HansV]

Probably because I'm using a different code page (Western) than you (Arabic?)

[YasserKhalil]

Is there a way that I can change the code page??

[HansV]

See https://knowledgebase.progress.com/arti ... ticle/4677

[YasserKhalil]

Thanks a lot. Just last question : Will Arabic as language will not work if I select another language?

[HansV]

I don't have any experience with that. Sorry.

[YasserKhalil]

Thanks a lot ..
I have put the text `Poppenbütteler Hauptstraße` in a cell then tried that code that worked like charm

Code: Select all

Sub MyTest()
Const adSaveCreateNotExist = 1
Const adSaveCreateOverWrite = 2
Const adTypeBinary = 1
Const adTypeText = 2

Dim objStreamUTF8: Set objStreamUTF8 = CreateObject("ADODB.Stream")
Dim objStreamUTF8NoBOM: Set objStreamUTF8NoBOM = CreateObject("ADODB.Stream")

With objStreamUTF8
  .Charset = "UTF-8"
  .Open
  .WriteText ActiveCell.Value
  .Position = 0
  .SaveToFile "Toto.csv", adSaveCreateOverWrite
  .Type = adTypeText
  .Position = 3
End With

'With objStreamUTF8NoBOM
'  .Type = adTypeBinary
'  .Open
'  objStreamUTF8.CopyTo objStreamUTF8NoBOM
'  .SaveToFile "toto-nobom.csv", adSaveCreateOverWrite
'End With

objStreamUTF8.Close
'objStreamUTF8NoBOM.Close
End Sub

How can I read the contents of the CSV file in a correct way to be able to use the code?