Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

sqldatasource dal, wwwxxxenden, convert raid5 to raid 10 mdadm, apache force chunked, nrao wwwxxx, xxxxxdup, procmail change subject header, wwwXxx not20, Wwwxxx.doks sas, linux raid resync after reboot

Links

XODOX
Impressum

#1: Perl script to insert data in mysql from Excel file

Posted on 2011-02-23 12:30:05 by francesca casalino

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

Report this message

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

Posted on 2011-02-23 19:13:21 by Wolfgang Radke

Hi Francesca,

your code looks alright.
What is the problem?

Wolfgang


Am 23.02.2011, 12:30 Uhr, schrieb francesca casalino
<francy.casalino@gmail.com>:

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

Report this message

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

Posted on 2011-02-23 22:00:25 by Henry Wong

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 <francy.casalino@gmail.com>
>> 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 (<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/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

Report this message

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

Posted on 2011-02-24 00:06:15 by Hal Wigoda

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


-----Original Message-----
>From: francesca casalino <francy.casalino@gmail.com>
>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 (<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/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Report this message

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

Posted on 2011-02-24 01:40:44 by Gisbert.Selke

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 (<FILE>) {
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=FCngliche 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 (<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

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

Report this message

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

Posted on 2011-02-24 11:28:27 by francesca casalino

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

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. <Gisbert.Selke@wido.bv.aok.de>

> 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 (<FILE>) {
> 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=FCngliche 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 (<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();
> >
>

--bcaec53f8f6d8d9fa5049d04ac39--

Report this message