Tuesday, January 3, 2017

PHP script to import CSV data to MySQL table

While populating the database, many time we come across the situation where we need to use old backups in different file formats. It's not necessary that every time you will get the data in .sql format. While working on one of my latest PHP application, I had to use Excel & CSV files to dump one of my MySQL table. So I thought of sharing the script on my blog. So lets learn how to dump mysql database with the data from CSV file with an example.

Step 1 - Create MySQL database connection
Let's first create a connection to database using following code.
    // Open a connection to your MySQL database
    $host        ='localhost';  // Host Name.
    $db_user     = 'root';      //User Name
    $db_password = 'root';      //Password
    $db          = 'test';      // Database Name.

    $conn        = mysql_connect($host,$db_user,$db_password) 
                   or die (mysql_error());

    mysql_select_db($db) or die (mysql_error());

Step 2 - Read from file(CSV) & inserting in to database
Once the database connection is created and the database is selected we can simply use the following code to read the csv file. I have used the php method fgetcsv() to accomplish our job. PHP have many file reading methods but the benefit of using this method is it's capability to parse the CSV fields and returns an array. So our job becomes easy as we get one array per CSV file row.

$filename = '/path/to/file.csv';
 
// check file size here
if($filename!='' && filesize($filename)>0)
 {
 // to deal with Mac line endings
 ini_set('auto_detect_line_endings',TRUE);
  
 $file = fopen($filename, "r");
  
 while (($csvData = fgetcsv($file, 2000, ",")) !== FALSE)
 {
  $sql = "INSERT INTO 
              tableName 
          (`field1`,
           `field2`,
           `field3`,
           `field4`) 
              VALUES 
          ('".$csvData[0]."', 
           '".$csvData[1]."',
           '".$csvData[2]."',
           '".$csvData[3]."');
         ";
   mysql_query($sql);
 }
        
 fclose($file);

 ini_set('auto_detect_line_endings',FALSE);
  
 echo 'CSV File has been successfully Imported'; 
 }
 else{
   echo 'Invalid File:Please Upload CSV File';
 }
If you read the code carefully, you can notice the use of auto_detect_line_endings directive is set to TRUE before reading the CSV file. This is recommended practice by php.net while using fgetcsv() method.

No comments:

Post a Comment