How to Import CSV File In Your PHP Application

Rate this topic:

(2 votes, average: 5.00 out of 5)

Loading ...

Popularity: 3,263 views

Posted by Ashish on 7 March, 2009

View Comments

PHP gives an ease when it comes to Import flat data (Data without hierarchical structure). If you are using XML to fetch and store data that is fine but what about the case when your client have excel sheet ready with multiple records to go into database. CSV (Comma Separated Values) format is a good candidate format to import or export your application data.

Importing data from CSV files

Since we have lines of values separated by commas, the easiest way to process them is to parse each line using the PHP explode() function:

<?php
 
$arrResult = array();
$arrLines = file('data.csv');
foreach($arrLines as $line) {
$arrResult[] = explode( ',', $line);
}
 
?>

Dealing with special characters

This simple solution will not work if you have a comma in a value, like for instance when the column is an address and it has a value is like “North street, 1″.

In such cases, the column value in the CSV file is quoted to indicate that the data between quotes should be read as a single column. To deal with this situation, you can use a specially tailored regular expression or use the PHP fgetcsv() function.

<?php
 
$arrResult = array();
$handle = fopen("data.csv", "r");
if( $handle ) {
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$arrResult[] = $data;
}
fclose($handle);
}
?>

Note that locale settings may affect how fgetcsv() function works. As noted in the PHP manual, if LANG locale setting is for instance “en_US.UTF-8″, files in one-byte encoding are read wrong by this function, so be aware.

Importing CSV data directly into the database

If you are importing data from a CSV file into a MySQL database, you import it directly into a database table. It will be much faster than processing it line by line using PHP. Fortunately, most relational database have tools for the bulk data import from CSV files. Load data command will help you here. For instance, in MySQL you can use LOAD DATA INFILE query statement.

Friendly User Interface to Import CSV Data into a Database

An alternative solution to import CSV data is to use my class Quick CSV Import. This is even more user-friendly alternative to import CSV data into a database consists in using the application “Quick CSV import with visual mapping”. This application helps importing CSV files into a database table allowing to define with CSV file columns are mapped to which columns of the database table. This application can even suggest automatically separator character besides the comma. Following is an example of Quick CSV import

<?php
 
class Quick_CSV_import
{
  var $table_name; //where to import to
  var $file_name;  //where to import from
  var $use_csv_header; //use first line of file OR generated columns names
  var $field_separate_char; //character to separate fields
  var $field_enclose_char; //character to enclose fields, which contain separator char into content
  var $field_escape_char;  //char to escape special symbols
  var $error; //error message
  var $arr_csv_columns; //array of columns
  var $table_exists; //flag: does table for import exist
  var $encoding; //encoding table, used to parse the incoming file. Added in 1.5 version
 
