none
Reading an Excel file that contains unicode characters RRS feed

  • Question

  • I have the following code:

            Dim myConnection As SqlConnection
            Dim mySqlDataAdapter As SqlDataAdapter
            Dim GF As New GenericFunctions
            Dim x As Integer
            Dim nUserID As Integer

            ' Create connection string variable. Modify the "Data Source" parameter as
            ' appropriate for your environment.
            Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                        & "Data Source=" & saveFileLocation _
                        & ";" & "Extended Properties=Excel 8.0;"
            Dim objConn As New OleDbConnection(sConnectionString)

            Try
                ''==============================================================
                ''= BEGIN Read Excel file and store into a GridView
                ''==============================================================
                saveFileLocation = Replace(saveFileLocation, "\\", "\")

                ' Create the connection object by using the preceding connection string.
                ' Open connection with the database.
                objConn.Open()

                ' The code to follow uses a SQL SELECT command to display the data from the worksheet.
                ' Create new OleDbCommand to return data from worksheet.
                Dim objCmdSelect As New OleDbCommand("SELECT * FROM [Sheet1$] where email <> """"", objConn)

                ' Create new OleDbDataAdapter that is used to build a DataSet
                ' based on the preceding SQL SELECT statement.
                Dim objAdapter1 As New OleDbDataAdapter()

                ' Pass the Select command to the adapter.
                objAdapter1.SelectCommand = objCmdSelect

                ' Create new DataSet to hold information from the worksheet.
                Dim objDataset1 As New DataSet()

                At this line I get the error message I have provided at the bottom of this post
                ' Fill the DataSet with the information from the worksheet.
                objAdapter1.Fill(objDataset1, "XLData")

                ' Build a table from the original data.
                ExcelUploadRecipients.DataSource = objDataset1.Tables(0).DefaultView
                ExcelUploadRecipients.DataBind()


                ' Clean up objects.
                objConn.Close()
                objConn.Dispose()

    Here is the error message:
    Error Message:    System.Data.OleDb.OleDbException: Selected collating sequence not supported by the operating system.
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
       at DefineEmailList.UpdateRecipientTable(String saveFileLocation)

    This code works perfect when the Excel file does not contain unicode characters.

    Any help would be greatly appreciated.
    Tuesday, August 7, 2007 7:35 PM

All replies

  • As I know Jet provider does not have any options for the connection string to force reading Unicode characters, but I believe it should read them properly. What makes you believe that Unicode characters cause it? It could just specific collating sequence (language) that causes that issue. Which language are you using to store the data inside of the workbook? Could you send sample of the file to my email account and I could try to open it?

    Wednesday, August 8, 2007 10:19 AM
    Moderator
  • Upon further testing it appears the problem was the user entered some text that was a hyperlink.

    Once I removed the hyperlink and just had straight text, I was able to import the Excel file.

    Here was the original row in Excel:

    Email                                         Field1       Field2       Field3       Field 4                      Attachment 1

    test@test .com 헤이그룹 곽훈 팀장 \\Seosfs01\Temp$\7.DB\Mail merge\brochure.ppt

     

    Here is the new row in Excel

    Email                                         Field1       Field2       Field3       Field 4                      Attachment 1

    test@test.com
    헤이그룹 곽훈 팀장 \\Seosfs01\Temp$\7.DB\Mail merge\brochure.ppt

    Wednesday, August 8, 2007 4:51 PM
  •   Only query that can read such excel is SELECT * FROM SheetName. You can not use any other operation with this query like TOP n, WHERE, column select etc.

    This error comes when you try to read Excel (*.xls) file created using Non-English version of Excel. i.e. it uses Unicode font for Enlish characters and not ANSI.

    In your OleDb query use plain SELECT *, and it will work.

    Please remove Where condition and then once DataSet is populated filter out records which you do not want.
    SDE, Microsoft
    Thursday, December 25, 2008 6:47 PM