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

Isn't there a Perl module CSV that will extract data from a CSV file?


-----Original Message-----
>From: francesca casalino <[email protected]>
>Sent: Feb 23, 2011 5:30 AM
>To: [email protected]
>Subject: 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 =E2=80=9Ctest=E2=80=9D), set up th=
e 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=E2=80=A6Could 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 in=
to
>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 =E2=80=9C tabke_1_i=
d=E2=80=9D), but I
>am also looking for a way to record which column these values came from, b=
y
>filling in another field in mysql with 0 if they came from the column =E2=
=80=9CREF=E2=80=9D
>and 1 if they came from =E2=80=9CALT=E2=80=9D.
>
>
>
>-----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 =E2=80=9CREF=E2=
=80=9D or from the
>column =E2=80=9CALT=E2=80=9D (if REF then the value of =E2=80=9Creference=
=E2=80=9D in mysql table is 0,
>while if ALT the value of =E2=80=9Creference=E2=80=9D is 1).
>
>
>
>And the issue becomes even more complicated since the next columns contain
>information of the sample_id=E2=80=99s, one column for each sample_id, and=
 each has
>a specific value that I need to insert specific for each of these fields=
=E2=80=A6
>
>
>
>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();


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