PHP's fgetcsv function sucks

Monday, May 19, 2008, 12:23 AM
Source Code by John

Fgetcsv is a function that ships with PHP since sometime in version 4. It allows you to parse a Comma Separated Values format spreadsheet.

But, it comes with an ugly flaw: it doesn't automatically belch out null values.

Now, if you are using CSVs to create databases in MySQL, that sucks big time. Because every iteration of an INSERT command in MySQL has to have the right number of data inserts to correspond the all the fields you request.

Yes, I am aware that the LOAD command works more efficiently from a DBA standpoint. But, for my purposes, I'm letting folks upload CSVs, and there is no viable way to allow them to use the LOAD command. So, I am stuck with CREATE and INSERT to provide the simplest and most accessible approach.

Advertisements

Yeah, I probably should have written the script in Perl. But, I like to keep a project as close to a basic framework as possible. Plus, I'm just not exceedingly fond of diving into Perl.

But, you'd thinks such a basic function would have been integrated with the notion in mind that CSV files are rarely consistent beyond the fact they have commas in them.

My workaround is simple. The first line of these CSV files is supposed to have the title for those fields in it. Count all the fields in the first line, and then iterate out some blank spaces into the empty fields if they yielding null values for those fields.

The project itself uses slightly under 100 fields. It is large enough that importing the spreadsheets from Excel is too cumbersome and eventually runs the server into an out of memory error.

And, with 100 fields being dynamically inserted from mismatched content over perhaps 35,000 or so entries, you're pretty much begging MySQL to get catty about something.

Now, here's the code that the main PHP website suggests using...

$row = 1;
$handle = fopen("test.csv", "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$num = count($data);

    echo " $num fields in line $row: 
\n";

$row++;
for ($c=0; $c < $num; $c++) {

Advertisements

        echo $data[$c] . "
\n";

}
}

fclose($handle);

The primary flaw in this code is that it assumes each line will be consistently populated with content. Anyone who has ever handled CSV files will tell you that ain't the truth at all.

But, if you add this little bit to the code...

$row = 0;
$c=0;
while (($data = fgetcsv($handle, 4096, ',', '"'))){
$row++;
if($row==1 and $c==0) $num = count($data);

Something funny happens when you go to iterate all those cells into the fields in MySQL. Now you have empty sets that can be used to insert null values into MySQL. Haha.

Now you won't get those MySQL errors griping about not having a match at row 1 or whatever. Because now if the CSV doesn't give you a piece of content, you still have a proper column count that allows you to stop and say, "Well, if I'm missing data, let's use a conditional structure to insert a null value into MySQL."

Haha. Stupid row count error begone!

Mail article to a friend

Tools

Check Google PageRank

Recent articles

Welcome!

Wonder where to start with your web design business?

This blog follows along with my efforts to build and grow a website design business, Pro Content and Design.

The goal of this blog is to fill in blanks that may be empty as you get your business rolling.

This blog, particularly the source code section, is not intended for beginners. If you are not comfortable with databases, Ajax, DOM objects and other advanced methods, I strongly suggest you go take a look over at W3 Schools before even reading -- let alone tinkering with -- any of the code here.

I hope this blog has some value to web designers as they attempt to get their businesses going.

Good luck, and happy reading.

Thank you,
John Crawford
Pro Content and Design