Solved

Convert XML to CVS

Posted on 2004-08-18
27
6,105 Views
Last Modified: 2013-11-19
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>
0
Comment
Question by:jpegvarn
  • 11
  • 8
  • 5
  • +1
27 Comments
 
LVL 35

Expert Comment

by:YZlat
Comment Utility
0
 
LVL 35

Expert Comment

by:YZlat
Comment Utility
here is an example using xslt:

http://www.scit.wlv.ac.uk/~jphb/xml/xmlcsv.html
0
 
LVL 26

Expert Comment

by:rdcpro
Comment Utility
<?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
0
 
LVL 3

Expert Comment

by:wadalhag
Comment Utility
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>


0
 

Author Comment

by:jpegvarn
Comment Utility
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
0
 
LVL 26

Expert Comment

by:rdcpro
Comment Utility
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

0
 
LVL 3

Expert Comment

by:wadalhag
Comment Utility
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
0
 
LVL 3

Expert Comment

by:wadalhag
Comment Utility
Corrections

2- save my style sheet as change.xsl and your xml as ResultSet.xml
0
 
LVL 26

Expert Comment

by:rdcpro
Comment Utility
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
0
 
LVL 3

Expert Comment

by:wadalhag
Comment Utility
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.  
0
 

Author Comment

by:jpegvarn
Comment Utility
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.
0
 
LVL 3

Expert Comment

by:wadalhag
Comment Utility
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

0
 
LVL 26

Expert Comment

by:rdcpro
Comment Utility
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
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 35

Expert Comment

by:YZlat
Comment Utility
jpegvarn, did you look at the links I provided?
0
 

Author Comment

by:jpegvarn
Comment Utility
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?

0
 
LVL 3

Expert Comment

by:wadalhag
Comment Utility
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
0
 

Author Comment

by:jpegvarn
Comment Utility
wadalhag,

could you give me some direction as far as settting up the java version?  I got the windows version to work fine.  thanks.
0
 
LVL 3

Expert Comment

by:wadalhag
Comment Utility
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.
 
0
 

Author Comment

by:jpegvarn
Comment Utility
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
0
 

Author Comment

by:jpegvarn
Comment Utility
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.
0
 
LVL 3

Accepted Solution

by:
wadalhag earned 500 total points
Comment Utility
<?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.xls" method="text">
<xsl:text>LASTNAME</xsl:text>
<xsl:text>&#x9;</xsl:text>
<xsl:text>FIRSTNAME</xsl:text>
<xsl:text>&#x9;</xsl:text>
<xsl:text>OTHER</xsl:text>
<xsl:text>&#013;</xsl: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>&#x9;</xsl:text>
  </xsl:if>
</xsl:for-each>
<xsl:text>&#013;</xsl:text>
</xsl:for-each>
       
      </saxon:output>
      
      </xsl:template>
</xsl:stylesheet>
0
 

Author Comment

by:jpegvarn
Comment Utility
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.
0
 
LVL 3

Expert Comment

by:wadalhag
Comment Utility
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    
0
 

Author Comment

by:jpegvarn
Comment Utility
Sorry about that.  You are absolutley right.  I'm opening a new question if you are interested, thank you.
0
 
LVL 3

Expert Comment

by:wadalhag
Comment Utility
thanks jpegvarn
0
 
LVL 26

Expert Comment

by:rdcpro
Comment Utility
Actually, I had already answered that part in this very question:

http://www.experts-exchange.com/Web/Web_Languages/XML/Q_21098399.html#11834944

Regards,
Mike Sharp
0
 
LVL 3

Expert Comment

by:wadalhag
Comment Utility
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"
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

771 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

12 Experts available now in Live!

Get 1:1 Help Now