Importing text file into mysql

PHP
1

Hi, I’m using the following code to import a tab delimited text file into mysql. The code is working fine but since my text file is rather huge (60,000 records), my web server times-out after 30 seconds.Is there a more efficient code you could suggest? I prefer not to reduce the size of my text file as it is inconvenient to do so.

$upload_condo_agent_rent = fopen("/home/path/to/my/public_html/" . "condo_agent_rent_" . "$thismonth" . ".xlsx.txt", "r");
while(!feof($upload_condo_agent_rent)) { 
  $data = explode("\t", fgets($upload_condo_agent_rent));

   $date = mysql_real_escape_string($data[0]);
   $location = mysql_real_escape_string($data[1]);
   $property_type = mysql_real_escape_string($data[2]);
   $tenure = mysql_real_escape_string($data[3]);
   $status = mysql_real_escape_string($data[4]);
   $sale_rent = mysql_real_escape_string($data[5]);
   $size = mysql_real_escape_string($data[6]);
   $price = mysql_real_escape_string($data[7]);
   $price_psf = mysql_real_escape_string($data[8]);
   $rooms = mysql_real_escape_string($data[9]);
   $baths = mysql_real_escape_string($data[10]);
   $facilities = mysql_real_escape_string($data[11]);
   $renovations = mysql_real_escape_string($data[12]);
   $furnishings = mysql_real_escape_string($data[13]);
   $phone_1 = mysql_real_escape_string($data[14]);
   $advertiser = mysql_real_escape_string($data[15]);
   $e_num = mysql_real_escape_string($data[16]);
   $unique = mysql_real_escape_string($data[17]);
   
//import the data
mysql_query("INSERT IGNORE INTO `condo` (`id`, `date`, `location`, `property_type`, `tenure`,  `status`, `sale_rent`, `size`, `price`, `price_psf`, `rooms`, `baths`, `facilities`, `renovations`, `furnishings`, `phone_1`, `advertiser`, `e_num`, `unique`) 
VALUES (NULL, '$date', '$location', '$property_type', '$tenure',  '$status', '$sale_rent', '$size', '$price', '$price_psf', '$rooms', '$baths', '$facilities', '$renovations', '$furnishings', '$phone_1', '$advertiser', '$e_num', '$unique')") or die(mysql_error());

    }
    fclose($upload_condo_agent_rent);

echo "Excellent! condo_agent_rent uploaded!";
2

The easiest way is to run the script from command line. Scripts executed by the shell do not have time-out limits like browser/apache executed scripts.

SSH in and execute it.

Another option is to enter the entire 60 000 rows in one query. Restructure the sql along these lines:


$sql = "INSERT IGNORE INTO `condo` (`id`, `date`, `location`, `property_type`, `tenure`,  `status`, `sale_rent`, `size`, `price`, `price_psf`, `rooms`, `baths`, `facilities`, `renovations`, `furnishings`, `phone_1`, `advertiser`, `e_num`, `unique`) 
 VALUES ";
$values = array();
while(!feof($upload_condo_agent_rent)) { 
  $data = explode("\t", fgets($upload_condo_agent_rent));

   $date = mysql_real_escape_string($data[0]);
   $location = mysql_real_escape_string($data[1]);
   $property_type = mysql_real_escape_string($data[2]);
   $tenure = mysql_real_escape_string($data[3]);
   $status = mysql_real_escape_string($data[4]);
   $sale_rent = mysql_real_escape_string($data[5]);
   $size = mysql_real_escape_string($data[6]);
   $price = mysql_real_escape_string($data[7]);
   $price_psf = mysql_real_escape_string($data[8]);
   $rooms = mysql_real_escape_string($data[9]);
   $baths = mysql_real_escape_string($data[10]);
   $facilities = mysql_real_escape_string($data[11]);
   $renovations = mysql_real_escape_string($data[12]);
   $furnishings = mysql_real_escape_string($data[13]);
   $phone_1 = mysql_real_escape_string($data[14]);
   $advertiser = mysql_real_escape_string($data[15]);
   $e_num = mysql_real_escape_string($data[16]);
   $unique = mysql_real_escape_string($data[17]);
   
   $values[] = " (NULL, '$date', '$location', '$property_type', '$tenure',  '$status', '$sale_rent', '$size', '$price', '$price_psf', '$rooms', '$baths', '$facilities', '$renovations', '$furnishings', '$phone_1', '$advertiser', '$e_num', '$unique')";

    }

$str_values = implode(',',$values);
$sql .= $str_values;

$qry = mysql_query($sql) or die(mysql_error());
3

Is there any particular reason why you’re using PHP to read the file and generate the queries? It looks like you could instead use LOAD DATA INFILE.

4

Thank you metho & salathe. Both your advice worked fine. After much thought, I decided to use Load Data Infile because it’s much faster.

I appreciate your advice. Have a blessed day.

5

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.