using datareader to read an excel file, not reading first row-vb.net --- Pls help urgent

I hv an excel file which got 3 columns and no header also.

I need to read my 3rd column,anyway my program will read the third column and since i dont have a header, reading the excel data starting only from the second row and program treating the first row as header. Appreciate if somebody can help me to modify the program to read the data from the from the first row itself.

here is my coding

For xfile As Integer = 0 To TempDir.GetFiles("*.xls").Length - 1  ================ 'reading the excel file from tem folder one by one

Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & TempDir.GetFiles("*.xls")(xfile).FullName & ";Extended Properties=Excel 8.0"

conxls = New OleDb.OleDbConnection(ConnectionString)
conxls.Open()

  ''get sheet name
                Dim oExcel As New Excel.Application
                Dim oBooks As Excel.Workbooks, oBook As Excel.Workbook
                Dim oSheet As Excel.Worksheet
                Dim oCells As Excel.Range
                Dim strsheetname As String
                Dim strSQL As String
                oExcel.Visible = False : oExcel.DisplayAlerts = False
                oBooks = oExcel.Workbooks
                File.Copy(TempDir.GetFiles("*.xls")(xfile).FullName, "c:\" & fname) =========getting the sheetnam
                oBooks.Open("c:\" & fname)
                oBook = oBooks.Item(1)
                oSheet = oExcel.Sheets.Item(1)
                strsheetname = oSheet.Name
                oExcel.Quit()
                oExcel = Nothing
               
      strSQL = "Select * from [ " & strsheetname & "$]"
     Dim da As New System.Data.OleDb.OleDbDataAdapter("Select * from [" + strsheetname + "$]", conxls)  ====getting the sheet name
     Dim dt As New DataTable
     da.Fill(dt)
     Dim dr As DataRow
                    For Each dr In dt.Rows
                        strLine = CType(dr(2), String) ==========================I need read data from my third column only

    SQLcmd.CommandText = "If not Exists(Select * from applesource Where SerialNo ='" & CType(dr(2), String) & "')   " & _
                                          " Insert into applesource values ('" & CType(dr(2), String) & "')" ===========inserting data to sql server table
                        SQLcmd.CommandType = CommandType.Text
                        RowsAffected = SQLcmd.ExecuteNonQuery()

         
     Pls help me to change my program to read the first row as the current program is treating the first row as header and reading from second row only. Pls help. I need it very urgent............
           




nyeesgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jereteCommented:
Have you tried using the HDR parameter in your connection string?

Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & TempDir.GetFiles("*.xls")(xfile).FullName & ";Extended Properties=Excel 8.0;HDR=Yes;"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SanclerCommented:
Except that, as there is no header here, the last bit should be "HDR=No;"

I think ;-)

Roger
nyeesgAuthor Commented:
hI experts,

I have used the HDR settings but throwing as exception pls advice

Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & TempDir.GetFiles("*.xls")(xfile).FullName & ";Extended Properties=Excel 8.0;HDR=NO"

Error message- Could not find installable ISAM

pls HELP
jereteCommented:
Try:

Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & TempDir.GetFiles("*.xls")(xfile).FullName & ";Extended Properties=""Excel 8.0;HDR=No;"""

although I think that the HDR=No is the default...


nyeesgAuthor Commented:
Hi i hv use the below code and its works fine

Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & TempDir.GetFiles("*.xls")(xfile).FullName & ";Extended Properties=""Excel 8.0;HDR=No"";"

 I d like to say a big thanks to both jerete & Sancler for the great help

I would like accept jerete annwer and will keep sancler as assist answer.

Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.