Topic
  • 19 replies
  • Latest Post - ‏2016-04-06T10:38:56Z by HermannSW
bydplearner
bydplearner
39 Posts

Pinned topic How to parse a csv file using Gateway Script

‏2016-03-16T08:55:49Z | datapower xi52

Hi 

I am in XI52 , firmware 7.2.0 , I have an requirement where I should read a csv file and do a lookup by passing some parameter like it.

The basic Xpath lookup which we do in XML files , I want to do in CSV files with hashmap. As a first step I am able to read a CSV file using Gateway script.  I am not able to get the next step of how to hash it and how to do lookup.

Any ideas or suggestions please share and also let me know whether it is doable in datapower.

  • HermannSW
    HermannSW
    8322 Posts
    ACCEPTED ANSWER

    Re: How to parse a csv file using Gateway Script

    ‏2016-03-31T10:15:54Z  

    Hermann

    Thanks for the quick reply. I see that ECMA 6 has something called Map inbuilt and I see in google that hashmaps can be created from that inbuilt function.

    Given that there is no inbuilt MAP in  ECMA 5.1 which is supported in Datapower  , Is this the best solution we can do for the problem statement.  Is HashMap not at all possible in Gatewayscript?

    Seems you mean the "Map" datatype from ECMA 6:
    http://adripofjavascript.com/blog/drips/using-ecmascript-6-maps.html

    It is available in DataPower firmwares ≥7.5.0.0 because GatewayScript is based on ECMA 6 starting with 7.5.0.0.


    But I am not sure why you want that Map type.
    All it does provide more than obj["blah"]=42; and obj.blah available in ECMA 5 and therefore GatewayScript is that the key type can be something different than a string (it is an object in samples of above URL). But I do not see where you need that in your scenario.


    So either you do it as you did initially (build array of "hash" objects and use search function on it) or more efficently as I did, what are you missing?

    ECMA 5 objects allow for "hashmaps" with key type "string".


    Hermann.

    Updated on 2016-03-31T10:17:02Z at 2016-03-31T10:17:02Z by HermannSW
  • HermannSW
    HermannSW
    8322 Posts

    Re: How to parse a csv file using Gateway Script

    ‏2016-03-16T11:34:47Z  

    You can index XML via XPath, or JSON via JSON navigation.

    So first you need to convert CSV to JSON, then you can access.

    I used the converted album.json sample from here:

    http://www.csvjson.com/csv2json

    This GatewayScript show you how you can extract information:

    $ coproc2 album.js album.json http://quicksilver.boeblingen.de.ibm.com:2227; echo
    2001
    $ 
    $ cat album.js
    session.input.readAsJSON(function (error, json) {
        if (error) {
            session.reject("Unexpected error in readAsJSON(): " + error);
            return;
        }
    
        session.output.write(json[2].year);
    });
    $
    

     

    Hermann,

  • bydplearner
    bydplearner
    39 Posts

    Re: How to parse a csv file using Gateway Script

    ‏2016-03-21T06:28:15Z  

    Herman 

    This idea worked like a charm,  Thanks for that,

     

    Is it possible to convert that JSON again to a Hash map. 

     

    Idea behind this is : we are going to have  a CSV file having more than 1000 s of lines, and we want to achieve quick lookup from csv file.

     

     

  • HermannSW
    HermannSW
    8322 Posts

    Re: How to parse a csv file using Gateway Script

    ‏2016-03-21T13:45:49Z  

    Herman 

    This idea worked like a charm,  Thanks for that,

     

    Is it possible to convert that JSON again to a Hash map. 

     

    Idea behind this is : we are going to have  a CSV file having more than 1000 s of lines, and we want to achieve quick lookup from csv file.

     

     

    Javascript has no hashmap:

    http://stackoverflow.com/questions/368280/javascript-hashmap-equivalent


    What hashmap are you referring to?


    Hermann.

    Updated on 2016-03-21T13:46:06Z at 2016-03-21T13:46:06Z by HermannSW
  • bydplearner
    bydplearner
    39 Posts

    Re: How to parse a csv file using Gateway Script

    ‏2016-03-28T13:25:10Z  

    I am trying something like this .

    CSV file

    Countrycode,CountryName,CurrencyType

    AUS,Australia,AUD

    IND,India,INR

    xxx,yyy,zzz

    abc,def,ghi

     

    I have read this and converted to JSON objects. Now I want to convert the JSON to key value pair.

    Like:

    Key       Value

    AUS      Australia,AUD

    IND       India,INR

    xxx        yyy,zzz

    abc        def,ghi

     

    We have a requirement that Datapower processing time should be minimal as we are expecting huge volume of transaction like 800TPS. So we are trying to achieve this hashmap so that look up can be done faster. Every transaction will need this lookup.

     

    Please advise.

  • HermannSW
    HermannSW
    8322 Posts

    Re: How to parse a csv file using Gateway Script

    ‏2016-03-29T08:53:10Z  

    I am trying something like this .

    CSV file

    Countrycode,CountryName,CurrencyType

    AUS,Australia,AUD

    IND,India,INR

    xxx,yyy,zzz

    abc,def,ghi

     

    I have read this and converted to JSON objects. Now I want to convert the JSON to key value pair.

    Like:

    Key       Value

    AUS      Australia,AUD

    IND       India,INR

    xxx        yyy,zzz

    abc        def,ghi

     

    We have a requirement that Datapower processing time should be minimal as we are expecting huge volume of transaction like 800TPS. So we are trying to achieve this hashmap so that look up can be done faster. Every transaction will need this lookup.

     

    Please advise.

    I have read this and converted to JSON objects.

    >
    Please attach sample GatewayScript on what you do.

     Now I want to convert the JSON to key value pair.
    >
    For some values or for all?

    Why do you need JSON at all and cannot generate the output directly while parsing the CSV input?
    What you did show above would allow to skip the JSON step.

    > as we are expecting huge volume of transaction like 800TPS
    >
    Depends on input size and transformation complexity, but what you showed aboe should not be a problem in any way on a physical XI52.


    Hermann.

  • bydplearner
    bydplearner
    39 Posts

    Re: How to parse a csv file using Gateway Script

    ‏2016-03-29T12:09:11Z  

    Hermann

    I went with the approach of converting CSV to JSON as you suggested that in initial post. Before that I tried to convert CSV directly to Array / Hash map .I was not successfull in that attempt, so I went ahead with this JSON conversion option.

    I have attached the ErrorLookup.js file I used .  I am passing two parameters named ServiceName and PErrorCode (I take these from input xml and store them in context variable and use in Gateway script) , I use these for lookup against the JSON message and create an output xml from the gateway script.  Now instad of that lookup being done in JSON message I want the CSV file to be converted to a hash map and then do a lookup on that hashmap.

     

    Hope I am clear on my requirement. Please advise whether this is possible even without the JSON conversion?

     

    Attachments

  • HermannSW
    HermannSW
    8322 Posts

    Re: How to parse a csv file using Gateway Script

    ‏2016-03-29T22:51:59Z  

    Hermann

    I went with the approach of converting CSV to JSON as you suggested that in initial post. Before that I tried to convert CSV directly to Array / Hash map .I was not successfull in that attempt, so I went ahead with this JSON conversion option.

    I have attached the ErrorLookup.js file I used .  I am passing two parameters named ServiceName and PErrorCode (I take these from input xml and store them in context variable and use in Gateway script) , I use these for lookup against the JSON message and create an output xml from the gateway script.  Now instad of that lookup being done in JSON message I want the CSV file to be converted to a hash map and then do a lookup on that hashmap.

     

    Hope I am clear on my requirement. Please advise whether this is possible even without the JSON conversion?

     

    Some remarks:

      var test = JSON.stringify(result); 
    var test1 = JSON.parse(test);
    


    result is already JSON object, then you stringify it and parse it again -- result and test1 are identical !

     

    Why building the JSON object (or "hashmap" in your words) if you want to lookup only one value?

    You can definitely determine the only line you are interested in (based on ServiceName and PErrorCode) in the for loop iterating over lines.
    From that you can generate the xml you want.

    I cannot test this because "temporary:///my_file.txt" is not attached.


    Hermann. 

     

    Updated on 2016-03-29T22:53:14Z at 2016-03-29T22:53:14Z by HermannSW
  • bydplearner
    bydplearner
    39 Posts

    Re: How to parse a csv file using Gateway Script

    ‏2016-03-31T08:04:56Z  

    Hermann

    I have attached my_file.txt. I am going for a hash map option as this csv lookup files is going to have 1000 + lines and want to make the search efficient.

    I understand that datapower s processing capabilities are too fast , but input size may be huge. Whatever I am showing here is all for a POC purpose.

    So please suggest some ideas on how to get a hashmap either from CSV directly or from JSON.

     

    Attachments

  • HermannSW
    HermannSW
    8322 Posts

    Re: How to parse a csv file using Gateway Script

    ‏2016-03-31T09:48:32Z  

    Hermann

    I have attached my_file.txt. I am going for a hash map option as this csv lookup files is going to have 1000 + lines and want to make the search efficient.

    I understand that datapower s processing capabilities are too fast , but input size may be huge. Whatever I am showing here is all for a POC purpose.

    So please suggest some ideas on how to get a hashmap either from CSV directly or from JSON.

     

    This does the search while reading the lines, aborting when line found:

    $ coproc2 ErrorLookup.js <(echo '') http://quicksilver:2227; echo
    <?xml version="1.0" encoding="UTF-8"?>
    <ErrorMessage><ServiceName>SVC001</ServiceName><InterfaceName>customersearch</InterfaceName><ErrorCode>2</ErrorCode><ErrorDescription>backend failure</ErrorDescription></ErrorMessage>
    $
    

    And this is the GatewayScript (also attached):

    //var ServiceName = session.name('Lookup').getVar('ServiceName');
    var ServiceName = "SVC001";
    
    //var PErrorCode = session.name('Lookup').getVar('PErrorCode');
    var PErrorCode = 200;
    
    var fs = require('fs');
    fs.readFile("temporary:///my_file.txt", function(error,data) {
      if(error) {
    
      } else {
        var y;
        var content = data.toString();
        var lines=content.split("\n");
        var headers=lines[0].split(",");
        for(var i=1;i<lines.length;i++){
          y = {};
          var currentline=lines[i].split(",");
          for(var j=0;j<headers.length;j++){
            y[headers[j]] = currentline[j];  
          }
          if (y.PErrorCode==PErrorCode && y.ServiceName==ServiceName)
            break;
        }
    
        var xml="<ErrorMessage>";
        xml += "<ServiceName>";
        xml += y.ServiceName;
        xml += "</ServiceName>";
    
        xml += "<InterfaceName>";
        xml += y.InterfaceName;
        xml += "</InterfaceName>";
    
        xml += "<ErrorCode>";
        xml += y.ErrorCode;
        xml += "</ErrorCode>";
    
        xml += "<ErrorDescription>";
        xml += y.ErrorDescription;
        xml += "</ErrorDescription>";
        xml +="</ErrorMessage>";
        var hm = require('header-metadata');
        var headers = hm.current;
        headers.set('Content-Type', 'application/xml');
          session.output.write(XML.parse(xml));
      }
    })
    


    Hermann.

    Attachments

  • bydplearner
    bydplearner
    39 Posts

    Re: How to parse a csv file using Gateway Script

    ‏2016-03-31T09:59:58Z  

    Hermann

    Thanks for the quick reply. I see that ECMA 6 has something called Map inbuilt and I see in google that hashmaps can be created from that inbuilt function.

    Given that there is no inbuilt MAP in  ECMA 5.1 which is supported in Datapower  , Is this the best solution we can do for the problem statement.  Is HashMap not at all possible in Gatewayscript?

  • HermannSW
    HermannSW
    8322 Posts

    Re: How to parse a csv file using Gateway Script

    ‏2016-03-31T10:15:54Z  

    Hermann

    Thanks for the quick reply. I see that ECMA 6 has something called Map inbuilt and I see in google that hashmaps can be created from that inbuilt function.

    Given that there is no inbuilt MAP in  ECMA 5.1 which is supported in Datapower  , Is this the best solution we can do for the problem statement.  Is HashMap not at all possible in Gatewayscript?

    Seems you mean the "Map" datatype from ECMA 6:
    http://adripofjavascript.com/blog/drips/using-ecmascript-6-maps.html

    It is available in DataPower firmwares ≥7.5.0.0 because GatewayScript is based on ECMA 6 starting with 7.5.0.0.


    But I am not sure why you want that Map type.
    All it does provide more than obj["blah"]=42; and obj.blah available in ECMA 5 and therefore GatewayScript is that the key type can be something different than a string (it is an object in samples of above URL). But I do not see where you need that in your scenario.


    So either you do it as you did initially (build array of "hash" objects and use search function on it) or more efficently as I did, what are you missing?

    ECMA 5 objects allow for "hashmaps" with key type "string".


    Hermann.

    Updated on 2016-03-31T10:17:02Z at 2016-03-31T10:17:02Z by HermannSW
  • bydplearner
    bydplearner
    39 Posts

    Re: How to parse a csv file using Gateway Script

    ‏2016-03-31T11:28:48Z  

    Agreed Herman.

    I will check the solution provided by you by lookup on a file containing 1000's of lines and will see the performance. Will this function read in gateway script behave like document () we use in xslt? I mean will it cache the csv file in Document cache ? 

  • bydplearner
    bydplearner
    39 Posts

    Re: How to parse a csv file using Gateway Script

    ‏2016-04-01T07:14:41Z  

    Hermann,

    The solution is printing the last row's value of csv file if there is no match for the input passed. You have hardcoded the values in your ErrorLookup.js, But while taking it from input (Passing a combination which is not present in the csv file) it is printing the last row it read in output.

    Also can you let me know how to construct a XML /SOAP response with namespace in gateway script? something like below. I saw that in jquery we can declare namespace on the top of file, I did not find any js having namespace declared. please advise.

     

      var xml="<ns1:ErrorMessage>";

     

  • HermannSW
    HermannSW
    8322 Posts

    Re: How to parse a csv file using Gateway Script

    ‏2016-04-01T08:14:16Z  

    Hermann,

    The solution is printing the last row's value of csv file if there is no match for the input passed. You have hardcoded the values in your ErrorLookup.js, But while taking it from input (Passing a combination which is not present in the csv file) it is printing the last row it read in output.

    Also can you let me know how to construct a XML /SOAP response with namespace in gateway script? something like below. I saw that in jquery we can declare namespace on the top of file, I did not find any js having namespace declared. please advise.

     

      var xml="<ns1:ErrorMessage>";

     

    
    Now you can check on whether "y" is the empty object (no hit) or not (hit).
    

     

    > You have hardcoded the values in your ErrorLookup.js,
    >
    Only because I did not want to setup complete service, the getVar()s are just commented out.

    > But while taking it from input (Passing a combination which is not present in the csv file) it is printing the last row it read in output.
    >
    Please change like this:

    } else {
        var y = {};
        var content = data.toString();
        var lines=content.split("\n");
        var headers=lines[0].split(",");
        for(var i=1;i<lines.length;i++){
          var currentline=lines[i].split(",");
          for(var j=0;j<headers.length;j++){
            y[headers[j]] = currentline[j];  
          }
          if (y.PErrorCode==PErrorCode && y.ServiceName==ServiceName)
            break;
          y = {};
        }
    


     

    I did not find any js having namespace declared. please advise.
    >

    In <7.2.0.0 GatewayScript there is no direct XML support and you can only handle it as string.

    Starting with 7.2.0.0 XML is natively supported in GatewayScript.
    In example below you see how to just create a XML string, and parse it if on ≥7.2.0.0 firmware (quicksliver).
    The (added) XML declaration indicates that real XML processing happened.

    $ coproc2 byd.js ab.json http://dp1-l3:2227; echo
    <ns1:ErrorMessage xmlns:ns1='urn:foobar'/>
    $ 
    $ coproc2 byd.js ab.json http://quicksilver:2227; echo
    <?xml version="1.0" encoding="UTF-8"?>
    <ns1:ErrorMessage xmlns:ns1="urn:foobar"/>
    $ 
    $ cat byd.js 
    var xmlstr="<ns1:ErrorMessage xmlns:ns1='urn:foobar'/>";
    
    if (typeof XML == 'undefined') {
      session.output.write(xmlstr);    
    } else {
      // firmware >= 7.2.0.0
      var xml = XML.parse(xmlstr);
    
      session.output.write (xml);
    }
    $
    

     

    Hermann.

    Updated on 2016-04-01T08:15:34Z at 2016-04-01T08:15:34Z by HermannSW
  • bydplearner
    bydplearner
    39 Posts

    Re: How to parse a csv file using Gateway Script

    ‏2016-04-01T09:18:26Z  

    Hermann,

    Emptying the contents of Y , meaning y={} should come before break statement I believe, as break is executed first it just comes out of loop before emptying contents of y.

    I managed this with a variable flag, instead of emptying contents of Y.

    I am using virtual appliance with firmware 7.2.0.0 , and I am trying below to get a XML with namespace.

    var xmlstr="<ns1:ErrorMessage xmlns:ns1='urn:foobar'/>";  
        xmlstr += "<ServiceName>";
        xmlstr += y.ServiceName;
        xmlstr += "</ServiceName>";
        xmlstr +="</ns1:ErrorMessage>";
        var hm = require('header-metadata');
        var headers = hm.current;
        headers.set('Content-Type', 'application/xml');
     var xml = XML.parse(xmlstr);
      session.output.write (xml);
    

    but i am getting error like below

    "GatewayScript processing Error 'Error: Unable to parse the input data as XML: illegal character 'S' at offset 43 of the XML.parse() parameter. In file 'gatewayscript:///modules/xml.js' line:0, stack:Error: Unable to parse the input data as XML: illegal character 'S' at offset 43 of the XML.parse() parameter. at Object.parse (gatewayscript:///modules/xml.js:27:18) at Object.<anonymous> (local:///POC_GW_CSV/HerErrorLookup.js:59:16)'"

     

    What am i missing here ??

  • HermannSW
    HermannSW
    8322 Posts

    Re: How to parse a csv file using Gateway Script

    ‏2016-04-01T11:45:18Z  

    Hermann,

    Emptying the contents of Y , meaning y={} should come before break statement I believe, as break is executed first it just comes out of loop before emptying contents of y.

    I managed this with a variable flag, instead of emptying contents of Y.

    I am using virtual appliance with firmware 7.2.0.0 , and I am trying below to get a XML with namespace.

    <pre dir="ltr">var xmlstr="<ns1:ErrorMessage xmlns:ns1='urn:foobar'/>"; xmlstr += "<ServiceName>"; xmlstr += y.ServiceName; xmlstr += "</ServiceName>"; xmlstr +="</ns1:ErrorMessage>"; var hm = require('header-metadata'); var headers = hm.current; headers.set('Content-Type', 'application/xml'); var xml = XML.parse(xmlstr); session.output.write (xml); </pre>

    but i am getting error like below

    "GatewayScript processing Error 'Error: Unable to parse the input data as XML: illegal character 'S' at offset 43 of the XML.parse() parameter. In file 'gatewayscript:///modules/xml.js' line:0, stack:Error: Unable to parse the input data as XML: illegal character 'S' at offset 43 of the XML.parse() parameter. at Object.parse (gatewayscript:///modules/xml.js:27:18) at Object.<anonymous> (local:///POC_GW_CSV/HerErrorLookup.js:59:16)'"

     

    What am i missing here ??

    You have a superfluous '/' at the end of "var xmlstr" line -- that closes the element and the following stuff makes the whole Non-XML.

  • bydplearner
    bydplearner
    39 Posts

    Re: How to parse a csv file using Gateway Script

    ‏2016-04-01T12:47:19Z  

    OOPS!!!!! Somehow missed to see that '/'.

    It worked. .. :)

     

  • bydplearner
    bydplearner
    39 Posts

    Re: How to parse a csv file using Gateway Script

    ‏2016-04-06T08:15:19Z  

    OOPS!!!!! Somehow missed to see that '/'.

    It worked. .. :)

     

    Hermann,

    How does this fs.readfile in Gatewayscript behaves in terms of caching documents. If I CSV file and inturn a gateway script , will the CSV file be kept in cache after the first use , Or every time the file will be read from local directory as a new file. 

    When you use some XML documents for lookup it gets cached in Document  cache , but i don't see the Document cache, caching the csv file referenced by Gateway script.

    Please help me in understanding this,. For my scenario I am trying to ensure that CSV file lookup is going to be efficient when compared to XML lookup.

    My CSV file is going to have hundreds of lines.

     

  • HermannSW
    HermannSW
    8322 Posts

    Re: How to parse a csv file using Gateway Script

    ‏2016-04-06T10:38:56Z  

    Hermann,

    How does this fs.readfile in Gatewayscript behaves in terms of caching documents. If I CSV file and inturn a gateway script , will the CSV file be kept in cache after the first use , Or every time the file will be read from local directory as a new file. 

    When you use some XML documents for lookup it gets cached in Document  cache , but i don't see the Document cache, caching the csv file referenced by Gateway script.

    Please help me in understanding this,. For my scenario I am trying to ensure that CSV file lookup is going to be efficient when compared to XML lookup.

    My CSV file is going to have hundreds of lines.

     

    Hi,

    I just verified that fs.readFile() does cache read document in document cache -- if you enable docuemnt caching!

    By default docuemnt cache size is 0 (bytes) in XML Manager, increase that and add a matching caching policy (I used "?*" and "fixed").

    Executing this GatewayScript:

    var fs = require ('fs');
    
    fs.readFile("store:///identity.js", function(error,data) {
      if (error) {
        // Handle the error.
      } else {
        session.output.write(data);
      }
    });
    


    does cache the read file:


    Hermann.