Good afternoon!

There is an extension that converts data from XML to CSV. In the fields "S_Street" and "R_Street" there is data in several lines, which of course breaks the CSV format.

Tell me. How to replace the new line character in the "S_Street" and "R_Street" fields with a semicolon (",").

<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" xmlns:in="com.cisag.app.sales.obj.ShippingOrder" xmlns:sutil="org.apache.commons.lang3.StringUtils"> <xsl:output method="text" encoding="ISO-8859-1" version="1.0" indent="yes" /> <xsl:strip-space elements="*"/> <xsl:key name="deliveryReceipient" match="in:ShippingOrder" use="in:customerData/in:CustomerPartner/in:number" /> <xsl:template match="/"> <xsl:for-each select="//in:ShippingOrder/in:UnitLoads"> <xsl:sort select="in:customerData/in:CustomerPartner/in:number" /> <!-- <xsl:key name="unitLoad" match="in:ShippingOrder" use="in:UnitLoads/in:number" /> <xsl:for-each select="in:unitLoad"> --> <xsl:value-of select="substring(concat(../in:Type/in:code, '-', ../in:number), 1, 26)"/> <!-- OrderId --> <xsl:value-of select="substring(concat('|', '1'), 1, 4)"/> <!-- OrderPosition --> <xsl:value-of select="substring(concat('|', ../in:date), 1, 20)"/> <!-- CreatedDate --> <xsl:value-of select="substring(concat('|', ''), 1, 20)"/> <!-- PlannedDispatchDate --> <xsl:value-of select="substring(concat('|', ''), 1, 20)"/> <!-- PlannedDeliveryDate --> <xsl:value-of select="substring(concat('|', '1300'), 1, 256)"/> <!-- DispatchIds --> <xsl:choose> <xsl:when test="../in:deliveryData/in:ShippingTerms/in:code='10'"> <xsl:value-of select="substring(concat('|', 'EXPRESS'), 1, 21)"/> <!-- DispatchType --> </xsl:when> <xsl:otherwise> <xsl:value-of select="substring(concat('|', 'STANDARD'), 1, 21)"/> <!-- DispatchType --> </xsl:otherwise> </xsl:choose> <xsl:value-of select="substring(concat('|', '10'), 1, 31)"/> <!-- Status --> <xsl:value-of select="substring(concat('|', '272637665'), 1, 21)"/> <!-- Client_id --> <xsl:value-of select="substring(concat('|', ''), 1, 21)"/> <!-- User_id --> <xsl:value-of select="substring(concat('|', ''), 1, 31)"/> <!-- TrackingNo --> <xsl:value-of select="substring(concat('|', 'test_test'), 1, 51)"/> <!-- PrinterName --> <xsl:value-of select="substring(concat('|', ''), 1, 21)"/> <!-- Kennzeichen --> <xsl:value-of select="substring(concat('|', ''), 1, 4)"/> <!-- Send_Frankatur --> <xsl:value-of select="substring(concat('|', ''), 1, 21)"/> <!-- NachnahmeWährung --> <xsl:value-of select="substring(concat('|', ''), 1, 11)"/> <!-- NachnahmeWert --> <xsl:value-of select="substring(concat('|', in:number), 1, 101)"/> <!-- Referenz --> <xsl:value-of select="substring(concat('|', ../in:deliveryPartnerData/in:Partner/in:number), 1, 21)"/> <!-- S_ID --> <xsl:value-of select="substring(concat('|', ../in:deliveryPartnerData/in:name), 1, 31)"/> <!-- S_Name1 --> <xsl:value-of select="substring(concat('|', ''), 1, 31)"/> <!-- S_Name2 --> <xsl:value-of select="substring(concat('|', ''), 1, 31)"/> <!-- S_Name3 --> <xsl:value-of select="substring(concat('|', ../in:deliveryPartnerData/in:addressData/in:street), 1, 31)"/> <!-- S_Street --> <xsl:value-of select="substring(concat('|', ''), 1, 31)"/> <!-- S_StreetNo --> <xsl:value-of select="substring(concat('|', ../in:deliveryPartnerData/in:addressData/in:postalCode), 1, 11)"/> <!-- S_Postcode --> <xsl:value-of select="substring(concat('|', ../in:deliveryPartnerData/in:addressData/in:city), 1, 31)"/> <!-- S_City --> <xsl:value-of select="substring(concat('|', ../in:deliveryPartnerData/in:addressData/in:Country/in:isoCode), 1, 3)"/> <!-- S_Country --> <xsl:value-of select="substring(concat('|', ''), 1, 3)"/> <!-- S_Province --> <xsl:value-of select="substring(concat('|', ''), 1, 31)"/> <!-- S_Phone --> <xsl:value-of select="substring(concat('|', ../in:customerData/in:CustomerPartner/in:number), 1, 21)"/> <!-- R_ID --> <xsl:value-of select="substring(concat('|', ../in:customerData/in:imp_name1), 1, 31)"/> <!-- R_Name1 --> <xsl:value-of select="substring(concat('|', ../in:customerData/in:imp_name2), 1, 31)"/> <!-- R_Name2 --> <xsl:value-of select="substring(concat('|', ../in:customerData/in:imp_name3), 1, 31)"/> <!-- R_Name3 --> <xsl:value-of select="substring(concat('|', ../in:customerData/in:addressData/in:street), 1, 31)"/> <!-- R_Street --> <xsl:value-of select="substring(concat('|', ''), 1, 31)"/> <!-- R_StreetNo --> <xsl:value-of select="substring(concat('|', ../in:customerData/in:addressData/in:postalCode), 1, 11)"/> <!-- R_Postcode --> <xsl:value-of select="substring(concat('|', ../in:customerData/in:addressData/in:city), 1, 31)"/> <!-- R_City --> <xsl:value-of select="substring(concat('|', ../in:customerData/in:addressData/in:Country/in:isoCode), 1, 3)"/> <!-- R_Country --> <xsl:value-of select="substring(concat('|', ''), 1, 3)"/> <!-- R_Province --> <xsl:value-of select="substring(concat('|', ''), 1, 31)"/> <!-- R_Phone --> <xsl:value-of select="substring(concat('|', in:physicalDimensions/in:height/in:amount), 1, 11)"/> <!-- Send_Height --> <xsl:value-of select="substring(concat('|', in:physicalDimensions/in:length/in:amount), 1, 11)"/> <!-- Send_Length --> <xsl:value-of select="substring(concat('|', in:physicalDimensions/in:width/in:amount), 1, 11)"/> <!-- Send_Width --> <xsl:choose> <xsl:when test="in:actualGrossWeight/in:amount='0'"> <xsl:value-of select="substring(concat('|', '1'), 1, 11)"/> <!-- Send_Weight --> </xsl:when> <xsl:otherwise> <xsl:value-of select="substring(concat('|', in:actualGrossWeight/in:amount), 1, 11)"/> <!-- Send_Weight --> </xsl:otherwise> </xsl:choose> <xsl:value-of select="substring(concat('|', 1), 1, 4)"/> <!-- Send_Pieces --> <xsl:value-of select="substring(concat('|', 1), 1, 4)"/> <!-- Parcles --> <xsl:value-of select="substring(concat('|', ''), 1, 256)"/> <!-- Send_DTL_Send_Commodity_Code (Warengruppe) --> <xsl:value-of select="substring(concat('|', ''), 1, 11)"/> <!-- Send_DTL_Hangers (Bügel) --> <xsl:value-of select="substring(concat('|', ''), 1, 11)"/> <!-- Send_DTL_Handlebars (Griffeinheiten) --> <xsl:value-of select="substring(concat('|', ''), 1, 256)"/> <!-- Comment --> <!-- </xsl:for-each> --> <xsl:text>&#xa;</xsl:text> </xsl:for-each> </xsl:template> </xsl:stylesheet>

