0

I am transforming XML to .CSV with XSLT, I have few employee id’s which start with zeros and when xslt is converting file to .csv format leading zeros are getting stripped off. Is there anyway that we can prevent leading zeros getting stripped off when XSLT is converting file from XML to .csv?

Following is my xml input

 <wd:Report_Data xmlns:wd="urn:com.workday.report/Demograhics_Report">
 <wd:Report_Entry>
 <wd:Flag>Active_Employee</wd:Flag>
 <wd:Employeeid>00012345</wd:Employeeid>
 <wd:FirstName>Jack</wd:FirstName>
 <wd:LastName>Jones</wd:LastName>
 <wd:businessTitle>Service Assistant</wd:businessTitle>
 </wd:Report_Entry>
 <wd:Report_Entry>
 <wd:Flag>Inactive_Employee</wd:Flag>
 <wd:Userid>00012355</wd:Userid>
 <wd:FirstName>Maddy</wd:FirstName>
 <wd:LastName>Bolt</wd:LastName>
 <wd:businessTitle>Service Assistant</wd:businessTitle>
 </wd:Report_Entry>

I have tried with the following XSLT.

  <?xml version="1.0" encoding="UTF-8"?>
  <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
  xmlns:xs="http://www.w3.org/2001/XMLSchema" exclude-result-prefixes="xs" 
  xmlns:xtt="urn:com.workday/xtt" 
  xmlns:wd="urn:com.workday.report/Demograhics_Report" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0">
  <xsl:variable name="linefeed" select="'&#xA;'"/>
  <xsl:template match="wd:Report_Data">
    <File>
                        <xsl:text>"EMPLOYEE ID"</xsl:text>
            <xsl:value-of select="$linefeed"/>
            <!--  for each Employee section -->
            <xsl:for-each select="/wd:Report_Data/wd:Report_Entry">
            <xsl:if test="wd:Flag ='Active_Employee'">
                <xsl:text>"</xsl:text>
                <xsl:value-of select="concat('','&quot;',wd:Employeeid)"/>
                <xsl:text>"</xsl:text>
                <xsl:value-of select="$linefeed"/>
                </xsl:if>
            </xsl:for-each>
    </File>
 </xsl:template>
 </xsl:stylesheet>

If employee id is 00012345, with the above line in XSLT, return value was 00012345”. Is there anyway that makes the value to be returned as 00012345 without any quotes or any special characters?

Thanks, Jithen.

  • Please show what your input XML is and your XSLT code so we can reproduce the behaviour. Also state which XSLT processor you are using. – Sebastien Jul 28 '20 at 13:56
  • Why are you only adding quotes before your Employeeid, but not after the number in your concat function? Are you instead trying to do something like this : concat('"',wd:Employeeid,'"'). Also I'm guessing your output is fine but it might be the software you are opening your .csv in that is removing the 000? – Sebastien Jul 28 '20 at 14:13
  • sebastian, thanks for your response. added my input xml with the XSLT i used. when i use concat('"',wd:Employeeid,'"'), i am getting return value as "00012345". can't we get the value without quotes ? –  Jul 28 '20 at 14:20
  • with the above XSLT, i have tried was not removing zero's but was giving quotes end of id like this 00012345", but i wanted it to return as 00012345 without quotes at the end. –  Jul 28 '20 at 14:35
  • 1
    Are you sure the zeros are getting stripped by the xslt? How are you viewing the csv output? – Daniel Haley Jul 28 '20 at 14:36
  • If you don't want quotes you could just use : . Also your example is missing wd:ReportData. Try and make your example work using this tool : https://xsltfiddle.liberty-development.net/ then we can help you out. – Sebastien Jul 28 '20 at 14:36
  • Sebastian, missed report data while copying,updated now. thing is when just employee id is used as you mentioned , zeros are getting stripped off in csv and when i use , zeros are retained but i see quotes at the end of ID in CSV. –  Jul 28 '20 at 14:49
  • Daniel Haley, Yes zeros are getting stripped when i just use and when i use , zeros are retained but quotes are getting added at the end of id.i just open the CSV file. –  Jul 28 '20 at 14:51
  • Which software do you open the .csv file in? – Sebastien Jul 28 '20 at 14:53
  • Sebastien ,its Microsoft excel. –  Jul 28 '20 at 14:59
  • 1. I believe the zeros are stripped by Excel, not by your XSL transformation. If you want to keep them, keep the quotes around the value. 2. The stylesheet you have posted here does NOT produce a CSV file - and it puts TWO quotes in front of the Employeeid value. – michael.hor257k Jul 28 '20 at 15:30
  • Even putting quotes around the value will not stop excel helpfully stripping leading zeroes. Insert a \t (tab character) before the 0's and it will retain them. https://stackoverflow.com/questions/165042/stop-excel-from-automatically-converting-certain-text-values-to-dates/15107122#15107122. Of course excel could break that any time. – Bryn Lewis Jul 29 '20 at 00:58

0 Answers0