ODBC connection to Excell eats first row. How to stop it?
Hi,
I'm using Visual Studio 2005 Pro.
I have an ODBC (not OLEdb) Connection to a spreadsheet.
Works fine except the first row is missing.
How do I stop this behavior?
I can see that for OLEdb there is a way to suppress this behavior using the HDR=No parameter in the connection string.
When I try that for the ODBC connection, No change. Row Still Eaten.
I also went to the HKLM\Software\Microsoft\Jet\4.0\Engines\Excel registry key and changed the FirstRowHasNames value to 00. No change. Row Still Eaten.
Looking for help in, hopefully, all the Right places....
-g
It might help if you show the code in question.
Sure, rather straightforward as I've knocked it down to minimums for testing
Code Snippet
Dim sScratchString As String
Dim XLSConnString As String
Dim XLSConn As OdbcConnection
Dim XLSSQLCommand As OdbcCommand
Dim XLSSQLReader As OdbcDataReader
XLSConnString = "Driver={Microsoft Excel Driver (*.xls)};dbq=pathto.xls;defaultdir=directory;driverid=790;fil=excel 8.0;maxbuffersize=2048;maxscanrows=8;pagetimeout=5;readonly=1;safetransactions=0;threads=3;usercommitsync=Yes"
XLSConn = New OdbcConnection(XLSConnString)
XLSConn.Open()
XLSSQLCommand = New OdbcCommand()
XLSSQLCommand.Connection = XLSConn
'read the spreadsheet
SQLString = "Select * from [Agency List$]"
XLSSQLCommand.CommandText = SQLString
XLSSQLReader = XLSSQLCommand.ExecuteReader()
sScratchString = ""
While XLSSQLReader.Read
For i As Integer = 0 To XLSSQLReader.FieldCount - 1
If TypeOf XLSSQLReader(i) Is DBNull Then
sScratchString += "<NULL>, "
ElseIf XLSSQLReader(i).ToString = "" Then
sScratchString += "<BLANK>, "
Else
sScratchString += XLSSQLReader(i).ToString & ", "
End If
Next i
LogStatusMsg(sScratchString)
sScratchString = ""
End While
XLSSQLReader.Close()
As I said, it's working except that the first row is eaten. It's probably trying to use the first row as field names but the first row doesn't contain field names. The sheet is a form and the fields I'm interested in are sprinkled around on it.
For what it's worth, the Server Explorer in the VS IDE experiences the same shortcomming. The first row is missing and some of the data from the first row shows up as column headers. Not what I want at all.
An additional side, there are 4 sheets in this workbook. The VS IDE Server Explorer only sees two of them. The middle two. I can specify the missing sheets' names in the above sample and the data in them appears, but the sheets don't show up in the VS IDE Server Explorer. Any help on that would be nice as well (planning on opening this as a separate item later).
-g
See this documentation:
http://msdn2.microsoft.com/en-us/library/ms254500(VS.80).aspx
particularly the section: "Connecting to Excel"
It looks like "HDR=No" is part of the "Extended Properties" attribute when using the Microsoft.Jet.OLEDB... provider, not the way you are doing it. My bet is when you tried simply adding "HDR=No" to your connection string attributes, it was unrecognized and thus ignored. I bet you'll have to set it up like in the link I posted.
I have read all of the connection stuff I can find about this problem on Microsoft and other websites. I read the link provided and I'd already been there. None if it has answered the question 'How to stop Microsoft's ODBC driver from treating the first line of the spreadsheet as headers'.
Surely Microsoft has a way to do this? They have seen and conquered the problem with OLEdb. There isn't a way to say 'treat the first line as data' to the ODBC driver? <flabberghasted>You gotta be kidding me.</flabberghasted>
-g
OuttaCyTE,
According to your question on ODBC connection with Excel to display the first row, I would like to provide you the related information and advice as follows:
1. Please take a look at the KB article on the issue:
BUG: Excel ODBC Driver Disregards the FirstRowHasNames or Header Setting
When you use the Microsoft Excel Open Database Connectivity (ODBC) Driver to connect to an Excel worksheet as an ODBC data source, the driver assumes by default that the first row of worksheet data contains column headers (field names). According to the driver documentation, the optional FirstRowHasNames connection setting can be used to change this default behavior by using 0 for False and 1 for True. However, in fact, the driver disregards this setting and the first row of data is always treated as column headings. If the first row of data does not contain column headings or other entries, the effective result is that the first row of data "disappears."
The HDR= setting is available and functional when you use the Microsoft Jet OLE DB Provider and its Excel ISAM driver. Therefore, the best solution is to use the Jet Provider rather than the Excel ODBC driver.
Because the Excel ODBC driver always assumes that the first row contains field names, the first row must always contain either valid field names, or dummy entries that the developer is willing to disregard.
2. Column headings: By default, it is assumed that the first row of your Excel data source contains columns headings that can be used as field names. If this is not the case, you must turn this setting off, or your first row of data "disappears" to be used as field names. This is done by adding the optional HDR= setting to the Extended Properties of the connection string. The default, which does not need to be specified, is HDR=Yes. If you do not have column headings, you need to specify HDR=No; the provider names your fields F1, F2, etc. Because the Extended Properties string now contains multiple values, it must be enclosed in double quotes itself, plus an additional pair of double quotes to tell Visual Basic to treat the first set of quotes as literal values, as in the following example (where extra spaces have been added for visual clarity). .ConnectionString = "Data Source=C:\MyFolder\MyWorkbook.xls;" & _
"Extended Properties=" " Excel 8.0; HDR=No;" " "
For further information, please take a look at this article: How To Use ADO with Excel Data from Visual Basic or VBA
Hope that can help you.
