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

Nik has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: How to insert a tab delimited text file to a mysql database by Withigo (Friar) on Apr 16, 2006 at 23:59 UTC
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)"` [download] 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)"` [download] 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.	[reply] [d/l] [select]
A reply falls below the community's threshold of quality. You may see it by logging in.
Re: How to insert a tab delimited text file to a mysql database by graff (Chancellor) on Apr 16, 2006 at 17:51 UTC
Why are you showing us a "CREATE TABLE guestbook ..." statement? Does this have something to do with the error that you are getting (which you have not shown yet)? If that is part of the problem, I would guess that you might have configured your DBI connection so that it would die on any sort of sql error, and you are getting an error because a table called "guestbook" already exists in the database. In order for the "CREATE TABLE" statement not to cause an error, you would either do "DROP TABLE IF EXISTS guestbook" before trying to create it, or else change the "CREATE TABLE" statement to "CREATE TABLE IF NOT EXISTS ..." For that matter, have you tried using the text from that statement to create the table manually, just to make sure there are no mistakes in the statement?	[reply]
A reply falls below the community's threshold of quality. You may see it by logging in.
A reply falls below the community's threshold of quality. You may see it by logging in.
Re: How to insert a tab delimited text file to a mysql database by tinita (Parson) on Apr 16, 2006 at 17:28 UTC
What am i doing wrong? without looking to closely at your code - what error or what inexpected result are you getting? i missed that in your posting. update: where do you set the variable `$file`? did you forget to assign the split-result to it? are you using strict?	[reply] [d/l]