Harvest Data From Multiple Text Files To Insert Into MySQL

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 split up into multiple text files. If your data is already in a single text file, see Harvesting Data From A Text File instead.

Note: My example is for inserting data into only 2 columns of a table (2 fields). If you 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 Each Text File

Step 1:

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

Step 2:

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. Also, make sure there is a delimiter on the very last line of each text file.

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”. Each 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 each text file is in the correct format, with each field starting a new line, and then each record separated with an asterisk, and each file ending with an asterisk on the last 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 the file names for your own text files on lines 9 – 14, and edit the path to these text files on line 22. The path on line 22 should be the path relative to this PHP file.

/**
* Join data from multiple text files into a PHP array that can
* be dumped into database using MySQL INSERT
*/ 

unset($string, $jointfile, $data);

// MUST EDIT FILE NAMES BELOW
$textFiles = array(
                'text_file1.txt',
                'text_file2.txt',
                'text_file3.txt',
                'text_file4.txt',
                'text_file5.txt',
                'text_file6.txt', 
);

 
// join separate text files into one long string
 
foreach($textFiles as $singleFile) {
 
    $file = $singleFile; // EDIT PATH TO YOUR FILES, FOR EXAMPLE 'dir/subdir/'.$singleFile
    $fh = fopen($file, "r");
    $string = fread($fh, filesize($file));
    fclose($fh);
    $jointfile .= trim($string);
 
}
 
// split text into array of separate records (database rows)
 
    $data = explode('*',$jointfile);
    $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.