Converting XML file to CSV

Posted on 2007-10-17
Last Modified: 2012-08-13
I've looked high and low for a PHP or Perl script that will take an XML file and turn it into a CSV or other delimited txt file.  It seems as if this XML file was created by Excel, but I can't import it into Excel and I can't figure out how to get it into a readable-usable form.


Here's my XML:
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
  <Style ss:ID="s21">
  <Style ss:ID="s22">
   <Font x:Family="Swiss" ss:Color="#FF0000"/>
 <Worksheet ss:Name="_combind">
  <Table ss:ExpandedColumnCount="15" ss:ExpandedRowCount="1326" x:FullColumns="1"
   x:FullRows="1" ss:StyleID="s21">
   <Column ss:StyleID="s21" ss:Width="141"/>
   <Column ss:Index="8" ss:StyleID="s21" ss:Width="194.25"/>
   <Column ss:StyleID="s21" ss:Width="192"/>
   <Column ss:Index="15" ss:StyleID="s21" ss:Width="192"/>
    <Cell><Data ss:Type="String">Full Name (Name Field)</Data></Cell>
    <Cell><Data ss:Type="String">Title</Data></Cell>
    <Cell><Data ss:Type="String">Salutation</Data></Cell>
    <Cell><Data ss:Type="String">Nickname</Data></Cell>
    <Cell><Data ss:Type="String">Last Name</Data></Cell>
    <Cell><Data ss:Type="String">First Name</Data></Cell>
    <Cell><Data ss:Type="String">Middle Initial</Data></Cell>
    <Cell><Data ss:Type="String">Office Name for Address</Data></Cell>
    <Cell><Data ss:Type="String">Member E-mail</Data></Cell>
    <Cell><Data ss:Type="String">Care of</Data></Cell>
    <Cell><Data ss:Type="String">Address</Data></Cell>
    <Cell><Data ss:Type="String">City</Data></Cell>
    <Cell><Data ss:Type="String">State</Data></Cell>
    <Cell><Data ss:Type="String">Zip</Data></Cell>
    <Cell><Data ss:Type="String">E-mail</Data></Cell>
   <Row ss:Hidden="1">
    <Cell><Data ss:Type="String">Gloria</Data></Cell>
    <Cell><Data ss:Type="String">Ms. </Data></Cell>
    <Cell><Data ss:Type="String">Smith</Data></Cell>
    <Cell><Data ss:Type="String">Gloria</Data></Cell>
    <Cell><Data ss:Type="String">Smith</Data></Cell>
    <Cell><Data ss:Type="String">Gloria</Data></Cell>
    <Cell><Data ss:Type="String">R</Data></Cell>
    <Cell><Data ss:Type="String">PROPERTIES</Data></Cell>
    <Cell><Data ss:Type="String"></Data></Cell>
    <Cell ss:Index="11"><Data ss:Type="String">400 Penn</Data></Cell>
    <Cell><Data ss:Type="String">Whitsett</Data></Cell>
    <Cell><Data ss:Type="String">State</Data></Cell>
    <Cell><Data ss:Type="Number">27111</Data></Cell>
    <Cell><Data ss:Type="String"></Data></Cell>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
  <Sorting xmlns="urn:schemas-microsoft-com:office:excel">
   <Sort>Last Name</Sort>
Question by:marcparillo
    LVL 21

    Expert Comment

    Do you really need a PHP or a Perl solution?
    Since you can and have saved your XML into a file, you can use a VB solution:

    Otherwise, the closest thing I can find is converting CSV file to XML, maybe you can try and reverse-engineer the code to convert a XML to CSV?

    Let me know how you go.
    LVL 17

    Expert Comment

    If you change the line: <Row ss:Hidden="1"> to: <Row ss:Hidden="0"> or just <row>, it'll import correctly to Excel.  You can also open it as is with Excel, select the whole worksheet, and select Format->Row->Unhide.
    LVL 3

    Author Comment


    mjcoyne -- your solution seems to be the easiest, but it didn't work for me.  Am I importing the data correctly?  I simply opened Excel and clicked File -> Open and then selected the 'myfile.xml' file on my desktop to open.

    Is there another way to import an XML file?  I only know how to import a text file into Excel by clicking Data -> Get External Data.
    LVL 17

    Accepted Solution

    I cut your data from here, and pasted it in a text document I saved as ee-xml.xml.  I then opened this file in Excel (using Right-click->Send to... or opening the file directly in Excel with File->Open), pressed Ctrl-Home to get to the first cell in the workbook (the Excel cursor is originally in cell F92, as specified by your XML in the <ActiveRow> and <ActiveCol> lines).

    At this point, I see just the column header lines.  I also notice that the rows are numbered 1, 3, 4, 5, ..., so row 2 is "missing".  That's because it's hidden, and the instructions I gave before rectify that.  Once unhidden, I can see the data under the column headers.

    If you'd rather a Perl script to output a CSV file, try something like this:

    #!/usr/bin/perl -w
    use strict;
    my (%lines, $section);

    open (IN, "ee-xml.xml") or die "Can't open input file $!\n";
    open (OUT, ">ee-xml.csv") or die "Can't open output file $!\n";

    while (<IN>) {
        if (/<Row/) {
        if (/<Row/ .. /<\/Row>/) {
            if (/^\s*<Cell>/) {
                my ($field) = (/<Data.+?\">(.+?)<\/Data>/);
                push @{$lines{$section}}, $field;

    foreach my $section (sort keys %lines) {
        print OUT join (",", @{$lines{$section}}), "\n";
    LVL 84

    Expert Comment

    > Here's my XML:
    What do you want the corresponding CSV file to look like?
    LVL 17

    Expert Comment

    I assumed marcparillo wanted the CSV file to look, once opened in Excel, like what I'm seeing when I open the xml file directly in Excel, so that's what my script does.
    LVL 3

    Author Comment

    It worked!

    mjcoyne -- I didn't realize it was that easy (now I feel stupid).  The "hidden" variable is what was screwing it up... and I'll keep your Perl script just in case I need to do it without Excel.

    Thanks again!!

    LVL 17

    Expert Comment

    No need to feel stupid, marcparillo.  That had me stumped for a bit, too -- until I happened to notice the missing second row...:)

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
    Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    755 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

    16 Experts available now in Live!

    Get 1:1 Help Now