Access Excel 2013 from Delphi 7 via ODBC/ADO

For an quite old project, I need to read data from a Excel 2013 file, which could have one or more worksheets. Using SQL commands, I need to enumerate all worksheet names within the file and then selecting the records within one of those worksheets.

It is important that this will work with Delphi 7.

Any hints or docs are highly appreciated.

TIA
Michael
mkmkoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sinisa VukSoftware architectCommented:
You can get some light from this article ...

for newer Excel you need "newer" connection string with ado:
src:='Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=%s;Mode=Share Deny None;Extended Properties="Excel 12.0 Xml;HDR=yes";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False';

adoQry.ConnectionString := Format(src, ['c:\path to excel file']);

Open in new window

...another one hint....

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jimyXCommented:
Excel can be simply handled as Database and the Sheets are Tables.
Here is a nice tutorial from Zarko from Delphi.About.com.
jimyXCommented:
I repeated what Sinisa already provided.

Should have refreshed before posting.
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Manuel Lopez-MichelonePhD candidateCommented:
If yoy don´t know how to access excel directly to deal with files and columns in a sheet, you can transform (using OLE inside delphi) a sheet and saving it (converting it) to text file delimited by commas. In that way you can work with a simple text file.

regards
Lopem
mkmkoAuthor Commented:
Sorry for being late - I wasn't able to solve this problem. I guess it's caused by incompatibilities between old Delphi and new Excel.

At least I decided to purchase Delphi 10 Seattle, and now I'm looking for solutions using this version. Still looking for a way to get (Excel-) table access by ODBC, for accessing using SELECT SQL commands.

Is there any good information available for my current environment?

Thanks again (and sorry for not being able to solve the in the first try)
Michael
mkmkoAuthor Commented:
Ok, now I'm able to explain a little bit better what I'm trying to accomplish:

I have Excel2013 on a W2K8 server somewhere in the intranet and I have a Citrix session to run an application (written in Delphi 10 Seattle), accessing the Excel workbook via SQL/ADO.
On the Citrix server, Excel is installed, and so the (Jet?) drivers are.
I need to enumerate all Worksheets contained within one workbook, and then I need to access the worksheet data in a simple way, that is, every row is a data record and every column is a field within that record.
I'd like to do this all using plain SQL. Possible?

TIA,
Michael
jimyXCommented:
Is there any good information available for my current environment?

You have been given a link that leads to a good article. What did you try so far, and what result did you get?
mkmkoAuthor Commented:
@jimyX: I tried to use the code parts along with the connection string provided, but I couldn't get it working with Excel 2013, even if I changed the connection string to something else (i.e. 'Extended Properties=Excel 12.0' or '...=14.0' or the like). And because I've to try and run it on a Citrix server (where also MS Office is installed), it's very hard to find the cause - Citrix always cuts my entire session if the application can't connect and throws an exception. In other words, I need too much time for too less tries.
Installing Office on my development PC isn't an option, because we don't have local licenses available. The licenses are available on a per-user base within our server environment.

To save test and development time, our management at least purchased Delphi 10 Seattle, which doesn't need do go legacy ways of programming - i hope :)

OTOH - if only my connection string could be updated to work with Excel2013, it should also be usable with D10. But I couldn't get it working so far....

TIA
Michael
mkmkoAuthor Commented:
Finally I got it working - it had to do with the connection string. I hope that splitting my points is ok. Thanks to you both.

Michael
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.