  function Quick_CSV_import($file_name="")
  {
    $this->file_name = $file_name;
    $this->arr_csv_columns = array();
    $this->use_csv_header = true;
    $this->field_separate_char = ",";
    $this->field_enclose_char  = "\"";
    $this->field_escape_char   = "\\";
    $this->table_exists = false;
  }
 
 
  function import()
  {
    if($this->table_name=="")
      $this->table_name = "temp_".date("d_m_Y_H_i_s");
 
    $this->table_exists = false;
    $this->create_import_table();
 
    if(empty($this->arr_csv_columns))
      $this->get_csv_header_fields();
 
    /* change start. Added in 1.5 version */
    if("" != $this->encoding && "default" != $this->encoding)
      $this->set_encoding();
    /* change end */
 
 
    if($this->table_exists)
    {
      $sql = "LOAD DATA INFILE '".@mysql_escape_string($this->file_name).
             "' INTO TABLE `".$this->table_name.
             "` FIELDS TERMINATED BY '".@mysql_escape_string($this->field_separate_char).
             "' OPTIONALLY ENCLOSED BY '".@mysql_escape_string($this->field_enclose_char).
             "' ESCAPED BY '".@mysql_escape_string($this->field_escape_char).
             "' ".
             ($this->use_csv_header ? " IGNORE 1 LINES " : "")
             ."(`".implode("`,`", $this->arr_csv_columns)."`)";
      $res = @mysql_query($sql);
      $this->error = mysql_error();
    }
  }
 
 
  //returns array of CSV file columns
  function get_csv_header_fields()
  {
    $this->arr_csv_columns = array();
    $fpointer = fopen($this->file_name, "r");
    if ($fpointer)
    {
      $arr = fgetcsv($fpointer, 10*1024, $this->field_separate_char);
      if(is_array($arr) && !empty($arr))
      {
        if($this->use_csv_header)
        {
          foreach($arr as $val)
            if(trim($val)!="")
              $this->arr_csv_columns[] = $val;
        }
        else
        {
          $i = 1;
          foreach($arr as $val)
            if(trim($val)!="")
              $this->arr_csv_columns[] = "column".$i++;
        }
      }
      unset($arr);
      fclose($fpointer);
    }
 
    else
      $this->error = "file cannot be opened: ".(""==$this->file_name ? "[empty]" : 
 
@mysql_escape_string($this->file_name));
    return $this->arr_csv_columns;
  }
 
 
  function create_import_table()
  {
    $sql = "CREATE TABLE IF NOT EXISTS ".$this->table_name." (";
 
    if(empty($this->arr_csv_columns))
      $this->get_csv_header_fields();
 
    if(!empty($this->arr_csv_columns))
    {
      $arr = array();
      for($i=0; $i<sizeof($this->arr_csv_columns); $i++)
          $arr[] = "`".$this->arr_csv_columns[$i]."` TEXT";
      $sql .= implode(",", $arr);
      $sql .= ")";
      $res = @mysql_query($sql);
      $this->error = mysql_error();
      $this->table_exists = ""==mysql_error();
    }
  }
 
 
  /* change start. Added in 1.5 version */
  //returns recordset with all encoding tables names, supported by your database
  function get_encodings()
  {
    $rez = array();
    $sql = "SHOW CHARACTER SET";
    $res = @mysql_query($sql);
    if(mysql_num_rows($res) > 0)
    {
      while ($row = mysql_fetch_assoc ($res))
      {
        $rez[$row["Charset"]] = ("" != $row["Description"] ? $row["Description"] : $row["Charset"]); //some MySQL databases return empty Description field
      }
    }
    return $rez;
  }
 
 
  //defines the encoding of the server to parse to file
  function set_encoding($encoding="")
  {
    if("" == $encoding)
      $encoding = $this->encoding;
    $sql = "SET SESSION character_set_database = " . $encoding; //'character_set_database' MySQL server variable is [also] to parse file with rigth encoding
    $res = @mysql_query($sql);
    return mysql_error();
  }
  /* change end */
 
}
 
?>

Test CSV File

