Link to home
Start Free TrialLog in
Avatar of jpegvarn
jpegvarnFlag for United States of America

asked on

Convert XML to CVS

Hello,

I've seen some other posts concerning this, but would like someone to look at the below XML output I am getting and explain in detail how I can get this data into a CSV file (I'm assuming the simpliest way is using XSL).  Thank you.

<?xml version="1.0" ?>
<ResultSet>
 <ResultSetData>
  <Row>
   <Column name="LASTNAME">Shmoe</Column>
   <Column name="FIRSTNAME">Joe</Column>
   <Column name="OTHER">Other Data</Column>
  </Row>
 </ResultSetData>
</ResultSet>
Avatar of YZlat
YZlat
Flag of United States of America image

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
      <xsl:output method="text"/>
      <xsl:template match="ResultSetData">
            <!-- Produce the heading -->
            <xsl:apply-templates select="Row[1]/Column" mode="mHeading"/>
            <xsl:text>&#013;</xsl:text>
            <xsl:apply-templates select="Row"/>
      </xsl:template>
      <xsl:template match="Row">
            <xsl:apply-templates select="Column"/>
            <xsl:text>&#013;</xsl:text>
      </xsl:template>
      <xsl:template match="Column" mode="mHeading">
            <xsl:value-of select="@name"/>
            <xsl:if test="position() != last()">
                  <xsl:text>,</xsl:text>
            </xsl:if>
      </xsl:template>
      <xsl:template match="Column">
            <xsl:value-of select="."/>
            <xsl:if test="position() != last()">
                  <xsl:text>,</xsl:text>
            </xsl:if>
      </xsl:template>

</xsl:stylesheet>


However, if your data will contain commas, apostrophes or quotes, you'll want to call a template for each text node to escape quotes, and put the text value in quotes...

Regards,
Mike Sharp
Avatar of wadalhag
wadalhag

This a solution using Saxon processor which will enable you to output your result as a stand alone files anywhere on your system.

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:saxon="http://icl.com/saxon" extension-element-prefixes="saxon" version="1.0">
<xsl:variable name="input" select="document('ResultSet.xml')"/>
<xsl:template match="/">
      <saxon:output href="result.txt" method="text">
      <xsl:for-each select="$input/ResultSet/ResultSetData/Row">
            <xsl:for-each select="Column" >
              <xsl:value-of select="."/>
     <xsl:if test="not(position() = last())">
  <xsl:text>,</xsl:text>
  </xsl:if>
</xsl:for-each>
<xsl:text>&#013;</xsl:text>
</xsl:for-each>      
      </saxon:output>
      </xsl:template>
</xsl:stylesheet>


Avatar of jpegvarn

ASKER

rdcpro & wadalhag,

rdcpro: The results I am getting output to an html page, not to a csv file.  They look like this (see example below)

Shmoe, Joe, Other Data  Smith, John, Other Data2

wadalhag: I am getting a "Element 'saxon:output' is not a recognized extension element" error.

Another note, I can't define which column names are going to be in the output because it will vary depeding upon which options are checked off.

If this is the XML data:

<?xml version="1.0" ?>
<ResultSet>
 <ResultSetData>
  <Row>
   <Column name="LASTNAME">Shmoe</Column>
   <Column name="FIRSTNAME">Joe</Column>
   <Column name="OTHER">Other Data</Column>
  </Row>
  <Row>
   <Column name="LASTNAME">Smith</Column>
   <Column name="FIRSTNAME">John</Column>
   <Column name="OTHER">Other Data2</Column>
  </Row>
</ResultSetData>
</ResultSet>

I want the ability to save or open the file from the browser and the output would look like this...

                    Column1        Column2         Column3

Row1            LASTNAME     FIRSTNAME    OTHER
Row2            Shmoe           Joe                Other Data
Row3            Smith             John              Other Data2
An HTML page?  of couse the results look like that--HTML pages do not care about newlines.  If you want the output to look a certain way (with line breaks) you must use markup for that.   You asked "explain in detail how I can get this data into a CSV file"  and the above does just that. But you can't open a CVS file in a browser and have it look like:

                    Column1        Column2         Column3

Row1            LASTNAME     FIRSTNAME    OTHER
Row2            Shmoe           Joe                Other Data
Row3            Smith             John              Other Data2


that requires layout and presentation.  CVS has none of that--it's only a format for transporting data.  You want HTML.

Regards,
Mike Sharp

using the browser prosseor will not create any other files, only Saxon processor can create stand alone files.

1- download  it from  http://prdownloads.sourceforge.net/saxon/instant_saxon6_5_3.zip

2- save my style sheet as change.xsl and your xml as ResultSet.xm

3- then place both XML and XSL fiiles at the same directory "e.g c:\chang"
then type

4-c:\change\saxon ResultSet.xml change.xsl

don't forget to change Result.txt in my style sheet to Result.csv

GOOD Luck
Corrections

2- save my style sheet as change.xsl and your xml as ResultSet.xml
wadahag says:  "using the browser prosseor will not create any other files, only Saxon processor can create stand alone files."

That's true to an extent--browser security settings won't allow you to create files on your local machine except through File >> Save.  However, that's not a parser limitation.  In Javascript, running from an HTA (html application, or mypage.hta) I can easily save one or many files to the filesystem.  If it's an HTML page loaded from the local filesystem, I can do it as well.

xmlDoc.save("path/to/filename.csv")

or I can save the result of a transform.

