Inserting Vaulues into MYSQL database from a Text File

Bawnmore

Hi all,

I'm sure there's a quick answer to this and I'm just missing something

I have a text file with a large number of values that i want to insert into a MYSQL database. They only need to go into one column so nothing majorly difficult i'm sure!

The values are delimited (i think thats the word:)) by a double dash e.g. -- and there's a line between each of them if that matters. Here's a quick example of the data:


Request: QUIT :"
--
Response: ERROR :Closing Link: client1[192.168.1.5] (Quit: )"
--
Request: NICK [skank]2224973"
--
Response: :192.168.1.10 NOTICE AUTH ** Looking up your hostname..."
--
Response: :192.168.1.10 NOTICE AUTH ** Couldn't resolve your hostname; using your IP address instead"


Someone recommend that i edited the data using VI and regular expressions to add the "insert into" syntax onto the beginning and end of each sentence but surely there's a way to import such data straight into MYSQL.

Any help at all is appreciated!

Thanks in advance,
Sean

squibs

Do you have shell access on the server? If so mysqlimport can do it.

Here's some sample syntax from a quick google:

mysqlimport --fields-optionally-enclosed-by=""" --fields-terminated-by=, --lines-terminated-by="\r\n" --user=YOUR_USERNAME --password YOUR_DATABASE YOUR_TABLE.csv

Bawnmore

thanks for the reply squibs, need all the help i can get

ya i have shell access on the server as its a student project, but i'm not great with mysql as i haven't really used it much before. If you get a chance would you be able to break down the syntax for me, and i'm sure i'd be capable from there?

Thanks again,
Sean

squibs

This explains it better than I could:
http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

Webmonkey

You'd do it very quick with a script, PHP or something
[php]
$file = fopen("file.txt", "r");
if ($file) {
while (!feof($file)) {
$line = fgets($file, 4096);
if (trim($line) != '--')
{
$query = "INSERT INTO `table` (`column`) VALUES(".trim($line).");";
mysql_query($query);
}

}
fclose($file);
}
[/php]

Bawnmore

thanks guys, let me give that a go and i'll get back to ye with the results