Excel, OleDb, ACE, and skipping header rows
-
An Excel sheet I was given to import to SQL Server today has some header rows (four of them) before the column header row. I did this with ADO.net and the OleDb classes. I was hoping that there was some Extended Property that would allow me to skip the first four rows, but I saw none. connectionstrings.com and other sources didn't show anything either, so I eventually bit the proverbial bullet and simply skipped the first four rows from the DataReader and used the Fxx column names. It worked, but I figured I'd ask whether or not anyone here knows of such an Extended Property.
-
An Excel sheet I was given to import to SQL Server today has some header rows (four of them) before the column header row. I did this with ADO.net and the OleDb classes. I was hoping that there was some Extended Property that would allow me to skip the first four rows, but I saw none. connectionstrings.com and other sources didn't show anything either, so I eventually bit the proverbial bullet and simply skipped the first four rows from the DataReader and used the Fxx column names. It worked, but I figured I'd ask whether or not anyone here knows of such an Extended Property.
None I know about, but you can select a range from the sheet like:
SELECT * FROM [Sheet1$A4:F1243]Which could solve your problem. But you would need to know, or find out, how many rows there are before doing the select.Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
None I know about, but you can select a range from the sheet like:
SELECT * FROM [Sheet1$A4:F1243]Which could solve your problem. But you would need to know, or find out, how many rows there are before doing the select.Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
But could it then use the first row as column headers?
-
But could it then use the first row as column headers?
First row of the range.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
None I know about, but you can select a range from the sheet like:
SELECT * FROM [Sheet1$A4:F1243]Which could solve your problem. But you would need to know, or find out, how many rows there are before doing the select.Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
OK, I'm just looking at this again, mainly for future reference (though I'll likely forget). Thank you, yes, that works as described -- or very nearly. The problem is that the name of the sheet contains a SPACE and therefore has to be within apostrophes e.g.
['sheet 1$']and I have been unable to get that to work with a range.System.Data.OleDb.OleDbException (0x80040E37): The Microsoft Access database engine could not find the object ''Report 1$B5:AE5737''.System.Data.OleDb.OleDbException (0x80040E37): The Microsoft Access database engine could not find the object ''Report 1$'B5:AE5737'.Just for testing I renamed the sheet to remove the SPACE and it works. :thumbsup: Do you have a work around for this? -
OK, I'm just looking at this again, mainly for future reference (though I'll likely forget). Thank you, yes, that works as described -- or very nearly. The problem is that the name of the sheet contains a SPACE and therefore has to be within apostrophes e.g.
['sheet 1$']and I have been unable to get that to work with a range.System.Data.OleDb.OleDbException (0x80040E37): The Microsoft Access database engine could not find the object ''Report 1$B5:AE5737''.System.Data.OleDb.OleDbException (0x80040E37): The Microsoft Access database engine could not find the object ''Report 1$'B5:AE5737'.Just for testing I renamed the sheet to remove the SPACE and it works. :thumbsup: Do you have a work around for this?I never knew about the space problem, thanks for the feedback. I can't think of any workaround at the moment, but I'm having a look at it tomorrow as I'm having a potential bug in one of my programs.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions