headers
francesca casalino | 23 Feb 2011 12:30
Picon

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

Permalink | Reply |
headers
hwigoda | 23 Feb 2011 18:26
Picon

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 <francy.casalino <at> gmail.com>
>Sent: Feb 23, 2011 5:30 AM
>To: perl <at> 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 “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:
>
>
>
(Continue reading)

Permalink | Reply |
headers
Henry Wong | 23 Feb 2011 22:00

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

TEXT::CSV

or 

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

On Feb 23, 2011, at 12:26 PM, hwigoda <at> mindspring.com wrote:

> Isn't there a Perl module CSV that will extract data from a CSV file?
> 
> 
> -----Original Message-----
>> From: francesca casalino <francy.casalino <at> gmail.com>
>> Sent: Feb 23, 2011 5:30 AM
>> To: perl <at> 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 “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
(Continue reading)

Permalink | Reply |
headers
Selke, Gisbert W. | 24 Feb 2011 01:40
Picon

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. 

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

Permalink | Reply |
headers
francesca casalino | 24 Feb 2011 11:28
Picon

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

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 trying
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 with
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 that
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;
(Continue reading)

Permalink | Reply |
headers
Wolfgang Radke | 23 Feb 2011 19:13
Picon
Picon

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  
<francy.casalino <at> 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?
>
>
(Continue reading)

Permalink | Reply |

Gmane