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

Hi Francesca,

your code looks alright.
What is the problem?

Wolfgang


Am 23.02.2011, 12:30 Uhr, schrieb francesca casalino  
<[email protected]>:

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

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