Beefy Boxes and Bandwidth Generously Provided by pair Networks kudra
Perl Monk, Perl Meditation
 
PerlMonks  

Re: How to insert a tab delimited text file to a mysql database

by Withigo (Friar)
on Apr 16, 2006 at 23:59 UTC ( #543731=note: print w/ replies, xml ) Need Help??


in reply to How to insert a tab delimited text file to a mysql database

Since you said it's a one-time script, perl may be overkill for what can be done in one or two shell commands.
Create your table:

mysql -u [username] -e "use [database name];CREATE TABLE games (file v +archar(30) primary key, name text, description text, counter int)"
Populate it with the data file:
mysql -u [username] -e"use [database name];LOAD DATA INFILE '../data/g +ames/descriptions.txt' REPLACE INTO TABLE games FIELDS TERMINATED BY +'\t' LINES TERMINATED BY '\n' (file, name, description, counter)"
Replace [ database name ] with the database you are using, and [ username ] with your mysql username. What you are trying to acheive with $count appears to be handled automatically by the REPLACE part of the LOAD DATA command.


Comment on Re: How to insert a tab delimited text file to a mysql database
Select or Download Code
Re^2: How to insert a tab delimited text file to a mysql database
by Nik on Apr 17, 2006 at 10:01 UTC
    yes, it works this way but i would prefer seeing it in a few liens of perl sourc ecode instead please. Maybe someone can write it shorter and better that the way i tried.

    I changed my script to this:
    #=======================INSERTING TEXT FILE TO DATABASE=============== +========= my $replace = $dbh->prepare( "REPLACE INTO games (file, name, descript +ion, counter) VALUES (?, ?, ?, ?)" ); open (FILE, "<../data/games/descriptions.txt") or die $!; while (<FILE>) { chomp; if (length) { $replace->execute( split(/\t/, $_, 2), 0 ) or print $dbh->errs +tr; } } close (FILE);
    but iam still getting an error:
    DBD::mysql::st execute failed: called with 3 bind variables when 4 are needed at D:\www\cgi-bin\init.pl line 36, <FILE> line 1.
    its oemthign in here aboitu the argumnts i pass to replace->execute( split(/\t/, $_, 2), 0 ) or print $dbh->errstr;
      right .. you're execute() has only 3 parameters but the sql has 4 placeholders .. the 3rd argument to split limits the result to a max of 2 pieces .. so that's 2, and the 0 is 3, and that's all you're providing, but it needs 4.
        Yes i replaces 2 with 3 and now its workign fine!
        But can someone xpalin it please because iam having a little bit of difficutly to understand the use of slpit here!
      Why not just connect to the database (with DBI) then use the LOAD DATA INFILE statment suggested above? Something like
      use DBI; my $dbh = DBI->connect("DBI:mysql:database=databasename;host=localhost +", "username", 'password', {'RaiseError' => 1}); $SQL = "LOAD DATA INFILE '$datafile' REPLACE INTO TABLE games FIELDS T +ERMINATED BY '\t' LINES TERMINATED BY '\n' (file, name, description, +counter)"; $dbh->prepare($SQL); $dbh->execute; $dbh->finish; $dbh->disconnect;
      Code untested and may contain typos. Use at your own risk.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://543731]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (6)
As of 2014-04-19 18:03 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (483 votes), past polls