Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
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
Replies are listed 'Best First'.
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;
      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.
      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!

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 chanting in the Monastery: (6)
As of 2015-08-30 17:08 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The oldest computer book still on my shelves (or on my digital media) is ...













    Results (350 votes), past polls