Jump to content

XML to Excel


gerop
 Share

Recommended Posts

Hello,I'm still very new to XML and XSLT.I'm trying to load a XML file into Excel but am not getting the results I want. Sometimes there are ClientTransactions where there are more than one set of Query and Response. When I open this in Excel, for each line of data, the first two columns span 2 or more rows. I want to put each ClientTransaction into one row of data only - even when there are more then one Query/Response. 'AmpHash013Semicolon' is actually "&_#_013_;" (minus the _) but I couldn't put that in as it doesn't show. Is it some sort of character for line break maybe? I've tried removing them from the XML but it doesn't help. Please give me some pointers on how to solve this.Thanks.

<?xml version="1.0" encoding="UTF-8"?><?xml-stylesheet type="text/xsl" href="format_dcs.xsl"?><siplog><ClientTransaction Type="Execute">  <Timestamp>20070903-050031.751</Timestamp>      <Query>FECK |TSMH2596Y03SEPKULKCH |TK2329901016635C1@ABCD/GUIDOMR |TK2329902016636C1@ABCD/JENNIFERMRS</Query>      <Response> 2329901016635 ABCD/GUIDOMR                 TICKET NOT FOUND'AmpHash013Semicolon'</Response></ClientTransaction><ClientTransaction Type="Execute">  <Timestamp>20070903-050042.927</Timestamp>      <Query>FECK |TSMH0612L03SEPSINKUL |TK6292100263333C2@MIN/ABCGMR</Query>      <Response> 6292100263333 MIN/ABCGMR                   TICKET NOT FOUND'AmpHash013Semicolon'</Response></ClientTransaction><ClientTransaction Type="Execute">  <Timestamp>20070903-050048.156</Timestamp>      <Query>FECK |TSMH2565T03SEPMYYKUL |TK2322403430247C1@KIKI/EDMUNDMR</Query>      <Response> ALL PROCESSED'AmpHash013Semicolon'</Response></ClientTransaction><ClientTransaction Type="Execute">  <Timestamp>20070903-050114.935</Timestamp>      <Query>FET/ 2322402394800</Query>      <Response> ELECTRONIC TICKET FILE'AmpHash013Semicolon' 2322402394800       NAME-LAI/SIKITENGMS'AmpHash013Semicolon' PNR-JX7338'AmpHash013Semicolon' ISSUED 20AUG07/MHWWWIB'AmpHash013Semicolon'                                                       STATUS'AmpHash013Semicolon' 01 KULBKI MH 2616 S 03SEP 1400 OK SAPOWMY         20K CHECK-IN'AmpHash013Semicolon'</Response>      <Query>FETB |NSO |CN1 |TRSDCS DELETE</Query>      <Response> ELECTRONIC TICKET FILE'AmpHash013Semicolon' 2322402394800       NAME-LAI/SIKITENGMS'AmpHash013Semicolon' PNR-JX7338'AmpHash013Semicolon' ISSUED 20AUG07/MHWWWIB'AmpHash013Semicolon'                                                       STATUS'AmpHash013Semicolon' 01 KULBKI MH 2616 S 03SEP 1400 OK SAPOWMY         20K OPEN'AmpHash013Semicolon'</Response></ClientTransaction><ClientTransaction Type="Execute">  <Timestamp>20070903-050125.888</Timestamp>      <Query>FECK |TSMH2709Y03SEPSDKBKI |TK2322409398195C2@DAIM/LLLEGI</Query>      <Response> ALL PROCESSED'AmpHash013Semicolon'</Response></ClientTransaction>

This is xslt code I'm using.

<?xml version="1.0" encoding="UTF-8"?><xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"><!-- Output as an HTML file. --><xsl:output method="html"/><!-- For SIP DCS Transactions --><xsl:template match="/">  <html>  <body>    <h3 align="center">SIP DCS TRANSACTIONS</h3>    <br />    <p> <b> Number of Transactions : </b>            <xsl:value-of select="count(siplog/ClientTransaction)" />    <br />        <b> Number of Errors : </b>	    <xsl:value-of select="count(siplog/ClientTransaction/ExceptionRaised)" />    </p>    <table border="1" frame="box">    <colgroup>    <col width="0*" />    <col width="0*" />    <col width="300"/>    <col width="400"/>        <tr bgcolor="#A63DDF">      <th align="center">Timestamp(UTC)</th>      <th align="center">Exception</th>      <th align="center">Query</th>      <th align="center">Response</th>    </tr>    <xsl:for-each select="siplog/ClientTransaction">    <tr>      <td align="left" valign="top"><xsl:value-of select="Timestamp"/></td>      <td align="left" valign="top"><xsl:value-of select="ExceptionRaised/@RC"/></td>      <td align="left" valign="top">	<ul>	<xsl:for-each select="Query">                     	<li><xsl:value-of select="current()" /></li>        </xsl:for-each>	</ul>      </td>      <td align="left" valign="top">	<ul>        <xsl:for-each select="Response">	<li><xsl:value-of select="current()" /></li>                     </xsl:for-each>	</ul>	      </td>        </tr>    </xsl:for-each>    </colgroup>    </table>  </body>  </html></xsl:template></xsl:stylesheet>

