asked on

XSLT - process pipe delimiter file

i am using XSLT to process pipe delimited text file to xml output...

Here is my Input file (Pipe delimited)

CLIENTID|PAYMENTID|PAYEEID|PAYEEDESCRIPTION|PAYMENTDATE|PAYMENTNUMBER|PAYMENTMETHODCODE|PAYMENTMETHODDESCRIPTION|TOTALPAYMENTAMOUNT|CURRENCYCODE|ISVOID|PAYMENTLINENUMBER|ORIGINATINGINVOICENUMBER|ORIGINATINGINVOICELINENUMBER|INVOICEDATE|PONUMBER|POLINENUMBER|DOCUMENTTYPE|DOCUMENTCOMPANY|DOCUMENTNUMBER|PAYMENTAMOUNT|REMARK|COMPANYID|GLACCOUNTCODE|SUBSIDIARY|SUBLEDGER|SUBLEDGERTYPE|LEDGERTYPE|COSTCENTER|ACCOUNTBLOCK|GLACCOUNTDESCRIPTION|CLIENTACCOUNTCODE|WBSCODE|ORIGINATINGPROJECTNUMBER|ORIGINATINGPROJECTSYSTEM|CLIENTPONUMBER|WORKORDERNUMBER|WORKORDERCOMPLETEDDATE|WORKORDERCREATEDDATE|CODINGBLOCK1|CODINGBLOCK2|CODINGBLOCK3|CODINGBLOCK4|CODINGBLOCK5|CODINGBLOCK6|CODINGDATE1|CODINGDATE2
JCP|460732|237936|24 7 GLASS STOREFRONT CORP|2013-08-12T00:00:00 |1000|PK|C|-100.0|USD|0|1|INV 1 JCP|001|2013-01-01T00:00:00 ||0.0|PV|03200|2161050|-100.0|Invoice 1 Rema |03200|50490 |001|PRO|C|AA| JCP00001M|001.50490|Cust - Lamp Replacement|J1|||||work ord 1 ||||||||||
JCP|460733|237937|A-OK SECURITY|2013-08-12T00:00:00 |1001|PK|C|-200.0|USD|0|1|INV 2 JCP|001|2013-01-01T00:00:00 ||0.0|PV|03200|2161051|-200.0|Invoice 2 Rema |03200|50490 |001|PRO|C|AA| JCP00004M|001.50490|Cust - Lamp Replacement|J1|||||work ord 2 ||||||||||
JCP|460734|237938|ACTION DOOR COMPANY|2013-08-12T00:00:00 |1002|PK|C|-300.0|USD|0|1|INV 3 JCP|001|2013-01-01T00:00:00 ||0.0|PV|03200|2161052|-300.0|Invoice 3 Rema |03200|50490 |001|PRO|C|AA| JCP00005M|001.50490|Cust - Lamp Replacement|J1|||||work ord 3 ||||||||||
JCP|460735|237939|ALADDIN ELECTRIC INC MI|2013-08-12T00:00:00 |1003|PK|C|-400.0|USD|0|1|INV 4 JCP|001|2013-01-01T00:00:00 ||0.0|PV|03200|2161053|-400.0|Invoice 4 Rema |03200|50490 |001|PRO|C|AA| JCP00005T|001.50490|Cust - Lamp Replacement|J1|||||work ord 4 ||||||||||
JCP|460736|237940|ALPINE ELECTRIC LODI|2013-08-12T00:00:00 |1004|PK|C|-500.0|USD|0|1|INV 5 JCP|001|2013-01-01T00:00:00 ||0.0|PV|03200|2161054|-500.0|Invoice 5 Rema |03200|50490 |001|PRO|C|AA| JCP00007M|001.50490|Cust - Lamp Replacement|J1|||||work ord 5 ||||||||||
JCP|460737|237941|ANTHONY ROOFING A TECTA AM CO|2013-08-12T00:00:00 |1005|PK|C|-600.0|USD|0|1|INV 6 JCP|001|2013-01-01T00:00:00 ||0.0|PV|03200|2161055|-600.0|Invoice 6 Rema |03200|50490 |001|PRO|C|AA| JCP00012M|001.50490|Cust - Lamp Replacement|J1|||||work ord 6 ||||||||||

-------------------------------------------

