Mass insert large text file into MySQL

Dani 1 Tallied Votes 1K Views Share

I recently had to mass insert a really large text file of strings into MySQL. Here's how I did it.

public function test_emails()
	{
	    set_time_limit(0);

	    // Don't save queries (CodeIgniter)
	    $this->db->save_queries = false;

	    echo "\nOpening File";

	    $handle = fopen('/home/daniweb/file.txt', 'r');

	    echo "\nFinished Opening File";

	    $strings = array();

	    $i = 0;

	    while (($line = fgets($handle)) !== false)
	    {
	        $strings[] = '(' . $this->wb_database->escape(trim($line)) . ')';

	        if (count($strings) > 5000)
	        {
	            echo "\nProcessing Batch " . $i++;

				// DaniWeb db library
                $this->wb_database->write("
        	        INSERT IGNORE INTO strings (string)
                    VALUES " . implode(',', $strings) . "
                ");

	            $strings = array();
	        }
	    }

	    fclose($handle);

	    if (!empty($strings))
	    {
    	    $this->wb_database->write("
    	        INSERT IGNORE INTO strings (string)
                VALUES " . implode(',', $strings) . "
            ");
	    }
	}
PARDEEP_2 0 Newbie Poster

http://www.modwest.com/help/kb6-253.html
if you have access to the command line, this would be easier

importing large text files using php probably uses too much memory

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.