CODEWITHSUNDEEP
xmlcsvxslt
Patrick Ammann
Patrick Ammann

Reputation: 1

Replace comma with a dot in in XSL

I have an XSLT which is used to transform a CSV into a XML. The second column of the csv contains numbers such as, i.e. 5,0000

What I need is to transform this number into 5.0000 or even better, just 5. Means: Replace the , by a . would be one possibility or cut away the decimal symbol and whatever follows it.

I've been trying with translate(), format-number(), number() for hours, considering everything I found with googeling and searching in forums. Unfortunately I didn't manage to make it work so far. The only error message I keep on getting, no matter what I try, is that the Stylesheet couldn't been compiled, due to an error. Debugging isn't possible either as far as I know, as the input file is a CSV and not an XML Could somebody please help me out?

See Input example (CSV) and XSL below (the variable to change is called testAmount):

9612045901;5,000;Stk.;SomeText 1;M;72,04
5-10495;1,000;Stk.;SomeText 2;M;5,93
9612045901;5,000;Stk.;SomeText 3;72,04
5-10495;1,000;Stk.;SomeText 4;M;5,93


<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:fn="fn" exclude-result-prefixes="xs fn">
<xsl:output indent="yes" encoding="UTF-8" />
<xsl:param name="csvFile"/>

<!-- 
Constants
-->
<xsl:variable name="V_ITEM_POS" select="number(1)" />
<xsl:variable name="V_ITEM_DESC_POS" select="number(4)" />
<xsl:variable name="V_AMOUNT_POS" select="number(2)" />
<xsl:variable name="V_ZONE">ZONE1</xsl:variable>
<xsl:variable name="V_PRIORITY">9</xsl:variable>
<!--  
  EVENTUELL SHPG & RECV für IBD & OBD UNTERSCHEIDEN !!! 
-->
<xsl:variable name="V_IBD_TYPE_ID">SHPG</xsl:variable>


 <!--
MAIN
-->
<xsl:template match="/" name="main">
  <xsl:variable name="csv" select="unparsed-text($csvFile)" />
  <xsl:variable name="lines" select="tokenize($csv, '&#xA;')" as="xs:string+" />
  <xsl:variable name="orderNumber" select="format-dateTime(current-dateTime(), '[Y0001][M01][D01][H01][m01][s01][f0001]')" />
  <!--

  Determine inbound or outbound order 

  positiv amound will create an inbound order
  negative amound will create an outbound order

  Test at first value line
  -->
  <xsl:for-each select="$lines">
     <xsl:variable name="rowPos" select="position()" />
     <xsl:if test="$rowPos = 1">
        <xsl:variable name="testLineItems" select="tokenize(., ';')" />
        <xsl:variable name="testAmount" select="number($testLineItems[$V_AMOUNT_POS])" />
        <!--
            INBOUND DELIVERY
        -->
        <xsl:if test="$testAmount &lt; 0">
           <xsl:call-template name="TMPL_INBOUND_DELIVERY">
              <xsl:with-param name="in_lines" select="$lines" />
              <xsl:with-param name="in_orderNumber" select="$orderNumber" />
           </xsl:call-template>
        </xsl:if>
        <!--
            OUTBOUND DELIVERY
        -->
        <xsl:if test="$testAmount &gt; 0">
           <xsl:call-template name="TMPL_OUTBOUND_DELIVERY">
              <xsl:with-param name="in_lines" select="$lines" />
              <xsl:with-param name="in_orderNumber" select="$orderNumber" />
           </xsl:call-template>
        </xsl:if>

     </xsl:if>
  </xsl:for-each>
</xsl:template>
<!--

 INBOUND DELIVERY TEMPLATE
