CODEWITHSUNDEEP
xmlxsltxslt-1.0
zyberjock
zyberjock

Reputation: 347

XML to Fixed width text file formatted details

I have an XML like this

<?xml version="1.0" encoding="UTF-8"?>
<Report>
  <table1>
    <Detail_Collection>
      <Detail>
        <ReceiptNo>RN12345678</ReceiptNo>
        <ReceiptDate>1980/11/11</ReceiptDate>
        <LastName>Dela Cruz</LastName>
        <FirstName>Juan</FirstName>
        <PurchaseDetails>
          <Item>Wood</Item>
          <Price>25.65</Price>
          <Quantity>2</Quantity>
        </PurchaseDetails>
        <PurchaseDetails>
          <Item>Axe</Item>
          <Price>50.56</Price>
          <Quantity>5</Quantity>
        </PurchaseDetails>
      </Detail>
    </Detail_Collection>
  </table1>
</Report>

and I need to convert it to a flat text file using XSLT 1.0

I found this nice solution

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
    <xsl:output method="text" indent="no"/>

    <xsl:variable name="some_spaces" select="'                                                                  '" />

    <xsl:template match="/">
        <xsl:apply-templates select="//Detail_Collection/Detail" />
    </xsl:template>

    <xsl:template match="Detail_Collection/Detail">
        <xsl:apply-templates mode="format" select="SSN">
            <xsl:with-param name="width" select="number(9-1)"/>
        </xsl:apply-templates>
        <xsl:apply-templates mode="format_date" select="DOB">
            <xsl:with-param name="width" select="number(17-10)"/>
        </xsl:apply-templates>
        <xsl:apply-templates mode="format" select="LastName">
            <xsl:with-param name="width" select="number(33-18)"/>
        </xsl:apply-templates>
        <xsl:apply-templates mode="format" select="FirstName">
            <xsl:with-param name="width" select="number(46-34)"/>
        </xsl:apply-templates>
        <xsl:apply-templates mode="format_date" select="Date">
            <xsl:with-param name="width" select="number(54-47)"/>
        </xsl:apply-templates>
        <xsl:apply-templates mode="format" select="Time">
            <xsl:with-param name="width" select="number(62-55)"/>
        </xsl:apply-templates>
        <xsl:apply-templates mode="format" select="CurrentStreetAddress1">
            <xsl:with-param name="width" select="number(90-63)"/>
        </xsl:apply-templates>
        <xsl:apply-templates mode="format" select="CurrentCity">
            <xsl:with-param name="width" select="number(115-91)"/>
        </xsl:apply-templates>
        <xsl:apply-templates mode="format" select="CurrentState">
            <xsl:with-param name="width" select="number(131-116)"/>
        </xsl:apply-templates>
        <xsl:text>&#10;</xsl:text>
    </xsl:template>

    <xsl:template  match="node()" mode ="format">
        <xsl:param name="width" />
        <xsl:value-of select="substring(concat(text(),$some_spaces ), 1, $width+1)"/>
    </xsl:template>
    <xsl:template  match="node()" mode="format_date">
        <xsl:param name="width" />
        <xsl:value-of select="substring(concat(translate(text(),'/',''),$some_spaces ), 1, $width+1)"/>
    </xsl:template>

</xsl:stylesheet>

But the problem is that I have to format every detail according to its data type like below

Alphanumeric - should be 30 characters right filled with spaces

Numeric (Unsigned) - should be 15 characters left filled with zeroes e.g. 000000000012345

Numeric (signed) - should be 15 characters left filled with zeroes and if negative should denote 'N' e.g. N00000000012345

From my XML file the output should be:

RN12345678                   19801111Dela Cruz                    Juan               Wood               000000000002565000000000000002
RN12345678                   19801111Dela Cruz                    Juan               Axe                000000000005056000000000000005

and for example the prices are negative then 

RN12345678                   19801111Dela Cruz                    Juan               Wood               N00000000002565000000000000002
RN12345678                   19801111Dela Cruz                    Juan               Axe                N00000000005056000000000000005

and by the way i have some fields that have 300 characters (like a filler) so i dont know if I need to put 300+ spaces in the variable some_spaces

Dates should be 8 characters YYYYMMDD.

I have a template which im using but not sure how to put the 'N' for the negative ones and how to format the dates according to the requirement.

Here is the template:

<xsl:template name="prepend-pad">
    <!-- recursive template to right justify and prepend the value with whatever padChar is passed in   -->
    <xsl:param name="padChar" />
    <xsl:param name="padVar" />
    <xsl:param name="length" />
    <xsl:choose>
      <xsl:when test="string-length($padVar) &lt; $length">
        <xsl:call-template name="prepend-pad">
          <xsl:with-param name="padChar" select="$padChar"/>
          <xsl:with-param name="padVar" select="concat($padChar,$padVar)"/>
          <xsl:with-param name="length" select="$length"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="substring($padVar,string-length($padVar) - $length + 1)" />
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>

Thanks

Upvotes: 2

Views: 3345

Answers (2)

michael.hor257k
michael.hor257k

Reputation: 117073

How about:

XSLT 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" encoding="utf-8"/>

<xsl:variable name="spaces" select="'                              '"/>

<xsl:template match="/">
    <xsl:for-each select="Report/table1/Detail_Collection/Detail/PurchaseDetails">
        <xsl:apply-templates select="../ReceiptNo"/>
        <xsl:apply-templates select="../ReceiptDate"/>
        <xsl:apply-templates select="../LastName"/>
        <xsl:apply-templates select="../FirstName"/>
        <xsl:apply-templates select="Item"/>
        <xsl:call-template name="format-number">
            <xsl:with-param name="number" select="100 * Price"/>
        </xsl:call-template>
        <xsl:call-template name="format-number">
            <xsl:with-param name="number" select="Quantity"/>
        </xsl:call-template>
        <xsl:if test="position()!=last()">
            <xsl:text>&#10;</xsl:text>
        </xsl:if>
    </xsl:for-each>     
