Read CSV UTF8 correctly in excel
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Read CSV UTF8 correctly in excel
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?
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?
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Read CSV UTF8 correctly in excel
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
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Read CSV UTF8 correctly in excel
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???
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???
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Read CSV UTF8 correctly in excel
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:
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"
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Read CSV UTF8 correctly in excel
Thanks a lot.
I tried the code like that but got unexpected function or variable at the line of assigning wb
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
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Read CSV UTF8 correctly in excel
Workbooks.OpenText doesn't return anything. Use
Code: Select all
Workbooks.OpenText FileName:=ThisWorkbook.Path & "\output.txt", Origin:=65001
Set wb = ActiveWorkbook
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Read CSV UTF8 correctly in excel
Thanks a lot. Now the unicode problem is solved but as for the new csv has only one column....!!
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Read CSV UTF8 correctly in excel
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 !!!
.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 !!!
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Read CSV UTF8 correctly in excel
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.
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
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Read CSV UTF8 correctly in excel
Amazing. Thank you very much for your support and your patience.
You are really a legend.
You are really a legend.
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Read CSV UTF8 correctly in excel
Can you please have a look at cells B4 and B8 for example? Does these cells appear correctly for you?
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Read CSV UTF8 correctly in excel
They do, on my computer.
The letter ß is the German letter Ringel-S.
The letter ß is the German letter Ringel-S.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Read CSV UTF8 correctly in excel
So why it doesn't appear properly on my PC???!! although it works well in notepad++ and appears well.
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Read CSV UTF8 correctly in excel
Probably because I'm using a different code page (Western) than you (Arabic?)
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Read CSV UTF8 correctly in excel
Is there a way that I can change the code page??
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Read CSV UTF8 correctly in excel
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Read CSV UTF8 correctly in excel
Thanks a lot. Just last question : Will Arabic as language will not work if I select another language?
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Read CSV UTF8 correctly in excel
I don't have any experience with that. Sorry.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Read CSV UTF8 correctly in excel
Thanks a lot ..
I have put the text `Poppenbütteler Hauptstraße` in a cell then tried that code that worked like charm
How can I read the contents of the CSV file in a correct way to be able to use the code?
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