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
PHPXML

Avatar of undefined
Last Comment
Gertone (Geert Bormans)

8/22/2022 - Mon
SOLUTION
Gertone (Geert Bormans)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Gertone (Geert Bormans)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Gertone (Geert Bormans)

Let me know if you don't know how to initiate an XSLT in PHP
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Ray Paseur

Yep, Your assumptions are probably correct and that's right where I am going with the question!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
williamskellorn

ASKER
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!
williamskellorn

ASKER
Very impressed by the swiftness, thought and effort afforded to my first question on EE.

My thanks to Gertone and Ray_Paseur!
Gertone (Geert Bormans)

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.