Insert 1 million rows from text file into MySQL database

[fishnfrogs]

Basically, I need to insert a million rows into a mysql table from a .txt file. The "easiest" way I can think about doing this is loading the file into an AIR application and have it strip down into 1000 workable .txt files of 1000 lines. Then after that is done, use the app to upload each file one after the other in a loop. Can anyone tell me if there is an easier, more efficient way of doing this. I really don't want to have to go through that whole building process. Thanks in advance!!

[Dormilich]

I’d try to read the file linewise and use a Prepared Statement (i.e. submit one line after another) to write each entry.

[fishnfrogs]

Hi, thanks for the response. I have played around with prepared statements before. However, I'll be doing this online. Wouldn't I have to worry about PHP timing out? For the life of me, I have never been able to change the timeout of my PHP scripts. Thanks!!

[Atli]

@fishnfrogs
Hey. Try set_time_limit(0).

If you can set an unlimited time-limit, then Dormilich's suggestion is also what I would recommend. A lot "cleaner" than all the other alternatives I can think of.

[fishnfrogs]

I'll give it a try and post back. THANKS!!!!!

[mshmyob]

If you don't need PHP to do it then you could just use the MySQL load data command line.

load data local infile 'c:\\yourfile.txt' into table YourDataBaseName.YourTableName fields terminated by ",";
 

You would change the "," to whatever seperates your field names.
If your records are on each line then that will work - if your records are all wrapped then you can add something like records terminiated by "#" after the fields terminater.

cheers,

[fishnfrogs]

Hi, I'm running into a 500 Internal Server Error. I get about 50 to 80k in, and the server throws that error. Here is the code I'm currently using:

<?php
 
set_time_limit(0);
ini_set("max_execution_time", 0);
 
require_once 'database_creds.php';
 
$mysqli = new mysqli($hostname, $username, $password, $database);
if (mysqli_connect_errno())
{
      printf("Connect failed: %s\n", mysqli_connect_error());
       exit();
}
if($stmt = $mysqli->prepare("INSERT INTO table (id, value) VALUES ('', ?)"))
{
    $fp = fopen('file.txt', 'r');
    echo "Starting...\n";
    while(!feof($fp))
    {
        $line = fgets($fp);
        $stmt->bind_param('s', $line);
        $stmt->execute();
    }
    $stmt->close();
    $result = $mysqli->close();
    if($result)
    {
        echo 'CLOSED';
    }
}
 
?>
 

Also, I'm using Firefox 3 to load the page. Is there something more I can do or does this have to do with my hosting company? Thanks!!

[philipwayne]

Question why not use the file function and a foreach loop? Also I'm guessing the ID shouldn't be submitted as a empty string going on the practice that all ID fields are auto_incremented integers.

Your if statement isn't doing any real error checking its simply checking if the variable can be set to something always true unless the function does not return a value.

if( ( $stmt = $mysqli->prepare("INSERT INTO table (id, value) VALUES ('', ?)") ) != FALSE )
 

[fishnfrogs]

The issues aren't coming from loading the file or the query string. All those seem to work fine. I'm guessing it's something else. I also tried making one long query string, but I either got the 500 error or the packet was too big for mysql. Then I tried to break it up into smaller arrays, and use a counting system and do smaller queries. I only got to 500k before my friend the 500 internal server error came calling again. *Sigh* It looks like I'll be going the AIR route again. Thanks again for your help!!