Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
17 replies Latest Post - ‏2014-08-07T02:52:24Z by PrasadaReddy
Sree_2012
Sree_2012
18 Posts
ACCEPTED ANSWER

Pinned topic How to transform csv to XML/Backend web service

‏2012-01-12T21:10:23Z |
Can any body help me how to convert non-xml (.csv file) to xml and submit to backend webservice without using WTX or FDD. I want to use xsl in the transformation.

Thanks in advance..
Updated on 2012-04-22T00:47:53Z at 2012-04-22T00:47:53Z by HermannSW
  • swlinn
    swlinn
    1346 Posts
    ACCEPTED ANSWER

    Re: How to transform csv to XML/Backend web service

    ‏2012-01-12T22:26:10Z  in response to Sree_2012
    DataPower is an XML appliance. In order to process non-XML such as a .csv, you must convert it to XML in your processing rules using either a Contivo .ffd file or a WTX .dpa file in a binary transformation. Your stylesheet can't take raw non-XML as input.

    Regards,
    Steve
    • Sree_2012
      Sree_2012
      18 Posts
      ACCEPTED ANSWER

      Re: How to transform csv to XML/Backend web service

      ‏2012-01-12T22:36:33Z  in response to swlinn
      Thank you for the clarification.

      I'm new to DataPower. Can any body provide me a simple example/link for this feature.

      My Requirement is:
      1. FTP Poller using MPG to pick the .csv file(s) from FTP/NFS folder and upload to backend web service with ffd transformation.

      Thanks,
      Sree
      • swlinn
        swlinn
        1346 Posts
        ACCEPTED ANSWER

        Re: How to transform csv to XML/Backend web service

        ‏2012-01-13T15:31:32Z  in response to Sree_2012
        Within DataPower, you would use a binary transformation action. In the case of a .ffd file, you would specify a stylesheet that would reference the .ffd file using ...

        <dp:input-mapping href="yourfile.ffd" type="ffd" />

        where the result of that ffd mapping would be an XML file that would be made available to the match="/" template of the stylesheet.

        In the case of a WTX .dpa file, the binary transformation would directly reference the .dpa file. In either case, you'll need a license to either WTX Studio or Contivo Analyst to generate the appropriate artifact.

        Regards,
        Steve
        • HermannSW
          HermannSW
          4501 Posts
          ACCEPTED ANSWER

          Re: How to transform csv to XML/Backend web service

          ‏2012-01-13T16:40:52Z  in response to swlinn
          This slide shows which binary processing on DataPower does not need WTX/Contivo licenses:
          http://www-01.ibm.com/support/docview.wss?uid=swg27022977&aid=3#page=3

          In fact csv2xml can be done byte the techniques described in above Webcast as well as in the 2nd:
          http://www-01.ibm.com/support/docview.wss?uid=swg27022979

          Stylesheet c2v2xml.xsl (attached) does what you want for UTF-8 encoded CSV files.
          For other encodings apply the techniques described in the webcast.
          
          $ cat csv2xml.xsl <xsl:stylesheet version=
          "1.0" xmlns:xsl=
          "http://www.w3.org/1999/XSL/Transform" xmlns:dp=
          "http://www.datapower.com/extensions" xmlns:str=
          "http://exslt.org/strings" extension-element-prefixes=
          "dp str" > <dp:input-mapping  href=
          "store:///pkcs7-convert-input.ffd" type=
          "ffd"/>   <xsl:output omit-xml-declaration=
          "yes" />   <xsl:variable name=
          "rowsep" select=
          "'&#10;'"/> <xsl:variable name=
          "colsep" select=
          "','"/>   <xsl:template match=
          "/"> <xsl:variable name=
          "input64" select=
          "dp:binary-encode(/object/message/node())"/>   <xsl:variable name=
          "str" select=
          "dp:decode($input64,'base-64')"/>   <table> <xsl:for-each select=
          "str:split($str,$rowsep)"> <xsl:
          
          if test=
          "position()!=last()"> <row> <xsl:for-each select=
          "str:split(.,$colsep)"> <col><xsl:value-of select=
          "."/></col> </xsl:for-each> </row> </xsl:if> </xsl:for-each> </table> </xsl:template> </xsl:stylesheet> $
          


          Here is a demonstration:
          
          $ cat support.txt command \ model,XA35,XS40,XG45,XI50,XB60,XM70 convert-http,yes,yes,yes,yes,yes,yes dp:input-mapping,no,no,dim,yes,yes,yes dp:output-mapping,no,no,dim,yes,yes,yes $ $ coproc2 csv2xml.xsl support.txt http:
          //dp3-l3:2224 -s | tidy -q -xml <table> <row> <col>command \ model</col> <col>XA35</col> <col>XS40</col> <col>XG45</col> <col>XI50</col> <col>XB60</col> <col>XM70</col> </row> <row> <col>convert-http</col> <col>yes</col> <col>yes</col> <col>yes</col> <col>yes</col> <col>yes</col> <col>yes</col> </row> <row> <col>dp:input-mapping</col> <col>no</col> <col>no</col> <col>dim</col> <col>yes</col> <col>yes</col> <col>yes</col> </row> <row> <col>dp:output-mapping</col> <col>no</col> <col>no</col> <col>dim</col> <col>yes</col> <col>yes</col> <col>yes</col> </row> </table>   $
          


           
          Hermann<myXsltBlog/>
          • Sree_2012
            Sree_2012
            18 Posts
            ACCEPTED ANSWER

            Re: How to transform csv to XML/Backend web service

            ‏2012-01-19T21:07:19Z  in response to HermannSW
            Thank you Herman.

            But now the issue is now i'm not able to convert the xml to soap request and send it back to backend webservice. I was able to generate the xml though. Is there a way i can read the csv and take the 1st row as column header and remaining rows as values when i generate the xml file.
            • HermannSW
              HermannSW
              4501 Posts
              ACCEPTED ANSWER

              Re: How to transform csv to XML/Backend web service

              ‏2012-01-20T14:42:34Z  in response to Sree_2012
              > But now the issue is now i'm not able to convert the xml to soap request and send it back to backend webservice. I was able to
              > generate the xml though. Is there a way i can read the csv and take the 1st row as column header and remaining rows as values
              > when i generate the xml file.

              Please provide a small sample .csv input file and the XML output you want to be created from it.

               
              Hermann<myXsltBlog/>
              • Sree_2012
                Sree_2012
                18 Posts
                ACCEPTED ANSWER

                Re: How to transform csv to XML/Backend web service

                ‏2012-01-20T15:04:42Z  in response to HermannSW
                Hermann,

                Please go through the attached sample soap output file. I'm wondering if i can set the static backend on Multi Protocol Gateway and send the xml soap output to that webservice without providing the backend webservice link in the xsl transformation?

                Here is the sample csv input file format:

                Item,Qty
                Chips,10
                Candy,20

                Thanks,
                Sree2012.
                • HermannSW
                  HermannSW
                  4501 Posts
                  ACCEPTED ANSWER

                  Re: How to transform csv to XML/Backend web service

                  ‏2012-01-21T23:08:00Z  in response to Sree_2012
                  Sree,

                  so you just want to skip the first row.
                  Stylesheet csv2xml.1.xsl does that, see below.
                  Just adding " and position()!=1" is what is needed.

                  While I use coproc2 service for ease of demonstration you can of course install
                  csv2xml.xsl or a modification of it inside a MPGW as transformation. I hope this
                  answers your last question.

                  $ coproc2 csv2xml.xsl sree.csv http://dp3-l3:2224 -s | tidy -q -xml
                  <table>
                    <row>
                      <col>Item</col>
                      <col>Qty</col>
                    </row>
                    <row>
                      <col>Chips</col>
                      <col>10</col>
                    </row>
                    <row>
                      <col>Candy</col>
                      <col>20</col>
                    </row>
                  </table>
                   
                  $ coproc2 csv2xml.1.xsl sree.csv http://dp3-l3:2224 -s | tidy -q -xml
                  <table>
                    <row>
                      <col>Chips</col>
                      <col>10</col>
                    </row>
                    <row>
                      <col>Candy</col>
                      <col>20</col>
                    </row>
                  </table>
                   
                  $ diff csv2xml.xsl csv2xml.1.xsl 
                  22c22
                  <         <xsl:if test="position()!=last()">
                  ---
                  >         <xsl:if test="position()!=last() and position()!=1">
                  $ 
                  $ cat sree.csv 
                  Item,Qty
                  Chips,10
                  Candy,20
                  $
                  


                   
                  Hermann <myXsltBlog/>
                  Updated on 2014-03-25T03:03:44Z at 2014-03-25T03:03:44Z by iron-man
                  • Sree_2012
                    Sree_2012
                    18 Posts
                    ACCEPTED ANSWER

                    Re: How to transform csv to XML/Backend web service

                    ‏2012-02-07T16:59:55Z  in response to HermannSW
                    Thank you Herman.

                    Now i facing another issue with the csv to xml conversion. if there are any special characters in csv file like "&","," etc., Its not able to conver the columns properly.

                    Example:
                    Item,Qty,Price
                    "Soft Candy",2,$3.0
                    "Soda,Soft Drinks",4,$5.0
                    "Test'Abc',8,$3.0

                    I tried extending the exslt functions by adding a new javascript function into str.tokenize.msxsl.xsl file with function name as splitcsvdata(data,sep) it creates the nodes similar to str:tokenize function. If i use eclipse and include the modified xsl file it produces the result as expected. But i'm not sure how to add an include in xsl transformation file. I added it as

                    <xsl:include href="local:///str.tokenize.msxsl_modified.xsl"/>

                    datapower is not able to recognize it.

                    Any help on this would be greatly appreciated.

                    Thanks,
                    Sree.
                    • HermannSW
                      HermannSW
                      4501 Posts
                      ACCEPTED ANSWER

                      Re: How to transform csv to XML/Backend web service

                      ‏2012-02-08T23:16:37Z  in response to Sree_2012
                      Apersand is not a problem:
                      
                      $ cat sree.2.csv Item,Qty Chips&that,10 Candy,20 $ $ coproc2 csv2xml.1.xsl sree.2.csv http:
                      //dp3-l3:2224 -s | \ > tidy -q -xml <table> <row> <col>Chips&amp;that</col> <col>10</col> </row> <row> <col>Candy</col> <col>20</col> </row> </table>   $
                      


                      Your problem statement changed again.
                      While the previous solution was fine for what you asked for at that time, dealing with colsep characters inside quotes is the point where str:split() should be replaced by regexp:match() call from DataPower.

                      But for that you need to provide a problem specification, and define all terms.
                      For example you need to state what are quote characters, only " or ' in addition?
                      Then you need to specify whether these can be nested and what is the required behavior then, Is for example " ' " ' " allowd as a valid field entry?

                      The best is you do your specification by regular expresssion, then you can just reuse those for the solution.

                      You can just use grep to work on this, taking your example this is a possible regexp doing what might solve your problem besides the last line (do you really want "unclosed" quotes?) -- without your specification we do not know:
                      
                      $ grep 
                      '^\(\([^,"]*\|[^,]*"[^"]*"[^,]*\),\)*[^,]*$' sree.1.csv Item,Qty,Price 
                      "Soft Candy",2,$3.0 
                      "Soda,Soft Drinks",4,$5.0 $ $ cat sree.1.csv Item,Qty,Price 
                      "Soft Candy",2,$3.0 
                      "Soda,Soft Drinks",4,$5.0 
                      "Test'Abc',8,$3.0 $
                      

                      Last on your
                      <xsl:include href="local:///str.tokenize.msxsl_modified.xsl"/>

                      The string "msxsl" seems to indicate that you used Microsoft extension functions -- they do not exists on DataPower.
                      DataPower supports most of exslt.org extension functions, see:
                      https://www.ibm.com/developerworks/mydeveloperworks/blogs/HermannSW/entry/exslt_support_in_datapower_firefox_xalan_xsltproc12
                      And you talk about Javascript -- Javascript is supported in Microsoft IE Browser XSLT, but not in other Browsers and not in DataPower. DataPower is XSLT+EXSLT only ...

                       
                      Hermann<myXsltBlog/>
                      • Sree_2012
                        Sree_2012
                        18 Posts
                        ACCEPTED ANSWER

                        Re: How to transform csv to XML/Backend web service

                        ‏2012-02-09T21:56:32Z  in response to HermannSW
                        Hermann,

                        I agree with you in csv i never come across with "unclosed quotes)". I tried using regular expression as
                        '("(?:|"")*"|^,*)'

                        This expression works outside of datapower splitting the data into different array strings using "expresso tool". But if i use in data power it doesn't split into arrays by using

                        In datapower i declared a variable as
                        ---
                        <xsl:variable name="reg2" select="'(&quot;(?:&quot;|&quot;&quot;)*&quot;|,*)'"/>
                        ---
                        regex:match('"candy&salty",20','("(?:|"")*"|^,*)')

                        I'm expecting to see the output as
                        candy&salty
                        20

                        I attached a text document to see the regular expression in correct format.

                        Thanks,
                        Sree.

                        Attachments

                        • mefeai
                          mefeai
                          8 Posts
                          ACCEPTED ANSWER

                          Re: How to transform csv to XML/Backend web service

                          ‏2012-04-21T15:02:17Z  in response to Sree_2012
                          wdb,A123,descri,"234,12",09/14/2010
                        • mefeai
                          mefeai
                          8 Posts
                          ACCEPTED ANSWER

                          Re: How to transform csv to XML/Backend web service

                          ‏2012-04-21T15:03:10Z  in response to Sree_2012
                          How can we handle numbers that have a comma like this line

                          wdb,A123,descri,"234,12",09/14/2010
                          • HermannSW
                            HermannSW
                            4501 Posts
                            ACCEPTED ANSWER

                            Re: How to transform csv to XML/Backend web service

                            ‏2012-04-22T00:47:53Z  in response to mefeai
                            Hi,

                            > How can we handle numbers that have a comma like this line
                            >
                            > wdb,A123,descri,"234,12",09/14/2010
                            >
                            with a regexp that "knows" about the shielding character of a pair of quotes:
                            
                            ([^,
                            "]*|[^,"]*
                            "[^"]*
                            "[^,"]*)
                            

                            This says that a field consist either of characters different to quote and comma (left side of |)
                            or consists of characters different to quote and comma, a quote, characters not being a quote, another quote and then characters not being comma or quote.
                            (that was loooong)

                            I just doubled your sample line for having two lines of output:
                            
                            $ cat Errol.csv wdb,A123,descri,
                            "234,12",09/14/2010 wdb,A123,descri,
                            "234,12",09/14/2010 $
                            


                            Here you can see, that the modified stylesheet does what you want:
                            
                            $ coproc2 csv2xml.2.xsl Errol.csv http:
                            //dp3-l3:2224 -s | tidy -q -xml <table> <row> <col>wdb</col> <col>A123</col> <col>descri</col> <col>
                            "234,12"</col> <col>09/14/2010</col> </row> <row> <col>wdb</col> <col>A123</col> <col>descri</col> <col>
                            "234,12"</col> <col>09/14/2010</col> </row> </table>   $
                            


                            And here is the modified stylesheet -- as you can see the stylesheet "needs to know" the column count:
                            
                            $ cat csv2xml.2.xsl <xsl:stylesheet version=
                            "1.0" xmlns:xsl=
                            "http://www.w3.org/1999/XSL/Transform" xmlns:dp=
                            "http://www.datapower.com/extensions" xmlns:str=
                            "http://exslt.org/strings" xmlns:regexp=
                            "http://exslt.org/regular-expressions" extension-element-prefixes=
                            "dp str regexp" > <dp:input-mapping  href=
                            "store:///pkcs7-convert-input.ffd" type=
                            "ffd"/>   <xsl:output omit-xml-declaration=
                            "yes" />   <xsl:variable name=
                            "rowsep" select=
                            "'&#10;'"/>   <xsl:variable name=
                            "f">([^,
                            "]*|[^,"]*
                            "[^"]*
                            "[^,"]*)</xsl:variable> <xsl:variable name=
                            "pcre" select=
                            "concat($f,',',$f,',',$f,',',$f,',',$f)"/>   <xsl:template match=
                            "/"> <xsl:variable name=
                            "input64" select=
                            "dp:binary-encode(/object/message/node())"/>   <xsl:variable name=
                            "str" select=
                            "dp:decode($input64,'base-64')"/>   <table> <xsl:for-each select=
                            "str:split($str,$rowsep)"> <xsl:
                            
                            if test=
                            "position()!=last()"> <row> <xsl:for-each select=
                            "regexp:match(., $pcre, 'g')"> <col><xsl:value-of select=
                            "."/></col> </xsl:for-each> </row> </xsl:if> </xsl:for-each> </table> </xsl:template> </xsl:stylesheet> $
                            


                             
                            Hermann<myXsltBlog/>
                            • PrasadaReddy
                              PrasadaReddy
                              11 Posts
                              ACCEPTED ANSWER

                              Re: How to transform csv to XML/Backend web service

                              ‏2014-08-05T15:30:23Z  in response to HermannSW

                              Can you guys transform the following

                              Item,Qty,Price
                              "Soft Candy",2,$3.0
                              "Soft Drinks",4,$5.0
                              "TestAbc',8,$3.0
                               

                              to
                               

                              <Table>

                              <Row>
                              <Item>Soft Candy</Item>
                              <Qty>2</Qty>
                              <Price>$3.0</Price>
                              </Row>

                              <Row>
                              <Item>Soft Drinks</Item>
                              <Qty>4</Qty>
                              <Price>$5.0</Price>
                              </Row>

                              <Row>
                              <Item>TestAbc</Item>
                              <Qty>8</Qty>
                              <Price>$3.0</Price>
                              </Row>

                              </Table>

                               

                              - Using just datapower's transformations alone?

                              Thanks.

                               

                              • HermannSW
                                HermannSW
                                4501 Posts
                                ACCEPTED ANSWER

                                Re: How to transform csv to XML/Backend web service

                                ‏2014-08-06T09:16:52Z  in response to PrasadaReddy

                                Hi Prasada,

                                attached stylesheet does what you want.

                                It is based on csv2xml.1.xsl from above and works if

                                • $colsep does not occur in strings
                                • and $quot is not needed anywhere else (you requested to strip from output)


                                Here you see the small changes needed:

                                $ diff csv2xml.1.xsl csv2xml.1b.xsl
                                12a13
                                >   <xsl:variable name="quot"   select="'&quot;'"/>
                                19a21,23
                                >     <xsl:variable name="rows" select="str:split($str,$rowsep)"/>
                                >     <xsl:variable name="row1" select="str:split($rows[1],$colsep)"/>
                                > 
                                21c25
                                <       <xsl:for-each select="str:split($str,$rowsep)">
                                ---
                                >       <xsl:for-each select="$rows">
                                25c29,32
                                <               <col><xsl:value-of select="."/></col>
                                ---
                                >               <xsl:variable name="col" select="position()"/>
                                >               <xsl:element name="{$row1[$col]}">
                                >                 <xsl:value-of select="translate(., $quot, '')"/>
                                >               </xsl:element>
                                $
                                


                                And here you can see the output (prettyfied by tidy tool):

                                $ coproc2 csv2xml.1b.xsl Prasada.csv http://dp2-l3:2224 -s | tidy -q -xml
                                <table>
                                  <row>
                                    <Item>Soft Candy</Item>
                                    <Qty>2</Qty>
                                    <Price>$3.0</Price>
                                  </row>
                                  <row>
                                    <Item>Soft Drinks</Item>
                                    <Qty>4</Qty>
                                    <Price>$5.0</Price>
                                  </row>
                                  <row>
                                    <Item>TestAbc</Item>
                                    <Qty>8</Qty>
                                    <Price>$3.0</Price>
                                  </row>
                                </table>
                                
                                $
                                

                                 


                                Hermann <myBlog/> <myTweets/> | <GraphvizFiddle/> | <xqib/> | <myCE/> <myFrameless/> | 

                                Attachments

                                • PrasadaReddy
                                  PrasadaReddy
                                  11 Posts
                                  ACCEPTED ANSWER

                                  Re: How to transform csv to XML/Backend web service

                                  ‏2014-08-07T02:52:24Z  in response to HermannSW

                                  Hermann,

                                  Thank you very much for your quick help. I appreciate it a lot.

                                  This xsl is working good for names of the elements starting with alpha.

                                  Let us say - an element name is like "5yearMax" - this is not working - it is throwing it as inline data, rather than putting it in enclosed tags. Like if we have the following input:

                                  Item,5YrsQty,Price
                                  "Soft Candy",2,$3.0
                                  "Soft Drinks",4,$5.0
                                  "TestAbc',8,$3.0

                                  This is transforming into like this:

                                  <table>
                                    <row>
                                      <Item>Soft Candy</Item>
                                      2
                                      <Price>$3.0</Price>
                                    </row>
                                    <row>
                                      <Item>Soft Drinks</Item>
                                      4
                                      <Price>$5.0</Price>
                                    </row>
                                    <row>
                                      <Item>TestAbc</Item>
                                      8
                                      <Price>$3.0</Price>
                                    </row>
                                  </table>

                                  Element name tags starting with digits(5YrsQty) are not making to the final XML.

                                   

                                  Please advise.

                                  Thanks.