Here is my XSLT:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
                        xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                        xmlns="http://integration.cbre.com/schemas/gcs/remittance/v1">


      <xsl:output method="xml" indent="yes" omit-xml-declaration="yes"/>
      <xsl:strip-space elements="*"/>

      <xsl:template match="/">
            <Remittance>
                  <xsl:for-each select="//batch/row">

                        <xsl:element name="ClientId">
                              <xsl:value-of select="CLIENTID" disable-output-escaping="yes"/>
                        </xsl:element>
                        <xsl:element name="PaymentId">
                              <xsl:value-of select="PAYMENTID" disable-output-escaping="yes"/>
                        </xsl:element>
                        <xsl:element name="PayeeId">
                              <xsl:value-of select="PAYEEID" disable-output-escaping="yes"/>
                        </xsl:element>
                        <xsl:element name="PayeeDescription">
                              <xsl:value-of select="PAYEEDESCRIPTION" disable-output-escaping="yes"/>
                        </xsl:element>
                        <xsl:element name="PaymentDate">
                              <xsl:value-of select="normalize-space(PAYMENTDATE)" disable-output-escaping="yes"/>
                        </xsl:element>
                        <xsl:element name="PaymentNumber">
                              <xsl:value-of select="PAYMENTNUMBER" disable-output-escaping="yes"/>
                        </xsl:element>
                        <xsl:element name="PaymentMethodCode">
                              <xsl:value-of select="PAYMENTMETHODCODE" disable-output-escaping="yes"/>
                        </xsl:element>
                        <xsl:element name="PaymentMethodDescription">
                              <xsl:value-of select="PAYMENTMETHODDESCRIPTION" disable-output-escaping="yes"/>
                        </xsl:element>
                        <xsl:element name="TotalPaymentAmount">
                              <xsl:value-of select="TOTALPAYMENTAMOUNT" disable-output-escaping="yes"/>
                        </xsl:element>
                        <xsl:element name="CurrencyCode">
                              <xsl:value-of select="CURRENCYCODE" disable-output-escaping="yes"/>
                        </xsl:element>
                        <xsl:element name="IsVoid">
                              <xsl:value-of select="ISVOID" disable-output-escaping="yes"/>
                        </xsl:element>

                        
                        <PaymentDetails>
                              <PaymentDetail>
                                    <xsl:element name="PaymentLineNumber">
                                          <xsl:value-of select="PAYMENTLINENUMBER" disable-output-escaping="yes"/>
                                    </xsl:element>
                                    <xsl:element name="OriginatingInvoiceNumber">
                                          <xsl:value-of select="ORIGINATINGINVOICENUMBER" disable-output-escaping="yes"/>
                                    </xsl:element>
                                    <xsl:element name="OriginatingInvoiceLineNumber">
                                          <xsl:value-of select="ORIGINATINGINVOICELINENUMBER" disable-output-escaping="yes"/>
                                    </xsl:element>
                                    <xsl:element name="InvoiceDate">
                                          <xsl:value-of select="normalize-space(INVOICEDATE)" disable-output-escaping="yes"/>
                                    </xsl:element>
                                    <xsl:element name="PONumber">
                                          <xsl:value-of select="PONUMBER" disable-output-escaping="yes"/>
                                    </xsl:element>
                                    <xsl:element name="POLineNumber">
                                          <xsl:value-of select="POLINENUMBER" disable-output-escaping="yes"/>
                                    </xsl:element>
                                    <xsl:element name="DocumentType">
                                          <xsl:value-of select="DOCUMENTTYPE" disable-output-escaping="yes"/>
                                    </xsl:element>
                                    <xsl:element name="DocumentCompany">
                                          <xsl:value-of select="DOCUMENTCOMPANY" disable-output-escaping="yes"/>
                                    </xsl:element>
                                    <xsl:element name="DocumentNumber">
                                          <xsl:value-of select="DOCUMENTNUMBER" disable-output-escaping="yes"/>
                                    </xsl:element>
                                    <xsl:element name="PaymentAmount">
                                          <xsl:value-of select="PAYMENTAMOUNT" disable-output-escaping="yes"/>
                                    </xsl:element>
                                    <xsl:element name="Remark">
                                          <xsl:value-of select="normalize-space(REMARK)" disable-output-escaping="yes"/>
                                    </xsl:element>

                                    
                                    <GLAccount>
                                          <xsl:element name="CompanyId">
                                                <xsl:value-of select="COMPANYID" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="GLAccountCode">
                                                <xsl:value-of select="GLACCOUNTCODE" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="Subsidiary">
                                                <xsl:value-of select="SUBSIDIARY" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="SubLedger">
                                                <xsl:value-of select="SUBLEDGER" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="SubLedgerType">
                                                <xsl:value-of select="SUBLEDGERTYPE" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="CostCenter">
                                                <xsl:value-of select="normalize-space(COSTCENTER)" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="AccountBlock">
                                                <xsl:value-of select="ACCOUNTBLOCK" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="GLAccountDescription">
                                                <xsl:value-of select="GLACCOUNTDESCRIPTION" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="ClientAccountCode">
                                                <xsl:value-of select="CLIENTACCOUNTCODE" disable-output-escaping="yes"/>
                                          </xsl:element>
                                    </GLAccount>

                                    
                                    <AdditionalCoding>
                                          <xsl:element name="WBSCode">
                                                <xsl:value-of select="WBSCode" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="OriginatingProjectNumber">
                                                <xsl:value-of select="ORIGINATINGPROJECTNUMBER" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="OriginatingProjectSystem">
                                                <xsl:value-of select="ORIGINATINGPROJECTSYSTEM" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="ClientPONumber">
                                                <xsl:value-of select="CLIENTPONUMBER" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="WorkOrderNumber">
                                                <xsl:value-of select="WORKORDERNUMBER" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="WorkOrderCompletedDate">
                                                <xsl:value-of select="normalize-space(WORKORDERCOMPLETEDDATE)" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="WorkOrderCreatedDate">
                                                <xsl:value-of select="normalize-space(WORKORDERCREATEDDATE)" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="CodingBlock1">
                                                <xsl:value-of select="CODINGBLOCK1" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="CodingBlock2">
                                                <xsl:value-of select="CODINGBLOCK2" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="CodingBlock3">
                                                <xsl:value-of select="CODINGBLOCK3" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="CodingBlock4">
                                                <xsl:value-of select="CODINGBLOCK4" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="CodingBlock5">
                                                <xsl:value-of select="CODINGBLOCK5" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="CodingBlock6">
                                                <xsl:value-of select="CODINGBLOCK6" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="CodingDate1">
                                                <xsl:value-of select="normalize-space(CODINGDATE1)" disable-output-escaping="yes"/>
                                          </xsl:element>
                                          <xsl:element name="CodingDate2">
                                                <xsl:value-of select="normalize-space(CODINGDATE2)" disable-output-escaping="yes"/>
                                          </xsl:element>
                                    </AdditionalCoding>
                              </PaymentDetail>
                        </PaymentDetails>
                  </xsl:for-each>
            </Remittance>
      </xsl:template>

