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

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.=20

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 as needed!). From what you write, it is not clear to me whether =
the "location" field is actually a unique identifier that you can use as =
a primary key for table_1. In this case, the code would become still =
noticeably simpler.
CREATE TABLE table_1 (=20
  table_1_id int(10) NOT NULL,
  location int(10) NOT NULL,
  name varchar(10) NOT NULL,=20
  PRIMARY KEY (table_1_id)
);
CREATE TABLE table_2 (=20
  table_2_id int(10) unsigned NOT NULL AUTO_INCREMENT,=20
  table_1_id int(10) NOT NULL,
  binary_assign varchar(10) NOT NULL,
  reference tinyint(1) NOT NULL,=20
  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=20
  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=20
  die $dbh->errstr;
my $insert_table_2 =3D $dbh->prepare(q{INSERT INTO table_2 (table_1_id, =
binary_assign, reference) VALUES (?, ?, ?)})=20
  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 =
uses 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 =
room 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 =
satisfy your assumptions on what they should look like.

Hope this helps (for starters).

\Gisbert




> -----Urspr=FCngliche Nachricht-----
> Von: francesca casalino [mailto:[email protected]]=20
> Gesendet: Mittwoch, 23. Februar 2011 12:30
> An: [email protected]
> Betreff: Perl script to insert data in mysql from Excel file
>=20
>=20
> Hi everybody,
>=20
>=20
>=20
> I am a real newbie in both perl and relational databases like=20
> mysql, and I
> have been banging my head on the wall trying to understand=20
> how to populate a
> mysql database using an Excel file (.csv).
>=20
>=20
>=20
> 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=20
> how to approach
> this problem, so I resort to the experts...Could you please=20
> help me understand
> how to approach this?
>=20
>=20
>=20
> The database on mysql has tables where each one is related to=20
> the other
> through foreign keys, so for example table_2 is:
>=20
>=20
>=20
> CREATE TABLE table_2 (
>=20
>   table_2_id int(10) unsigned NOT NULL AUTO_INCREMENT,
>=20
>   table_1_id int(10) NOT NULL,
>=20
>   binary_assign varchar(10) NOT NULL,
>=20
>   reference tinyint(1) NOT NULL,
>=20
>=20
>=20
>   PRIMARY KEY (table_2_id),
>=20
> );
>=20
>=20
>=20
> Now, my Excel file has 4 fields, with the first 2 fields that=20
> 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 "=20
> tabke_1_id"), but I
> am also looking for a way to record which column these values=20
> came from, by
> filling in another field in mysql with 0 if they came from=20
> the column "REF"
> and 1 if they came from "ALT".
>=20
>=20
>=20
> -----Table_1-----------                        ----Table_2---
>=20
> LOCATION    NAME             REF     ALT
>=20
> 1234                syd                   G         C
>=20
> 1235                brux                 C         T
>=20
>=20
>=20
> 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=20
> "REF" or from the
> column "ALT" (if REF then the value of "reference" in mysql=20
> table is 0,
> while if ALT the value of "reference" is 1).
>=20
>=20
>=20
> And the issue becomes even more complicated since the next=20
> columns contain
> information of the sample_id's, one column for each=20
> sample_id, and each has
> a specific value that I need to insert specific for each of=20
> these fields...
>=20
>=20
>=20
> Anyway if you could help me with the initial part that would=20
> be a great
> start, I am really stuck! Thank you so much!!
>=20
>=20
>=20
> ----------------------------------------------------------
>=20
> This is what I have done so far:
>=20
>=20
>=20
> #!/usr/bin/perl
>=20
> use strict;
>=20
> use warnings;
>=20
> use DBI();
>=20
> # Declare varaibles
>=20
> my $dbname =3D "test";
>=20
> my $user =3D "root";
>=20
> my $pass =3D "francy";
>=20
>=20
>=20
> #Connect to database or die
>=20
> my $dbh =3D DBI->connect("DBI:mysql:$dbname", "$user", "$pass")
>=20
>                                     || die "Could not connect=20
> to database:
> $DBI::errstr";
>=20
>=20
>=20
> my $insert_table_2=3D $dbh->prepare(q{INSERT INTO table_2=20
> (location, name)
> VALUES (?, ?)}) or die $dbh->errstr;
>=20
>=20
>=20
> #Open the file using filehandle
>=20
> my $file =3D shift(@ARGV);
>=20
> open (FILE, $file) or die "Couldn't read $file: $!";
>=20
>=20
>=20
> while (<FILE>)
>=20
>             {
>=20
>         chomp;
>=20
>         my @fields =3D split(',', $_);
>=20
>=20
>=20
>         my $loc =3D shift(@fields);
>=20
>         my $name =3D shift(@fields);
>=20
>=20
>=20
>        $insert_table_2->execute($loc, $name) or die $dbh->errstr;
>=20
>             }
>=20
>=20
>=20
> close (FILE);
>=20
> $dbh->disconnect();
>=20

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]