JamieJ has asked for the wisdom of the Perl Monks concerning the following question:

When I write to a csv file from Perl using SQL Insert, I get either 0123 or """0123""", but I need "0123". Neither concatenation nor regex seem to resolve the issue.

Here's my code:

my $dbh = DBI->connect(qq{DBI:CSV:csv_eol=\n;csv_sep_char=\\,;});   
$dbh->{'csv_tables'}->{'Table'} = {'file' => 'data.csv','col_names' =>
+ 
["num","id"]};
#Setup error variables
$dbh->{'RaiseError'} = 1;
$@ = '';

## Attempts to change $num
##$num = '"'.$num.'"';## this causes """0123""" 
##$num = "\"$num\"";## this causes """0123""" even if I additionally d
+o this: 
## $num=~s/"""/"/g;
##$num = " ".$num;## causes " 0123" VERY CLOSE.  Try next line: 
##$num=~s/ //g;## This causes 0123
##$num = "".$num; ## causes 0123
##$num = "'".$num."'";## causes 123

my $value = "\'$num\',\'$id\'";
my $insert = "INSERT INTO Table VALUES ($value)";
my $sth = $dbh->prepare($insert);
$sth->execute();
$sth->finish();
$dbh->disconnect();

[download]

I would like to have the output of $num to end up being "0123" in the CSV, but instead I get 0123 or """0123""" or " 0123"

Any suggestions?

Replies are listed 'Best First'.
Re: Extra quotes doing SQL insert from Perl to CSV.
by poj (Abbot) on Oct 10, 2019 at 16:26 UTC

    Use placeholders and always_quote option

    #!/usr/bin/perl
use strict;
use DBI;

my $dbh = DBI->connect("dbi:CSV:", "", "",{
   'RaiseError' => 1 }
);  
 
$dbh->{'csv_tables'}->{'MyTable'} = {
 'file'         => 'data1.csv',
 'col_names'    => ["num","id"],
 'always_quote' => 1,
};

my $num = '0123';
my $id  = '0124';
my $sql = "INSERT INTO MyTable VALUES (?,?)";
my $result = $dbh->do($sql,undef,$num,$id);

    [download]
    poj
[reply]
[d/l]
      poj, Excellent! That did the trick, particularly 'always_quote' => 1 Thank you so much! -JJ
[reply]
Re: Extra quotes doing SQL insert from Perl to CSV.
by Tux (Canon) on Oct 11, 2019 at 06:27 UTC

    This is also documented: use csv_null.

     my $dbh = DBI->connect ("dbi:CSV:csv_null=1");

    [download]

    What I also do not understand is ;csv_sep_char=\\,;. Why escape the ,? The comma is not a special character to perl


    Enjoy, Have FUN! H.Merijn
[reply]
[d/l]
[select]
Re: Extra quotes doing SQL insert from Perl to CSV.
by choroba (Cardinal) on Oct 10, 2019 at 19:38 UTC
    Crossposted to StackOverflow. It's considered polite to inform about crossposting to avoid duplicate efforts of people not attending both the sites.

    map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]
[reply]
[d/l]
      Thank you for the tip. I am new to posting. I'll remember to cite crossposts next time.
[reply]

Back to Seekers of Perl Wisdom