dplinnane
asked on
xml to csv, csv to xml
I want to be able to convert xml files into csv files, alter the csv file with code I have written and then convert the csv file back to xml. I know there is a bunch of classes already written ie pear. Problem is I'm not sure where to start. Do I need pears XML_Parser, the XML_RSS parser, XML_Serializer. Do I need one for xml to csv and another for csv to xml.
Any input appreciated, don't want to waste my time barking up the wrong tree.
Any input appreciated, don't want to waste my time barking up the wrong tree.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Having that said, if you do want to convert between the two, that you use XSLT for xml->csv, and simply iterate through the values for visa-vearsa.
ASKER
Here is the xml file
<data><row><REGISTRATION_N UM>abcderx 11</REGIST RATION_NUM ><SERIAL_N UM>####### #######143 82A</SERIA L_NUM><PHO NE_NUM>512 -257-2821< /PHONE_NUM ></row>
<row><REGISTRATION_NUM>abc derx12</RE GISTRATION _NUM><SERI AL_NUM>### ########## #P2000A</S ERIAL_NUM> <PHONE_NUM >512-257-2 821</PHONE _NUM></row >
<row><REGISTRATION_NUM>abc derx13</RE GISTRATION _NUM><SERI AL_NUM>### ########## #SP2482</S ERIAL_NUM> <PHONE_NUM >512-257-2 821</PHONE _NUM></row >
</data>
I would like to be able to place this in an array
$array_val = array($column_header=>$dat a);
where column_header = REGISTRATION_NUM,SERIAL_NU M, PHONE_NUM
$data is the data associated with each column.
Once I do this I can format the data and then either convert it to csv or back to xml.
I would alos like to be able to do the reverse, convert this to an array and the to xml.
Basically once I get an array I can do whatever I want xml or csv. I have been looking at XML_serialiser on pear.php
there is also a class called PARSER which is based on xpath.
I know nothingabout xslt so I am limited there. Any further inputwould be appreciated, I will look at DOM_XML and see can I make any sense of that.
"PHONE_NUM","SALARY_AMT"," DATE"
"(512) 257-2821","$6,050.00","Thu rsday, December, 04, 2003"
"(512) 257-2821","$3,700.00","Thu rsday, December, 04, 2003"
"(512) 257-2821","$0.00","Thursda y, December, 04, 2003"
<data><row><REGISTRATION_N
<row><REGISTRATION_NUM>abc
<row><REGISTRATION_NUM>abc
</data>
I would like to be able to place this in an array
$array_val = array($column_header=>$dat
where column_header = REGISTRATION_NUM,SERIAL_NU
$data is the data associated with each column.
Once I do this I can format the data and then either convert it to csv or back to xml.
I would alos like to be able to do the reverse, convert this to an array and the to xml.
Basically once I get an array I can do whatever I want xml or csv. I have been looking at XML_serialiser on pear.php
there is also a class called PARSER which is based on xpath.
I know nothingabout xslt so I am limited there. Any further inputwould be appreciated, I will look at DOM_XML and see can I make any sense of that.
"PHONE_NUM","SALARY_AMT","
"(512) 257-2821","$6,050.00","Thu
"(512) 257-2821","$3,700.00","Thu
"(512) 257-2821","$0.00","Thursda
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oops, I forgot to do the bit with the column headings -- if you can't modify what I've givien you to take care of that, give me a shout and I'll lend a hand with that also.
ASKER
Thanks, This looks great. I can't test because I cannot get domxml going on windows. I tried the following.
Copied iconv.dll from the C:/PHP/dlls folder to your C:/WINNT/SYSTEM32 folder. I followed instructions from http://us2.php.net/domxml.
Do they mean move that file or copy it? I restarted apache but mo luck. Any ideas. Thx
Call to undefined function: domxml_open_file() in C:\Program Files\Apache Group\Apache2\htdocs\body\ body_test_ execute.ph p on line 628
Copied iconv.dll from the C:/PHP/dlls folder to your C:/WINNT/SYSTEM32 folder. I followed instructions from http://us2.php.net/domxml.
Do they mean move that file or copy it? I restarted apache but mo luck. Any ideas. Thx
Call to undefined function: domxml_open_file() in C:\Program Files\Apache Group\Apache2\htdocs\body\
Did you copy php_domxml.dll to the system directory as well?
Usually what I do when installing a new PHP distribution on Windows is just to copy everything from the extensions and dll directories to system32. ;)
Don't forget to uncomment the lines referring to php_domxml.dll and iconv.dll in your php.ini, either.
Usually what I do when installing a new PHP distribution on Windows is just to copy everything from the extensions and dll directories to system32. ;)
Don't forget to uncomment the lines referring to php_domxml.dll and iconv.dll in your php.ini, either.
ASKER
excellent that works.
However, I'm not sure how to get the first part xml to csv to add the column headers, if you could assist I would appreciate it. One more question, I will be uploading an xml file is there a function to check that the file is in the desired format ie simple xml where there is a column headers and a row of matching data like in the example I have given above. Thx.
However, I'm not sure how to get the first part xml to csv to add the column headers, if you could assist I would appreciate it. One more question, I will be uploading an xml file is there a function to check that the file is in the desired format ie simple xml where there is a column headers and a row of matching data like in the example I have given above. Thx.
Just output a line with the column headers, since you already know what they're supposed to be. Just change
$output = "";
to
$output = "REGISTRATION NUMBER,SERIAL NUMBER, PHONE NUMBER\n";
or however you want them to read.
If you want to validate the XML, you'll need to write a DTD to validate it against.
Are you saying is that what you're after after is a more generic solution?
$output = "";
to
$output = "REGISTRATION NUMBER,SERIAL NUMBER, PHONE NUMBER\n";
or however you want them to read.
If you want to validate the XML, you'll need to write a DTD to validate it against.
Are you saying is that what you're after after is a more generic solution?
You could ignore XML entirely and use plain text or regular expressions to alter the data.
What alterations do you want to do?
What alterations do you want to do?
ASKER
Your suggestion will work
$output = "REGISTRATION NUMBER,SERIAL NUMBER, PHONE NUMBER\n";
but the solution will needs to be dynamic because a user could upload an xml file, with different column names so we need to be able to grab
the first row of data from xml file and use the tag name <REGISTRATION_NUMBER><SERI AL_NUM><PH ONE_NUM>. I have added the following code for xml to csv, this seems to work I get echo = REGISTRATION_NUM,SERIAL_NU M,PHONE_NU M, for csv to xml I can easily grab the 1st row which will the col_hdr instead of using $el_names = array("REGISTRATION_NUM", "SERIAL_NUM", "PHONE_NUM");
foreach($row_elements as $row_element)
{
$col_hdr = $row_element->tagname();
$row_col_hdr[] = $col_hdr;
}
$csv_row_col_hdr = implode(",", $row_col_hdr). "\n";
echo "csv_row_col_hdr $csv_row_col_hdr".'<br>';
You could ignore XML entirely and use plain text or regular expressions to alter the data. HAS TO BE XML
What alterations do you want to do? THIS PART IS EASY, the xml->csv and csv->xml was the difficult part.
Concerning my question on validating an xml file it should always be in the same format. Thanks for your help new to xml just a week so didn't really know where to start.
I'll close this one out on reply let me know if you want me to post this
"If you want to validate the XML, you'll need to write a DTD to validate it against."
as a new question as you deserve more then 500 points for this one.
Not a clue on where to start with DTD to validate it against.
$output = "REGISTRATION NUMBER,SERIAL NUMBER, PHONE NUMBER\n";
but the solution will needs to be dynamic because a user could upload an xml file, with different column names so we need to be able to grab
the first row of data from xml file and use the tag name <REGISTRATION_NUMBER><SERI
foreach($row_elements as $row_element)
{
$col_hdr = $row_element->tagname();
$row_col_hdr[] = $col_hdr;
}
$csv_row_col_hdr = implode(",", $row_col_hdr). "\n";
echo "csv_row_col_hdr $csv_row_col_hdr".'<br>';
You could ignore XML entirely and use plain text or regular expressions to alter the data. HAS TO BE XML
What alterations do you want to do? THIS PART IS EASY, the xml->csv and csv->xml was the difficult part.
Concerning my question on validating an xml file it should always be in the same format. Thanks for your help new to xml just a week so didn't really know where to start.
I'll close this one out on reply let me know if you want me to post this
"If you want to validate the XML, you'll need to write a DTD to validate it against."
as a new question as you deserve more then 500 points for this one.
Not a clue on where to start with DTD to validate it against.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
am trying to do something similar but with a single row and n nuber of variables, what parts of the csv to xml code above should i change to make it work for me ??