List:MySQL and Perl« Previous MessageNext Message »
From:Gisbert W. Selke Date:February 24 2011 12:40am
Subject:AW: Perl script to insert data in mysql from Excel file
View as plain text  
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 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 ( 
  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 = "test";
my $user   = "root";
my $pass   = "francy";

# Connect to database or die
my $dbh = DBI->connect("DBI:mysql:database=$dbname", $user, $pass) or 
  die "Could not connect to database: $DBI::errstr";

my $insert_table_1 = $dbh->prepare(q{INSERT INTO table_1 (table_1_id, location, name)
VALUES (?, ?, ?)}) or 
  die $dbh->errstr;
my $insert_table_2 = $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 = shift(@ARGV);

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

while (<FILE>) {
  chomp;
  my($loc, $name, $ref, $alt) = 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üngliche Nachricht-----
> Von: francesca casalino [mailto:francy.casalino@stripped] 
> Gesendet: Mittwoch, 23. Februar 2011 12:30
> An: perl@stripped
> 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 = "test";
> 
> my $user = "root";
> 
> my $pass = "francy";
> 
> 
> 
> #Connect to database or die
> 
> my $dbh = DBI->connect("DBI:mysql:$dbname", "$user", "$pass")
> 
>                                     || die "Could not connect 
> to database:
> $DBI::errstr";
> 
> 
> 
> my $insert_table_2= $dbh->prepare(q{INSERT INTO table_2 
> (location, name)
> VALUES (?, ?)}) or die $dbh->errstr;
> 
> 
> 
> #Open the file using filehandle
> 
> my $file = shift(@ARGV);
> 
> open (FILE, $file) or die "Couldn't read $file: $!";
> 
> 
> 
> while (<FILE>)
> 
>             {
> 
>         chomp;
> 
>         my @fields = split(',', $_);
> 
> 
> 
>         my $loc = shift(@fields);
> 
>         my $name = shift(@fields);
> 
> 
> 
>        $insert_table_2->execute($loc, $name) or die $dbh->errstr;
> 
>             }
> 
> 
> 
> close (FILE);
> 
> $dbh->disconnect();
> 
Thread
Perl script to insert data in mysql from Excel filefrancesca casalino23 Feb
  • AW: Perl script to insert data in mysql from Excel fileGisbert W. Selke24 Feb
    • Re: Perl script to insert data in mysql from Excel filefrancesca casalino24 Feb
  • Re: Perl script to insert data in mysql from Excel fileWolfgang Radke24 Feb
Re: Perl script to insert data in mysql from Excel filehwigoda23 Feb
  • Re: Perl script to insert data in mysql from Excel fileHenry Wong23 Feb