Perl script to insert data in mysql from Excel file

--bcaec53f903f1e83a4049cf16bb0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

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 =93test=94), 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=85Could you please help me underst=
and
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 int=
o
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 =93 tabke_1_id=94), =
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 =93RE=
F=94
and 1 if they came from =93ALT=94.



-----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 =93REF=94 or fro=
m the
column =93ALT=94 (if REF then the value of =93reference=94 in mysql table i=
s 0,
while if ALT the value of =93reference=94 is 1).



And the issue becomes even more complicated since the next columns contain
information of the sample_id=92s, one column for each sample_id, and each h=
as
a specific value that I need to insert specific for each of these fields=85



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

--bcaec53f903f1e83a4049cf16bb0--