</xsl:template>

<xsl:template match="ReceiptNo | LastName | FirstName | Item">
    <xsl:value-of select="substring(concat(., $spaces), 1, 30)"/>
</xsl:template>

<xsl:template match="ReceiptDate">
    <xsl:value-of select="translate(., '/', '')"/>
</xsl:template>

<xsl:template name="format-number">
    <xsl:param name="number" select="0"/>
    <xsl:choose>
        <xsl:when test="$number >= 0">
            <xsl:value-of select="format-number($number, '000000000000000')"/>
        </xsl:when>
        <xsl:otherwise>
            <xsl:value-of select="format-number(-$number, 'N00000000000000')"/>
        </xsl:otherwise>
    </xsl:choose>
</xsl:template>

</xsl:stylesheet>

I am afraid I did not understand this part:

and by the way i have some fields that have 300 characters (like a filler) so i dont know if I need to put 300+ spaces in the variable some_spaces

Edit:

To insert 300 spaces in the resulting line, I suggest you use simply:

<xsl:text>  (300 spaces here)  </xsl:text>

It's possible to use a named template to generate any amount of spaces dynamically but since you need a constant number, I can't see any advantage to it.

And regarding the date it will be given to me as MM/dd/yyyy and i need to format it as yyyyMMdd, sorry for the wrong sample data I provided.

If so, change the template matching the date field to:

<xsl:template match="ReceiptDate">
    <xsl:value-of select="concat(substring(., 7, 4), substring(., 1, 2), substring(., 4, 2))"/>
</xsl:template>

Upvotes: 1

user1754036
user1754036

Reputation: 544

As mentioned in the comments, your XSL has very little to do with the XML you provided. So, working from the vague requirements for a flat text file and formatting the data with padding I made some guesses and came up with the following. 

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
    <xsl:output method="text" indent="no"/>

    <xsl:variable name="some_spaces" select="'                                                                  '" />

    <xsl:template match="/">
        <xsl:apply-templates select="//Detail_Collection/Detail" />
    </xsl:template>

    <xsl:template match="Detail_Collection/Detail">
        <xsl:for-each select="PurchaseDetails">
            <xsl:call-template name="format">
                <xsl:with-param name="textnode" select="../LastName"/>
                <xsl:with-param name="width" select="number(33-18)"/>
                <xsl:with-param name="type" select="string('A')"/>
            </xsl:call-template>
            <xsl:text>,</xsl:text>
            <xsl:call-template name="format">
                <xsl:with-param name="textnode" select="../FirstName"/>
                <xsl:with-param name="width" select="number(46-34)"/>
                <xsl:with-param name="type" select="string('A')"/>
            </xsl:call-template>
            <xsl:text>,</xsl:text>
            <xsl:call-template name="format">
                <xsl:with-param name="textnode" select="../ReceiptDate"/>
                <xsl:with-param name="width" select="number(54-45)"/>
                <xsl:with-param name="type" select="string('A')"/>
            </xsl:call-template>
            <xsl:text>,</xsl:text>
            <xsl:call-template name="format">
                <xsl:with-param name="textnode" select="Item"/>
                <xsl:with-param name="width" select="number(54-45)"/>
                <xsl:with-param name="type" select="string('A')"/>
            </xsl:call-template>
            <xsl:text>,</xsl:text>
            <xsl:call-template name="format">
                <xsl:with-param name="textnode" select="Quantity"/>
                <xsl:with-param name="width" select="number(54-45)"/>
                <xsl:with-param name="type" select="string('N')"/>
            </xsl:call-template>
            <xsl:call-template name="format">
                <xsl:with-param name="textnode" select="Price"/>
                <xsl:with-param name="width" select="number(54-45)"/>
                <xsl:with-param name="type" select="string('N')"/>
            </xsl:call-template>
            <xsl:text>&#10;</xsl:text>
        </xsl:for-each>
    </xsl:template>

    <xsl:template name="format">
        <xsl:param name="width"/>
        <xsl:param name="textnode"/>
        <xsl:param name="type"/>
        <xsl:variable name="leader_padding">
            <xsl:choose>
                <xsl:when test="$type='N' or $type='S'">
                    <xsl:value-of select="translate($some_spaces,' ','0')"/>
                </xsl:when>
                <xsl:otherwise>
                    <xsl:value-of select="$some_spaces"/>
                </xsl:otherwise>
            </xsl:choose>
        </xsl:variable>

        <xsl:variable name="bigstring" select="concat($leader_padding,$textnode)"/>
        <xsl:variable name="truncatedstring" select="substring($bigstring,string-length($bigstring)-$width)"/>

        <xsl:choose>
            <xsl:when test="$type='A'">
                <xsl:text> </xsl:text>
            </xsl:when>
            <xsl:when test="$type='N' or ($type='S' and number($textnode >= 0))">
                <xsl:text>0</xsl:text>
            </xsl:when>
            <xsl:otherwise>
                <!-- type must be S and value must be negative -->
                <xsl:text>N</xsl:text>
            </xsl:otherwise>
        </xsl:choose>
        <xsl:value-of select="$truncatedstring"/>

    </xsl:template>
</xsl:stylesheet>

Which when run against your input xml file produces:

    Dela Cruz,          Juan, 1980/11/11,       Wood,0000000000200000025.65
    Dela Cruz,          Juan, 1980/11/11,        Axe,0000000000500000050.56

There are two lines here because the input format isn't really flat. So I opted to go with one line per purchased item.

Upvotes: 0

Related Questions