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=Micr osoft.Jet.OLEDB .4.0;Data
Source = MyFile.xls;Exte nded Properties=Exce l 8.0;")
cnExcel.Open()
lookup = cnExcel.GetOleD bSchemaTable(Ol eDbSchemaGuid.T ables, Nothing)
Dim cmdExcel As New OleDbCommand("S ELECT * FROM [" &
lookup.Rows(0)( "TABLE_NAME ") & "]", cnExcel)
Dim dr As OleDbDataReader
dr = cmdExcel.Execut eReader
If dr.Read Then
response.write( dr(0))
end if
dr.Close()
cnExcel.Close()

Thanks in advance,

Stu

[Chris Botha]

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

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


"Stu" <s.lock@cergis. com> wrote in message
news:eO6vDSAzFH A.2132@TK2MSFTN GP15.phx.gbl...[color=blue]
> 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=Micr osoft.Jet.OLEDB .4.0;Data
> Source = MyFile.xls;Exte nded Properties=Exce l 8.0;")
> cnExcel.Open()
> lookup = cnExcel.GetOleD bSchemaTable(Ol eDbSchemaGuid.T ables, Nothing)
> Dim cmdExcel As New OleDbCommand("S ELECT * FROM [" &
> lookup.Rows(0)( "TABLE_NAME ") & "]", cnExcel)
> Dim dr As OleDbDataReader
> dr = cmdExcel.Execut eReader
> If dr.Read Then
> response.write( dr(0))
> end if
> dr.Close()
> cnExcel.Close()
>
> Thanks in advance,
>
> Stu
>
>
>[/color]

[Stu]

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

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" <chris_s_botha@ AThotmail.com> wrote in message
news:%23Tmoj2Az FHA.2848@TK2MSF TNGP15.phx.gbl. ..[color=blue]
> Tell it that there is no header row by using "HDR=No"
> "Extended Properties=Exce l 8.0;HDR=No"
>
>
> "Stu" <s.lock@cergis. com> wrote in message
> news:eO6vDSAzFH A.2132@TK2MSFTN GP15.phx.gbl...[color=green]
>> 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=Micr osoft.Jet.OLEDB .4.0;Data
>> Source = MyFile.xls;Exte nded Properties=Exce l 8.0;")
>> cnExcel.Open()
>> lookup = cnExcel.GetOleD bSchemaTable(Ol eDbSchemaGuid.T ables, Nothing)
>> Dim cmdExcel As New OleDbCommand("S ELECT * FROM [" &
>> lookup.Rows(0)( "TABLE_NAME ") & "]", cnExcel)
>> Dim dr As OleDbDataReader
>> dr = cmdExcel.Execut eReader
>> If dr.Read Then
>> response.write( dr(0))
>> end if
>> dr.Close()
>> cnExcel.Close()
>>
>> Thanks in advance,
>>
>> Stu
>>
>>
>>[/color]
>
>[/color]

[Edwin Knoppert]

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

Quotes are important!

"Stu" <s.lock@cergis. com> schreef in bericht
news:OcdVX5WzFH A.2960@tk2msftn gp13.phx.gbl...[color=blue]
> 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" <chris_s_botha@ AThotmail.com> wrote in message
> news:%23Tmoj2Az FHA.2848@TK2MSF TNGP15.phx.gbl. ..[color=green]
>> Tell it that there is no header row by using "HDR=No"
>> "Extended Properties=Exce l 8.0;HDR=No"
>>
>>
>> "Stu" <s.lock@cergis. com> wrote in message
>> news:eO6vDSAzFH A.2132@TK2MSFTN GP15.phx.gbl...[color=darkred]
>>> 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=Micr osoft.Jet.OLEDB .4.0;Data Source =
>>> MyFile.xls;Exte nded Properties=Exce l 8.0;")
>>> cnExcel.Open()
>>> lookup = cnExcel.GetOleD bSchemaTable(Ol eDbSchemaGuid.T ables, Nothing)
>>> Dim cmdExcel As New OleDbCommand("S ELECT * FROM [" &
>>> lookup.Rows(0)( "TABLE_NAME ") & "]", cnExcel)
>>> Dim dr As OleDbDataReader
>>> dr = cmdExcel.Execut eReader
>>> If dr.Read Then
>>> response.write( dr(0))
>>> end if
>>> dr.Close()
>>> cnExcel.Close()
>>>
>>> Thanks in advance,
>>>
>>> Stu
>>>
>>>
>>>[/color]
>>
>>[/color]
>
>[/color]

[Paul Clement]

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

On Mon, 10 Oct 2005 08:43:41 +0100, "Stu" <s.lock@cergis. com> wrote:

¤ 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=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=e:\My Documents\Book1 0.xls;Extended Properties=""Ex cel 8.0;HDR=NO;"""


Paul
~~~~
Microsoft MVP (Visual Basic)