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)

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.