Batch inserts with Perl's DBI

Question

I'm trying to do a batch insert of rows of data into a postgres database.

I have the data populated in an array ref, I think. It was fetched using a perl script from a space delimited file using the Spreadsheet::BasicReadNamedColmodule. The code to fetch the data is

 $ss = new Spreadsheet::BasicReadNamedCol($xlsFileName) || die "Could not open '$xlsFileName': $!"; $ss->setColumns(@columnHeadings); my @array; my $row = 0; while (my $data = $ss->getNextRow()) { $row++; push @array, "@$data"; }

Below is the content of the array ref.

Cristan McX 123 W State Street North Aurora IL William Sch 123 South Third St #367 Geneva IL Kellie xxx 123 South East St. Gardner IL John xx 321 Princeton Ct. Frankfort IL Peter xxxxxxx 123 N Myrtle Avenue Elmhurst IL Izabella xxx 321 S 3rd St. #367 Geneva IL

The Perl DBI code I'm using to do the inserts is:

my $dbh = DBI->connect("DBI:Pg:dbname=greenthumb;host=localhost; port=5432","","", {'RaiseError' => 1}); my $sth = $dbh->prepare( 'INSERT INTO testtable (?, ?, ?, ?, ?, ?)' ); foreach(@array) { $sth->execute( @{$_} ); } $sth->finish;

The error I'm getting is:

Can't use string ("FirstName LastName BusinessName "...) as an ARRAY ref while "strict refs" in use at ./f.pl line 38.

$_ is not an array, it is a string. You'll likely want to split it first. — JRD
– JRD, Commented Nov 17, 2015 at 22:11
Additionally, if it truly is space separated, how are you going to differenciate the fields when they contain space, i.e. 123 W State Street North? — JRD
– JRD, Commented Nov 17, 2015 at 22:14
You also need the keyword VALUES before your list of values: INSERT INTO table_name VALUES .... It seems like you probably want to do your inserts in the loop that iterates over the spreadsheet contents rather than stringifying and de-stringifying. — Matt Jacob
– Matt Jacob, Commented Nov 17, 2015 at 22:33
$_ is not an arrayref, it is a string. But $data was an arrayref. You could have just said push @array, $data. — mob
– mob, Commented Nov 17, 2015 at 23:26

Curt Tilmes · Accepted Answer · 2015-11-18 04:16:24Z

Not a general DBI solution, but since you are using PostgreSQL, you can also use "COPY" for efficient bulk inserts, adding each row as you grab it from the spreadsheet:

... $dbh->do("COPY testtable FROM STDIN"); while ( my $data = $ss->getNextRow ) { $dbh->pg_putcopydata(join("\t", @$data) . "\n"); } $dbh->pg_putcopyend();

Borodin · Accepted Answer · 2015-11-18 03:18:44Z

Taking into account the advice you have received in comments and adding a few corrections of my own, your program should look something like this. I've not been able to test it as I'm posting from a tablet

There's no need to read the whole spreadsheet into memory -- simply insert the data into the database table as you read it from the XLS file

Clearly you will have to replace the column names with real-world values. If you simply want all of the data in the spreadsheet then you should use Spreadsheet::BasicRead

use strict; use warnings 'all'; use DBI; use DBD::Pg; use Spreadsheet::BasicReadNamedCol; use constant XLS_FILE => 'myfile.xls'; my $dbh = DBI->connect( 'dbi:Pg:dbname=greenthumb;host=localhost;port=5432', '', '', { RaiseError => 1 } ); my $insert = $dbh->prepare( 'INSERT INTO testtable VALUES (?, ?, ?, ?, ?, ?)' ); my $ss = Spreadsheet::BasicReadNamedCol->new( fileName => XLS_FILE, columns => [ qw/ FirstName LastName BusinessName col4name col5name col6name / ], skipHeadings => 1, skipBlankRows => 1, ) or die sprintf "Could not open '%s': %s", XLS_FILE, $!; while ( my $data = $ss->getNextRow ) { $insert->execute(@$data); }

Community · Accepted Answer · 2017-05-23 11:44:59Z

You get an array reference from $ss->getNextRow, but you dereference it and convert the array into a string when pushing it on to the array.

push @array, "@$data";

(The @$data dereferences the referenced array and "..." converts the dereferenced array into a string.)

If you just pushed the array reference that you have, then everything would work.

push @array, $data;

But I like Borodin's answer as it eliminates the need for the intermediate @array and processes the spreadsheet a row at a line.

Collectives™ on Stack Overflow

Batch inserts with Perl's DBI

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related