I recently received a tutorial request about creating a tutorial using PHP to parse Excel and CSV files. So today’s your lucky day, and by the end of this tutorial, you’ll be able an Excel and CSV parsing professional.
When migrating to Church Community Builder (CCB), sometimes other Church Management Software providers give you a backup of your data in an Excel or XML file. But don’t fear when migrating to CCB, and you’re sitting confused and puzzled, endlessly staring at an Excel or XML file wondering how you’re going to migrate your data.
I recently wrote a tutorial about how to parse XML using XPATH and PHP, so that should help those of you handle the XML file migration portion. But for those of you with rows and rows of Excel data, this tutorial is for you.
For starters, there are many PHP libraries available to download online to parse Excel files. PHPExcel, php-excel-reader and simple XLXS are just a few libraries that allow you amazing flexibility for reading and writing Excel files.
I won’t cover these libraries in this tutorial, but do feel free to use the libraries. All are well documented and easy to follow if you have a development experience or programming background. Nevertheless, I’ll show you how to parse your Excel or CSV document using built-in PHP functionality.
Preparing a sample Excel and CSV file
One of the first items well complete for this tutorial is to create an Excel file with a header row. I’ll provide a simple excel spreadsheet to start with that consists of the following headers: First Name, Last Name, Email and Phone.
Create and save a file named testfile.xlsx with the listed headers above, and enter in dummy information as I have below. Now, perfrom a “Save As” and name the file testfile.csv. That’s it, your data is now ready to be PHP parsed. Of course, don’t forget to place the testfile.csv in your web directory.
Let the PHP parsing magic begin…
Now let us get down to business with the heavy lifting of PHP parsing magic using fopen, fgetcsv and fclose built-in PHP functions. Okay, so let me explain these two magical functions.
The fopen function allows you to open and read files or urls while the fgetcsv function reads or parses each line of a CSV in an array. And of course, the fclose function closes that which was open.
So, open your favorite Text Editor of choice and create a err_upTmpName variable and assign it a text string of ‘testfile.csv‘, the name of your CSV file you created in the last section. Oh yes, and be sure to save the file too, naming the file parse-csv.php.
The next variable to set and assign is the iterator variable. Remember how your file has header values? We’re setting an iterator variable to bypass the header values.
Typical spreadsheets have header values, but when parsing, you may not want to account for the header values or row. It would be easy to simply remove the row and not need the iterator variable.
But for the sake of you possibly needing it for a variety of future projects, we’ll set a row iterator variable and assign it 0 (zero).
Next, I’ll create what looks to be a complicated if statement the check to see if the file has been open. Notice we assign the handle variable and assign to it the fopen function, passing to it the err_upTmpName variable as the first argument and the string “r” as the second argument.
Notice that the expression is wrapped in parenthesis and compared using the !== operator, also known as the not identical operator. Simply put, if the fopen function returns TRUE or anything not identical to FALSE, then we move inside the if statement to perform additional code. If not (else), then we echo to the web browser that the file was unable to be opened.
Of course, you could get more specific or greater detail when it comes to error handling, but for the sake of this tutorial, I’ll keep it simple.
<?PHP /** * parse-csv.php */ $err_upTmpName = 'testfile.csv'; $row = 0; if (($handle = fopen($err_upTmpName, "r")) !== FALSE) { } else { echo 'File could not be opened.'; } fclose($handle); ?>
Parsing CSV using the fgetcsv function
It’s time to focus on parsing each row of your CSV file, testfile.csv. To do so, we’ll use a while statement, which acts like a iterator, but performs a task or action repeatedly until it can longer perform the task or action — rather performs the action as long as it’s TRUE.
Create another complex expression using the while statement. This time, we’ll create a data variable and set it equal to the fgetcsv function, passing it the handle variable as the first argument, 0 as the second argument, and “,” as the third argument (the most common CSV delimiter). This while statement will run for each line of your CSV file as long as the result is TRUE. I’ll explain…
Again, wrap the expression in parenthesis and use the !== operator to test whether or not it is TRUE or FALSE. In this case, we want to test to make sure the value is not identical to FALSE. Set your open curly brace, and now you’re ready to skip your header row.
Remember early when we set the row variable equal to 0 (zero)? Well, we’ll use an if statement to skip the first row since it is the header row. Like I said early, you could easily remove the header row from the CSV file if you wanted.
Nevertheless, inside the if row is equal to 0 (zero) statement increment the row variable, now making it one and this ends the first while statement for the first row of your CSV file. Onto the next row.
The next time the while statement executes, it will use the logic found inside of the else of the if else statement. I’ve commented out one line inside the else statement. This is to help you grasp what values are associated with each data array variable.
Next I use a simple if statement to validate whether or not the first name and last name fields hare not empty. If they are empty, nothing is echoed to the web browser; however, when both are completed, we echo each of the data array fields or columns, with dashing concatenated between each value, of the CSV file for each row.
<?PHP while (($data = fgetcsv($handle, 0, ",")) !== FALSE) { if($row == 0){ $row++; } else { // $data[0] = first name; $data[1] = last name; $data[2] = email; $data[3] = phone /*********************************************************************************************************************/ if(!empty($data[0]) && !empty($data[1])) echo $data[0].' - '.$data[1].' - '.$data[2].' - '.$data[3].'<br/>'; } } ?>
Let the PHP magic show begin and end with parsed Excel or CSV data
And here’s what it’s like when it all comes together. Below you can see the entire file. Again, this is a simple tutorial aiming to help you understand how to parse your Excel or CSV data using PHP should you not have the courage to tackle MySQL databases and tables.
Of course, in this example, we echoed data to the web browser, but you could easily setup a MySQL database and insert the parsed Excel or CSV data into their appropriate database table columns.
Well, that’s it! Load your parse-csv.php file into your web directory with your testfile.csv, open in a web browser and happy parsing… Do let me know if you have issues, and/or comments. Stay tuned for our next tutorial.
<?PHP /** * parse-csv.php */ $err_upTmpName = 'testfile.csv'; $row = 0; if (($handle = fopen($err_upTmpName, "r")) !== FALSE) { while (($data = fgetcsv($handle, 0, ",")) !== FALSE) { if($row == 0){ $row++; } else { // $data[0] = first name; $data[1] = last name; $data[2] = email; $data[3] = phone /*********************************************************************************************************************/ if(!empty($data[0]) && !empty($data[1])) echo $data[0].' - '.$data[1].' - '.$data[2].' - '.$data[3].'<br/>'; } } } else { echo 'File could not be opened.'; } fclose($handle); ?>