Saxon does have an extension which allows a single XSLT transform to output multiple files, but this is also possible programmatically using MSXML or .NET or any other platform/processor.  Oleg Tkachencko described an extension for the .NET parser in MSDN Magazine that does the same thing.

Regards,
Mike Sharp
You are right rdcpro  but my problem that I don’t trust or deal with Microsoft stuff. Things like XSLT2 or XPath2 can only be implemented with Saxon.  
Hi guys, thank you for the replies.  

Let me re-phrase how I need this to work.

Once a user submits a request on a form, the data will be coming back in XML format.  When they hit the submit button, I want it to appear as though they are clicking on a link to open a file, therefore...giving them the OPEN, SAVEAS...option.  When saved, the filename will be today's date with time...and the format will be an EXCEL CSV file...so there will be column and row formatted as I labled above (I just found out we need this in EXCEL rather than true CSV format with commas and quotations).

Again, thank you for your responses.
agin the same steps + change  <xsl:text>,</xsl:text>  to  <xsl:text>&#x9;</xsl:text>
and the file  name  from filename.csv to filename.xls

To get the OPEN, SAVE AS... dialog, set the content-type on the server to something the browser doesn't understand.  You can use (pseudocode, since you haven't told us what platform):

Response.ContentType("application/unknown")
Response.addHeader("Content-Disposition", "attachment; filename=" + myFileName )

I think you can also use application/octet-stream as well.  You also might have to specify the content-length.

Regards,
Mike Sharp
jpegvarn, did you look at the links I provided?
Okay, let me start over:

I am putting this on a linux/unix server that is being hosted elsewhere, so the windows saxon program will not do (I see there is no other format).

Here is EXACTLY what I need/want, but this is using ASP...

http://aspalliance.com/articleviewer.aspx?aId=471 (please go to ARTICLE CONTENTS, then drop-down to CONCLUSION AND SAMPLE, then click on RUN SAMPLE)

Can someone help me here?

This is not correct as I use Saxon within LINUX as my main conversion tool
Instant Saxon work for windows only but there is a java version which can be used in any platform under java environment.
Set up the saxon.jar to do the job for you
wadalhag,

could you give me some direction as far as settting up the java version?  I got the windows version to work fine.  thanks.
You will need a file called saxon.jar which contain the whole functionality of saxon.
http://prdownloads.sourceforge.net/saxon/saxon6_5_3.zip?use_mirror=ovh

To run this you need to make sure either this file in the same directory as the other two files
java –jar saxon.jar ResultSet.xml change.xsl

if you wish to use saxon from anywhere   you can either point to it or set it up as within the environment variables.
 
wadalhag,

This works great and the output is fine, except I was wondering if you had an answer to this:

When it generates to the excel file, I need the column heading.

NOW using the following:

<?xml version="1.0" ?>
<ResultSet>
 <ResultSetData>
  <Row>
   <Column name="LASTNAME">Shmoe</Column>
   <Column name="FIRSTNAME">Joe</Column>
   <Column name="OTHER">Other Data</Column>
  </Row>
  <Row>
   <Column name="LASTNAME">Smith</Column>
   <Column name="FIRSTNAME">John</Column>
   <Column name="OTHER">Other Data2</Column>
  </Row>
</ResultSetData>
</ResultSet>

NOW XLS:

                    Column1        Column2         Column3
Row1            Shmoe           Joe                Other Data
Row2            Smith             John              Other Data2

WHAT I'D LIKE:

                    Column1        Column2         Column3
Row1            LASTNAME     FIRSTNAME    OTHER
Row2            Shmoe           Joe                Other Data
Row3            Smith             John              Other Data2
And is there any way to have the file name of the xls be today's date, with the second (just incase someone needs to do it more than once in the same day...the file will not be overwritten).  Thanks.
ASKER CERTIFIED SOLUTION
Avatar of wadalhag
wadalhag

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sorry,

I forgot to mention...the xml columns heading names can change depending on what the user wants to query (the xsl column names can't be hard-coded)....so the XML file can at one time look like this:

1.

<?xml version="1.0" ?>
<ResultSet>
 <ResultSetData>
  <Row>
   <Column name="LASTNAME">Shmoe</Column>
   <Column name="FIRSTNAME">Joe</Column>
   <Column name="OTHER">Other Data</Column>
  </Row>
  <Row>
   <Column name="LASTNAME">Smith</Column>
   <Column name="FIRSTNAME">John</Column>
   <Column name="OTHER">Other Data2</Column>
  </Row>
</ResultSetData>
</ResultSet>


Or, it can look like this:

2.

<?xml version="1.0" ?>
<ResultSet>
 <ResultSetData>
  <Row>
   <Column name="ID">234234</Column>
   <Column name="PURCHASEID">5555</Column>
   <Column name="OTHER">Other Data</Column>
  </Row>
  <Row>
   <Column name="ID">567567</Column>
   <Column name="PURCHASEID">77777</Column>
   <Column name="OTHER">Other Data2</Column>
  </Row>
</ResultSetData>
</ResultSet>


Etc., etc.
I am sorry. I will not answer any more questions for this number points.
I already answered more than two topics.
Points are awarded for a question and not for a whole project.
If you need to ask any more questions please submit them individually    
Sorry about that.  You are absolutley right.  I'm opening a new question if you are interested, thank you.
thanks jpegvarn
Actually, I had already answered that part in this very question:

https://www.experts-exchange.com/questions/21098399/Convert-XML-to-CVS.html#11834944

Regards,
Mike Sharp
I take this as another win for Saxon
i am sure will see more and more winners evrey day using saxon "It's just matter of time"