Bob_Gneu Bob_Gneu - 1 month ago 12
Perl Question

How do i connect Perl with an Excel file (*.xlsx or *.xls) using ODBC?

I have been asked to look into using ODBC as a database driver to be able to use SQL on an Excel workbook. I have seen a number of people using OLE as a driver, but the only ODBC walkthroughs I've seen are regarding connecting to an MSSQL Server or MySQL.

I have confirmed that i have ODBC setup and that it below are the ODBC drivers i have available. Can anyone shed some light on connecting to an XLS file?

Available Drivers:

DBI Drivers:
Chart, DBM, ExampleP, File, ODBC, Oracle, Proxy, SQLite, SQLite2, Sponge, mysql

ODBC Drivers:
DBI:ODBC:MS Access Database
DBI:ODBC:Excel Files
DBI:ODBC:dBASE Files
DBI:ODBC:Visio Database Samples
DBI:ODBC:Xtreme Sample Database 2003
DBI:ODBC:Xtreme Sample Database 2008


inside test.pl

my $dbh = DBI->connect('DBI:ODBC:Driver{Excel Files}MyExcelFile');

Answer

To connect you need connect string. There you can use DSN version ad bihica described, or show what driver you can use and use driver specific properties. For Excel this can look like:

Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;

You will find more examples at: http://www.connectionstrings.com/excel#p86