PROGRAMNAME,PROGRAMURL,LASTUPDATED,NAME,KEYWORDS,DESCRIPTION,SKU,MANUFACTURER,MANUFACTURERID,UPC,ISBN,CURRENCY,SALEPRICE,PRICE,RETAILPRICE,FROMPRICE,BUYURL,IMPRESSIONURL,IMAGEURL,ADVERTISERCATEGORY,THIRDPARTYID,THIRDPARTYCATEGORY,AUTHOR,ARTIST,TITLE,PUBLISHER,LABEL,FORMAT,SPECIAL,GIFT,PROMOTIONALTEXT,STARTDATE,ENDDATE,OFFLINE,ONLINE,INSTOCK,CONDITION,WARRANTY,STANDARDSHIPPINGCOST
Buy Electrical Direct,http://www.be-direct.co.uk,01/06/2005 04:46:48,Stoves 60cm Wide Dual Fuel Cooker,61DFDO,Stoves 60cm Wide Dual Fuel Cooker,16488,Stoves,61DFDO,,,GBP,0.00,583.99,0.00,,http://www.kqzyfj.com/click-1679007-10385498?url=http%3A%2F%2Fwww.be-direct.co.uk%2FproductDetails.asp%3Freferer%3Dcommissionjunction%26id%3D16488,http://www.awltovhc.com/image-1679007-10385498,,Cooking (cookers and microwaves),,,,,,,,,,,Free Delivery,,,no,yes,yes,,,0.0
Buy Electrical Direct,http://www.be-direct.co.uk,01/06/2005 04:46:48,Hotpoint Ultima 60cm Gas Cooker with Sandstone Finish,EG73T,Hotpoint Ultima 60cm Gas Cooker with Sandstone Finish,16122,Hotpoint,EG73T,,,GBP,0.00,473.99,0.00,,http://www.tkqlhce.com/click-1679007-10385498?url=http%3A%2F%2Fwww.be-direct.co.uk%2FproductDetails.asp%3Freferer%3Dcommissionjunction%26id%3D16122,http://www.lduhtrp.net/image-1679007-10385498,http://www.be-direct.co.uk/additional/product_images/id16122.jpg,Cooking (cookers and microwaves),,,,,,,,,,,Free Delivery,,,no,yes,yes,,,0.0
Buy Electrical Direct,http://www.be-direct.co.uk,01/06/2005 04:46:48,Hotpoint 60cm Electric Ceramic/Halogen Hob with Sandstone Finish,EW83T,Hotpoint 60cm Electric Ceramic/Halogen Hob with Sandstone Finish,16390,Hotpoint,EW83T,,,GBP,0.00,503.99,0.00,,http://www.tkqlhce.com/click-1679007-10385498?url=http%3A%2F%2Fwww.be-direct.co.uk%2FproductDetails.asp%3Freferer%3Dcommissionjunction%26id%3D16390,http://www.tqlkg.com/image-1679007-10385498,http://www.be-direct.co.uk/additional/product_images/id16390.jpg,Cooking (cookers and microwaves),,,,,,,,,,,Free Delivery,,,no,yes,yes,,,0.0
Buy Electrical Direct,http://www.be-direct.co.uk,01/06/2005 04:46:48,Hotpoint 60cm Gas Cooker with Polar White Finish,GW62P,Hotpoint 60cm Gas Cooker with Polar White Finish,16402,Hotpoint,GW62P,,,GBP,0.00,495.99,0.00,,http://www.dpbolvw.net/click-1679007-10385498?url=http%3A%2F%2Fwww.be-direct.co.uk%2FproductDetails.asp%3Freferer%3Dcommissionjunction%26id%3D16402,http://www.tqlkg.com/image-1679007-10385498,http://www.be-direct.co.uk/additional/product_images/id16402.jpg,Cooking (cookers and microwaves),,,,,,,,,,,Free Delivery,,,no,yes,yes,,,0.0

Now, a working PHP example to use the above Quick CSV import Class and import the CSV File provided above

<?php
include "Quick_CSV_import.php"; // Have you class seperate from your working code.
 
//connect to database
mysql_connect("localhost", "root", "1");
mysql_select_db("test"); //your database
 
$csv = new Quick_CSV_import();
 
$arr_encodings = $csv->get_encodings(); //take possible encodings list
$arr_encodings["default"] = "[default database encoding]"; //set a default (when the default database encoding should be used)
 
if(!isset($_POST["encoding"]))
  $_POST["encoding"] = "default"; //set default encoding for the first page show (no POST vars)
 
if(isset($_POST["Go"]) && ""!=$_POST["Go"]) //form was submitted
{
  $csv->file_name = $HTTP_POST_FILES['file_source']['tmp_name'];
 
  //optional parameters
  $csv->use_csv_header = isset($_POST["use_csv_header"]);
  $csv->field_separate_char = $_POST["field_separate_char"][0];
  $csv->field_enclose_char = $_POST["field_enclose_char"][0];
  $csv->field_escape_char = $_POST["field_escape_char"][0];
  $csv->encoding = $_POST["encoding"];
 
  //start import now
  $csv->import();
}
else
  $_POST["use_csv_header"] = 1;
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
 
<html>
<head>
    <title>Quick CSV import</title>
  <style>
  .edt
  {
    background:#ffffff; 
    border:3px double #aaaaaa; 
    -moz-border-left-colors:  #aaaaaa #ffffff #aaaaaa; 
    -moz-border-right-colors: #aaaaaa #ffffff #aaaaaa; 
    -moz-border-top-colors:   #aaaaaa #ffffff #aaaaaa; 
    -moz-border-bottom-colors:#aaaaaa #ffffff #aaaaaa; 
    width: 350px;
  }
  .edt_30
  {
    background:#ffffff; 
    border:3px double #aaaaaa; 
    font-family: Courier;
    -moz-border-left-colors:  #aaaaaa #ffffff #aaaaaa; 
    -moz-border-right-colors: #aaaaaa #ffffff #aaaaaa; 
    -moz-border-top-colors:   #aaaaaa #ffffff #aaaaaa; 
    -moz-border-bottom-colors:#aaaaaa #ffffff #aaaaaa; 
    width: 30px;
  }
  </style>
</head>
 
