1

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.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Chandan D N
  • 335
  • 1
  • 4
  • 16
  • 1
    What encoding does your CSV file have? – ttaaoossuuuu Feb 16 '18 at 07:53
  • csv File have UTF-8 – Chandan D N Feb 16 '18 at 07:54
  • 1
    Try [worlbooks.opentext](https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/opentextfile-method?f=255&MSPPError=-2147217396) with iomode:=TristateTrue for unicode or data, get data, from text and choose the utf-8 language. –  Feb 16 '18 at 07:57
  • 1
    Possible duplicate of [How to open a text file with Excel in UTF-8 encoding?](https://stackoverflow.com/questions/43307776/how-to-open-a-text-file-with-excel-in-utf-8-encoding/43308865#43308865). –  Feb 16 '18 at 08:07
  • I have tried to use `Set wb = Workbooks.OpenText(strDir & strFile, ForReading, TristateTrue)` but it is throwing compilation error – Chandan D N Feb 16 '18 at 08:09
  • You can't call a function in VBA like this with `()`. See my answer below. – ttaaoossuuuu Feb 16 '18 at 09:39
  • I have recorded macro using get data ad using utf-8 language, it has worked fine – Chandan D N Feb 18 '18 at 10:16

1 Answers1

1

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

ttaaoossuuuu
  • 7,786
  • 3
  • 28
  • 58