Example xml file:

 <documentRecipientAddressData> <street>Centroallee 1000 Einfahrt Parkhaus 7 an der "Alten Waltz" gegenüber Tryp Hotel (H&amp;M sowie C&amp;A zu sehen), vom Ladenhof E über den Serviceeingang zur Fläche EG_1</street> <city>Oberhausen</city>
  • Show example input xml. A small piece, precisely with the field S_Street. - Alexander Petrov
  • I can’t add xml here with four spaces. I had to fill in again: drive.google.com/file/d/0B8P7x0eHO8zNNDQ2d3c3alNMNms/… - Alexander Gross
  • Did I format correctly? That is, in csv you need to remove the line Einfahrt between 1000 and Einfahrt ? I do not see any S_Street. - Alexander Petrov
  • By the way, what is the meaning of substring(concat()) ? They are mostly not needed, IMHO. - Alexander Petrov
  • Yes, it was formatted correctly. And yes, a line break between 1000 and Einfahrt. S_Street just in the comments opposite the field - Alexander Gross

1 answer 1

A line break is a character with code 13. Or the essence &#13; . Those. all that is needed is to replace one delimiter in the string with another. If the delimiter length were the same, then the translate() function could be used.

 <xsl:value-of select="translate(street, '&#13;', ',')"/>

But since we have a different length, we have to recursively parse and collect lines

 <xsl:template name="replace"> <xsl:param name="val"/> <xsl:choose> <xsl:when test="contains($val, '&#13;')"> <xsl:value-of select="concat(substring-before($val, '&#13;'), ', ')"/> <xsl:call-template name="replace"> <xsl:with-param name="val" select="substring-after($val, '&#13;')"/> </xsl:call-template> </xsl:when> <xsl:otherwise> <xsl:value-of select="$val"/> </xsl:otherwise> </xsl:choose> </xsl:template> <xsl:call-template name="replace"> <xsl:with-param name="val" select="street"/> </xsl:call-template>

And for perversions - a universal template-substitute

 <xsl:template name="replace"> <xsl:param name="val"/> <xsl:param name="from"/> <xsl:param name="to"/> <xsl:choose> <xsl:when test="contains($val, $from)"> <xsl:value-of select="concat(substring-before($val, $from), $to)"/> <xsl:call-template name="replace"> <xsl:with-param name="val" select="substring-after($val, $from)"/> <xsl:with-param name="from" select="$from"/> <xsl:with-param name="to" select="$to"/> </xsl:call-template> </xsl:when> <xsl:otherwise> <xsl:value-of select="$val"/> </xsl:otherwise> </xsl:choose> </xsl:template> <xsl:call-template name="replace"> <xsl:with-param name="val" select="street"/> <xsl:with-param name="from" select="'&#13;'"/> <xsl:with-param name="to" select="', '"/> </xsl:call-template>