Topic
  • 17 replies
  • Latest Post - ‏2014-08-07T02:52:24Z by PrasadaReddy
Sree_2012
Sree_2012
18 Posts

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
    1396 Posts

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

    ‏2012-01-12T22:26:10Z  
    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

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

    ‏2012-01-12T22:36:33Z  
    • swlinn
    • ‏2012-01-12T22:26:10Z
    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
    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
    1396 Posts

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

    ‏2012-01-13T15:31:32Z  
    • Sree_2012
    • ‏2012-01-12T22:36:33Z
    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
    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
    6388 Posts

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

    ‏2012-01-13T16:40:52Z  
    • swlinn
    • ‏2012-01-13T15:31:32Z
    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
    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

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

    ‏2012-01-19T21:07:19Z  
    • HermannSW
    • ‏2012-01-13T16:40:52Z
    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.
    <pre class="jive-pre"> $ 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> $ </pre>

    Here is a demonstration:
    <pre class="jive-pre"> $ 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> $ </pre>

     
    Hermann<myXsltBlog/>
    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
    6388 Posts

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

    ‏2012-01-20T14:42:34Z  
    • Sree_2012
    • ‏2012-01-19T21:07:19Z
    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.
    > 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

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

    ‏2012-01-20T15:04:42Z  
    • HermannSW
    • ‏2012-01-20T14:42:34Z
    > 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/>
    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
    6388 Posts

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

    ‏2012-01-21T23:08:00Z  
    • Sree_2012
    • ‏2012-01-20T15:04:42Z
    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.
    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

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

    ‏2012-02-07T16:59:55Z  
    • HermannSW
    • ‏2012-01-21T23:08:00Z
    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.

    <pre class="java dw" data-editor-lang="java" data-pbcklang="java" dir="ltr">$ 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 $ </pre>

     
    Hermann <myXsltBlog/>
    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
    6388 Posts

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

    ‏2012-02-08T23:16:37Z  
    • Sree_2012
    • ‏2012-02-07T16:59:55Z
    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.
    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

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

    ‏2012-02-09T21:56:32Z  
    • HermannSW
    • ‏2012-02-08T23:16:37Z
    Apersand is not a problem:
    <pre class="jive-pre"> $ 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> $ </pre>

    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:
    <pre class="jive-pre"> $ 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 $ </pre>
    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/>
    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

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

    ‏2012-04-21T15:02:17Z  
    • Sree_2012
    • ‏2012-02-09T21:56:32Z
    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.
    wdb,A123,descri,"234,12",09/14/2010
  • mefeai
    mefeai
    8 Posts

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

    ‏2012-04-21T15:03:10Z  
    • Sree_2012
    • ‏2012-02-09T21:56:32Z
    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.
    How can we handle numbers that have a comma like this line

    wdb,A123,descri,"234,12",09/14/2010
  • HermannSW
    HermannSW
    6388 Posts

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

    ‏2012-04-22T00:47:53Z  
    • mefeai
    • ‏2012-04-21T15:03:10Z
    How can we handle numbers that have a comma like this line

    wdb,A123,descri,"234,12",09/14/2010
    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

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

    ‏2014-08-05T15:30:23Z  
    • HermannSW
    • ‏2012-04-22T00:47:53Z
    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:
    <pre class="jive-pre"> ([^, "]*|[^,"]* "[^"]* "[^,"]*) </pre>
    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:
    <pre class="jive-pre"> $ cat Errol.csv wdb,A123,descri, "234,12",09/14/2010 wdb,A123,descri, "234,12",09/14/2010 $ </pre>

    Here you can see, that the modified stylesheet does what you want:
    <pre class="jive-pre"> $ 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> $ </pre>

    And here is the modified stylesheet -- as you can see the stylesheet "needs to know" the column count:
    <pre class="jive-pre"> $ 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> $ </pre>

     
    Hermann<myXsltBlog/>

    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
    6388 Posts

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

    ‏2014-08-06T09:16:52Z  

    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.

     

    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

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

    ‏2014-08-07T02:52:24Z  
    • HermannSW
    • ‏2014-08-06T09:16:52Z

    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:

    <pre class="javascript dw" data-editor-lang="js" data-pbcklang="javascript" dir="ltr">$ 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> $ </pre>


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

    <pre class="javascript dw" data-editor-lang="js" data-pbcklang="javascript" dir="ltr">$ 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> $ </pre>

     


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

    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.