Re: Perl script to insert data in mysql from Excel file

--bcaec53f8f6d8d9fa5049d04ac39
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Dear all perl experts,

Thank you very much for all your help! I have modifeied my script using Dr.
Helmut's suggested code (which is similar to what Selke is suggesting as
well). T*hank you* all for your comments and feedback because, even if I am
not using all your suggestions here (like the TEXT::CSV), I am sure I will
use them in other scripts!

This is the script that works, with one exception that I am currently tryin=
g
to understand, which is that it prints the line with headers, and if I say
to skip the first line (using next if ($. =3D=3D 1)), it oddly adds a 0 wit=
h
extra values to the first table, and therefore extra two values for the
second table as well.
Also, Helmut suggested to add a check on column number, but I cannot do tha=
t
unfortunately because my file continues (I wish it was finished...), and
what follows are IDs each with a specific location, name, ref and alt value=
!
And each in one cell split by semicolumns. I will have to figure out how to
loop through that as well.

Anyway here is what I have now, and thank you again!
---------------------------------------------------------------------------=
-------------

#!/usr/bin/perl

use strict;

use warnings;

use DBI;



# Declare variables for database access:

my $dbname =3D "test";

my $user   =3D "root";

my $pass   =3D "francy";



# Connect to database or die

my $dbh =3D DBI->connect("DBI:mysql:database=3D$dbname", $user, $pass) or d=
ie
"Could not connect to database: $DBI::errstr";



my $insert_table_1 =3D $dbh->prepare(q{INSERT INTO table_1 (table_1_id,
location, name) VALUES (?, ?, ?)}) or die $dbh->errstr;



#Open the file using filehandle

my $file =3D shift(@ARGV);

open (FILE, $file) or die "Couldn't read $file: $!";



while (<FILE>) {

            chomp;

            my $line =3D $_;

            my @fields =3D split(',', $_);

#Skip past the first line with column names

            next if ($. =3D=3D 1);

my $loc =3D shift(@fields);

            my $name =3D shift(@fields);



$dbh->do(q{INSERT INTO variation (location, name) VALUES (?, ?)}, undef,
$loc, $name);



            # get the primary key value of the inserted record of table 1

my $table_1_id =3D $dbh->selectrow_array (qq (select last_insert_id() ));



# insert the REF value in table 2

$dbh->do (qq (insert into table_2 (table_1_id, binary_assign, reference)

values (?,?,?) ), undef, $table_1_id, shift(@fields), 1);



# insert the ALT value in table 2

$dbh->do (qq (insert into table_2 (table_1_id, binary_assign, reference)

values (?,?,?) ), undef, $table_1_id, shift(@fields), 0);

            }

            close (FILE);

$dbh->disconnect();

2011/2/24 Selke, Gisbert W. <[email protected]>

