Inserting Vaulues into MYSQL database from a Text File

Bawnmore · 25-04-2009 04:37PM #1

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 · 25-04-2009 08:06PM

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 · 26-04-2009 12:26PM

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 · 26-04-2009 02:07PM

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

Webmonkey · 26-04-2009 03:10PM

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 · 26-04-2009 05:23PM

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

Inserting Vaulues into MYSQL database from a Text File

Comments