Member Avatar for 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

  • 4 Contributors
  • 3 Replies
  • 127 Views
  • 6 Days Discussion Span
  • Latest Post Latest Post by mitchems

Dani AI

Generated

Building on , and : there are two practical routes — export to CSV (quick, portable) or produce a native Excel file (XLSX) when you need cell formats, multiple sheets or to avoid locale/CSV quirks. For CSV use Text::CSV_XS; for XLSX use Excel::Writer::XLSX; on Windows you can also automate a running Excel with Win32::OLE if you need the live application. DB access is handled through DBI with the appropriate DBD driver for your server. Docs: DBI, DBD::mysql, Text::CSV_XS, Excel::Writer::XLSX, Win32::OLE.

A practical, robust workflow:

  1. Install the modules (cpanm or cpan).
  2. Write a script that prepares a parameterized SQL statement, executes it, and streams rows instead of slurping them all into memory.
  3. For CSV: use Text::CSV_XS with binary => 1 and write with an explicit UTF-8 filehandle (and optionally a UTF-8 BOM so Excel recognises encoding).
  4. For XLSX: stream rows into Excel::Writer::XLSX using add_worksheet and write_row for best performance.

Example snippets (streaming, not memory-hungry):

use Text::CSV_XS;
# assume $sth is an executed statement handle
my $csv = Text::CSV_XS->new({ binary => 1, eol => "\n" });
open my $out, '>:encoding(utf8)', 'out.csv' or die $!;
$csv->print($out, [ @{ $sth->{NAME_lc} } ]);
while (my $r = $sth->fetchrow_arrayref) { $csv->print($out, $r) }
close $out;
use Excel::Writer::XLSX;
my $wb = Excel::Writer::XLSX->new('out.xlsx');
my $ws = $wb->add_worksheet;
$ws->write_row(0, 0, $header);
while (my $r = $sth->fetchrow_arrayref) { $ws->write_row($row++, 0, $r) }
$wb->close;

Troubleshooting tips: if DBD::mysql fails to build you probably need the MySQL client headers (e.g., libmysqlclient-dev on Debian/Ubuntu) or use a packaged Perl distro. Use DBI options like RaiseError for clearer failures, finish/ disconnect when done, and handle NULLs/dates explicitly (Excel often needs strings or proper date conversion). For simple importability start with CSV; switch to XLSX if you need typed cells or to avoid Excel locale issues.

Recommended Answers

All 3 Replies

Member Avatar for 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.

Member Avatar for 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.

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.