</xsl:stylesheet>

------------------------------------

i am getting this error:

XmlException
------------
Data at the root level is invalid. Line 1, position 1.

Any Idea?

Thanks

Gertone (Geert Bormans)

input data for XSLT requires to be wellformed XML
try to pass it in as a parameter

Mani Pazhana

ASKER

can you please provide me sample code?

ASKER CERTIFIED SOLUTION

Gertone (Geert Bormans)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gertone (Geert Bormans)

google for " pipe delimited to xml converter "

or import in excel and export as xml

Mani Pazhana

ASKER

Thanks.

Mani Pazhana

ASKER

Here is my issue:
Remark tag is having some issue by wrapping into two lines... (we are seeing CR LF in the output)

Here is the Input XMl:

<root xmlns="http://integration.cbre.com/schemas/gcs/batchinvoice/v2">
<Invoice xmlns:common="http://integration.cbre.com/schemas/gcs/common/v1">
            <ClientId>ATT</ClientId>
            <OriginatingInvoiceId>1970</OriginatingInvoiceId>
            <VendorId>242354</VendorId>
            <AlternativeVendorId>PRV-13-1307</AlternativeVendorId>
            <VendorInvoiceNumber>13-8041</VendorInvoiceNumber>
            <InvoiceDate>2013-08-08T00:00:00</InvoiceDate>
            <InvoiceReceiveDate>2013-08-15T00:00:00</InvoiceReceiveDate>
            <GLDate>2013-08-21T20:53:03.81-05:00</GLDate>
            <Remark>Please repair torn and worn carpet squares on the sales floor.
