marcus welz

Handling CSV data with PHP the Smart Way

Let's assume we have a file "users.csv", containing the following data:

"Username","Firstname","Lastname","Age"
"johndoe","John","Doe","21"
"hmiller","Hank","Miller","35"

Processing the data in PHP is rather straight forward, by simply using fgetcsv(). Let's display the user's name and age:

// open the file for reading
$f = fopen('users.csv', 'r');

// just a dummy read to get the header out of the way
fgetcsv($f);

// loop through each line
while ($data = fgetcsv($f))
{
	// and write the line "{Firstname} {Lastname} is {age} years old"
	echo($data[1] . ' ' . $data[2] . ' is ' . $data[3] . " years old.\n");
}

The problem that we have here is the "magic numbers". $data[1], $data[2], and $data[3]. Anytime there are magic numbers (Numbers which aren't immediately obvious) they should be replaced with constants or other logic to make their purpose more apparent. For example, if you're working with geometry, rather than using 3.14159 one should use the constant M_PI.

Another reason is that the columns could be re-arranged, so all the sudden we're now looking at users.csv in the following file format:

"Username","Password","Firstname","Lastname","Age"
"johndoe","sekret","John","Doe","21"
"hmiller","trustno1","Hank","Miller","35"

If we ran our script, $data[1] now refers to the column containing the password. We can easily fix this by making a few minor changes to our script:

// open the file for reading
$f = fopen('users.csv', 'r');

// load the row containing the column headers
$hdr = fgetcsv($f);

// flip key -> value (rather than 1 => 'Password', we have 'Password' => 1)
$hdr = array_flip($hdr);

// loop through each line
while ($data = fgetcsv($f))
{
	// and write the line "{Firstname} {Lastname} is {age} years old"
	echo($data[$hdr['Firstname']] . ' ' . $data[$hdr['Lastname']] . ' is ' . $data[$hdr['Age']] . " years old.\n");
}

By simply using $hdr as lookup array we can access each row by its column name rather than some magic number. But be careful: The column header is case-sensitive, and it is possible that two columns contain the same header, in which case array_flip() will use the latter column.

But of course we can detect that if we wanted to:

// open the file for reading
$f = fopen('users.csv', 'r');

// load the row containing the column headers
$hdr = fgetcsv($f);

// flip-flip achieves the same
// as array_unique() but at much better performance
$hdr_unique = array_flip(array_flip($hdr));
$dupes = array_diff(array_keys($hdr), array_keys($hdr_unique));
if (count($dupes))
{
	echo("The following columns have duplicate headers:\n");
	foreach (array_flip(array_flip($dupes)) as $dupe)
		echo($dupe . ' - ' . $hdr[$dupe] . "\n");
	exit();
}

// flip key -> value (rather than 1 => 'Password', we have 'Password' => 1)
$hdr = array_flip($hdr);

// loop through each line
while ($data = fgetcsv($f))
{
	// and write the line "{Firstname} {Lastname} is {age} years old"
	echo($data[$hdr['Firstname']] . ' ' . $data[$hdr['Lastname']] . ' is ' . $data[$hdr['Age']] . " years old.\n");
}

That's all folks!