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

{

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--

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
:

> 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 ()
>
> {
>
> 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/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

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, hwigoda@mindspring.com wrote:

> Isn't there a Perl module CSV that will extract data from a CSV file?
>=20
>=20
> -----Original Message-----
>> From: francesca casalino
>> Sent: Feb 23, 2011 5:30 AM
>> To: perl@lists.mysql.com
>> 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 ()
>>=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/perl?unsub=3Dhwong@dja.com
>=20



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

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
>Sent: Feb 23, 2011 5:30 AM
>To: perl@lists.mysql.com
>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 â€=9Ctestâ€=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â€=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 â€=9C tabke_1_i=
dâ€=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=
€œREFâ€=9D
>and 1 if they came from â€=9CALTâ€=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 â€=9CREF=E2=
€ or from the
>column â€=9CALTâ€=9D (if REF then the value of â€=9Creference=
â€=9D in mysql table is 0,
>while if ALT the value of â€=9Creferenceâ€=9D is 1).
>
>
>
>And the issue becomes even more complicated since the next columns contain
>information of the sample_idâ€=99s, one column for each sample_id, and=
each has
>a specific value that I need to insert specific for each of these fields=
â€=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 ()
>
> {
>
> 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/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

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

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.=20

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 (=20
table_1_id int(10) NOT NULL,
location int(10) NOT NULL,
name varchar(10) NOT NULL,=20
PRIMARY KEY (table_1_id)
);
CREATE TABLE table_2 (=20
table_2_id int(10) unsigned NOT NULL AUTO_INCREMENT,=20
table_1_id int(10) NOT NULL,
binary_assign varchar(10) NOT NULL,
reference tinyint(1) NOT NULL,=20
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 =3D "test";
my $user =3D "root";
my $pass =3D "francy";

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

my $insert_table_1 =3D $dbh->prepare(q{INSERT INTO table_1 (table_1_id, =
location, name) VALUES (?, ?, ?)}) or=20
die $dbh->errstr;
my $insert_table_2 =3D $dbh->prepare(q{INSERT INTO table_2 (table_1_id, =
binary_assign, reference) VALUES (?, ?, ?)})=20
or die $dbh->errstr;

# Open the file using filehandle
my $file =3D shift(@ARGV);

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

while () {
chomp;
my($loc, $name, $ref, $alt) =3D 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@gmail.com]=20
> Gesendet: Mittwoch, 23. Februar 2011 12:30
> An: perl@lists.mysql.com
> Betreff: Perl script to insert data in mysql from Excel file
>=20
>=20
> Hi everybody,
>=20
>=20
>=20
> I am a real newbie in both perl and relational databases like=20
> mysql, and I
> have been banging my head on the wall trying to understand=20
> how to populate a
> mysql database using an Excel file (.csv).
>=20
>=20
>=20
> 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=20
> how to approach
> this problem, so I resort to the experts...Could you please=20
> help me understand
> how to approach this?
>=20
>=20
>=20
> The database on mysql has tables where each one is related to=20
> 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=20
> 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 "=20
> tabke_1_id"), but I
> am also looking for a way to record which column these values=20
> came from, by
> filling in another field in mysql with 0 if they came from=20
> the column "REF"
> and 1 if they came from "ALT".
>=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=20
> "REF" or from the
> column "ALT" (if REF then the value of "reference" in mysql=20
> table is 0,
> while if ALT the value of "reference" is 1).
>=20
>=20
>=20
> And the issue becomes even more complicated since the next=20
> columns contain
> information of the sample_id's, one column for each=20
> sample_id, and each has
> a specific value that I need to insert specific for each of=20
> these fields...
>=20
>=20
>=20
> Anyway if you could help me with the initial part that would=20
> 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=20
> to database:
> $DBI::errstr";
>=20
>=20
>=20
> my $insert_table_2=3D $dbh->prepare(q{INSERT INTO table_2=20
> (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 ()
>=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

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

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

--bcaec53f8f6d8d9fa5049d04ac39
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Dear all perl experts,

Thank you very much for all your help! I have modifeied my script using Dr.
Helmut's suggested code (which is similar to what Selke is suggesting as
well). T*hank you* all for your comments and feedback because, even if I am
not using all your suggestions here (like the TEXT::CSV), I am sure I will
use them in other scripts!

This is the script that works, with one exception that I am currently tryin=
g
to understand, which is that it prints the line with headers, and if I say
to skip the first line (using next if ($. == 1)), it oddly adds a 0 wit=
h
extra values to the first table, and therefore extra two values for the
second table as well.
Also, Helmut suggested to add a check on column number, but I cannot do tha=
t
unfortunately because my file continues (I wish it was finished...), and
what follows are IDs each with a specific location, name, ref and alt value=
!
And each in one cell split by semicolumns. I will have to figure out how to
loop through that as well.

Anyway here is what I have now, and thank you again!
------------------------------------------------------------ ---------------=
-------------

#!/usr/bin/perl

use strict;

use warnings;

use DBI;



# Declare variables for database access:

my $dbname =3D "test";

my $user =3D "root";

my $pass =3D "francy";



# Connect to database or die

my $dbh =3D DBI->connect("DBI:mysql:database=3D$dbname", $user, $pass) or d=
ie
"Could not connect to database: $DBI::errstr";



my $insert_table_1 =3D $dbh->prepare(q{INSERT INTO table_1 (table_1_id,
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 () {

chomp;

my $line =3D $_;

my @fields =3D split(',', $_);

#Skip past the first line with column names

next if ($. == 1);

my $loc =3D shift(@fields);

my $name =3D shift(@fields);



$dbh->do(q{INSERT INTO variation (location, name) VALUES (?, ?)}, undef,
$loc, $name);



# get the primary key value of the inserted record of table 1

my $table_1_id =3D $dbh->selectrow_array (qq (select last_insert_id() ));



# insert the REF value in table 2

$dbh->do (qq (insert into table_2 (table_1_id, binary_assign, reference)

values (?,?,?) ), undef, $table_1_id, shift(@fields), 1);



# insert the ALT value in table 2

$dbh->do (qq (insert into table_2 (table_1_id, binary_assign, reference)

values (?,?,?) ), undef, $table_1_id, shift(@fields), 0);

}

close (FILE);

$dbh->disconnect();

2011/2/24 Selke, Gisbert W.

> 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 a=
s
> needed!). From what you write, it is not clear to me whether the "locatio=
n"
> field is actually a unique identifier that you can use as a primary key f=
or
> 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 =3D "test";
> my $user =3D "root";
> my $pass =3D "francy";
>
> # Connect to database or die
> my $dbh =3D DBI->connect("DBI:mysql:database=3D$dbname", $user, $pass) or
> die "Could not connect to database: $DBI::errstr";
>
> my $insert_table_1 =3D $dbh->prepare(q{INSERT INTO table_1 (table_1_id,
> location, name) VALUES (?, ?, ?)}) or
> die $dbh->errstr;
> my $insert_table_2 =3D $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 =3D shift(@ARGV);
>
> open (FILE, $file) or die "Couldn't read $file: $!";
> my $table_1_id =3D 0;
>
> while () {
> chomp;
> my($loc, $name, $ref, $alt) =3D split(/,/); # assuming your CSV files u=
ses
> 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 ro=
om
> 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 satis=
fy
> your assumptions on what they should look like.
>
> Hope this helps (for starters).
>
> \Gisbert
>
>
>
>
> > -----Ursprüngliche Nachricht-----
> > Von: francesca casalino [mailto:francy.casalino@gmail.com]
> > Gesendet: Mittwoch, 23. Februar 2011 12:30
> > An: perl@lists.mysql.com
> > 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 =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 ()
> >
> > {
> >
> > 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();
> >
>

--bcaec53f8f6d8d9fa5049d04ac39--