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

[Withigo]

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.

[Nik]

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;

[davidrw]

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.

[Nik]

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!

[xorl]

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.