mootools php faith camera linux


CSV Import - Use
header row to make a
field map

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';

// header field name => db field name
$map = array(
'Menu Completed'=>'menu_date',
'Factual ID'=>'factual_id',

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

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

$row=fgetcsv($h)) {

// setup the expected field mapping
if (empty($flds)) {
$map as $single_field=>&$db_field) {
$row as $k=>&$v) {
            if (
$single_field==$v$flds[$k] = $db_field;
// skip the rest of the first loop
// the mapped field locations within each $row:

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())';

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
Creative Commons License Jay Johnston is a Zend Certified Engineer in PHP 5 LPI-ID: LPI000102450, Verfication Code: wmnnt56y2k  ... Jay Johnston is certified as LPIC-1 by the Linux Professional Institute