How to parse and save XML as CSV/Excel file

Today’s tutorial about how to parse and save XML as a CSV/Excel file will be simple as it builds on a previous tutorial: How to retrieve CCB Event Details.

If you briefly recall, this tutorial answered the question about how to retrieve a list of events and events detail based on a given date and event creator.  The results were then displayed in the web browser.

Using the same tutorial, we’ll modify the codebase to parse and save XML data as a CSV and Excel file, which most church staff should be comfortable manipulating in terms of filtering, sorting, etc.

Are you ready to begin?  Let’s do it!

Using PHP to create CSV and Excel files

As I know some of your are quite technical, let me start by saying that there are a few libraries available that offer greater performance and more organized structure than the example that I will use in this tutorial.

In this tutorial, I’ll show you how to use built-in PHP methods to quickly create a file (fopen), write data to a file (fwrite) and close a file once you’re done writing data (fclose).  These are just simple methods and is not the only way to create a file.  For example, you could also create a file using fputcsv instead of fwrite.

For more expansive methods of creating CSV and Excel files using php, check out the following libraries:

• PHPExcel
• SImple Excel Writer
• Spreadsheet_Excel_Writer
• Create files with PHP
• PHP XLSXWriter

Feel free to go research and play around with the various aforementioned PHP libraries for creative CSV and Excel files.

Parse and save XML data

Okay, so let’s start by creating and saving a php file name excel-event-profiles.php and copying and pasting the code below into it.  I chose event details only because I had a previous tutorial to build upon and plus, I thought it would be easiest to understand.

Nevertheless, I’m not going into much detail about the tutorial as you can read the previous tutorial for step-by-step understanding.  I’m going to jump in head first, so here go…

You’ve now down to parsing your XML via the nodes variable. Before iterating through each event, you previously set the response_object variable to null or empty string, and this is ok.

Creating your CCB Events CSV file using PHP

To create headers in your CSV file, we are going to create a comma delimited string of headings and assign the string to the response_object variable.  Notice on the end of the string there is a .”\n”, which is there to indicate a line break.  So now that we have our headers list, we can move forward with displaying the appropriate data in its respective row and column.

If you desire a list of all of your events and not based on a certain person, then feel free to remove the if statement (i.e., if($node->creator == $personName){) and its respective closed curly ( } ).

Now, using the response_object variable, assign each event datapoint detail as string using double quotes to encapsulate the string followed by a comma (see below).  It too also uses a line break at the very end to signify a line has ended and a new has begun.

As usual, we echo the response_object variable.  I left this functionality in place so that you could see the output data in the web browser.

So now for the magic file creation.  Assign a variable, f variable in this example, and pass to the fopen php function the apiService variable concatenated to ‘.csv’ as the first of two fopen arguments.  This function basically will create a file named event_profiles.csv for reading and writing purposes, place the file pointer at the beginning of file and truncates the file to zero length (deletes all of the content and preps to write new content).

Next, using the fwrite method, pass to it the f and response_object variables separated by a comma.  This writes the response_object variable data to the events_profiles.csv file in the proper csv format.

Finally, close the file by passing the f variable to fclose method, and that’s it.  You should now have a events_profiles.csv file in the same web directory as your excel-event-profiles.php.  

From this point, you can open the file using Excel or Numbers and save it as a XLXS file or keep it in the current CSV format.  Your data from the events_profiles.csv should look like the following from the web browser and in Excel/Numbers respectively (although your data should be different than mine).

Content as shown in web browser

File as a CSV

That’s all it is to parsing and saving XML data as a CSV or Excel file.  Well, stay tuned until the next tutorial.

<?PHP

// contains resusable globals and functions
include("includes/config.php");

$apiService = 'event_profiles'; // CCB api service

$modifiedDate = date('Y-m-d');  // sample field to search for (See CCB API documentation for more $apiService fields)
$personName = 'Alvin Brown';

$urlData = array_filter( array(
	'srv' => "$apiService",
	'modified_since' => "$modifiedDate"
	) );

$rss = ccbDetails('get',$urlData); // transform to XML

$nodes = $rss->xpath('//events/event');  // xpath for events->event

$response_object = '';

$response_object = '"Name","Description","Date","Start Time","End Time","Person"' . "\n";

foreach ($nodes as $node)
{
	if($node->creator == $personName){
    	$response_object .= '"'.$node->name.'","'.$node->description.'","'.$node->start_date.'","'.$node->start_time.'","'.$node->end_time.'","'.$node->creator.'"'."\n";  // now prep row data for csv file (See CCB API documentation for more $apiService fields)
    }
}

echo $response_object;
 
$f = fopen($apiService.'.csv' , 'w+');
fwrite($f , $response_object );
fclose($f);

?>