HELP - Large flat file conversion to MySQL using PHP

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

HELP - Large flat file conversion to MySQL using PHP

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
?>
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Thanks...

Post 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.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
Post Reply