<body bgcolor="#f2f2f2">
  <h2 align="center">Quick CSV import</h2>
  <form method="post" enctype="multipart/form-data">
    <table border="0" align="center">
      <tr>
          <td>Source CSV file to import:</td>
          <td rowspan="30" width="10px">&nbsp;</td>
          <td><input type="file" name="file_source" id="file_source" class="edt" value="<?=$file_source?>"></td>
      </tr>
      <tr>
          <td>Use CSV header:</td>
          <td><input type="checkbox" name="use_csv_header" id="use_csv_header" <?=(isset($_POST["use_csv_header"])?"checked":"")?>/></td>
      </tr>
      <tr>
          <td>Separate char:</td>
          <td><input type="text" name="field_separate_char" id="field_separate_char" class="edt_30"  maxlength="1" value="<?=(""!=$_POST["field_separate_char"] ? htmlspecialchars($_POST["field_separate_char"]) : ",")?>"/></td>
      </tr>
      <tr>
          <td>Enclose char:</td>
          <td><input type="text" name="field_enclose_char" id="field_enclose_char" class="edt_30"  maxlength="1" value="<?=(""!=$_POST["field_enclose_char"] ? htmlspecialchars($_POST["field_enclose_char"]) : htmlspecialchars("\""))?>"/></td>
      </tr>
      <tr>
          <td>Escape char:</td>
          <td><input type="text" name="field_escape_char" id="field_escape_char" class="edt_30"  maxlength="1" value="<?=(""!=$_POST["field_escape_char"] ? htmlspecialchars($_POST["field_escape_char"]) : "\\")?>"/></td>
      </tr>
      <tr>
          <td>Encoding:</td>
          <td>
          <select name="encoding" id="encoding" class="edt">
          <?
            if(!empty($arr_encodings))
              foreach($arr_encodings as $charset=>$description):
          ?>
            <option value="<?=$charset?>"<?=($charset == $_POST["encoding"] ? "selected=\"selected\"" : "")?>><?=$description?></option>
          <? endforeach;?>
          </select>
        </td>
      </tr>
      <tr>
          <td colspan="3">&nbsp;</td>
      </tr>
      <tr>
          <td colspan="3" align="center"><input type="Submit" name="Go" value="Import it" onclick=" var s = document.getElementById('file_source'); if(null != s && '' == s.value) {alert('Define file name'); s.focus(); return false;}"></td>
      </tr>
    </table>
  </form>
<?=(!empty($csv->error) ? "<hr/>Errors: ".$csv->error : "")?>
</body>
</html>

If you’ve got any thoughts, comments or suggestions for things we could add, leave a comment! Also please Subscribe to our RSS for latest tips, tricks and examples on cutting edge stuff.

Popularity: 3,263 views

Posted under [ PHP ]

Tagged with: [ PHP ]

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

raid recovery

I have tried this code for importing CSV file with php application
jose vazquez

this code doesnt work, it doesnt help with the comma issues stated at the beginning of the tutorial
Ashish

Can you list out what is the exact problem you have faced? The code worked fine when i used last time and it should work. Let me know what is the problem so i can dig deeper.
jose

basically is that when it faces the first table column with a comma in it, everything else goes into a mess. I wont be back at work till monday since the code is over there, im already working on an alternate solution which is not necesarily badwidth friendly, but if this one works it would be great.
Ashish

Try the CSV given above to verify the code first before commencing with your own database. Sometimes, the problem lies in the CSV data which doesn't let it to import. Please verify that the CSV you are trying to import is correct.

blog comments powered by Disqus

« Recession? 10 Tips to Manage Your Recession Hit Staff

How To Administer Risk Associated With Web Applications »

Mind Tree

Recent Posts

Apple Unveils new iPod and Play For TV Market

WordPress XMLRPC – Posting Content From Outside WordPress Admin Panel

Top Mobile Apps For Business Users

Should You Optimize Your Website For Bing Search Engine – How?

Browse by Tags

Most Viewed

How to Import CSV File In Your PHP Application

Importing data from CSV files

Dealing with special characters

Importing CSV data directly into the database

Friendly User Interface to Import CSV Data into a Database

Related posts

Subscription

Buy this Advertisement slot

Choose your preferred language

Latest Videos

Sponsers

Top Rated Posts

Popular Posts

Archives

Small Help To Keep us Running

Follow Us