CSV export issue in nodejs

Question

I have excel sheet with list of data.

1. Reading excel data
2. searching excel data against another system using API
3. Taking top result data and converting to csv file.

Upto this step working good. But after that I need to format the data in csv file like excel data and search results both have to display in csv file.

Here i'm not able to bring excel data into csv file.For example "Honda" is car name in excel file and i'm reading it and searching against another system.Those results need to be display in csv.

Please advise.

Excel input:

Car name, Description
Honda, some description

API response data:

[{
    "total": 10,
    "results": [{
      "name": {
        "val": "Honda",
        "id": "0271b276",
        "type": "String",
      },
      "attributes": [{
        "val": "accord",
        "type": "field1",

      },  {
        "val": "test123",
        "type": "field3",
      }],

    }]
  },

]

Expectation output in the CSV file.

Car Name , Description,Total results,Make , Model
honda ,  Description,10 , Honda, accord

Code

const _ = require('lodash');
const xlsx = require('xlsx');

const workbook = xlsx.readFile(__dirname + '/test.xlsx');
const worksheet = workbook.Sheets[workbook.SheetNames[0]];
for (let z in worksheet) {
  if(z.toString()[0] === 'A'){

request({
    url: 'http://url', //URL to hit
    method: 'POST',
    json: {
        query: worksheet[z].v, 

    }
}, function(error, response, data){
    if(error) {
        console.log(error);
    } else {




    var fields = ['Make','Model','total', 'results[0].name.val','results[0].name[0].val'];
    var fieldNames = ['Make','Model','Total','Name','Description'];

    var opts1 = {
       data: data,
      fields: fields,
      fieldNames: fieldNames,

    };

    var json2csv = require('json2csv');
    var csv = json2csv(opts1);

    fs.writeFile('file.csv', csv, function(err) {
      if (err) throw err;
      console.log('file saved');
    });

Answer 1

Your JSON looks really weird, as pointed out before. If there's nothing you can do about the structure, I'd recommend writing an extra layer to extract the data from the nested response object. Here's what its configuration could look like:

var paths = [
  {
    name: "Car Name",
    getter: function(resp) {
      return resp.results[0].name.val;
    }
  }
];

Using this structure, you can

• specify the order of your columns by ordering the path array, and
• specify how to get the data using the getter function, and
• specify how to name the column using the name property.

You create columns by feeding in a response to a path:

var row = { }; // keys: column names, values: row's values
paths.forEach(function(path) {
  row[path.name] = path.getter(response);
});

I've tried to create a running example in the snippet below. Note that I've had to:

• Mock the json2csv library, I couldn't find a cdn...
• Extract the Description field from your query rather than your response (the API response data had no description)

Of course, this snippet is made to run in the browser and might need some extra work to port it to nodejs.

The main point is that it shows how to specify the convertion logic between two data formats.

var response = [{
  "total": 10,
  "results": [{
    "name": {
      "val": "Honda",
      "id": "0271b276",
      "type": "String",
    },
    "attributes": [{
      "val": "accord",
      "type": "field1",

    }, {
      "val": "test123",
      "type": "field3",
    }],

  }]
}];

var paths = [{
  name: "Car Name",
  source: "RESPONSE",
  getter: function(resp) {
    return resp.results[0].name.val;
  }
}, {
  name: "Description",
  source: "QUERY",
  getter: function(query) {
    return query.Description;
  }
}, {
  name: "Total results",
  source: "RESPONSE",
  getter: function(resp) {
    return resp.total;
  }
}, {
  name: "Make",
  source: "RESPONSE",
  getter: function(resp) {
    return resp.results[0].name.val;
  }
}, {
  name: "Model",
  source: "RESPONSE",
  getter: function(resp) {
    return resp.results[0].attributes[0].val;
  }
}];


var processResponse = function(query, response) {
  var results = [];
  response.forEach(function(response) {
    var result = {};

    paths.forEach(function(path) {
      var source;
      if (path.source === "RESPONSE") source = response;
      else if (path.source === "QUERY") source = query;
      else return;


      result[path.name] = path.getter(source);
    });

    results.push(result);
  });

  var csv = json2csv({
    data: results,
    fields: paths.map(function(path) {
      return path.name;
    })
  });

  var pre = document.querySelector("pre");

  document.querySelector("pre").innerHTML = csv;
};

var testQuery = {
  "Car name": "Honda",
  "Description": "some description"
};
processResponse(testQuery, response);

// Mocking lib
function json2csv(opts) {
  var head = opts.fields.join(",");
  var lines = opts.data
    .map(function(obj) {
      return opts.fields.map(function(k) {
        return obj[k];
      }).join(",");
    })

  return [head].concat(lines).join("\n");

};

<h3>Output csv</h3>
<pre style="background: #ccc"></pre>

Answer 2

I've formatted your JSON so that I can understand it better:

let data = [
  {
    "total": 10,
    "results": [
      {
        "name": {
          "val": "test value1",
          "id": "0271b276",
          "type": "String",
        },
        "attributes": [
          {
            "val": "test value2",
            "type": "field1",
          },
          {
            "val": "test description",
            "type": "field2",
          },
          {
            "val": "test123",
            "type": "field3",
          }
        ],
      }
    ]
  },
  [
    {
      "Make": "Honda",
      "Model": "Accord"
    }
  ]
];

This is some bizarre JSON. At the top level, it's an array with two elements. The first element is an object and the second element is another array.

The values you're looking for seem to be

• data[1][0].Make ("Honda") <-- note the uppercase M
• data[1][0].Model ("Accord") <-- note the uppercase M
• data[0].total (10)
• data[0].results[0].name.val ("test value1")
• data[0].results[0].attributes[0].val ("test value2")

...but I'm not sure.

From the npm page for json2csv, the data object must be an array of JSON objects. You'll have to restructure your data into a way that json2csv understands. Maybe your data object should look like this:

[
  {
    "name": {
      "val": "test name 1",
      "id": "0271b276",
      "type": "String"
    }
    "attributes": [
      {
        "val": "attribute 1",
        "type": "String"
      },
      {
        "val": "attribute 2",
        "type": "String"
      },
      {
        "val": "attribute 3",
        "type": "String"
      }
    ],
    "make": "Honda",
    "model": "Accord"
  },
  {
    "name": {
      "val": "test name 2",
      "id": "22e5b24e",
      "type": "String"
    }
    "attributes": [
      {
        "val": "attribute A",
        "type": "String"
      },
      {
        "val": "attribute B",
        "type": "String"
      },
      {
        "val": "attribute C",
        "type": "String"
      }
    ],
    "make": "Toyota",
    "model": "Corolla"
  }
]