CODEWITHSUNDEEP
vbaexcel
Chandan D N
Chandan D N

Reputation: 335

I am getting japanese characters as Junk while converting csv to Excel using Macro

I am new to Macro scripts, I am trying to convert a Csv file to excel, which contains japnese characters. After conversion excel is not holding right format. could you please suggets me how to encode excel to UTF-8 while conversion from csv to excel.

below is my Macro code.

Sub Csv2Excel()
Dim wb As Workbook
Dim strFile As String, strDir As String

strDir = "D:\DH\testFile\EQT_OFFER_DATA_0000567\"
strFile = Dir(strDir & "EQT_OFFER_DATA_0000567.csv")

    Set wb = Workbooks.Open(strDir & strFile)
    ActiveWorkbook.WebOptions.Encoding = msoEncodingUTF8
    With wb
        .SaveAs Replace(wb.FullName, ".csv", ".xlsx"), 51 'UPDATE:
        .Close True
    End With
    Set wb = Nothing
End Sub

Csv file content
xxxxxxx","1298153","xxxxxx","本多　周二","大阪府 富田林市 梅の里 1丁目 18ｰ5","Individual

Out out Excel
xxxxxxx 1298153 xxxxxx 本多　周二 大阪府 富田林市 梅ã®é‡Œ 1ä¸ç›® 18ï½°5 Individual

Please guide me.

Upvotes: 1

Views: 1874

Answers (1)

ttaaoossuu
ttaaoossuu

Reputation: 7884

To open Unicode files, try using OpenText method instead of Open:

Workbooks.OpenText filename:=strDir & strFile, origin:=65001, DataType:=xlDelimited, textqualifier:=xlTextQualifierDoubleQuote, comma:=True

For the complete list of codepage numbers (origin parameter) see this page: https://msdn.microsoft.com/en-us/library/windows/desktop/dd317756(v=vs.85).aspx

Upvotes: 1

Related Questions