Avatar of Mani Pazhana
Mani PazhanaFlag for United States of America 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>
                  
                        <!-- Payment Details -->
                        <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>

                                    <!-- GL Account -->
                                    <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>

                                    <!-- Additional Coding -->
                                    <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
XML.NET ProgrammingProgramming

Avatar of undefined
Last Comment
Mani Pazhana

8/22/2022 - Mon
Gertone (Geert Bormans)

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

can you please provide me sample code?
ASKER CERTIFIED SOLUTION
Gertone (Geert Bormans)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Gertone (Geert Bormans)

google for " pipe delimited to xml converter "

or import in excel and export as xml
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
Mani Pazhana

Thanks.
ASKER
Mani Pazhana

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">
      <!--Client: AT&T  V1
      Corrigo XML File to Invoice CIM format.

      Version 1.0            Amy Force      Initial Version of the file based on batch/row/ nodes in file
            7/9/2013            Amy Force      Pickup and compare against invoice (root)
            7/23/2013            Amy Force      REST of batch of Invoices-->

      <xsl:output method="xml" indent="yes" />
      
      <xsl:variable name="new-root-node">
            <xsl:text>root</xsl:text>
      </xsl:variable>
 
      <!-- ITP is expecting multiple rows to be wrapped with batch -->
      <xsl:template match="/*">
 
            <!-- Create a new element with new node name. -->
            <xsl:element name="{$new-root-node}">
                  <!-- Copy all children of the root node. -->
                  <xsl:copy-of select="*" />
            </xsl:element>
      </xsl:template>
 
</xsl:stylesheet>


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

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

Thanks
ASKER
Mani Pazhana

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

Thanks
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gertone (Geert Bormans)

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

Thanks
Gertone (Geert Bormans)

welcome
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
Mani Pazhana