jpegvarn
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</Col umn>
<Column name="FIRSTNAME">Joe</Colu mn>
<Column name="OTHER">Other Data</Column>
</Row>
</ResultSetData>
</ResultSet>
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</Col
<Column name="FIRSTNAME">Joe</Colu
<Column name="OTHER">Other Data</Column>
</Row>
</ResultSetData>
</ResultSet>
<?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>
</xsl:text >
<xsl:apply-templates select="Row"/>
</xsl:template>
<xsl:template match="Row">
<xsl:apply-templates select="Column"/>
<xsl:text>
</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
<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>
</xsl:text
<xsl:apply-templates select="Row"/>
</xsl:template>
<xsl:template match="Row">
<xsl:apply-templates select="Column"/>
<xsl:text>
</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
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('ResultSe t.xml')"/>
<xsl:template match="/">
<saxon:output href="result.txt" method="text">
<xsl:for-each select="$input/ResultSet/R esultSetDa ta/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>
</xsl:text >
</xsl:for-each>
</saxon:output>
</xsl:template>
</xsl:stylesheet>
<?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
<xsl:variable name="input" select="document('ResultSe
<xsl:template match="/">
<saxon:output href="result.txt" method="text">
<xsl:for-each select="$input/ResultSet/R
<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>
</xsl:text
</xsl:for-each>
</saxon:output>
</xsl:template>
</xsl:stylesheet>
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</Col umn>
<Column name="FIRSTNAME">Joe</Colu mn>
<Column name="OTHER">Other Data</Column>
</Row>
<Row>
<Column name="LASTNAME">Smith</Col umn>
<Column name="FIRSTNAME">John</Col umn>
<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
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</Col
<Column name="FIRSTNAME">Joe</Colu
<Column name="OTHER">Other Data</Column>
</Row>
<Row>
<Column name="LASTNAME">Smith</Col
<Column name="FIRSTNAME">John</Col
<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
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
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
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/filen ame.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
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/filen
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.
ASKER
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.
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>	</xsl:text>
and the file name from filename.csv to filename.xls
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("appl ication/un known")
Response.addHeader("Conten t-Disposit ion", "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
Response.ContentType("appl
Response.addHeader("Conten
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?
ASKER
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?
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
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
ASKER
wadalhag,
could you give me some direction as far as settting up the java version? I got the windows version to work fine. thanks.
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.
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.
ASKER
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</Col umn>
<Column name="FIRSTNAME">Joe</Colu mn>
<Column name="OTHER">Other Data</Column>
</Row>
<Row>
<Column name="LASTNAME">Smith</Col umn>
<Column name="FIRSTNAME">John</Col umn>
<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
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</Col
<Column name="FIRSTNAME">Joe</Colu
<Column name="OTHER">Other Data</Column>
</Row>
<Row>
<Column name="LASTNAME">Smith</Col
<Column name="FIRSTNAME">John</Col
<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
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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</Col umn>
<Column name="FIRSTNAME">Joe</Colu mn>
<Column name="OTHER">Other Data</Column>
</Row>
<Row>
<Column name="LASTNAME">Smith</Col umn>
<Column name="FIRSTNAME">John</Col umn>
<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</Co lumn>
<Column name="OTHER">Other Data</Column>
</Row>
<Row>
<Column name="ID">567567</Column>
<Column name="PURCHASEID">77777</C olumn>
<Column name="OTHER">Other Data2</Column>
</Row>
</ResultSetData>
</ResultSet>
Etc., etc.
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</Col
<Column name="FIRSTNAME">Joe</Colu
<Column name="OTHER">Other Data</Column>
</Row>
<Row>
<Column name="LASTNAME">Smith</Col
<Column name="FIRSTNAME">John</Col
<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</Co
<Column name="OTHER">Other Data</Column>
</Row>
<Row>
<Column name="ID">567567</Column>
<Column name="PURCHASEID">77777</C
<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
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
ASKER
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
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"
i am sure will see more and more winners evrey day using saxon "It's just matter of time"
https://www.experts-exchange.com/questions/20353183/Convert-XML-to-CSV.html