SQL query to read a single cell from an Excel spreadsheet...

[Mark- 31]

Hello,

 

Delphi 10.2.3, FireDAC, OBDC.

 

Does an SQL query exist that can read a single cell from an Excel spreadsheet?

 

Thanks,

 

Mark

Edited March 9 by Mark-

[Lars Fosdal 1832]

There are numerous posts on StackOverflow on this topic, most asking why it is not working as expected 😛
Would it be an alternative to open the excel file in excel and use COM to extract the data from Delphi code?

[Mark- 31]

3 hours ago, Lars Fosdal said:

There are numerous posts on StackOverflow on this topic, most asking why it is not working as expected 😛
Would it be an alternative to open the excel file in excel and use COM to extract the data from Delphi code?

Thanks for your response.

 

It needs to be accomplished with SQL and I can say after many hours of trying, it is not possible. Excel does not provide a naming scheme to access a cell, like, "B2" via SQL queries. If it does, no one has ever documented the syntax and published it. I tried a few formats, no joy.

 

It really, is not surprising. SQL/databases are all about records/tables and its structures. Or that is the way it appears to me. A single cell, does not fit the table name/field name paradigm.

[Die Holländer 82]

Quote

SELECT *  
  FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',  
   'Excel 12.0; Database=C:\ExcelFile.xlsx; HDR=NO; IMEX=1',  
   'SELECT * FROM [Sheet1$A1:A1]');

Maybe such is possible..

[TomF 3]

Maybe you can use Firedac via ODBC to get a dataset from the Excel-File on your Form / DataModule.
FireDac has LocalSQL to query any dataset with SQL. So eventually you could query this dataset via SQL.

[Mark- 31]

33 minutes ago, Die Holländer said:

Maybe such is possible..

Thanks for the response.

 

The syntax has a problem but, changing to SELECT * FROM `Sheet1$A1:A1` was accepted. The result is interesting. 

 

TomF, I am using FireDAC and ODBC.