Issue with using CSV_XS and DBI in Perl to write SQL query results

Question

I'm attempting to use the following Perl script to write the results of a SQL query to a CSV file:

#!/usr/bin/perl
#
use DBI;
use Text::CSV_XS;
use strict;

my $dbh = DBI->connect('DBI:mysql:dbname', 'username', 'password') ||
    die "Cannot connect to database\n", $DBI::errstr;

my $sth = $dbh->prepare("SELECT g.factGroupName, b.CI, a.Config
                    FROM alpha a
                    INNER JOIN beta b ON a.TypeId = b.MetricType
                    INNER JOIN gamma g ON g.factGroupId = b.Factgroup
                    WHERE a.ToolName = 2 AND a.TypeName = 'inputs.conf' AND a.Deploy = 'Y'");
$sth->execute || die "failed to execute:\n ", $DBI::errstr;


my $csv = Text::CSV_XS->new({ 'quote_char'  => '"',
                       'escape_char' => '"',
                       'sep_char'    => ',',
                       'binary'      => 0,
                       'eol'         => "\r\n"
                     });

open (my $FH, '>', '/home/XXXXX/inputs.csv') || die "Cannot open file\n";

while (my @row = $sth->fetchrow_array) {
  if ($csv->combine(@row)) {
    print $csv->string;
  } else {
my $err = $csv->error_input;
print "combine() failed on argument: ", $err, "\r\n";
  }
}

close $FH;

$dbh->disconnect;

When I execute the script, this is the output I receive on my display:

oem,/opt/oracle/product/gc_inst1/em/EMGC_OMS*/sysman/log/emctl.log,"sourcetype=servicelog2,index=oem_prod"
oem,/opt/oracle/product/gc_inst1/em/EMGC_OMS*/sysman/log/emoms.log,"sourcetype=servicelog2,index=oem_prod"
oem,/opt/oracle/product/gc_inst1/user_projects/domains/GCDomain/servers/EMGC_OMS*/logs/EMGC_OMS*.log,"sourcetype=Bealog,index=oem_prod"

Only problem is, it's not writing to the inputs.csv that I cited in the script; that file exists in the given directory, but has a filesize of zero.

The other issue is that the third field ("a.Config") being returned in each line of output has double quotes that I'd like to remove before it's written to the CSV file. The field contains two strings separated by a comma, and is stored using a GLOB configuration in the MySQL database with no quotes used; my guess is that the DBI query is adding the double quotes as part of its operation.

Any assistance would be greatly appreciated!

If you're not actually manipulating the data with Perl, you can dump query results to CSV directly with MySQL using SELECT ... INTO OUTFILE. — ThisSuitIsBlackNot
– ThisSuitIsBlackNot, Commented Feb 23, 2017 at 15:27

simbabque · Accepted Answer · 2017-02-23 15:15:23Z

1

Writing to the file

You need to tell Text::CSV_XS that it should write to your file.

if ($csv->combine(@row)) {
   print $csv->string;
}

This prints to the screen. Instead, print to $FH.

if ($csv->combine(@row)) {
   print $FH $csv->string;
}

It's probably better to use $csv->print directly as that is more efficient.

$csv->print($FH, \@row);

In that case, error_input is undef so you can't use it for displaying debugging information.

Removing double quotes

I don't think those come from the database. You set your escape_char to ", and the data contains a comma ,, so Text::CSV_XS correctly adds quotes around the value. If you don't want that, but actually want to treat the value in $row[2] like multiple values, split it on comma.

while (my @row = $sth->fetchrow_array) {
    $csv->print($FH, [ @row[0, 1], split /,/, $row[2] ] );
}

The @row[1, 2] is an array slice. It's the same as saying $row[0], $row[1].

edited Feb 23, 2017 at 15:15

answered Feb 23, 2017 at 15:09

simbabque

54.4k8 gold badges77 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

B. Juice Over a year ago

Thanks for the guidance! This is now working as I anticipated.

Collectives™ on Stack Overflow

Issue with using CSV_XS and DBI in Perl to write SQL query results

1 Answer 1

Writing to the file

Removing double quotes

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Writing to the file

Removing double quotes

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related