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

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"
  }
]