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 ( &#1111;0] =&gt; 0800 Darwin (NT) &#1111;1] =&gt; 0800 Darwin City (NT) &#1111;2] =&gt; 0800 Darwin GPO (NT) &#1111;3] =&gt; 0801 Darwin GPO Private Boxes (NT) &#1111;4] =&gt; 0810 Alawa (NT) &#1111;5] =&gt; 0810 Brinkin (NT) &#1111;6] =&gt; 0810 Casuarina (NT) &#1111;7] =&gt; 0810 Coconut Grove (NT) &#1111;8] =&gt; 0810 Jingili (NT) &#1111;9] =&gt; 0810 Lee Point (NT) &#1111;10] =&gt; 0810 Millner (NT) &#1111;11] =&gt; 0810 Moil (NT) &#1111;12] =&gt; 0810 Nakara (NT) &#1111;13] =&gt; 0810 Nightcliff (NT) &#1111;14] =&gt; 0810 Rapid Creek (NT) &#1111;15] =&gt; 0810 Tiwi (NT) &#1111;16] =&gt; 0810 Wagaman (NT) &#1111;17] =&gt; 0810 Wanguri (NT) &#1111;18] =&gt; 0811 Casuarina Private Boxes (NT) &#1111;19] =&gt; 0812 Anula (NT) &#1111;20] =&gt; 0812 Karama (NT) &#1111;21] =&gt; 0812 Leanyer (NT) &#1111;22] =&gt; 0812 Malak (NT) &#1111;23] =&gt; 0812 Marrara (NT) &#1111;24] =&gt; 0812 Sanderson (NT) &#1111;25] =&gt; 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 />';
}

?>