Japanese not uploading from Excel to SQL Server using Microsoft.ACE.OLEDB.12.0

I have a web page that imports translations from a spreadsheet using Microsoft.ACE.OLEDB.12.0;
The process works almost flawlessly, except that Japanese (and likely Korean and Chinese) can not be imported with this method.

 ' Connect to the Excel Spreadsheet
Dim xConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source=" & Server.MapPath(String.Format("~/transText/importUpload/{0}", fileName)) & ";" & _
"Extended Properties=Excel 8.0;"

' create your excel connection object using the connection string
 Dim objXConn As New OleDbConnection(xConnStr)
 objXConn.Open()

This works for French, Italian, Chinese (on an English PC), Turkish, Spanish, Portuguese, Dutch, Ukrainian, etc. I believe this issue is the Japanese Windows 7 OS.

How can I get around this barrier?

After adding the following to the connection string...

"Extended Properties=""Excel 8.0;CharacterSet=UTF-16;"""

I double quoted the properties so that the provider could identify each. This cleared the previous error but still will not properly read the file.

If I send the file to an English OS PC, and don't even open the file in Excel on the English OS PC, I can still import the exact same .xls on the English PC but not on the Japanese OS PC. I have tried a few different character sets also.

1 answer

Try adding some flags on the connection string to force the UTF properties.