-->
<xsl:template name="TMPL_INBOUND_DELIVERY">
  <xsl:param name="in_lines" />
  <xsl:param name="in_orderNumber" />
  <DI_TELEGRAM>
     <HEADER>
        <FULL>
           <HEADER_SOURCE>HOST</HEADER_SOURCE>
           <HEADER_DESTINATION>WAMAS</HEADER_DESTINATION>
           <HEADER_SEQUENCE>1</HEADER_SEQUENCE>
           <HEADER_RECORDTYPENAME>LOGIMATIBD00001</HEADER_RECORDTYPENAME>
        </FULL>
     </HEADER>
     <BODY>
        <LogimatIbdGroup>
           <LOGIMATIBD00001>
              <InboundDelivery_ibdNo>
                 <xsl:value-of select="$in_orderNumber" />
              </InboundDelivery_ibdNo>
              <InboundDelivery_priority>
                 <xsl:value-of select="$V_PRIORITY" />
              </InboundDelivery_priority>
              <InboundDelivery_deliveryNoteNo></InboundDelivery_deliveryNoteNo>
              <InboundDelivery_type_ibdTypeId>
                 <xsl:value-of select="$V_IBD_TYPE_ID" />
              </InboundDelivery_type_ibdTypeId>
              <InboundDelivery_externalRef></InboundDelivery_externalRef>
              <InboundDelivery_unloadingPoint_area_areaId>
                 <xsl:value-of select="$V_ZONE" />
              </InboundDelivery_unloadingPoint_area_areaId>
              <LOGIMATIBD00001_subList>
                 <xsl:for-each select="$in_lines">
                    <xsl:variable name="lineItems" select="tokenize(., ';')" />
                       <!-- remove empty lines -->
                       <xsl:if test="number($lineItems[$V_AMOUNT_POS])!= number(0)">
                          <xsl:call-template name="TMPL_INBOUND_DELIVERY_LINE">
                             <xsl:with-param name="in_orderNumber" select="$in_orderNumber" />
                             <xsl:with-param name="in_sysPartnerLine" select="position() -1" />
                             <xsl:with-param name="in_itemNumber" select="$lineItems[$V_ITEM_POS]" />
                             <xsl:with-param name="in_amount" select="$lineItems[$V_AMOUNT_POS]" />
                          </xsl:call-template>
                       </xsl:if>
                 </xsl:for-each>
              </LOGIMATIBD00001_subList>
           </LOGIMATIBD00001>
        </LogimatIbdGroup>
     </BODY>
  </DI_TELEGRAM>
</xsl:template>
<!--

 INBOUND DELIVERY LINE TEMPLATE
-->
<xsl:template name="TMPL_INBOUND_DELIVERY_LINE">
  <xsl:param name="in_orderNumber" />
  <xsl:param name="in_sysPartnerLine" />
  <xsl:param name="in_itemNumber" />
  <xsl:param name="in_amount" />
  <LOGIMATIBDL00001>
     <InboundDeliveryLine_InboundDelivery_ibdNo>
        <xsl:value-of select="$in_orderNumber" />
     </InboundDeliveryLine_InboundDelivery_ibdNo>
     <InboundDeliveryLine_sysPartnerLine>
        <xsl:value-of select="$in_sysPartnerLine" />
     </InboundDeliveryLine_sysPartnerLine>
     <InboundDeliveryLine_iga_pkv_Item_itemNo>
        <xsl:value-of select="$in_itemNumber" />
     </InboundDeliveryLine_iga_pkv_Item_itemNo>
     <InboundDeliveryLine_iga_batch></InboundDeliveryLine_iga_batch>
     <InboundDeliveryLine_iga_bbDate></InboundDeliveryLine_iga_bbDate>
     <InboundDeliveryLine_orderAmount_baseQty>
        <xsl:value-of select="$in_amount" />
     </InboundDeliveryLine_orderAmount_baseQty>
  </LOGIMATIBDL00001>
</xsl:template>
<!--

 OUTBOUND DELIVERY TEMPLATE
