Read Data From Excel... Not finding Sheet Name

[Meleagant]

All,

Not sure if this is the right forumn, but as this is an Asp.Net application, figured good place to start.

I have users who upload excel files and then I read the data from them no problem. The problem is when the actual sheet name is really really long.

When that workbook with the long name goes through my function I get the error: The Microsoft Jet database engine could not find the object 'American_University_Health_Syst$A'. Make sure the object exists and that you spell its name and the path name correctly.

Same file if I change the sheet name to something shorter it will go in no problem.

Any ideas?

    Public Overloads Function ExtractExcelData(Optional ByVal colCount As Integer = -1) As DataSet

        Dim connStr As String '= ConfigurationManager.ConnectionStrings("xls").ConnectionString
        connStr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;""", SavedFileName)

        If Me.FileExt = ".xlsx" Then
            connStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source{0};Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;""", SavedFileName)
        End If

        ' Create the connection object
        Dim oledbConn As OleDbConnection = New OleDbConnection(connStr)

        ' Create a DataSet which will hold the data extracted from the worksheet.
        Dim ds As DataSet = New DataSet()

        Try
            ' Open connection
            oledbConn.Open()

            Dim dt As New DataTable
            dt = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

            dt = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Views, Nothing)


            If dt.Rows.Count >= 1 AndAlso dt.Rows(0).ItemArray.Length > 3 Then
                If dt.Rows(0).Item(3).ToString.Trim = "TABLE" AndAlso dt.Rows(0).Item(3).ToString.Trim <> "" Then
                    Me.SheetName = dt.Rows(0).Item(2).ToString.Trim.Replace("$", "").Replace("'", "")
                End If
            End If

            Dim xlsCol As Integer = -1
            Dim A1Ref As String = ""

            If colCount <> -1 Then
                xlsCol = colCount
            ElseIf ColumnCount <> -1 Then
                xlsCol = ColumnCount
            End If

            A1Ref = ConvertToLetter(xlsCol)
            If A1Ref = "" Then A1Ref = "IV"

            ' Create OleDbCommand object and select data from worksheet Sheet1
            Dim SqlStr As String = String.Format("SELECT * FROM [{0}$A:{1}]", SheetName, A1Ref)

            Dim oCmd As OleDbCommand = New OleDbCommand(SqlStr, oledbConn)

            ' Create new OleDbDataAdapter
            Dim oleDa As OleDbDataAdapter = New OleDbDataAdapter()

            oleDa.SelectCommand = oCmd

            ' Fill the DataSet from the data extracted from the worksheet.
            oleDa.Fill(ds, "ExcelData")

            oCmd.Dispose()
            oleDa.Dispose()

            Return ds

        Catch ex As Exception
            CommonFunctions.SendGenericErrorEmail(ex)

        Finally
            'Close connection
            If oledbConn.State <> ConnectionState.Closed Then oledbConn.Close()

        End Try

        Return ds

    End Function

* Sine scientia ars nihil est
* Respondeat superior

 

[jmeckley]

you may want to research the table name character limit when querying an excel file. if this is indeed the problem you will need your end users to name the worksheets with shorter names.

you should also check that invalid characters are not used in the worksheet name, this could also be causing the problem.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[Meleagant]

I know there is a 31 char limit for an XLS sheet name. Don't know if XLSX has removed that limitation. But it seems when reading data from excel using the Jet provider, the sheet name needs to be less than 30 characters not less than 31.

I just wish some other type of exception was thrown.

I think I will need to check: If Me.SheetName.Length >= 31 Then 'Somehow get a message back to the user to shorten the worksheet name.'


* Sine scientia ars nihil est
* Respondeat superior