Harvest Data From A Text File To Insert Into MySQL of 2 Columns

Here is a way of preparing a large amount of data to insert into a MySQL database table via PHP. This method works if your data is all in one text file. If your data is split up into multiple text files, see Harvesting Data From Multiple Text Files To Insert Into MySQL instead.

Note: My example is for inserting data into only 2 columns of a table (2 fields). If you have are inserting into more than 2 columns, you must alter the PHP below to suit more fields.

This will put all of your data into an array consisting of records that you want to insert into the database. Each element of the array will be another array of the 2 field values that make up one database record.

Prepare Data in the Text File

Step 1

Have all of the data in one text file.

Step 2

Each value for each field should begin on a new line.

Step 3

Each group of values for one database record should be separated by a delimiter. This group of values will be one “row” in the database. I will use an asterisk, *, as the delimiter in this example. The delimiter should be on its own line.

For example, let’s use a sample MySQL table named “People” that we want to insert data into. The table fields are “Name”, and “Email”. My data text file will look like this:

Billy
bla@bla.com

*
Jane
bla23@bla.com

*
Brent
bla34@bla.com

*
Anne
bla56@bla.com

etc...

If your text file is in the correct format, with each field starting a new line, and then each record separated with an asterisk, which is also on its own line, then you can move to the next step.

Prep The PHP Script to Harvest The Data

Step 1

Create a blank .php file named whatever you like, as long as it ends with .php.

Step 2

Paste the following into code into it, but edit line 6 with the name of your text file:

/* Arrange data from text file into a PHP array that can be dumped into database using MySQL INSERT
*/ 
 
    unset($alltext,$data);
 
    $file = "dump.txt"; // THE NAME OF YOUR TEXT FILE
    $fh = fopen($file, "r");
    $string = fread($fh, filesize($file));
    fclose($fh);
    $alltext = trim($string);
 
// split text into array of separate records
 
    $data = explode('*',$alltext);
    $data = array_filter($data); // filter empty elements from bottom   
 
    // split each array element into array of [0]=field1, and [1]=field2
 
    foreach($data as $field1 => $field2) {
     
        $data[$field1] = explode("\n", trim($field2));
        $data = array_filter($data);
     
    }

// NOW YOUR $data is ready to be inserted into database table 
// NOW ADD HERE THE CODE TO INSERT THE $data
 
 

Step 3

Now that all of your data is set into the $data variable, add code to the bottom of the script above that will insert this data into your database. See Insert Data Array Into MySQL Table Using PHP for the rest of the script.