Link to comment
Share on other sites

Errr... you do know that your stylesheet is supposed to generate XHTML output, right? Not really a thing for Excel (though I suppose it's exportable).If it's not going to mess up the query itself, you can perform whitespace normalization to remove the entity and all extra whitespace, like so:

<li><xsl:value-of select="normalize-space(.)" /></li>

That should deal with queries and responses that use more then a single line. As for when you have more then a single set of query and response... It may be an Excel problem. The only thing you may do is write the queries and responses in plain text, rather then on list items and hope for the best.BTW, </colgroup> shuold come right after <col width="400"/>.

Link to comment
Share on other sites

Errr... you do know that your stylesheet is supposed to generate XHTML output, right? Not really a thing for Excel (though I suppose it's exportable).If it's not going to mess up the query itself, you can perform whitespace normalization to remove the entity and all extra whitespace, like so:
<li><xsl:value-of select="normalize-space(.)" /></li>

That should deal with queries and responses that use more then a single line. As for when you have more then a single set of query and response... It may be an Excel problem. The only thing you may do is write the queries and responses in plain text, rather then on list items and hope for the best.BTW, </colgroup> shuold come right after <col width="400"/>.

ya :) Not sure how else to get the xml file into excel form thoughOk thanks ! I'll try it out
Link to comment
Share on other sites

Errr... you do know that your stylesheet is supposed to generate XHTML output, right? Not really a thing for Excel
ya :) Not sure how else to get the xml file into excel form though
boen_robot's point was that your particular xsl is written to generate html output, which is more suitable for a web page than Excel, as it includes presentation and layout information.Excel will in fact open your original xml file (once the closing </siplog> tag is added). So the task for your xsl is to transform that xml not into html, but into a different xml (or csv) structure that suits your needs when opening it in Excel (namely, handling the query/response pairs).Your data contains one or more query/response pairs per transaction. You would like each transaction on a single row in Excel. You don't say what you want to happen to the multiple queries and responses - possible options include (1) concatenating all query/response pairs in one cell, (2) showing only the first query/response pair or (3) ignoring all query/response pairs. (However, depending on how you will use the data in Excel, it might be more meaningful to keep a row for each query/response pair but repeat the transaction id in the first cell.) All of these (and more) could be done with the right xsl transformation.The simplest transformation is (3) above and I have included the xsl for this below so you can use it as a starting point:
<?xml version="1.0" encoding="UTF-8"?><xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">  <xsl:output method="xml" />  <xsl:template match="/">	<ClientTransactions>	  <xsl:for-each select="siplog/ClientTransaction">		<ClientTransaction>		  <Type>			<xsl:value-of select="@Type"/>		  </Type>		  <Timestamp>			<xsl:value-of select="Timestamp"/>		  </Timestamp>		</ClientTransaction>	  </xsl:for-each>	</ClientTransactions>  </xsl:template></xsl:stylesheet>

This produces the following xml output, which Excel will open as one transaction per row:

<?xml version="1.0" encoding="utf-8"?><ClientTransactions>  <ClientTransaction>	<Type>Execute</Type>	<Timestamp>20070903-050031.751</Timestamp>  </ClientTransaction>  <ClientTransaction>	<Type>Execute</Type>	<Timestamp>20070903-050042.927</Timestamp>  </ClientTransaction>  <ClientTransaction>	<Type>Execute</Type>	<Timestamp>20070903-050048.156</Timestamp>  </ClientTransaction>  <ClientTransaction>	<Type>Execute</Type>	<Timestamp>20070903-050114.935</Timestamp>  </ClientTransaction>  <ClientTransaction>	<Type>Execute</Type>	<Timestamp>20070903-050125.888</Timestamp>  </ClientTransaction></ClientTransactions>

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...