Tips for importing CSV files

By Andy Mornes | 04/24/2009 - 3:59pm

During my time working with CATS, I've dealt a lot with CSV files, whether it be exporting a MySQL query using the "INTO OUTFILE" clause, or converting a MSSQL database table to Access, and then CSV or reading a CSV import into CATS. Most of the time we're reading in the CSV file to import, and have to use each column in a different spot. One annoyance I came across when reading the CSV files was matching up which columns were which when there are more than 5 or so. Anyways, I found a very simple way to solve this problem. Basically, you always want to open the file and read the first line containing the headers. Then we'll create a map using the array_flip() function.
This works by reversing the keys and values in the $header array. When we first get the headers, the array could be something like

array(4) { [0]=> "First Name", [1]=> "Last Name", [2]=> "Email", [3]=> "Cell Phone" }

After using the array_flip() function, we'll get a more useful array telling us which headers are at which index.

array(4) { ["First Name"]=> 0, ["Last Name"]=> 1, ["Email"]=> 2, ["Cell Phone"]=> 3 }

After that, while reading each row, we can use the "mapped" array to determine the index without looking at the original file.

$firstName = $row[ $map['First Name'] ] will properly retrieve column 0, or the First Name field.

<?php

// Defensively open the file here
if (!$fp = fopen('/tmp/testdata.csv', 'r'))
die('Error opening file');

// Read the first line containing the headers
$headers = fgetcsv($csv);

// Create the header map
$map = array_flip($headers);

while(!feof($fp) && $row = fgetcsv($fp))
{
echo "First Name = ".$row[$map['First Name']]."\n";
}

fclose($fp);

?>

See a list of our other blogs.
 

Recent blogs by Andy Mornes

A Twitter Memo: Auto Responding with PHP - 03/09/2010 - 4:54pm
Twitter continues to becoming more and more popular these days, especially for businesses. As a business, you want to keep your followers interested, and in-tune with all... read more

Setting up a redundant email server - 10/05/2009 - 10:32am
In the past few weeks, we have seen an increase in the amount of emails being sent through the CATS system. In response to this increase, I was assigned the task of... read more

Search and Import LinkedIn Profiles into CATS - 09/09/2009 - 4:33pm
There's no shortage of requests for LinkedIn integration. We're proud to announce that as of today, via our Firefox toolbar, you can now import LinkedIn profiles, resumes... read more