PHP script converting XML to CSV with column headers returning blank on some headers and rows
my full XML is below which is named user.xml:
<?xml version="1.0" encoding="utf-8"?>
<users>
<user id='1'>
<user_name>John</user_name>
<user_lastname>Doe</user_lastname>
<previous_requisitions>
<requisition_code>X321</requisition_code>
<requisition_code>Y321</requisition_code>
<requisition_code>Z321</requisition_code>
</previous_requisitions>
<user_requisition>
<requisition_code>X123</requisition_code>
<requisition_title>Ssr Dev 1</requisition_title>
<requisition_relocation>10~20%</requisition_relocation>
</user_requisition>
</user>
<user id='2'>
<user_name>James</user_name>
<user_lastname>Smith</user_lastname>
<previous_requisitions>
<requisition_code>X222</requisition_code>
<requisition_code>Y222</requisition_code>
<requisition_code>Z222</requisition_code>
</previous_requisitions>
<user_requisition>
<requisition_code>Y123</requisition_code>
<requisition_title>Sr Dev 1</requisition_title>
<requisition_relocation>20~30%</requisition_relocation>
</user_requisition>
</user>
<user id='3'>
<user_name>Jess</user_name>
<user_lastname>Ssej</user_lastname>
<previous_requisitions>
<requisition_code>X111</requisition_code>
<requisition_code>Y111</requisition_code>
<requisition_code>Z111</requisition_code>
</previous_requisitions>
<user_requisition>
<requisition_code>Z123</requisition_code>
<requisition_title>Jr Dev 2</requisition_title>
<requisition_relocation>0~10%</requisition_relocation>
</user_requisition>
</user>
</users>
since not very good with XML and with the help of online resources and stackoverflow; was able to create this PHP script:
<?php
$file='user.xml';
if (file_exists($file)) {
$xml = simplexml_load_file($file);
$f = fopen('user.csv', 'w');
// array to hold the field names
$headers = array();
// loop through the first set of fields to get names
foreach ($xml->user->children() as $field) {
// put the field name into array
$headers[] = $field->getName();
}
// print headers to CSV
fputcsv($f, $headers, ',', '"');
foreach ($xml->user as $users) {
fputcsv($f, get_object_vars($users), ',', '"');
}
fclose($f);
}
?>
when i run the PHP script, the file is written but with some issues:
- not all headers are written.
- not all data are written
- first column is populated with "Array"
see below for screenshot of the result(open in excel):
QUESTIONS:
since it appears that the $headers = array(); is not looping properly. any fix on the PHP script to show all headers and the data from the XML properly?
i only need the following child nodes to be exported to CSV, so these 6 will also be the headers:
user_id,
user_name,
user_lastname,
user_requisition_code,
user_requisition_title,
user_requisition_relocation,
pls direct me how i can make the script "selective" in which omitting or not to include other child nodes from the XML like:
previous_requisitions
requisition_code
- is there a way for PHP(without using BASH, wk or sed) to work or write a "pipe delimited file" instead of CSV from XML assuming to use the same XML file above given. i tried replacing the comma in the script for a PIPE character and browser output gives me this warning: Warning: fputcsv() expects parameter 1 to be resource, boolean
thanks a lot for the help and happy new year!
sidenote: would be sticking with opensource scripting hoping no one will answer with suggestion to use a proprietary software.
For 1.) it might appear to you that $headers = array(); is not looping properly, but I don't see anything wrong with the looping. It's perfectly fine and absolutely follows what is documented with SimpleXML: http://php.net/book.simplexml .
For 2.) I'd say you want to skip all same-named elements within a grouping element (if any). This somewhat reminds me to this question and answer but in that example, siblings with the same name and the same parent were interpolated into additional rows. In your case you just want to skip them. For you perhaps easier is to just query via xpath the values you're looking for. As the elements you query also have a name which is even the column name, this should work quite well, too. (I will show both variants in example codes below)
For 3.) this should be perfectly well possible. The error message you give makes it look to me you confused some other parameters. It's not related to setting pipe as a delimiter (the code examples will use the pipe symbol "|" as CSV delimiter, it perfectly works).
That being said, a solution that comes close to what you visually outlined in the question (but is missing the user id) could be:
$csv = new SplFileObject('php://output', 'w');
$csv->setCsvControl("|");
$users = iterator_to_array($xml->user, false);
foreach ($users as $index => $user) {
$fields = [];
foreach ($user->xpath('(*[not(./*)]|user_requisition/*)') as $field) {
$fields[$field->getName()] = trim($field);
}
// first iteration output headers
$index || $csv->fputcsv(array_keys($fields));
$csv->fputcsv($fields);
}
The exemplary output with your example XML is:
user_name|user_lastname|requisition_code|requisition_title|requisition_relocation
John|Doe|X123|"Ssr Dev 1"|10~20%
James|Smith|Y123|"Sr Dev 1"|20~30%
Jess|Ssej|Z123|"Jr Dev 2"|0~10%
The use-case with the mapping would be a slightly different code:
$csv = new SplFileObject('php://output', 'w');
$csv->setCsvControl("|");
$fieldDefs = [
'user_id' => '@id',
'user_name' => 'user_name',
'user_lastname' => 'user_lastname',
'requisition_code' => 'user_requisition/requisition_code',
'requisition_title' => 'user_requisition/requisition_title',
'requisition_relocation' => 'user_requisition/requisition_relocation',
];
// output CSV headers
$csv->fputcsv(array_keys($fieldDefs));
$users = $xml->user;
foreach ($users as $user) {
$fields = [];
foreach ($fieldDefs as $fieldDef) {
$fields[] = $user->xpath($fieldDef)[0];
}
$csv->fputcsv($fields);
}
With a slightly different output:
user_id|user_name|user_lastname|requisition_code|requisition_title|requisition_relocation
1|John|Doe|X123|"Ssr Dev 1"|10~20%
2|James|Smith|Y123|"Sr Dev 1"|20~30%
3|Jess|Ssej|Z123|"Jr Dev 2"|0~10%
This second example shows how to combine a more concrete mapping to the headers. This is a bit more explicit and allows to name the headers differently from the element names.
I hope these examples are quite self-explanatory, but you might stumble over some things I do differently to what you might have done. E.g. I use SplFileObject to do the CSV operations, it's basically really the same as the method you use, however in form of an object which keeps the code a bit more clean (as there is no need to repeat some of the parameters).
The XPath expressions in the second example as well might be new to you, but I hope they are really self explanatory just from looking at the array and the XML you've been given.
The examples should be backwards compatible down to PHP 5.4.