---

To prevent new fields in a csv import from breaking an import, make a field map.

Given a CSV that has a header row like this: Menu Completed,Factual ID,locationId,name,longitude,latitude,address1,address2,city,region,postcode,phone,website and a database table defined as such:


create table singleplatform (
  id integer unsigned primary key auto_increment,
  menu_date date,
  factual_id varchar(255),
  location_id varchar(255),
  name varchar(255),
  longitude varchar(255),
  latitude varchar(255),
  address1 varchar(255),
  address2 varchar(255),
  city varchar(255),
  region varchar(255),
  zip varchar(255),
  phone varchar(255),
  website varchar(255),
  dateposted datetime
)

We can write a field map that will help us to always find the right columnar data to insert into our database. This excerpt is from a class that does some other things with SinglePlatform data, but this method stands alone and handles the CSV import and field mapping:

  public function load_csv() {

    $query = 'truncate singleplatform';
    $this->db->query($query);

    // header field name => db field name
    $map = array(
      'Menu Completed'=>'menu_date',
      'Factual ID'=>'factual_id',
      'locationId'=>'location_id',
      'name'=>'name',
      'longitude'=>'longitude',
      'latitude'=>'latitude',
      'address1'=>'address1',
      'address2'=>'address2',
      'city'=>'city',
      'region'=>'region',
      'postcode'=>'zip',
      'phone'=>'phone',
      'website'=>'website',
    );

    // query fragment
    $q = 'insert into singleplatform values' ;

    // open the csv from the current directory
    $h = fopen('singleplatform.csv','r');

    while($row=fgetcsv($h)) {

      // setup the expected field mapping
      if (empty($flds)) {
        foreach($map as $single_field=>&$db_field) {
          foreach($row as $k=>&$v) {
            if ($single_field==$v) $flds[$k] = $db_field;
          }
        }
        continue; // skip the rest of the first loop
      }
      
      // the mapped field locations within each $row:
      //die('<pre>'.print_r($flds,true));

      for($i=0;$i<count($flds);$i++) {

        // we don't insert all columns, just the mapped ones
        $val = mysql_real_escape_string($row[$i]);
        $cols[] = '"'.$val.'"';
      }
      $query = $q.'(null,'.implode(',',$cols).',now())';
      $db->insert($query);
    }
  }

Note: If the existing column names change another solution will be necessary.

Using the CSV fields map, parse each row and insert query the query without fear that a new column will break the import in the future.



Last Updated: 2012-01-17 20:36:17