Solved

xml to csv, csv to xml

Posted on 2003-12-01
13
5,710 Views
Last Modified: 2013-12-13
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.

0
Comment
Question by:dplinnane
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 6

Assisted Solution

by:aolXFT
aolXFT earned 20 total points
ID: 9877625
Can you give a sample XML file, and a Sample CSV file.

I personally suggest you edit the XML File directly using the DOM_XML functions.
0
 
LVL 6

Expert Comment

by:aolXFT
ID: 9877634
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.
0
 

Author Comment

by:dplinnane
ID: 9880327
Here is the xml file

<data><row><REGISTRATION_NUM>abcderx11</REGISTRATION_NUM><SERIAL_NUM>##############14382A</SERIAL_NUM><PHONE_NUM>512-257-2821</PHONE_NUM></row>
<row><REGISTRATION_NUM>abcderx12</REGISTRATION_NUM><SERIAL_NUM>##############P2000A</SERIAL_NUM><PHONE_NUM>512-257-2821</PHONE_NUM></row>
<row><REGISTRATION_NUM>abcderx13</REGISTRATION_NUM><SERIAL_NUM>##############SP2482</SERIAL_NUM><PHONE_NUM>512-257-2821</PHONE_NUM></row>
</data>

I would like to be able to place this in an array
$array_val    = array($column_header=>$data);

where column_header = REGISTRATION_NUM,SERIAL_NUM, 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","Thursday, December, 04, 2003"
"(512) 257-2821","$3,700.00","Thursday, December, 04, 2003"
"(512) 257-2821","$0.00","Thursday, December, 04, 2003"
0
 
LVL 11

Accepted Solution

by:
Zontar earned 480 total points
ID: 9890378
If you have the DOMXML extensions enabled, this is easy:

<?php
  /* XML to CSV */

  $csv = array();

  $doc = domxml_open_file( realpath("myfile.xml") );

  $rows = $doc->get_elements_by_tagname("row");
  echo count($rows) . "<br>";

  foreach($rows as $row)
  {
    $row_elements = $row->child_nodes();
    echo count($row_elements) . "<br>";
    $temp = array();
   
    foreach($row_elements as $row_element)
    {
      $tag = $row_element->tagname();
      $text_node = $row_element->first_child();
      $text = $text_node->get_content();
      $temp[$tag] = $text;
    }
   
    $csv[] = $temp;
  }
  echo "<pre>";
  print_r($csv);
 
  $output = "";
 
  foreach($csv as $item)
  {
    $output .= implode(",", array_values($item)) . "\n";
  }
 
  $output = substr($output, 0, -1);
 
  echo $output;
 
  echo "</pre>";
 
  $fp = fopen("myfile.csv", "w");
  fwrite($fp, $output);
  fclose($fp);
?>

<?php
  /* CSV TO XML */

  $el_names = array("REGISTRATION_NUM", "SERIAL_NUM", "PHONE_NUM");

  $fp = fopen("myfile.csv", "r");

  $doc = domxml_new_doc("1.0");
  $data = $doc->create_element("data");

  while( !feof($fp) )
  {
    $row = $doc->create_element("row");
    $row_els= array();
    $items = explode(",", fgets($fp, 512));

    for($i = 0; $i < count($el_names); $i++)
    {
      $el_name = $el_names[$i];
      $row_els[$el_name] = $doc->create_element($el_name);

      $item = $doc->create_text_node($items[$i]);

      $row_els[$el_name]->append_child($item);
      $row->append_child($row_els[$el_name]);
    }

    $data->append_child($row);
  }

  $doc->append_child($data);

  echo "<pre>" . htmlspecialchars( $doc->dump_mem(TRUE) ) . "</pre>";
 
  $doc->dump_file(realpath("new-file.xml"), TRUE, FALSE);
?>

Enabling DOMXML for PHP on Windows is also quite easy. Holler if you need a hand with it.

Note: DOMXML was overhauled for PHP 4.3.0 -- don't waste your time using the earlier versions of this extension, they're not W3C DOM compliant.
0
 
LVL 11

Expert Comment

by:Zontar
ID: 9890387
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.
0
 

Author Comment

by:dplinnane
ID: 9893718
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.php on line 628
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 11

Expert Comment

by:Zontar
ID: 9893766
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.
0
 

Author Comment

by:dplinnane
ID: 9894832
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.
0
 
LVL 11

Expert Comment

by:Zontar
ID: 9895777
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?
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 9896526
You could ignore XML entirely and use plain text or regular expressions to alter the data.

What alterations do you want to do?
0
 

Author Comment

by:dplinnane
ID: 9897771
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><SERIAL_NUM><PHONE_NUM>. I have added the following code for xml to csv, this seems to work I get echo = REGISTRATION_NUM,SERIAL_NUM,PHONE_NUM, 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.
0
 
LVL 11

Assisted Solution

by:Zontar
Zontar earned 480 total points
ID: 9901382
The basics of writing a DTD are not that hard. Here's links to a couple of tutorials I used a while back to get up to speed:

http://www-106.ibm.com/developerworks/xml/library/x-buildappl/writedtd.html -- this will show you all the basics

http://www.vivtek.com/xml/writing_dtd.html -- not as good as the the one from IBM, but I figured I'd better provide at least one alternative

You can also learn quite a bit from looking at the DTD for a markup language you're doubtless familiar with -- HTML. You can find the HTML DTDs at W3C.org (included with the HTML specifications).

Here's a sample DTD for the original bit of XML you posted:

<!ELEMENT data (row)+>
<!ELEMENT row (REGISTRATION_NUM, SERIAL_NUM, PHONE_NUM)>
<!ELEMENT REGISTRATION_NUM (CDATA)>
<!ELEMENT SERIAL_NUM (CDATA)>
<!ELEMENT PHONE_NUM (CDATA)>

AS you'll see in the tutoprials, you can also define attributes and entities. For example, if you wanted to use the format

<data>
  <row REGISTRATION_NUM="abcde12" SERIAL_NUM="#####" PHONE_NUM="512-555-1234"  />
  <row REGISTRATION_NUM="abcde15" SERIAL_NUM="#####" PHONE_NUM="512-555-2345"  />
  <row REGISTRATION_NUM="abcde27" SERIAL_NUM="#####" PHONE_NUM="512-555-3456"  />
</data>

you could use a DTD like this:

<!ELEMENT data (row)+>
<!ELEMENT row (EMPTY)>
<!ATTLIST
  REGISTRATION_NUM (CDATA) #REQUIRED
  SERIAL_NUM (CDATA) #REQUIRED
  PHONE_NUM (CDATA) #REQUIRED  >

You also might want to take a look at XML Schemas, which are basically just a way to write DTDs using XML. (I'm not an expert with XML Schemas -- they're on my Must Learn For Real List, heh.) There's a helpful introduction to these at http://www.w3.org/TR/xmlschema-0/ ... the advantage to using XML Schemas is that you have much more control over datatypes than you do with a DTD.
0
 

Expert Comment

by:humptybubba
ID: 10145001
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 ??
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now