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

TEXT::CSV

or=20

Spreadsheet::ParseExcel makes you to get information from Excel95, =
Excel97, Excel2000 file.

On Feb 23, 2011, at 12:26 PM, [email protected] wrote:

> Isn't there a Perl module CSV that will extract data from a CSV file?
>=20
>=20
> -----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
>>=20
>> Hi everybody,
>>=20
>>=20
>>=20
>> 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).
>>=20
>>=20
>>=20
>> 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 =
understand
>> how to approach this?
>>=20
>>=20
>>=20
>> The database on mysql has tables where each one is related to 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 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 =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 =
=93REF=94
>> and 1 if they came from =93ALT=94.
>>=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 =93REF=94 =
or from the
>> column =93ALT=94 (if REF then the value of =93reference=94 in mysql =
table is 0,
>> while if ALT the value of =93reference=94 is 1).
>>=20
>>=20
>>=20
>> 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 has
>> a specific value that I need to insert specific for each of these =
fields=85
>>=20
>>=20
>>=20
>> Anyway if you could help me with the initial part that would 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 to =
database:
>> $DBI::errstr";
>>=20
>>=20
>>=20
>> my $insert_table_2=3D $dbh->prepare(q{INSERT INTO table_2 (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
>=20
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe:    http://lists.mysql.com/[email protected]
>=20



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