Reading an Excel file - missing first record....

[Stu]

Hi,

I am using the following code to read the data from an Excel spreadsheet. It
runs basically OK but it skips the first row of the spreadsheet. Does anyone
know how to either read the first line or (*hack warning*) insert a blank
record in the first line of the worksheet....?

Dim cnExcel As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source = MyFile.xls;Extended Properties=Excel 8.0;")
cnExcel.Open()
lookup = cnExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim cmdExcel As New OleDbCommand("SELECT * FROM [" &
lookup.Rows(0)("TABLE_NAME") & "]", cnExcel)
Dim dr As OleDbDataReader
dr = cmdExcel.ExecuteReader
If dr.Read Then
response.write(dr(0))
end if
dr.Close()
cnExcel.Close()

Thanks in advance,

Stu

 

[Chris Botha]

Tell it that there is no header row by using "HDR=No"
"Extended Properties=Excel 8.0;HDR=No"

 

[Stu]

Hi,

I have just tried adding that and am now getting the error - 'Could not find
installable ISAM.' when I try to open the connection.

Any ideas?

Stu

Tell it that there is no header row by using "HDR=No"
"Extended Properties=Excel 8.0;HDR=No"

Hi,

I am using the following code to read the data from an Excel spreadsheet.
It runs basically OK but it skips the first row of the spreadsheet. Does
anyone know how to either read the first line or (*hack warning*) insert
a blank record in the first line of the worksheet....?

Dim cnExcel As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source = MyFile.xls;Extended Properties=Excel 8.0;")
cnExcel.Open()
lookup = cnExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim cmdExcel As New OleDbCommand("SELECT * FROM [" &
lookup.Rows(0)("TABLE_NAME") & "]", cnExcel)
Dim dr As OleDbDataReader
dr = cmdExcel.ExecuteReader
If dr.Read Then
response.write(dr(0))
end if
dr.Close()
cnExcel.Close()

Thanks in advance,

Stu

 

[Edwin Knoppert]

Quotes are important!

Hi,

I have just tried adding that and am now getting the error - 'Could not
find installable ISAM.' when I try to open the connection.

Any ideas?

Stu

Tell it that there is no header row by using "HDR=No"
"Extended Properties=Excel 8.0;HDR=No"

Hi,

I am using the following code to read the data from an Excel
spreadsheet. It runs basically OK but it skips the first row of the
spreadsheet. Does anyone know how to either read the first line or
(*hack warning*) insert a blank record in the first line of the
worksheet....?

Dim cnExcel As New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source =
MyFile.xls;Extended Properties=Excel 8.0;")
cnExcel.Open()
lookup = cnExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim cmdExcel As New OleDbCommand("SELECT * FROM [" &
lookup.Rows(0)("TABLE_NAME") & "]", cnExcel)
Dim dr As OleDbDataReader
dr = cmdExcel.ExecuteReader
If dr.Read Then
response.write(dr(0))
end if
dr.Close()
cnExcel.Close()

Thanks in advance,

Stu

 

[Paul Clement]

¤ Hi,
¤
¤ I have just tried adding that and am now getting the error - 'Could not find
¤ installable ISAM.' when I try to open the connection.
¤
¤ Any ideas?

Yes, your syntax isn't quite right. See the following connection string example:

"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\Book10.xls;Extended Properties=""Excel 8.0;HDR=NO;"""


Paul
~~~~
Microsoft MVP (Visual Basic)