Page 1 of 1
HELP - Large flat file conversion to MySQL using PHP
Posted: Mon May 31, 2004 3:31 pm
by RobertGonzalez
I have a very large, CSV datafile that I need to be able to import into my MySQL database. My host does not allow file import using phpMyadmin so I need to convert this file (52,600 lines - each line consists of 8 fields) into a MySQL file that will allow me to directly add each line of the datafile into my MySQL database. Can someone help me?
The datafile is the complete list of all US Zipcodes with latitudes and longitudes. I would be more than happy to give someone a copy if they would like to attempt this conversion (so long as I get a copy of the conversion as well). As always, all help would be greatly appreciated.
Posted: Mon May 31, 2004 3:40 pm
by pickle
Well, I don't know if you want to do the whole thing in one big query, I'd suggest multiple queries.
Ex: A simple way to do one entry per line (still not the best way, I'd do about 500 lines in a query).
Code: Select all
<?php
$file_contents = file('path/to/file');//each line in file->array entry
foreach($file_contents as $zip_code)
{
$exploded_info = explode(',',$zip_code);
$query = <<<SQL
INSERT
INTO
some_table
(value1,
value2,
value3,
value4,
value5,
value6,
value7,
value8)
VALUES
('$exploded_info[0]',
'$exploded_info[1]',
'$exploded_info[2]',
'$exploded_info[3]',
'$exploded_info[4]',
'$exploded_info[5]',
'$exploded_info[6]',
'$exploded_info[7]')
SQL;
$query = mysql_query($query);//assuming you've made your connection already.
?>
Posted: Mon May 31, 2004 10:11 pm
by JAM
Might be of interest aswell;
Code: Select all
ini_set ("max_execution_time", "1000");
This because it might take more than the default 30 seconds executing this query, using the entire file. If you can boost the max_execution_time you might be able to do it in one run.
Thanks...
Posted: Mon May 31, 2004 10:21 pm
by RobertGonzalez
Thanks for the feedback. I am in the process of manually editing the lines (1500 at a time). It is a little slow, but it is going OK. But again, thanks for the quick feedback.
Posted: Tue Jun 01, 2004 4:26 am
by JAM
Another thing comes to mind. There are applications that might be in handy, as the MySQL-Front (can easely be found on Google), that might aid you in this. Perhaps worth looking into if you need to save time.