Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Convert XML to CVS

Posted on 2004-08-18
27
Medium Priority
?
6,213 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
ID: 11833857
0
 
LVL 35

Expert Comment

by:YZlat
ID: 11834060
here is an example using xslt:

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

Expert Comment

by:rdcpro
ID: 11834944
<?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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 3

Expert Comment

by:wadalhag
ID: 11840980
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
ID: 11842151
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
ID: 11842297
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
ID: 11842300
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
ID: 11842324
Corrections

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

Expert Comment

by:rdcpro
ID: 11842432
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
ID: 11842507
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
ID: 11842587
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
ID: 11842809
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
ID: 11844131
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
 
LVL 35

Expert Comment

by:YZlat
ID: 11853199
jpegvarn, did you look at the links I provided?
0
 

Author Comment

by:jpegvarn
ID: 11934751
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
ID: 11935233
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
ID: 12018740
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
ID: 12024405
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
ID: 12025957
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
ID: 12026294
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 2000 total points
ID: 12026988
<?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
ID: 12027062
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
ID: 12027111
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
ID: 12027152
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
ID: 12027153
thanks jpegvarn
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 12028524
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
ID: 12031416
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Shoutout to Emily Plummer (http://www.experts-exchange.com/members/eplummer26.html) for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
Create a Windows 10 custom Image with custom task bar and custom start menu using XML for deployment.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.
Suggested Courses

810 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