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开发者_Python百科 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');

I'm not in front of a Windows machine right now but this is approximately what you need to do. Find the ODBC Administrator and depending on whether you are going to be the only one running your Perl or others as well create a USER or SYSTEM DSN. Select Excel as the driver from the list and click add then fill in any required fields you are asked for - at least the location of the excel file. Give the DSN a name.

Now use DBI->connect('dbi:ODBC:DSN=name_you_gave_DSN');

Once connected, read about odbc_out_connect_string attribute which returns the ODBC out connection string. It will look something like:

Driver={Excel Files};workbook=c:\x.xls;something=somethingelse;

You can use that string instead of DSN=name_you_have_DSN in the connect call now and you'll no longer need the DSN you created - so called DSN-less connection.

After that there are loads of tutorials on using DBD::ODBC including the ones at http://www.easysoft.com/developer/languages/perl/index.html

I use the Microsoft Excel Driver outlined here in what's called a "DSN-less connection" where only the driver is specified and you specify the Excel file as a parameter in the connection string itself: http://www.connectionstrings.com/excel-2007-odbc/

    my $file = 'c:\temp\myfile.xslx';
    my $dbh = DBI->connect('dbi:ODBC:driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ='.$file.';');

    my $sth = $dbh->prepare( "SELECT * FROM [Sheet1\$]" );

    $sth->execute();

    while (my $row = $sth->fetchrow_hashref) {
        print Dumper( \$row );
    }

Watch out if you are running 64 bit perl and need to access a 32 bit ODBC driver though. You can't mix bits like that and have to resort to using 32 bit perl or some kind of ODBC bridge: Can i use a 32 Bit ODBC Driver for my 64 Bit app

More recently I have had to install the Microsoft Access Redistributable to get the Excel driver on Windows 10: https://stackoverflow.com/a/54757113/74585

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