Reading an Excel sheet using ADO/ODBC in Delphi 7

0
votes

I'm trying to read an Excel sheet from an XLS or XLSX file in memory using Delphi 7. When possible I use automation to read the cells one by one, but when Excel is not installed, I revert to using the ADO/ODBC Jet driver. I connect using either

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=file.xls;Extended Properties="Excel 8.0;Persist Security Info=False;IMEX=1;HDR=No";
Provider=Microsoft.ACE.OLEDB.12.0; Data Source=file.xlsx;Extended Properties="Excel 12.0;Persist Security Info=False;IMEX=1;HDR=No";

My problem then is that when I use the following query:

SELECT * FROM [SheetName$]

the returned results do not contain the empty rows or empty columns, so if the sheet contains such rows or columns, the following cells are shifted and do not end up in their correct position. I need the sheet to be loaded "as is", ie know exactly from what cell position each value comes from.

I tried to read the cells one by one by issuing one query of the form

SELECT F1 FROM `SheetName$A1:A1`

but now the driver returns an error saying "There is data outside the selected region". btw I had to use backticks to enclose the name because using brackets like this [SheetName$A1:A1] gave out a syntax error message.

Is there a way to tell the driver to select the sheet as-is, whithout skipping blanks? Or maybe a way to know from which cell position each value is returned?

For internal policy reasons (I know they are bad but I do not decide these), it is not possible to use a third party library, I really need this to work from standard Delphi 7 components.

2
exceldelphiodbcdelphi-7ado
Can you show a small example of such excel sheet, and to what you refer as empty rows? I'm not sure exactly what you want... BTW, Have you tried removing IMEX=1? this should return blank rows also. - kobik
Unfortunately I cannot give you a real life example, because these are not "small", which is the core of the problem (another problem being that the actual data is confidential...) ; data is not arranged nicely in columns. The "D" column for example first contains 5 lines of text then later a logo, then later some rows of data that I need to extract, then some more text, then some more data I need to extract, etc... IMEX=0 will not work because the column contains mixed types. - Stephan Leclercq

2 Answers

1
votes

I assume that if your data is say in the range B2:D10 for example, you want to include the column A as an empty column? Maybe? Is that correct? If that's the case, then your data set, when you read the sheet (SELECT * FROM [SheetName$]) would also return 1 million rows by 16K columns!

Can you not execute a query like: SELECT * FROM [SheetName$B2:D10] and use the ADO GetRows function to get an array - which will give you the size of the data. Then you can index into the array to get what data you want?

0
votes

OK, the correct answer is

Use a third party library no matter what your boss says. Do not even try ODBC/ADO to load arbitrary Excel files, you will hit a wall sooner or later.

It may work for excel files that contain a single data table, but not when you want to cherry pick data in a sheet primarily made for human consumption (ie where a single column contains some cells with introductory text, some with numerical data, some with comments, etc...)

  • Using IMEX=1 ignores empty lines and empty columns
  • Using IMEX=0 sometimes no longer ignores empty lines, but now some of the first non empty cells are considered field names instead of data, although HDR=No. Would not work anyway since valules in a column are of mixed types.
  • Explicitly looping across cells and making a SELECT * FROM [SheetName$A1:A1] works until you reach an empty cell, then you get access violations (see below)
Access violation at address 1B30B3E3 in module 'msexcl40.dll'. Read of address 00000000

I'm too old to want to try and guess the appropriate value to use so it works until someone comes with yet another mix of data in a column. Sorry for having wasted everybody's time.