-->
<xsl:template name="TMPL_OUTBOUND_DELIVERY">
  <xsl:param name="in_lines" />
  <xsl:param name="in_orderNumber" />
  <DI_TELEGRAM>
     <!-- The document root -->
     <HEADER>
        <FULL>
           <HEADER_SOURCE>HOST</HEADER_SOURCE>
           <HEADER_DESTINATION>WAMAS</HEADER_DESTINATION>
           <HEADER_SEQUENCE>1</HEADER_SEQUENCE>
           <HEADER_RECORDTYPENAME>LOGIMATOBD00001</HEADER_RECORDTYPENAME>
        </FULL>
     </HEADER>
     <BODY>
        <!-- The content data -->
        <LogimatObdGroup>
           <LOGIMATOBD00001>
              <OutboundDelivery_obdNo>
                 <xsl:value-of select="$in_orderNumber" />
              </OutboundDelivery_obdNo>
              <OutboundDelivery_priority>
                 <xsl:value-of select="$V_PRIORITY" />
              </OutboundDelivery_priority>
              <OutboundDelivery_deliveryTime></OutboundDelivery_deliveryTime>
              <OutboundDelivery_deliveryNoteNo></OutboundDelivery_deliveryNoteNo>
              <OutboundDelivery_type_obdTypeId>
                 <xsl:value-of select="$V_IBD_TYPE_ID" />
              </OutboundDelivery_type_obdTypeId>
              <OutboundDelivery_externalRef></OutboundDelivery_externalRef>
              <Zone>
                 <xsl:value-of select="$V_ZONE" />
              </Zone>
              <LOGIMATOBD00001_subList>
                 <xsl:for-each select="$in_lines">
                    <xsl:variable name="lineItems" select="tokenize(., ';')" />
                       <!-- remove empty lines -->
                       <xsl:if test="number($lineItems[$V_AMOUNT_POS])">
                          <xsl:call-template name="TMPL_OUTBOUND_DELIVERY_LINE">
                             <xsl:with-param name="in_orderNumber" select="$in_orderNumber" />
                             <xsl:with-param name="in_sysPartnerLine" select="position() -1" />
                             <xsl:with-param name="in_itemNumber" select="$lineItems[$V_ITEM_POS]" />
                             <xsl:with-param name="in_amount" select="number($lineItems[$V_AMOUNT_POS]) * -1" />
                          </xsl:call-template>
                       </xsl:if>
                 </xsl:for-each>
              </LOGIMATOBD00001_subList>
           </LOGIMATOBD00001>
        </LogimatObdGroup>
     </BODY>
  </DI_TELEGRAM>
</xsl:template>
<!--

 OUTBOUND DELIVERY LINE TEMPLATE
-->
<xsl:template name="TMPL_OUTBOUND_DELIVERY_LINE">
  <xsl:param name="in_orderNumber" />
  <xsl:param name="in_sysPartnerLine" />
  <xsl:param name="in_itemNumber" />
  <xsl:param name="in_amount" />
  <LOGIMATOBDL00001>
     <OutboundDeliveryLine_OutboundDelivery_obdNo>
        <xsl:value-of select="$in_orderNumber" />
     </OutboundDeliveryLine_OutboundDelivery_obdNo>
     <OutboundDeliveryLine_sysPartnerLine>
        <xsl:value-of select="$in_sysPartnerLine" />
     </OutboundDeliveryLine_sysPartnerLine>
     <OutboundDeliveryLine_oga_pkv_Item_itemNo>
        <xsl:value-of select="$in_itemNumber" />
     </OutboundDeliveryLine_oga_pkv_Item_itemNo>
     <OutboundDeliveryLine_oga_batch></OutboundDeliveryLine_oga_batch>
     <OutboundDeliveryLine_oga_bbDate></OutboundDeliveryLine_oga_bbDate>
     <OutboundDeliveryLine_orderAmount_baseQty>
        <xsl:value-of select="$in_amount" />
     </OutboundDeliveryLine_orderAmount_baseQty>
  </LOGIMATOBDL00001>
</xsl:template>
</xsl:stylesheet>

Upvotes: 0

Views: 5678

Answers (2)

Michael Kay
Michael Kay

Reputation: 163539

When you do this:

<xsl:variable name="testAmount" select="number($testLineItems[$V_AMOUNT_POS])" />

You are taking the second token in your input line, and converting to a number. This will give NaN, because the input is "5,0000". You need to convert it to the string "5.0000" before converting it to a number.

Diagnosing this kind of problem is often much easier if you declare the types of your variables. Add as="xs:string*" if it's a sequence of strings, as="xs:double" if it's a double, and so on. It also makes your code much more readable and understandable, and it can improve performance.

Upvotes: 0

michael.hor257k
michael.hor257k

Reputation: 117102

What I need is to transform this number into 5.0000 or even better, just 5.

If $testAmount contains the string 5,0000, then:

translate($testAmount, ',', '.')

will return 5.0000 and:

substring-before($testAmount, ',')

will return 5.

Upvotes: 1

Related Questions