Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

Perl Excel and ODBC

by ashojoseJ (Initiate)
on Oct 07, 2011 at 11:04 UTC ( #930165=perlquestion: print w/ replies, xml ) Need Help??
ashojoseJ has asked for the wisdom of the Perl Monks concerning the following question:

Hello There. I needed this help. I have an ODBC registered successfully through the "ODBC Data Source Administrator". Now I open excel --> click on Data --> Import External Data --> New Database Query This will show all databases in it Now I need to select the registered Database in step 1 and select a table from it. Is there a way I can do it using Perl ? Ashok

Comment on Perl Excel and ODBC
Re: Perl Excel and ODBC
by Corion (Pope) on Oct 07, 2011 at 11:26 UTC
Re: Perl Excel and ODBC
by davies (Vicar) on Oct 07, 2011 at 15:41 UTC

    Congratulations. You have successfully identified one of the most horrible rat's nests of complexity that Microsoft has ever inflicted on this miserable world. You are likely to catch some flak for not showing us what you have tried, though. In general, it's a good idea to record a macro in Excel and then to try to translate that into decent Perl, bearing in mind that the VBA generated by the macro recorder is not decent VBA.

    Getting back to what you want, however, I think it will look like this:

    use strict; use warnings; use diagnostics; use Win32::OLE; my $xl = Win32::OLE->new('Excel.Application'); $xl->{Visible} = 1; my $wb = $xl->Workbooks->Add(); for (2..$wb->Sheets->{Count}) { $wb->Sheets(2)->Delete; } my @cmd = ("SELECT bug_severity, short_desc FROM bugs"); my $qry = $wb->Sheets(1)->QueryTables->Add({ Connection => "ODBC;DATABASE=bugs;DSN=MyODBC;OPTION=0;PWD=sockmon +key;PORT=0;UID=bugs", Destination => $wb->Sheets(1)->Range("A1")}); $qry->{CommandText} = \@cmd; $qry->{Name} = "Query from MyODBC"; $qry->Refresh({BackgroundQuery => 0});

    The first two blocks in the code are pretty standard stuff needed to set up a clean instance of Excel. I'm not sure whether Corion's approach will actually do what you want. If you need to embed a query in the Excel spreadsheet so that a user will be able to refresh it without having Perl on his machine, I can't immediately see how it can be done that way. The code above will embed the SQL query so that it can be refreshed provided that the ODBC setup is the same on the target machine. This is not something I know how to control from Perl!

    You won't want to embed the user name (UID) and password in the code, but these are the defaults for BugZilla, and if anyone wants to see what I have on my machine, they're welcome as it's currently meaningless.

    The real nonsense comes with the CommandText option. Excel requires this to be an array. Passing it as a reference seems to be essential, as if you leave out the backslash, you get nothing unless you use diagnostics, in which case you get a SQL error. Passing the array without the backslash and printing the contents of the CommandText property returns the number of elements.

    Nor does the nonsense stop there. While the SQL is effectively unlimited in length, each array element is limited to something close to 240 characters. It's not 255 characters or anything sensible. You might want to write a little loop to check the array element lengths before committing to the query, as with the tail of a few lines knocked off in the wrong places, you might get something seriously unpatriotic happening to your server.

    Regards,

    John Davies

    Update: fixed two minor typos

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://930165]
Approved by Old_Gray_Bear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (12)
As of 2014-12-18 09:15 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (48 votes), past polls