> Hi Francy --
>
> If I understand correctly, the problem is not really related to Excel (in
> the sense of .xls files, for which you could use Sreadsheet::ParseExcel),
> and also not really to being able to read CSV files, which do have
> intricacies of their own, although these may not be immediately obvious.
> (Just in case that my understanding is wrong and that your CSV files are,
> after all, more complicated, do use either Text::CSV_XS, or even DBD::CSV=
,
> which would give you a sort of unified access to both the CSV and to he
> MySQL end of the task at your hand).
>
> This being out of the way, from what I understand, the problem is really =
to
> get the programming logic right. Actually, I think you're almost there,
> you've obviously studied the relevant docs well.
>
> Here's my attempt at the final steps I think you want to do. Lacking your
> input file, I could not test this, so likely there will be little errors
> here, but it's a start. I assume that your tables look like this (adapt a=
s
> needed!). From what you write, it is not clear to me whether the "locatio=
n"
> field is actually a unique identifier that you can use as a primary key f=
or
> table_1. In this case, the code would become still noticeably simpler.
> CREATE TABLE table_1 (
>  table_1_id int(10) NOT NULL,
>  location int(10) NOT NULL,
>  name varchar(10) NOT NULL,
>  PRIMARY KEY (table_1_id)
> );
> CREATE TABLE table_2 (
>  table_2_id int(10) unsigned NOT NULL AUTO_INCREMENT,
>  table_1_id int(10) NOT NULL,
>  binary_assign varchar(10) NOT NULL,
>  reference tinyint(1) NOT NULL,
>  PRIMARY KEY (table_2_id)
> );
>
> Here's my suggestion for the basic Perl code:
> #######################
> #!/usr/bin/perl
> use strict;
> use warnings;
>
> use DBI;
>
> # Declare variables for database access:
> my $dbname =3D "test";
> my $user   =3D "root";
> my $pass   =3D "francy";
>
> # Connect to database or die
> my $dbh =3D DBI->connect("DBI:mysql:database=3D$dbname", $user, $pass) or
>  die "Could not connect to database: $DBI::errstr";
>
> my $insert_table_1 =3D $dbh->prepare(q{INSERT INTO table_1 (table_1_id,
> location, name) VALUES (?, ?, ?)}) or
>  die $dbh->errstr;
> my $insert_table_2 =3D $dbh->prepare(q{INSERT INTO table_2 (table_1_id,
> binary_assign, reference) VALUES (?, ?, ?)})
>  or die $dbh->errstr;
>
> # Open the file using filehandle
> my $file =3D shift(@ARGV);
>
> open (FILE, $file) or die "Couldn't read $file: $!";
> my $table_1_id =3D 0;
>
> while (<FILE>) {
>  chomp;
>  my($loc, $name, $ref, $alt) =3D split(/,/);  # assuming your CSV files u=
ses
> comma as separator
>  $insert_table_1->execute(++$table_1_id, $loc, $name) or die $dbh->errstr=
;
>  $insert_table_2->execute($table_1_id, $ref, 0) or die $dbh->errstr;
>  $insert_table_2->execute($table_1_id, $alt, 1) or die $dbh->errstr;
> }
>
> close (FILE);
>
> $dbh->disconnect();
> #########################
>
> Obviously, there's no error checking on the INSERTs yet. There is also ro=
om
> for doing things more cleverly. E.g., this code assumes that table_1 is
> initially empty (otherwise likely the simple primary key generation will
> fail). You could fix this by first retrieving the maximum previously used
> table_1_id from the database, or more simply but less portably, you could
> use the mysql_insertid attribute (cf. the DBD::mysql docs).
>
> You also may need to handle cases where some fields may be missing from
> your input file. Or you may need to check whether your input fields satis=
fy
> your assumptions on what they should look like.
>
> Hope this helps (for starters).
>
> \Gisbert
>
>
>
>
> > -----Urspr=FCngliche Nachricht-----
> > Von: francesca casalino [mailto:[email protected]]
> > Gesendet: Mittwoch, 23. Februar 2011 12:30
> > An: [email protected]
> > Betreff: Perl script to insert data in mysql from Excel file
>  >
> >
> > Hi everybody,
> >
> >
> >
> > I am a real newbie in both perl and relational databases like
> > mysql, and I
> > have been banging my head on the wall trying to understand
> > how to populate a
> > mysql database using an Excel file (.csv).
> >
> >
> >
> > I constructed a my sql database (called "test"), set up the DBD::mysql
> > module, read a book on perl, but I still cannot figure out
> > how to approach
> > this problem, so I resort to the experts...Could you please
> > help me understand
> > how to approach this?
> >
> >
> >
> > The database on mysql has tables where each one is related to
> > the other
> > through foreign keys, so for example table_2 is:
> >
> >
> >
> > CREATE TABLE table_2 (
> >
> >   table_2_id int(10) unsigned NOT NULL AUTO_INCREMENT,
> >
> >   table_1_id int(10) NOT NULL,
> >
> >   binary_assign varchar(10) NOT NULL,
> >
> >   reference tinyint(1) NOT NULL,
> >
> >
> >
> >   PRIMARY KEY (table_2_id),
> >
> > );
> >
> >
> >
> > Now, my Excel file has 4 fields, with the first 2 fields that
> > should go into
> > the table_1, and the next two columns that should BOTH go into table_2
> > (table_2 is related to table_1 through the foreign key "
> > tabke_1_id"), but I
> > am also looking for a way to record which column these values
> > came from, by
> > filling in another field in mysql with 0 if they came from
> > the column "REF"
> > and 1 if they came from "ALT".
> >
> >
> >
> > -----Table_1-----------                        ----Table_2---
> >
> > LOCATION    NAME             REF     ALT
> >
> > 1234                syd                   G         C
> >
> > 1235                brux                 C         T
> >
> >
> >
> > The first 2 field go into table_1, and the REF and ALT values go into
> > table_2, but also record whether they came from the column
> > "REF" or from the
> > column "ALT" (if REF then the value of "reference" in mysql
> > table is 0,
> > while if ALT the value of "reference" is 1).
> >
> >
> >
> > And the issue becomes even more complicated since the next
> > columns contain
> > information of the sample_id's, one column for each
> > sample_id, and each has
> > a specific value that I need to insert specific for each of
> > these fields...
> >
> >
> >
> > Anyway if you could help me with the initial part that would
> > be a great
> > start, I am really stuck! Thank you so much!!
> >
> >
> >
> > ----------------------------------------------------------
> >
> > This is what I have done so far:
> >
> >
> >
> > #!/usr/bin/perl
> >
> > use strict;
> >
> > use warnings;
> >
> > use DBI();
> >
> > # Declare varaibles
> >
> > my $dbname =3D "test";
> >
> > my $user =3D "root";
> >
> > my $pass =3D "francy";
> >
> >
> >
> > #Connect to database or die
> >
> > my $dbh =3D DBI->connect("DBI:mysql:$dbname", "$user", "$pass")
> >
> >                                     || die "Could not connect
> > to database:
> > $DBI::errstr";
> >
> >
> >
> > my $insert_table_2=3D $dbh->prepare(q{INSERT INTO table_2
> > (location, name)
> > VALUES (?, ?)}) or die $dbh->errstr;
> >
> >
> >
> > #Open the file using filehandle
> >
> > my $file =3D shift(@ARGV);
> >
> > open (FILE, $file) or die "Couldn't read $file: $!";
> >
> >
> >
> > while (<FILE>)
> >
> >             {
> >
> >         chomp;
> >
> >         my @fields =3D split(',', $_);
> >
> >
> >
> >         my $loc =3D shift(@fields);
> >
> >         my $name =3D shift(@fields);
> >
> >
> >
> >        $insert_table_2->execute($loc, $name) or die $dbh->errstr;
> >
> >             }
> >
> >
> >
> > close (FILE);
> >
> > $dbh->disconnect();
> >
>

--bcaec53f8f6d8d9fa5049d04ac39--