Convert CSV to XML

[#1]

Hi,

Is there any policy in Apigee or any other way through which I can convert CSV to XML based on a query parameter like format=csv or format=xml??

[anilsr · Accepted solution]

Dear @apigee1234567 ,

Unfortunately, there is NO ready to use policy which does CSV to XML. But, You can leverage Node.js modules to manage these types of transformations. Please find module which does same,

1. CSV to JSON - https://www.npmjs.com/package/csv-to-json , use JSON to XML to get XML after this particular flow.

You might need to use another external service which does CSV to JSON using node.js module mentioned above, In your proxy use Service Callout Policy and JSON to XML to convert from CSV to XML.

Please find sample Apigee API proxy that converts CSV file to XML & access result using an API.

csvtojson-rev1-2016-11-11.zip

[sarthak]

You can use the following script. It is not awesome but gets the job done - kinda !!!

This converts Apigee's monetization revenue reports and in csv and converts it to json.

// Source: http://www.bennadel.com/blog/1504-Ask-Ben-Parsing-CSV-Strings-With-Javascript-Exec-Regular-Expressio...
// This will parse a delimited string into an array of
// arrays. The default delimiter is the comma, but this
// can be overriden in the second argument.


//var convertToArray=CSVToArray(csvInput);


function CSVToArray(inputData, strDelimiter) {
  print("just got the data inside CSVToArray"+inputData);
  var res = inputData.split("Summary Revenue Report");
  //var res = inputData.split("\r\n");
  // break the textblock into an array of lines
  var lines = res[1].split('\r');
  // remove one line, starting at the first position
  lines.splice(0,1);
  // join the array back into a single string
    var newtext = lines.join('\r');
    
    var strData=newtext;
  print("$$$$$$$$$printing second part" + strData);
    // Check to see if the delimiter is defined. If not,
    // then default to comma.
    strDelimiter = (strDelimiter || ",");
    // Create a regular expression to parse the CSV values.
    var objPattern = new RegExp((
    // Delimiters.
    "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +
    // Quoted fields.
    "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +
    // Standard fields.
    "([^\"\\" + strDelimiter + "\\r\\n]*))"), "gi");
    // Create an array to hold our data. Give the array
    // a default empty first row.
    var arrData = [[]];
    // Create an array to hold our individual pattern
    // matching groups.
    var arrMatches = null;
    // Keep looping over the regular expression matches
    // until we can no longer find a match.
    while (arrMatches = objPattern.exec(strData)) {
        // Get the delimiter that was found.
        var strMatchedDelimiter = arrMatches[1];
     // print("strMatchedDelimiterstrMatchedDeli   "+strMatchedDelimiter);
        // Check to see if the given delimiter has a length
        // (is not the start of string) and if it matches
        // field delimiter. If id does not, then we know
        // that this delimiter is a row delimiter.
      //if (strMatchedDelimiter == "\\r\\n"){
        if (strMatchedDelimiter.length && (strMatchedDelimiter != strDelimiter)) {
            // Since we have reached a new row of data,
            // add an empty row to our data array.
            arrData.push([]);
        }
        // Now that we have our delimiter out of the way,
        // let's check to see which kind of value we
        // captured (quoted or unquoted).
        if (arrMatches[2]) {
            // We found a quoted value. When we capture
            // this value, unescape any double quotes.
            var strMatchedValue = arrMatches[2].replace(
            new RegExp("\"\"", "g"), "\"");
        } else {
            // We found a non-quoted value.
            var strMatchedValue = arrMatches[3];
        }
        // Now that we have our value string, let's add
        // it to the data array.
        arrData[arrData.length - 1].push(strMatchedValue);
    }
    // Return the parsed data.
    return (arrData);
}


function CSV2JSON(csv) {
    var array = CSVToArray(csv);
       
    var objArray = [];
    for (var i = 2; i < array.length; i++) {
        objArray[i - 2] = {};
        for (var k = 0; k < array[2].length && k < array[i].length; k++) {
       
            var key = array[2][k];
            // print("Got the array now"+ key + "array k is" +);
            objArray[i - 2][key] = array[i][k]
        }
    }


    var json = JSON.stringify(objArray);
    var str = json.replace(/},/g, "},\r\n");
     
    print(str);
    return str;
}

var csvInput = context.getVariable("response.content");


var jsonResponse = CSV2JSON(csvInput);
print(jsonResponse);

context.setVariable('response.content',jsonResponse);