2

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');
Bob_Gneu
  • 1,591
  • 1
  • 18
  • 30
  • This syntax is what i am looking for: Driver={Excel Files};workbook=c:\x.xls;something=somethingelse; This is going to be (ultimately) incorporated into an existing reporting utility and i don't think that creating a DNS entry will necessarily work out. I would like to combine this with the later answer if possible, but for now ill look into this for my POC. Ill upvote if either of them work. – Bob_Gneu Jul 13 '11 at 18:11

3 Answers3

1

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

Matthew Lock
  • 13,144
  • 12
  • 92
  • 130
1

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

bohica
  • 5,932
  • 3
  • 23
  • 28
  • I was unable to get your example to work, and i had already been to the easysoft websites. Thank you for the ideas though. They helped with another problem we had. – Bob_Gneu Jul 14 '11 at 18:22
  • I could not connect to the xls file with the connection string you provided. It was very close to the string needed though, so +1 for you. – Bob_Gneu Jul 15 '11 at 18:09
0

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

Michał Niklas
  • 53,067
  • 18
  • 70
  • 114
  • Just to clarify, my answer would give you the string you quote after which you could (if you want) delete the DSN. – bohica Jul 13 '11 at 13:40