Page 1 of 1
[solved]Inserting a large ascii text file into the database?
Posted: Sat Jun 18, 2005 6:35 am
by robster
Hi all, I have a textfile, it contains australian postcodes and the place they reffer to. EG:
Code: Select all
0800 Darwin (NT)
0800 Darwin City (NT)
0800 Darwin GPO (NT)
0801 Darwin GPO Private Boxes (NT)
0810 Alawa (NT)
0810 Brinkin (NT)
0810 Casuarina (NT)
0810 Coconut Grove (NT)
0810 Jingili (NT)
0810 Lee Point (NT)
0810 Millner (NT)
0810 Moil (NT)
0810 Nakara (NT)
0810 Nightcliff (NT)
0810 Rapid Creek (NT)
0810 Tiwi (NT)
0810 Wagaman (NT)
0810 Wanguri (NT)
0811 Casuarina Private Boxes (NT)
0812 Anula (NT)
It goes on and on. I don't want to enter all these by hand into a database with tables similar to this say:
Code: Select all
id - postcode - location - popularity
Considering that id is autoincrimental and popularity should default to 0, is there a way to pump that data in from a copy/paste situation? I use phpMyAdmin but I doubt its ability to do such a thing, perhaps it can and I just don't know, or perhaps there's another tool for just this kind of thing?
Basically I'm wondering if anyone here has any leads or methods that could switch this from being a 2 day job into being a 10 min job

?
Any help greatly appreciated!
Rob
Posted: Sat Jun 18, 2005 7:06 am
by dnathe4th
This probably isnt the approach you want to take, but whenever I need to do a large amount of file parsing, I always write a short Python script, and run it through the interpreter. You can use python to connect to an online database, so if you already have Python on your computer, go for it. If not, and you want to persue that as an option, message me and I'll help you through learning it, its a really easy language I promise

Posted: Sat Jun 18, 2005 8:11 am
by Chris Corbyn
Tell me... are the postcodes numbers only? If so then this works...
Code: Select all
<?php
$lines = file('foo.txt');
foreach($lines as $v) {
$v = trim($v);
preg_match('/^(\d+)\s+(.*)$/', $v, $matches);
$postcode = $matches[1];
$location = $matches[2];
$query = "INSERT INTO `table` (`postcode`, `location`, `popularity`) VALUES ('$postcode', '$location', '0')";
mysql_query($query) or die (mysql_error());
echo 'MySQL query ('.$query.') executed succesfully<br />';
}
?>
Posted: Sat Jun 18, 2005 8:48 am
by timvw
Posted: Sat Jun 18, 2005 5:34 pm
by robster
Just so you can see exactly what kind of file it is, if I do this:
Code: Select all
$lines = file('research/austpcn.txt');
print_r($lines);
It will output this (only WAY longer):
Code: Select all
Array ( ї0] => 0800 Darwin (NT) ї1] => 0800 Darwin City (NT) ї2] => 0800 Darwin GPO (NT) ї3] => 0801 Darwin GPO Private Boxes (NT) ї4] => 0810 Alawa (NT) ї5] => 0810 Brinkin (NT) ї6] => 0810 Casuarina (NT) ї7] => 0810 Coconut Grove (NT) ї8] => 0810 Jingili (NT) ї9] => 0810 Lee Point (NT) ї10] => 0810 Millner (NT) ї11] => 0810 Moil (NT) ї12] => 0810 Nakara (NT) ї13] => 0810 Nightcliff (NT) ї14] => 0810 Rapid Creek (NT) ї15] => 0810 Tiwi (NT) ї16] => 0810 Wagaman (NT) ї17] => 0810 Wanguri (NT) ї18] => 0811 Casuarina Private Boxes (NT) ї19] => 0812 Anula (NT) ї20] => 0812 Karama (NT) ї21] => 0812 Leanyer (NT) ї22] => 0812 Malak (NT) ї23] => 0812 Marrara (NT) ї24] => 0812 Sanderson (NT) ї25] => 0812 Wulagi (NT)
Basically, looking at the first entry in the array [0], it would need to put '0800' in the posctcode and 'Darwin (NT)' in the location.
I'll have a look at your code d11wtq, and follow up on that manual timvw, if I get really lost I'll post back again (be prepared for this! LOL

)
Thanks again.
Rob
Posted: Sat Jun 18, 2005 6:00 pm
by robster
Thanks so much all, this is the final code (your code really, just added the database select etc) and it worked a treat. The file was so big it timed out on the default 30 sec execution, so "I had to adjust my php.ini file to a larger timout number so it could execute it all.
The HD went into a frenzy and parsed the file

which is nice and I won't get RSI from working 'dumb'.
Thanks again everyone!
I love auatomation!
Code: Select all
<?php
//Connect for drawing the menus below
$connection = mysql_connect($dbhost, $dbusername, $dbpassword);
include("config.php");
$lines = file('research/austpcn.txt');
//print_r($lines);
foreach($lines as $v) {
$v = trim($v);
preg_match('/^(\d+)\s+(.*)$/', $v, $matches);
$postcode = $matches[1];
$location = $matches[2];
$db_selected = mysql_select_db($dbname, $connection);
$query = "INSERT INTO `test_pcode` (`postcode`, `location`, `popularity`) VALUES ('$postcode', '$location', '0')";
mysql_query($query) or die (mysql_error());
echo 'MySQL query ('.$query.') executed succesfully<br />';
}
?>