972-409-0026</Remark>
           <TotalInvoiceAmount>239.23</TotalInvoiceAmount>
            <CurrencyCode>USD</CurrencyCode>
            <StatusCode>
                  <common:Code>Authorized</common:Code>
                  <common:Description>Authorized</common:Description>
            </StatusCode>
            <ClientApprovalNumber>1000119702</ClientApprovalNumber>
            <InvoiceSource>COR</InvoiceSource>
            <NumberOfLines>3</NumberOfLines>
            <InvoiceLineItems>
                  <InvoiceLineItem>
                        <OriginatingInvoiceLineID>4637</OriginatingInvoiceLineID>
                        <POLineNumber>34</POLineNumber>
                        <LineItemDescription>Replace case of carpet tiles on sales floor. 1 tech 2 hrs 2013-08-08T00:00:00</LineItemDescription>
                        <LocationId>ATR002037</LocationId>
                        <BuildingName>7800 N MACARTHUR BLVD SPACE#17</BuildingName>
                        <LineItemAmount>76.000000</LineItemAmount>
                        <GLAccount>
                              <CompanyId>51765</CompanyId>
                              <GLAccountCode>51765</GLAccountCode>
                              <AccountBlock>51765</AccountBlock>
                              <GLAccountDescription>51765</GLAccountDescription>
                        </GLAccount>
                        <AdditionalCoding>
                              <ClientPONumber />
                              <WorkOrderNumber>2037000001</WorkOrderNumber>
                              <WorkOrderCompletedDate>2013-08-19T16:12:38</WorkOrderCompletedDate>
                              <WorkOrderCreatedDate>2013-08-01T12:49:00</WorkOrderCreatedDate>
                              <CodingBlock1>Labor</CodingBlock1>
                              <CodingBlock2>Carpet Replace</CodingBlock2>
                              <CodingBlock4>Replace case of carpet tiles on sales floor. 1 tech 2 hrs</CodingBlock4>
                              <CodingBlock5>Flooring</CodingBlock5>
                        </AdditionalCoding>
                  </InvoiceLineItem>
                  <InvoiceLineItem>
                        <OriginatingInvoiceLineID>4638</OriginatingInvoiceLineID>
                        <POLineNumber>35</POLineNumber>
                        <LineItemDescription>carpet, glue</LineItemDescription>
                        <LocationId>ATR002037</LocationId>
                        <BuildingName>7800 N MACARTHUR BLVD SPACE#17</BuildingName>
                        <LineItemAmount>145.000000</LineItemAmount>
                        <GLAccount>
                              <CompanyId>51765</CompanyId>
                              <GLAccountCode>51765</GLAccountCode>
                              <AccountBlock>51765</AccountBlock>
                              <GLAccountDescription>51765</GLAccountDescription>
                        </GLAccount>
                        <AdditionalCoding>
                              <ClientPONumber />
                              <WorkOrderNumber>2037000001</WorkOrderNumber>
                              <WorkOrderCompletedDate>2013-08-19T16:12:38</WorkOrderCompletedDate>
                              <WorkOrderCreatedDate>2013-08-01T12:49:00</WorkOrderCreatedDate>
                              <CodingBlock1>Materials</CodingBlock1>
                              <CodingBlock2>Carpet Replace</CodingBlock2>
                              <CodingBlock4>carpet, glue</CodingBlock4>
                              <CodingBlock5>Flooring</CodingBlock5>
                        </AdditionalCoding>
                  </InvoiceLineItem>
                  <InvoiceLineItem>
                        <OriginatingInvoiceLineID>4636</OriginatingInvoiceLineID>
                        <POLineNumber>36</POLineNumber>
                        <LineItemDescription>Tax</LineItemDescription>
                        <LocationId>ATR002037</LocationId>
                        <BuildingName>7800 N MACARTHUR BLVD SPACE#17</BuildingName>
                        <LineItemAmount>18.230000</LineItemAmount>
                        <GLAccount>
                              <CompanyId>51765</CompanyId>
                              <GLAccountCode>51765</GLAccountCode>
                              <AccountBlock>51765</AccountBlock>
                              <GLAccountDescription>51765</GLAccountDescription>
                        </GLAccount>
                        <AdditionalCoding>
                              <ClientPONumber />
                              <WorkOrderNumber>2037000001</WorkOrderNumber>
                              <WorkOrderCompletedDate>2013-08-19T16:12:38</WorkOrderCompletedDate>
                              <WorkOrderCreatedDate>2013-08-01T12:49:00</WorkOrderCreatedDate>
                              <CodingBlock1>Tax</CodingBlock1>
                              <CodingBlock2>Carpet Replace</CodingBlock2>
                              <CodingBlock4>Tax</CodingBlock4>
                              <CodingBlock5>Flooring</CodingBlock5>
                        </AdditionalCoding>
                  </InvoiceLineItem>
            </InvoiceLineItems>
      </Invoice>
</root>

----------------------------------------------------

XSLT:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
                        xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                        xmlns="http://integration.cbre.com/schemas/gcs/batchinvoice/v2">
      

      <xsl:output method="xml" indent="yes" />

      <xsl:variable name="new-root-node">
            <xsl:text>root</xsl:text>
      </xsl:variable>

      
      <xsl:template match="/*">

            
            <xsl:element name="{$new-root-node}">
                  
                  <xsl:copy-of select="*" />
            </xsl:element>
      </xsl:template>

</xsl:stylesheet>

------------------------------

can we fix the wrapping issue <Remark> </Remark> in the XSLT?

Thanks

Mani Pazhana

ASKER

Gertone,
do you want me to post as new question?

Thanks

Gertone (Geert Bormans)

I saw the new question first,
you might need to explain what exactly you want

Mani Pazhana

ASKER

Thanks

Gertone (Geert Bormans)

welcome

Mani Pazhana

ASKER

please check this out:

https://www.experts-exchange.com/questions/28235740/XSLT-XML-to-CSV.html