Export as CSV File

[emunir]

Hi,

I am working with Perl and SQL and need certain data to be exported as a csv file. I also need it to work so that if the field contains a comma it doesn't confuse it and read it as two fields. The fields can contain " or any other special characters as well.

So what do I need to do for this?

Thanx!

[moltar]

You need to escape all the special characters. Like this:

"1","Here is a comma\, and this is quote\""

[emunir]

I need to know the full code to export the data from a sql table using Perl/CGI. And the commas and quotations are not fixed ... the data depends on what info a person has entered via a form so this method won't work for me.

Thanx!

[moltar]

I think this is right, haven't tested it though. But you get the idea.

#!/usr/bin/perl
use DBI;
$dbh = DBI->connect(...);
$sth = $dbh->prepare("SELECT * FROM table_name");
open(CSV, "> file.csv");
while ( $array_ref = $sth->fetchrow_arrayref ) {
 foreach my $col ( @$array_ref ) {
 print CSV '"' . strip($col) . '", ';
 }
 print CSV "\n";
}
close(CSV);
$dbh->disconnect();
sub strip {
 my $string = $_[0];
 $string =~ s/\"/\\"/g;
 $string =~ s/\\/\\\\/;
 $string =~ s/,/\\,/g;
}

[Storyteller]

I just want to note that it's often not a job for Perl. If you're using MySQL, read on SELECT INTO OUTFILE syntax. Many other DBs also have means of dumping out CSV data.

[emunir]

Yes, I know it can be exported via the SQL database directly but I won't be doing it myself. The data needs to be exported by people who won't have access to the database. So they will need to do that via an admin section programmed in Perl.