Gossamer Forum
(Advanced Search)
for
Home : General : Perl Programming :

Export line to tables in mySql

Dec 4, 2003, 9:58 PM
Suomi
User (194 posts)
Dec 4, 2003, 9:58 PM
Post #1 of 7
Views: 7483
Shortcut
Quote Reply
Export line to tables in mySql
How do I post following text file to the MySql database?
Code:
apple 245red 12.5
banana 19 yellow25.4
pineapple789 1.0
orange orange
banana 12 yellow 25


Data format is always same:

name nnnnnnnnn (9 Chars); count ddd (3 digits); color nnnnnn (6 chars); dddd (4 digits with decimal)
Each record is terminated with an end-of-line character.

Looking for a command that will post above sample info (actual sample.txt has appx 20.000 records)
with correct Regex to my database tables.

Text file name: sample.txt
Database name: fruits ; Username: Admin ; Password: pass
Tablenames: name; count; color; weight

Thank you in advance Cool

Sincerely: Suomi
(Program is variation of "Stock" database and sample.txt changes once a minute)

Last edited by:

Suomi: Dec 4, 2003, 10:29 PM
Dec 5, 2003, 7:42 AM
Chaz
Enthusiast (819 posts)
Dec 5, 2003, 7:42 AM
Post #2 of 7
Views: 7288
Shortcut
Quote Reply
Re: [Suomi] Export line to tables in mySql In reply to
Hi Suomi,

Here is some code that you can modify to work for you:

Code:
#!/usr/bin/perl
use warnings;
use strict;
use DBI;
use Fcntl qw/:flock/;

my $dbh = DBI->connect('DBI:mysql:database=fruits;host=localhost',
'Admin',
'pass',
{'RaiseError' => 1}) or die $DBI::errstr;


my $query = qq~INSERT INTO table_name (name, count, color, weight) VALUES (?, ?, ?, ?)~;
my $sth = $dbh->prepare($query);

open DATA, "sample.txt";
flock DATA, LOCK_EX;
while ( <DATA> ) {
chomp;
$sth->execute(split /\|/);
}
close DATA;

You have a couple of problems with your data though. 1) You need a primary key in your table if you will have duplicate fruit names:

Code:
# Table structure for table `fruits`
#
# Creation: Dec 05, 2003 at 07:33 AM
# Last update: Dec 05, 2003 at 07:33 AM
#
CREATE TABLE `fruits` (
`fruit_id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(9) binary NOT NULL default '',
`count` int(3) NOT NULL default '0',
`color` varchar(6) binary NOT NULL default '',
`weight` decimal(3,1) NOT NULL default '0.0',
PRIMARY KEY (`fruit_id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

2) You need uniform data and some sort of field separator in order to insert it into the database:

Code:
apple|245|red|12.5
banana|19|yellow|25.4
pineapple|789|yellow|1.0
orange|0|orange|1.0
banana|12|yellow|25

You can use tabs instead of pipes but you need to fill the empty spaces or make sure your table can handle null values (and adjust the code above accordingly).

Read through the DBI pod for more info.

~Charlie

Last edited by:

Chaz: Dec 5, 2003, 7:43 AM
Dec 5, 2003, 8:23 PM
Suomi
User (194 posts)
Dec 5, 2003, 8:23 PM
Post #3 of 7
Views: 7288
Shortcut
Quote Reply
Re: [Chaz] Export line to tables in mySql In reply to
Thanks Chaz for your answer.

About primary key fruit_name is primary key... ie if apple comes twice, first will get overwritten (I hope)

Second about pipe lines |. There must be a way to chop it on the fly (I would waste too much processing power and time, if I do everything twice.

Read 1 line at a time, and post it directly to MySQL database. (My perl is so rusty Tongue, I cant remember)

Thx

Suomi
Dec 5, 2003, 9:36 PM
Chaz
Enthusiast (819 posts)
Dec 5, 2003, 9:36 PM
Post #4 of 7
Views: 7302
Shortcut
Quote Reply
Re: [Suomi] Export line to tables in mySql In reply to
In Reply To:
Thanks Chaz for your answer.

No problem.

In Reply To:
About primary key fruit_name is primary key... ie if apple comes twice, first will get overwritten (I hope)

Nope, not with an INSERT. MySQL will complain instead of overwrite. Check into REPLACE, that might work for you.

In Reply To:
Second about pipe lines |. There must be a way to chop it on the fly (I would waste too much processing power and time, if I do everything twice.

How are your fields deliminated now? It looks like it might be tabs but it's hard to tell with the sample you gave. If it's tab deliminated, just change the regex on the split and it should work.

If some of your fields are blank (undef really) then you will need to make sure that your column will accept a NULL for that field.

~Charlie
Dec 6, 2003, 7:08 AM
Suomi
User (194 posts)
Dec 6, 2003, 7:08 AM
Post #5 of 7
Views: 7263
Shortcut
Quote Reply
Re: [Chaz] Export line to tables in mySql In reply to
Thx again.

I can take care of that Null/primary key problem.

sample.txt is NOT deliminated at all (it all comes out, as I tried to explain in first post)

i.e. name nnnnnnnnn (9 Chars); count ddd (3 digits); color nnnnnn (6 chars); dddd (4 digits with decimal)
Each record is terminated with an end-of-line character.

Line is always specific lenght where first 9 chars of the line has name value, next 3 digits is value of the count etc....Max data for line is 22 spaces.

ie one line could be: pineapple456orange7895

ThanksShocked
Dec 7, 2003, 11:17 AM
Suomi
User (194 posts)
Dec 7, 2003, 11:17 AM
Post #6 of 7
Views: 7253
Shortcut
Quote Reply
Re: [Suomi] Export line to tables in mySql In reply to
Just found this...

It might help here, but as I said, my perl is rusty... any help is appreciated.Cool

http://theoryx5.uwinnipeg.ca/...gth/FixedLength.html
Dec 11, 2003, 2:29 PM
Mel_g
Staff (79 posts)
Dec 11, 2003, 2:29 PM
Post #7 of 7
Views: 7237
Shortcut
Quote Reply
Re: [Suomi] Export line to tables in mySql In reply to
Hi Suomi,

I believe I can help you with this. Will send you a Private Message with a quote on this job.

Mel.
Mel Goulet
Developer - Gossamer Threads, Inc.