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

  • Thread starter Thread starter Stu
  • Start date Start date
S

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
 
Tell it that there is no header row by using "HDR=No"
"Extended Properties=Excel 8.0;HDR=No"
 
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

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


Stu said:
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
Click to expand...
Click to expand...
 
Quotes are important!

Stu said:
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

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


Stu said:
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
Click to expand...
Click to expand...
Click to expand...
 
¤ 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)
 
Back
Top