CODEWITHSUNDEEP
mysqlvbaexcelcharacter-encoding
MNM
MNM

Reputation: 37

Get ?? when taking chinese characters from excel sheet and exporting to mysql via vba

Let me preface this question by saying that I know that this subject is tackled from various angles in numerous posts. However, I have spent hours and hours on this and still cannot get it to work, so asking for help.

I have some chinese characters in cells in an Excel sheet. I am using VBA to export the content of these cells into a mysql table. From the mysql command line, I am able to insert the chinese characters into the table successfully (I am using utf8mb4 charset).

I have also set charset=utf8mb4 in the VBA connection string.

However, I cannot get VBA to include the chinese characters in the query string, which is as follows:

    SQLStr = "INSERT INTO " & VBA.Trim(databaseName) & ".translationTable" & " VALUES ('" & _
    timestamp & "','" & _
    Replace(Worksheets("translationTable").Range("IORange").Cells(Counter, 2), Chr(10), "") & "','" & _
    Replace(Worksheets("translationTable").Range("IORange").Cells(Counter, 3), Chr(10), "") & "','" & _
    Replace(Worksheets("translationTable").Range("IORange").Cells(Counter, 4), Chr(10), "") & "','" & _
    Replace(Worksheets("translationTable").Range("IORange").Cells(Counter, 4), Chr(10), "") & "')"
    rs.Open SQLStr, oConn

I know that VBA editor will not display the chinese characters in any case. However, chinese characters inserted from VBA are showing up as ?? in the database as well.

It seems that something that I am doing is converting the chinese characters to ASCII, before the INSERT statement is executed.

What am I doing wrong?

Upvotes: 1

Views: 1582

Answers (2)

F. Bas
F. Bas

Reputation: 31

I had the same problem using the Driver Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};, so I have changed it with Provider=Microsoft.ACE.OLEDB.12.0;, and it solved the problem.

In short, my old connection was:

Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DBQ=" & ID_Database & "; IMEX=1;

The new working one is:

Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & ID_Database & "; Extended Properties=""Excel 12.0; HDR=YES; IMEX=1;"";

Now I can retrieve and use Chinese characters from Excel sheets using SQL's SELECT.

Upvotes: 0

Rick James
Rick James

Reputation: 142346

The usual cause of multiple question marks:

  • you had utf8-encoded data (good)
  • SET NAMES latin1 was in effect (default, but wrong)
  • the column was declared CHARACTER SET latin1 (default, but wrong)

If those tips don't suffice, then show us what is in the tables, using something like

SELECT col, HEX(col) FROM tbl...

Chinese cannot be "converted to ascii".

Addenda

Try setting the DNS's 'Connect Options'->'Initial Statement' to 'SET NAMES utf8mb4'. -- MySQL ODBC 3.51 Driver UTF-8 encoding

Upvotes: 1

Related Questions