SitePoint Sponsor

User Tag List

Results 1 to 23 of 23
  1. #1
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Transform XML in txt file with XSL file

    Hi everyone, I need your help.

    This is the xml output:

    Code:
    <xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" 
     xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" 
     xmlns:rs="urn:schemas-microsoft-com:rowset" 
     xmlns:z="#RowsetSchema">
    	<s:Schema id="RowsetSchema">
    		<s:ElementType name="row" 
     content="eltOnly" rs:CommandTimeout="30">
    			<s:AttributeType name="ID" 
     rs:number="1" rs:nullable="true" rs:writeunknown="true">
    				<s:datatype dt:type="int" 
     dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/>
    			</s:AttributeType>
    			<s:AttributeType name="CODICE" 
     rs:number="2" rs:nullable="true" rs:writeunknown="true">
    				<s:datatype 
     dt:type="string" rs:dbtype="str" dt:maxLength="255"/>
    			</s:AttributeType>
    
    			<s:extends type="rs:rowbase"/>
    		</s:ElementType>
    	</s:Schema>
    		
    <rs:data>
                    <z:row ID="1" CODE="23982" />
    		<z:row ID="2" CODE="23977" />
    		<z:row ID="3" CODE="23976" />
    	</rs:data>
    </xml>
    I need save transform this xml file in the txt file with xsl file:
    Code:
    <?xml version="1.0"?>
    <xsl:stylesheet version="1.0" 
     xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
    xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
    xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
    xmlns:rs="urn:schemas-microsoft-com:rowset" 
     xmlns:z="#RowsetSchema">
    <xsl:output omit-xml-declaration="yes"/>
    <xsl:template match="/">
            <xsl:for-each select="/xml/rs:data/z:row">
                    <xsl:for-each select="@*">
                            <xsl:value-of select="."/>      
                            <xsl:text>, </xsl:text>
                    </xsl:for-each>
            </xsl:for-each>
    </xsl:template>
    </xsl:stylesheet>
    But I have this output incorrect: 1, 23982, 2, 23977, 3, 23976

    The output correct for me this is:

    1, 23982
    2, 23977
    3, 23976
    Can you help me?

    Thanks in advance.
    Viki

  2. #2
    SitePoint Addict Phidev's Avatar
    Join Date
    Oct 2008
    Location
    Texas
    Posts
    204
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Where is your code?

  3. #3
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Phidev View Post
    Where is your code?
    PHP Code:
    <% 

    '----------
      Dim xmlSource
      Dim xmlXForm
      Dim strResult
       
      Dim fso , file
      Dim strPath
      Const ForReading = 1
      Const ForWriting = 2
      Const ForAppending = 8
     
      Set xmlSource = CreateObject("MSXML2.DOMDocument.4.0")
      Set xmlXForm = CreateObject("MSXML2.DOMDocument.4.0")
     
      xmlSource.validateOnParse = True
      xmlXForm.validateOnParse = True
      xmlSource.async = False
      xmlXForm.async = False
     
      xmlSource.Load Server.Mappath("_output.xml")
       
      xmlXForm.Load Server.Mappath("_Trasform.xsl")
         
      strResult = xmlSource.transformNode(xmlXForm)
       
      Set fso = CreateObject("Scripting.FileSystemObject")
      strPath = Server.Mappath("_output.txt")
      Set file = fso.opentextfile(strPath, ForWriting, True)
      file.write strResult
      file.Close 
      Set file = Nothing
      
      Set fso = Nothing
      
      Set xmlSource = Nothing
      Set xmlXForm = Nothing
    '
    ----------

    %> 

  4. #4
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try this xsl

    Code:
    <?xml version="1.0"?>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
    	<xsl:output omit-xml-declaration="yes"/>
    	<xsl:template match="/">
    		<xsl:for-each select="/xml/rs:data/z:row">
    			<xsl:for-each select="@*">
    				<xsl:value-of select="."/>
    				<xsl:choose>
    					<xsl:when test="position()!=last()">
    						<xsl:text>,</xsl:text>
    					</xsl:when>
    					<xsl:otherwise>
    						<xsl:text>&#xa;</xsl:text>
    					</xsl:otherwise>
    				</xsl:choose>
    			</xsl:for-each>
    		</xsl:for-each>
    	</xsl:template>
    </xsl:stylesheet>

  5. #5
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your help is greatly appreciated and your xsl working !!!

    Now the output is:
    Code:
    1, 23982
    2, 23977
    3, 23976
    If I have need this output? (header columns...):
    Code:
    id,	code
    1, 	23982
    2, 	23977
    3, 	23976

  6. #6
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's another sample that looks at the first data row to get the column names. I'm not sure if this is where you expect the names to come from?
    hope it helps.

    Code:
    <?xml version="1.0"?>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
    	<xsl:output omit-xml-declaration="yes"/>
    	<xsl:template match="/">
    		<!-- show a header by looking attributes of the first data row-->
    		<xsl:for-each select="/xml/rs:data/z:row[1]/@*">
    			<xsl:value-of select="name()"/>
    			<xsl:choose>
    				<xsl:when test="position()!=last()">
    					<xsl:text>,</xsl:text>
    				</xsl:when>
    				<xsl:otherwise>
    					<xsl:text>&#xa;</xsl:text>
    				</xsl:otherwise>
    			</xsl:choose>
    		</xsl:for-each>
    		<!-- row data -->
    		<xsl:for-each select="/xml/rs:data/z:row">
    			<xsl:for-each select="@*">
    				<xsl:value-of select="."/>
    				<xsl:choose>
    					<xsl:when test="position()!=last()">
    						<xsl:text>,</xsl:text>
    					</xsl:when>
    					<xsl:otherwise>
    						<xsl:text>&#xa;</xsl:text>
    					</xsl:otherwise>
    				</xsl:choose>
    			</xsl:for-each>
    		</xsl:for-each>
    	</xsl:template>
    </xsl:stylesheet>

  7. #7
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Many thanks for your decisive help!
    I need now my output:

    Code:
    id,	code
    1, 	23982
    2, 	23977
    3, 	23976

  8. #8
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, I have new problem with this file xml

    If I have column value NULL in the mysql table the export is wrong.

    Mysql Table (correct):

    Code:
    ID	DATA_INS	TG	GRUP	NAME
    3626	29/11/2010	I	63	MED
    3630	29/11/2010	P	70	MARIO                
    22505	15/02/2011		6	COND
    3630	29/11/2010	P	70	MARIO
    Export to xml (wrong):
    Code:
    ID	DATA_INS	TG	GRUP	NAME
    3626	29/11/2010	I	63	MED
    3630	29/11/2010	P	70	MARIO                
    22505	15/02/2011	6	COND
    3630	29/11/2010	P	70	MARIO
    The value of next column (GRUP value 6) to move in the previous column when values is NULL (TG)...

    Can you help me?
    Thanks!

  9. #9
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what does the xml file look like? (the source xml before the xsl is run).

  10. #10
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jurn View Post
    what does the xml file look like? (the source xml before the xsl is run).
    Thanks x your answer.

    This is the asp code generate xml file:

    PHP Code:
    <!-- #include virtual="/_inc/_open_conn_mysql.asp" -->

    <% 

     
    Session.Timeout 200 
     Response
    .Buffer True 
      
     filename 
    "_output.xml"
     
     
    Set fso Server.CreateObject("Scripting.FileSystemObject"
        if 
    fso.FileExists(Server.MapPath("_XML/" filename "")) then 
        fso
    .DeleteFile(Server.MapPath("_XML/" filename "")) 
     
    end if 

     
    Set fso Nothing 

     Set xmlDoc 
    CreateObject("MSXML2.DOMDocument.4.0")
     
    Set xslDoc CreateObject("MSXML2.DOMDocument.4.0")

    %>

    <!-- 
    #include virtual="/_inc/adovbs.inc" -->

    <%


       
    sql "SELECT * FROM tbl_c" 
                
       
    Set Rs CreateObject("ADODB.Recordset")
       
    Rs.Open SQLobjconn
          
       
    if not Rs.eof then 
       
       Rs
    .Save xmlDoc
       xmlDoc
    .Save Server.MapPath("_XML/" filename "")
       
       
    end if

       
    Rs.Close() 
       
    Set Rs Nothing 

    %>

    <%

     
    Set oDOM CreateObject("MSXML2.DOMDocument.4.0")
     
    oDOM.async False 
     
     oDOM
    .Load Server.Mappath("_XML/" filename "")
      
     
    Set oXSL CreateObject("MSXML2.DOMDocument.4.0"
     
    oXSL.async False 
     
     oXSL
    .Load Server.Mappath("/_inc/Trasform_csv.xsl"
     
     
    strTransform oDOM.transformNode(oXSL
     
     
    Set fso CreateObject("Scripting.FileSystemObject")
      
     
    strPath Server.Mappath("_XML/_output.csv"
     
    Session("FileName") = Server.Mappath("_XML/_output.csv"
       
     
    Set file fso.opentextfile(strPath2True)
     
    file.write strTransform

     file
    .Close() 
     
    Set file Nothing
      
     Set fso 
    Nothing
                            
     Set oDOM 
    Nothing 
     Set oXML 
    Nothing 
     Set oXSL 
    Nothing 
       
    %>

    <!-- 
    #include virtual="/_inc/_close_conn_mysql.asp" --> 
    And this is the _output.xml:

    PHP Code:
    <xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
        <
    s:Schema id="RowsetSchema">
            <
    s:ElementType name="row" content="eltOnly" rs:CommandTimeout="30">

                <
    s:AttributeType name="ID" rs:number="1" rs:nullable="true" rs:writeunknown="true">
                    <
    s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/>
                </
    s:AttributeType>

                <
    s:AttributeType name="name" rs:number="2" rs:nullable="true" rs:writeunknown="true">
                    <
    s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>
                </
    s:AttributeType>

                <
    s:AttributeType name="email" rs:number="3" rs:nullable="true" rs:writeunknown="true">
                    <
    s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>
                </
    s:AttributeType>

                <
    s:AttributeType name="orig" rs:number="4" rs:nullable="true" rs:writeunknown="true">
                    <
    s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>
                </
    s:AttributeType>

                <
    s:extends type="rs:rowbase"/>
            </
    s:ElementType>
        </
    s:Schema>
        <
    rs:data>
            <
    z:row ID="1" name="walter" email="...@..." ORIG="0"/>
            <
    z:row ID="2" name="john" email="...@..." ORIG="1"/>
            <
    z:row ID="3" name="tony" email="...@..." ORIG="0"/>
        </
    rs:data>
    </
    xml

  11. #11
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm sorry... I confused the _output.xml.

    This is right output:


    PHP Code:
    <xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
        <
    s:Schema id="RowsetSchema">
            <
    s:ElementType name="row" content="eltOnly" rs:CommandTimeout="30">

                <
    s:AttributeType name="ID" rs:number="1" rs:nullable="true" rs:writeunknown="true">
                    <
    s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true"/>
                </
    s:AttributeType>

                <
    s:AttributeType name="DATA_INS" rs:number="2" rs:nullable="true" rs:writeunknown="true">
                    <
    s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>
                </
    s:AttributeType>

                <
    s:AttributeType name="TG" rs:number="3" rs:nullable="true" rs:writeunknown="true">
                    <
    s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>
                </
    s:AttributeType>

                <
    s:AttributeType name="GRUP" rs:number="4" rs:nullable="true" rs:writeunknown="true">
                    <
    s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>
                </
    s:AttributeType>

                <
    s:AttributeType name="NAME" rs:number="5" rs:nullable="true" rs:writeunknown="true">
                    <
    s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="255"/>
                </
    s:AttributeType>

                <
    s:extends type="rs:rowbase"/>
            </
    s:ElementType>
        </
    s:Schema>
        <
    rs:data>
            <
    z:row ID="3626" DATA_INS="29/11/2010" TG="I" GRUP="63" NAME="MED"/>
            <
    z:row ID="3630" DATA_INS="29/11/2010" TG="P" GRUP="70" NAME="MARIO"/>
            <
    z:row ID="3631" DATA_INS="15/02/2011" GRUP="6" NAME="COND"/>
            <
    z:row ID="3632" DATA_INS="29/11/2010" TG="P" GRUP="70" NAME="MARIO"/>
        </
    rs:data>
    </
    xml
    In the row ID = 3631 field TG is missing because in the table mysql is value null... :S

  12. #12
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, I've changed it to take the column names from the "s:Schema" element

    try this :P

    Code:
    <?xml version="1.0"?>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
    	<xsl:output omit-xml-declaration="yes"/>
    	<xsl:template match="/">
    		<!-- show a header by looking Schema element -->
    		<xsl:for-each select="/xml/s:Schema/s:ElementType/s:AttributeType">
    			<xsl:value-of select="@name"/>
    			<xsl:choose>
    				<xsl:when test="position()!=last()">
    					<xsl:text>,</xsl:text>
    				</xsl:when>
    				<xsl:otherwise>
    					<xsl:text>&#xa;</xsl:text>
    				</xsl:otherwise>
    			</xsl:choose>
    		</xsl:for-each>
    		<!-- row data -->
    		<xsl:for-each select="/xml/rs:data/z:row">
          <xsl:variable name="row" select="."/>
          <!-- for each column -->
    			<xsl:for-each select="/xml/s:Schema/s:ElementType/s:AttributeType">
    			   <!-- display each column if it exists -->
    			  <xsl:variable name="columnName" select="@name"/>
    				<!-- go through all columns and display something that matches-->
    				<xsl:value-of select="$row/@*[name()=$columnName]"/>
    				<xsl:if test="position()!=last()">
    					<xsl:text>,</xsl:text>
    				</xsl:if>
    	    </xsl:for-each>
    			<xsl:text>&#xa;</xsl:text>
    	  </xsl:for-each>
    	</xsl:template>
    </xsl:stylesheet>

  13. #13
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    We really appreciate your help- Many thanks !

  14. #14
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi all.

    I have another problem with this code:

    1) In my table of mysql I have this value in the field P: 6.6 (decimal 10,2)
    2) In xml output file I have this value: P = "6.60"
    3) In the output excel (xls) I see in the field value 0,291666666666667

    Can you help me?
    Thanks

    Code:
    <?xml version="1.0"?>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
    	<xsl:output omit-xml-declaration="yes"/>
    	<xsl:template match="/">
    		<!-- show a header by looking Schema element -->
    		<xsl:for-each select="/xml/s:Schema/s:ElementType/s:AttributeType">
    			<xsl:value-of select="@name"/>
    			<xsl:choose>
    				<xsl:when test="position()!=last()">
    					<xsl:text>&#x09;</xsl:text>
    				</xsl:when>
    				<xsl:otherwise>
    					<xsl:text>&#xa;</xsl:text>
    				</xsl:otherwise>
    			</xsl:choose>
    		</xsl:for-each>
    		<!-- row data -->
    		<xsl:for-each select="/xml/rs:data/z:row">
          <xsl:variable name="row" select="."/>
          <!-- for each column -->
    			<xsl:for-each select="/xml/s:Schema/s:ElementType/s:AttributeType">
    			   <!-- display each column if it exists -->
    			  <xsl:variable name="columnName" select="@name"/>
    				<!-- go through all columns and display something that matches-->
    				<xsl:value-of select="$row/@*[name()=$columnName]"/>
    				<xsl:if test="position()!=last()">
    					<xsl:text>&#x09;</xsl:text>
    				</xsl:if>
    	    </xsl:for-each>
    			<xsl:text>&#xa;</xsl:text>
    	  </xsl:for-each>
    	</xsl:template>
    </xsl:stylesheet>

  15. #15
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Output XML:

    Code:
    <xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
    	<s:Schema id="RowsetSchema">
    		<s:ElementType name="row" content="eltOnly" rs:CommandTimeout="30">
    			<s:AttributeType name="xID" rs:number="1" rs:writeunknown="true">
    				<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false"/>
    			</s:AttributeType>
    
    			<s:AttributeType name="DATA_INS" rs:number="2" rs:nullable="true" rs:writeunknown="true">
    				<s:datatype dt:type="date" dt:maxLength="6" rs:fixedlength="true"/>
    			</s:AttributeType>
    
    			<s:AttributeType name="P" rs:number="3" rs:nullable="true" rs:writeunknown="true">
    				<s:datatype dt:type="number" rs:dbtype="numeric" dt:maxLength="19" rs:scale="2" rs:precision="10" rs:fixedlength="true"/>
    			</s:AttributeType>
    		
    			<s:extends type="rs:rowbase"/>
    		</s:ElementType>
    	</s:Schema>
    	<rs:data>
               <z:row xID="20375" DATA_INS="2011-02-15" P="6.60"/>
    	</rs:data>
    </xml>

  16. #16
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi viki,

    I don't understand what excel has to do with this. After transforming with the xsl you have a tab delimited file...
    what did you do with it?

    xID DATA_INS P
    20375 2011-02-15 6.60

    Jurn

  17. #17
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jurn View Post
    hi viki,

    I don't understand what excel has to do with this. After transforming with the xsl you have a tab delimited file...
    what did you do with it?

    xID DATA_INS P
    20375 2011-02-15 6.60

    Jurn
    Hi Jurn, thanks x your reply and welcome back.

    Your XSL code working well when export to csv file (reply #12) delimited values with "," (comma).

    Output csv file:
    PHP Code:
    xIDDATA_INSP
     20375
    2011-02-156.60 
    For open the csv file I use the Microsoft Excel and import csv file with wizard procedure.
    When import values of csv file in excel software the value 6.60 is transformed in 0,291666666666667.

    Output in excel:
    PHP Code:
    xID        DATA_INS    P
    20375    2011
    -02-15    0,291666666666667 
    Now I need with XSL code export through xls file and I change your code in this lines:
    PHP Code:
    <xsl:text>,</xsl:text
    in
    PHP Code:
    <xsl:text>&#x09;</xsl:text> 
    I have output xls but with this problem:
    In the output excel (xls) I see in the field value P 0,291666666666667 instead of 6,6.

    I believe that in excel the value 6.60 is interpreted as time...

    I find one solution but I'm not sure that is the right.

    I change this line:
    PHP Code:
    <xsl:value-of select="$row/@*[name()=$columnName]"/> 
    with:
    PHP Code:
    <xsl:value-of select="translate($row/@*[name()=$columnName,'.',',')]" /> 
    And in excel output I see in field P 6,6, right value.

    You understand me ?

  18. #18
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm I'm not too familiar with excel, but I have 2007 installed it will ask you about the delimiter (tab in your case) and data type of each column (e.g. text / number / date) as you import the text file.
    I imported it from Data -> Get External Data -> From Text and it works ok for me

    Jurn

  19. #19
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jurn View Post
    hmm I'm not too familiar with excel, but I have 2007 installed it will ask you about the delimiter (tab in your case) and data type of each column (e.g. text / number / date) as you import the text file.
    I imported it from Data -> Get External Data -> From Text and it works ok for me

    Jurn
    I'm happy for you, but I continue with problem...
    What You think of the solution?

    PHP Code:
    <xsl:value-of select="translate($row/@*[name()=$columnName,'.',',')]" /> 

  20. #20
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi viki,
    I do not like it, because it is changing the data when it should not need to. It sounds like a workaround.
    I would test data (in any column) that contains the '.' character still works as expected.

    BUT, if it works for you and your users, that's good enough for me!

    Jurn

  21. #21
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jurn View Post
    hi viki,
    I do not like it, because it is changing the data when it should not need to. It sounds like a workaround.
    I would test data (in any column) that contains the '.' character still works as expected.

    BUT, if it works for you and your users, that's good enough for me!

    Jurn
    Hi Jurn, I can send my files?

  22. #22
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    just attach them to this thread, so other people can also help.

  23. #23
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Even I have a similar problem hope I will get some help here.

    I am trying to generate excel report from an ASP application using xsl and excel 2010.

    When I generate the report, all the data is populating in only one cell in the excel file instead of being populated as a report with multiple rows.

    I have attached the .xsl file. can anyone help me correct it?

    <?xml version="1.0"?>
    <xsl:stylesheet xmlnssl="http://www.w3.org/TR/WD-xsl">
    <xsl:template match="/">
    <xsli name="xml">version="1.0"</xsli>
    <Workbook xmlns="urn:schemas-microsoft-comffice:spreadsheet"
    xmlns="urn:schemas-microsoft-comfficeffice"
    xmlns="urn:schemas-microsoft-comffice:excel"
    xmlns:ss="urn:schemas-microsoft-comffice:spreadsheet"
    xmlns:html="http://www.w3.org/TR/REC-html40">
    <Worksheet ss:Name="XLReport">
    <Table ss:ExpandedColumnCount="23" ss:ExpandedRowCount="1" x:FullColumns="1"
    x:FullRows="1" ssefaultRowHeight="15">
    <Row ss:Height="45">
    <Cell><Data ss:Type="String">FEEDBACKNUMBER</Data></Cell>
    <Cell><Data ss:Type="String">CA</Data></Cell>
    <Cell><Data ss:Type="String">SA</Data></Cell>
    <Cell><Data ss:Type="String">CUSTOMERNAME</Data></Cell>
    <Cell><Data ss:Type="String">TYPE</Data></Cell>
    <Cell><Data ss:Type="String">ASGNTO</Data></Cell>
    <Cell><Data ss:Type="String">RECVDATE</Data></Cell>
    <Cell><Data ss:Type="String">DUEDATE</Data></Cell>
    <Cell><Data ss:Type="String">RESPDATE</Data></Cell>
    <Cell><Data ss:Type="String">CLOSEDATE</Data></Cell>
    <Cell><Data ss:Type="String">SENTDATE</Data></Cell>
    <Cell><Data ss:Type="String">XREFNUM</Data></Cell>
    <Cell><Data ss:Type="String">DEPTKIND</Data></Cell>
    <Cell><Data ss:Type="String">SRCDEPT</Data></Cell>
    <Cell><Data ss:Type="String">CLASS</Data></Cell>
    <Cell><Data ss:Type="String">CATG</Data></Cell>
    <Cell><Data ss:Type="String">ESCFLAG</Data></Cell>
    <Cell><Data ss:Type="String">MEDIA</Data></Cell>
    <Cell><Data ss:Type="String">PRIORITY</Data></Cell>
    <Cell><Data ss:Type="String">BILLSRV</Data></Cell>
    <Cell><Data ss:Type="String">DISP</Data></Cell>
    <Cell><Data ss:Type="String">ASGNDATE</Data></Cell>
    <Cell><Data ss:Type="String">a.Cust_Num</Data></Cell>
    </Row>
    <xsl:for-each select="xml/rs:data/z:row">
    <Row ss:AutoFitHeight="0" ss:Height="16.5">
    <Cell><Data ss:Type="String"><xsl:value-of select="@FEEDBACKNUMBER"/></Data></Cell>
    <Cell><Data ss:Type="String"><xsl:value-of select="@CA"/></Data></Cell>
    <Cell><Data ss:Type="String"><xsl:value-of select="@SA"/></Data></Cell>
    <Cell><Data ss:Type="String"><xsl:value-of select="@CUSTOMERNAME"/></Data></Cell>
    <Cell><Data ss:Type="String"><xsl:value-of select="@TYPE"/></Data></Cell>
    <Cell><Data ss:Type="String"><xsl:value-of select="@ASGNTO"/></Data></Cell>
    <Cell><Data ss:Type="String"><xsl:value-of select="@RECVDATE"/></Data></Cell>
    <Cell><Data ss:Type="String"><xsl:value-of select="@DUEDATE"/></Data></Cell>
    <Cell><Data ss:Type="String"><xsl:value-of select="@RESPDATE"/></Data></Cell>
    <Cell><Data ss:Type="String"><xsl:value-of select="@CLOSEDATE"/></Data></Cell>
    <Cell><Data ss:Type="String"><xsl:value-of select="@SENTDATE"/></Data></Cell>
    <Cell><Data ss:Type="String"><xsl:value-of select="@XREFNUM"/></Data></Cell>
    <Cell><Data ss:Type="String"><xsl:value-of select="@DEPTKIND"/></Data></Cell>
    <Cell><Data ss:Type="String"><xsl:value-of select="@SRCDEPT"/></Data></Cell>
    <Cell><Data ss:Type="String"><xsl:value-of select="@CLASS"/></Data></Cell>
    <Cell><Data ss:Type="String"><xsl:value-of select="@CATG"/></Data></Cell>
    <Cell><Data ss:Type="String"><xsl:value-of select="@ESCFLAG"/></Data></Cell>
    <Cell><Data ss:Type="String"><xsl:value-of select="@MEDIA"/></Data></Cell>
    <Cell><Data ss:Type="String"><xsl:value-of select="@PRIORITY"/></Data></Cell>
    <Cell><Data ss:Type="String"><xsl:value-of select="@BILLSRV"/></Data></Cell>
    <Cell><Data ss:Type="String"><xsl:value-of select="@DISP"/></Data></Cell>
    <Cell><Data ss:Type="String"><xsl:value-of select="@ASGNDATE"/></Data></Cell>
    <Cell><Data ss:Type="String"><xsl:value-of select="@a.Cust_Num"/></Data></Cell>
    </Row>
    </xsl:for-each>
    </Table>
    </Worksheet>
    </Workbook>
    </xsl:template>
    </xsl:stylesheet>
    asp code
    Const sConn = "Connect to DB2 Database"
    Const sXSL = "C:\Feedback.xsl"

    Response.Buffer = True

    'Retrieve an ADO recordset of the Orders Detail table in Northwind
    Dim strSQL, rs, nRecords

    Set rs = CreateObject("ADODB.Recordset")
    rs.Open "strRevSql = "Select ..... ", sConn, 3, 3

    'Persist the recordset to a new DOMDocument and store the record count
    Dim oXML
    Set oXML = CreateObject("Microsoft.XMLDOM")
    rs.Save oXML, 1
    nRecords = rs.RecordCount
    rs.Close

    'Load the XSL (the workbook template with XSL directives) into a DOMDocument
    Dim oXSL
    Set oXSL = CreateObject("Microsoft.XMLDOM")
    oXSL.Load sXSL

    'Transform the XML using the stylesheet
    Dim oResults
    Set oResults = CreateObject("Microsoft.XMLDOM")
    oXML.transformNodeToObject oXSL, oResults

    If oXSL.parseError.errorCode <> 0 Then
    Response.Write "Parse Error: " & oResults.parseError.reason
    Else

    'Modify the ss:ExpandedRowCount attribute for the <table> node in the XSL.
    Dim oTable
    Set oTable = oResults.selectSingleNode("Workbook/Worksheet/Table")
    oTable.setAttribute "ss:ExpandedRowCount", nRecords + 2

    'Return the resulting XML Spreadsheet for display in Excel
    Response.ContentType = "application/vnd.ms-excel"
    Response.Charset = "ISO-8859-1"
    Response.Write oResults.XML
    Response.Flush

    End If
    Query returns the data and If I save it in the local drive as xml file then it saves with all the data. I think my xsl file is wrong.

    The ss:ExpandedRowCount attribute for Table element is updated to the total number of rows in the xml file that is saved in the c:\

    Thanks in Advance
    PK


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •