Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses

Extra quotes doing SQL insert from Perl to CSV.

by JamieJ (Novice)
on Oct 10, 2019 at 15:34 UTC ( #11107315=perlquestion: print w/replies, xml ) Need Help??

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();

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);
      poj, Excellent! That did the trick, particularly 'always_quote' => 1 Thank you so much! -JJ
Re: Extra quotes doing SQL insert from Perl to CSV.
by Tux (Abbot) on Oct 11, 2019 at 06:27 UTC

    This is also documented: use csv_null.

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

    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
Re: Extra quotes doing SQL insert from Perl to CSV.
by choroba (Bishop) 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]
      Thank you for the tip. I am new to posting. I'll remember to cite crossposts next time.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://11107315]
Approved by Paladin
Front-paged by haukex
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (9)
As of 2019-10-14 17:49 GMT
Find Nodes?
    Voting Booth?