Link to home
Start Free TrialLog in
Avatar of williamskellorn
williamskellorn

asked on

Converting XML to CSV with PHP

I need to produce a PHP script (or find a function that can be called within the script) to regularly convert an XML file to CSV, examples of which are attached.

I have started looking at converting the XML to an object using PHP 5 simplexml_load_file, and then iterating through to write values to a CSV file using fputcsv.

However, I've hit three snags that I'm not sure how to go about solving -

The number of rows written to the CSV are dictated by the number of '<message>' fields in the xml document, but using a 'foreach' argument based on <message> would then need to be able to retrieve values from parent fields of that message (and children of fields on the same level of hierarchy as <message>.
Some fields are not included for all <satellitePass> (see second row in CSV for bird 2) - Not sure how to ensure these are consistently represented by an empty value in the CSV without having to do a check for each possible value to see if it is present.
Field keys need to be converted to header names for the first row of the CSV - This is not vital - they shouldn't change so could be pre-written to the CSV before parsing the rest of XML.

Any tips on the best (most sane) way to approach this would be gratefully received - I hope there are some shortcuts I'm not aware of rather than a very dense script full of deeply nested 'if' arguments to check the possible existence of each field.

Thanks.
example-XML-input.xml
example-required-CSV-output.csv
SOLUTION
Avatar of Gertone (Geert Bormans)
Gertone (Geert Bormans)
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of williamskellorn
williamskellorn

ASKER

Hi Gertone,

Thanks for the speedy answer!

The only varying factors are likely to be number of platforms, number of satellite passes per platform, and number of messages per satellite - so I think a bespoke XSLT script is looking like a good option.

Hadn't come across XSLT before, I'll dig into it (and your other posts) - but if it's a quickie for you to put together something that does the job, or gets me on the way, that would be fantastic!
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Let me know if you don't know how to initiate an XSLT in PHP
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yep, Your assumptions are probably correct and that's right where I am going with the question!
Gertone and Ray - Thank you both for thorough and rigourous answers to the initial question.

To fill in a little of the reasoning - data currently is provided via daily CSV attachments and these are stored as well as being parsed into a single table mySQL database. Currently fewer messages are delivered as there is an algorithm in effect that chooses the 'best' message for a satelite pass. We can move to retrieve the data via a webservice which returns octet stream xml, in the form shown in the example.

In the short-term we need to replicate the original workflow as closely as possible so that the data may still be stored as intermediate CSV files, mirroring the current workflow while we run both workflows in tandem.

Once we have moved over to the webservice alone, I would then potentially look to remove the intermediate storage step and write the xml directly to the database. Either way, the reduction of the number of messages to pick the best will need to be replicated our end, thus a single table approach will probably still be sufficient.

I'll be back on this project tomorrow and will look at initiating the XSLT script you have kindly provided via PHP then, as well as consider the flexibility versus hard-coded trade-off.

I'll mark up Gertone's solution providing the XSLT as accepted now though - didn't want to make it look like I wasn't grateful!
Very impressed by the swiftness, thought and effort afforded to my first question on EE.

My thanks to Gertone and Ray_Paseur!
welcome,
don't hesitate to come back here if you need changes to the XSLT, explanation of what it does and how it does it, or help initiating it from PHP