Perl Script Need... aka Point me in the right direction

[sk8te320]

Hi to All,
I am I guess for lack of a better word, A Newbie when it comes to Perl. I was given a project to accomplish the following;

1. write a perl executable script that will execute a SQL query.

2. then I need to take the data retrieved by the query and either convert it into a .csv file and then import the data into a Excel spreadsheet or, if possible, just import the query data into an Excel spreadsheet.

I need to know if someone could possibly point me in the right direction and I'll take it from there....

Thanks for any and ALL input....

sk8te320

[KevinADC]

There is no short or easy answer to your questions.

Perl uses the DBI (Database Interface) module to interact with a database. You also have to install the relevant drivers. For example if you wanted to communicate with a MySQL datasase you would install DBD::mysql.

For interaction with Excel you would use one of the Excel modules. The Excel module (or modules) would also need to be installed as they are not core perl modules.

The place to start on this long journey is CPAN:

http://search.cpan.org/

You can find the DBI module and DBD::mysql and search for Excel and read through the documentation of the Excel modules to find one that will suit your requirements.

My database skills are so rusty that I will not be able to help you there and I have never used any of the Excel modules so I can't help with that either. Google will be your friend to find resources.

[Comatose]

I'm not a big fan of just giving out code.... especially when I know it's for homework, but let me give you some detail.... once you do as KevinADC suggested, and get the DBI module installed, you need to have the SQL database setup. In this example, I connect to the mysql database called "tasks", with the username of "taskuser", and the password of "password":

use DBI;
$dbh = DBI->connect("DBI:mysql:tasks", "taskuser", "password");

The magic actually comes in the next few lines (ie, what you need), where we first prepare the sql connection, and then actually execute it. You need to know the name of the table, and need to have pretty intimate knowledge of the entities in your database. In the following, I'm returning all columns from the users table:

$query = $dbh->prepare("SELECT * FROM users");
$query->execute();

Now that we have executed the query, we should get some kind of response of records from the database right? No. We have to tell the DBI module to fetch the rows with fetchrow(). The fetchrow() method will return the data that we queried from the DB. I prefer to define a bunch of scalars that represent each column, but you could use an array.... it's uglier though, because then you have to keep track of which indice is linked to which column. This uses fetchrow() to retrieve a basic record from a users table... keep in mind, that this retrieves just one record, but you could very easily have many records, in which case you could make the scalars arrays... or keep them as scalars, and push them onto an array.... whatever:

($duid, $fname, $lname, $address, $phonenum) = $query->fetchrow();

That should give you a good step in the right direction for the rest of your application.

[mitchems]

I do this all the time. What database are you reading from? I can post some code that does this, but what you really need is a combination of two things: OLE and DBI/DBD. The DBI interface is an abstract interface to databases, DBD is the driver. You will need to install the DBD driver for your database and the DBI interface for that driver. Then use the DBI object to attach to the sql database and execute the sql. Then loop through the result. If you want to put the stuff DIRECTLY into excel (rather than first going to csv) I use OLE, which opens Excel and places the stuff into each cell directly and then saves and closes the file. Like I said I can post some code, but that's the basic way.