CSV file content to JSON

[praviningawale]

I have created an API Proxy. This proxy fetched the content of the csv file from source system.

When I test this proxy in POSTMAN I get csv file content in the POSTMAN console. However, When we call this proxy via web browser gives option to download the csv file itself.

My question can we intercept this CSV file data in apigee and then convert it into JSON format and then give it to the consumer.

I read some other threads and solution is to write a Javascript. But is that even possible in my case.

[dchiesa1]

Yes, you can do it in JavaScript. Writing your own Javascript to parse CSV should be pretty simple, assuming the values within the fields don't use commas or quotes. Just split the entire payload by newlines, then for each line, split by commas, and then create the JSON. Something like this: 

var result = [];
var fieldNames = null;
fullContent.split('\n') // split by lines
  .forEach(function(line, ix) {
    line = line.trim();
    if (line) {
      // split by commas
      var pieces = line.split(',').map(function(s) { return s.trim(); }); 
      if (ix == 0) {
        fieldNames = pieces;
      }
      else {
        var row = pieces.reduce(function(map, item, ix) {
              map[fieldNames[ix]] = item;
              return map;
            }, {});
        result.push(row);
      }
    }
  });

// result now contains the CSV transformed into JSON

But that's pretty naive. It treats every value as a string, and it does not handle quoted strings, etc.  If your CSV data might be more complex, then you might want to use a purpose-built library for that. For example, you could transform CSV to JSON with a Java callout.  This one works pretty well. https://github.com/DinoChiesa/Apigee-Csv-Shredder 

With Apigee X or hybrid, you could use an ExternalCallout for this purpose. 

[praviningawale]

Hi,

We are using Apigee Hybrid. As you mentioned we have to use ExternalCallout for this purpose. Do you mean we have to use something like node.js to achieve this ?

[dchiesa1]

We are using Apigee Hybrid. As you mentioned we have to use ExternalCallout for this purpose.

I wouldn't say it that way. I would say "you COULD use ExternalCallout for this purpose." You may have your own reasons to use an ExternalCallout, but the fact that you're using Apigee hybrid does not lead to the conclusion that you MUST use an ExternalCallout for extensions like this.

Do you mean we have to use something like node.js to achieve this ?

The ExternalCallout policy allows you to connect the Apigee MP to a custom GRPC service that you provide. You can build that GRPC service using any language you like. You could build your code in nodejs, or Java, or C#, or Golang, or some other language+platform that has libraries that support GRPC.

But again, it is not the case that you MUST build a GRPC service to accomplish what you want: a CSV to JSON transformation. You COULD use ExternalCallout and a GRPC service to do that, but there are other options. I gave a couple examples of ways to tackle the problem that do not involve an ExternalCallout.

[praviningawale]

Hi Dino,

I used the javascript which you had given earlier. But when I use it I get below error:

{
"fault": {
"faultstring": "Execution of JavaScript-1 failed with error: Javascript runtime error: \"TypeError: Cannot call method \"split\" of null. (JavaScript-1.js:8)\"",
"detail": {
"errorcode": "steps.javascript.ScriptExecutionFailed"
}
}
}

The JS which I am using is

var result = [];
var fieldNames = null;
var fullContent = context.getVariable(response.content);
fullContent.split('\n') // split by lines
.forEach(function(line, ix) {
line = line.trim();
if (line) {
// split by commas
var pieces = line.split('|').map(function(s) { return s.trim(); });
if (ix === 0) {
fieldNames = pieces;
}
else {
var row = pieces.reduce(function(map, item, ix) {
map[fieldNames[ix]] = item;
return map;
}, {});
result.push(row);
}
}
});

[dchiesa1]

Maybe you have a blank or empty line?  You can diagnose and troubleshoot that by running the JS in nodejs outside of Apigee.