Problem reading Excel-file

[jwigh]

I'm working on an application that need to read info from a Excel-file. The file is distributed to a number of clients where the application should run. I can´t affect the file before distribution. If the sheet I want to read is named Sheet1 everything is fine but if the sheet is named Sheet 1 i got problem.

The workbook name is picked from My.Settings. and the sheetname is in a variable SheetName by using GetOleDbSchemaTable earlier in the code.
If the Sheetname contains a space stSql contains
"SELECT DISTINCT T1, T2, T3 FROM ['Sheet 1$'C:E]"
If not stSql contains
"SELECT DISTINCT T1, T2, T3 FROM [Sheet1$C:E]"

When the sheetname includes a space it is between ' ' so i don´t understand why it is not working.

My other problem is that the file don´t have column headers. This code assumes column headers. If I want all columns a "SELECT * FROM.. will work without headers but hear i need a SELECT DISTINCT.. so i need to include a hint of which columns I want to read.

I have tried "SELECT DISTINCT [C],[D],[E] FROM [" & SheetName& "C:E]" but then I get "No Value Given For
One OR More required Parameters"

Any hints how to solve these problems.

      Try
            Dim j As Integer
            Dim stSQL As String = "SELECT DISTINCT  T1, T2, T3 FROM [" & SheetName& "C:E]"
            Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & My.Settings.Source_Sortfil & ";Extended Properties=""Excel 8.0;HDR=YES"";")
            Dim cmd As New OleDbCommand(stSQL, cn)

            cn.Open()

            Dim dr As OleDbDataReader = _
                         cmd.ExecuteReader(CommandBehavior.CloseConnection)
            j = 0 'counter for arrayindex
            While dr.Read()

                List_Etikettext(j, 0) = dr(0)
                List_Etikettext(j, 1) = dr(1)
                If IsDBNull(dr(2)) Then
                    List_Etikettext(j, 2) = ""
                Else
                    List_Etikettext(j, 2) = dr(2)
                End If

                j = j + 1
            End While



            dr.Close()
            cmd.Dispose()
            cn.Dispose()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

 

[spidydotnet]

Hi,

The below code works for me (which I use in my own app to read a CSV / Excel sheet). Give this a go. GOOD LUCK.

Dim ConnectionString, CommandText As String
Dim conn As OleDb.OleDbConnection
Dim Command As OleDb.OleDbCommand
Dim strMsg As String

Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSet

Try
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & "\;Extended Properties='Excel;HDR=Yes'"
CommandText = "select * from " & FileName
'Here Filename & Sheet name are the same.. if they are different see the addl code at the bottom.

conn = New System.Data.OleDb.OleDbConnection(ConnectionString)
Command = New System.Data.OleDb.OleDbCommand(CommandText, conn)

conn.Open()

da = New OleDb.OleDbDataAdapter(CommandText, conn)
ds = New DataSet
' fill dataset
da.Fill(ds, "Results")

Return ds.Tables(0)

Catch ex As Exception
ErrorLog(ex)
Finally
If conn.State = ConnectionState.Open Then conn.Close()
da = Nothing
ds = Nothing
Command = Nothing
conn = Nothing
End Try



'Use below if sheetname and filename are different:
'Dim dt As DataTable
'dt = myCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
'"SELECT * FROM [" & dt.Rows(0).Item("TABLE_NAME").ToString & "]"

Spidy.NET
'Discussion is Knowledge'

 

[Sorwen]

First your sheet name is ['Sheet 1$'] and not ['Sheet 1$'C:E]. The only thing I can think is how you are pulling it with the Schema it might be pulling the last cell in the range. Is the last cell that has data C:E? As to your second question I don't know of any way to query the data on the first pass because excel considers the first the column header as well as the first row of data.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[jwigh]

When I changed the line
Dim stSQL As String = "SELECT DISTINCT T1, T2, T3 FROM [" & SheetName& "C:E]"

to
Dim stSQL As String = "SELECT DISTINCT * FROM [" & SheetName & "C:E]"

I don't need to have headers in the columns anymore.

The worksheet has ~20000 rows in 8 columns, only columns C, D and E is of interest for me.

I get the Sheetname with this code.

  Try
            'Instansierar ADO-NET objekten. 
            Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & My.Settings.Source_Sortfil & ";Extended Properties=""Excel 8.0;HDR=NO"";")
            Dim dt As New DataTable
            'Öppnar anslutningen. 
            cn.Open()
            'Skapar datatabellen och fyller den med metadata om arbetsbladen. 
            dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
            
            SheetName= (dt.Rows(0)("TABLE_NAME"))

            'Frigör objekten från minnet. 
            cn.Close()
            dt.Dispose()
            cn.Dispose()
        Catch ex As Exception
           MsgBox(ex.Message)
        End Try

Sorwen, when I have a sheetname like Sheet1 my stSQL now look as "SELECT DISTINCT * FROM [Sheet1$C:E]" without ' '
whith sheetname like Sheet 1my stSQL look like
"SELECT DISTINCT * FROM ['Sheet 1$'C:E]" with ' ' around the sheetname.