How to correctly structure json and configure json2csv to output correct columns and rows?

Question

The data starts off in this shape:

   [
      {
        date: "July 5, 2020"
        name: "Calories"
        symbol: "CALORIES"
        value: 1,545.2
        ..,
      },
      {
        date: "July 7, 2020"
        name: "Total Carbs (g)"
        symbol: "TOTAL_CARBS"
        units: "g"
        value: 45.2
        ..,
      },
      ...
    ]

The intended shape of the outputted .csv file should be:

    ---------------   2020-07-05  2020-07-07   ...
    Calories          1,545.2     1,6276.3     ...
    Total Carbs (g)   45.2        56.9         ...
    ...

How should I transform the data before passing to the parsing function? to something like:

const transformedQueryData = [
      {
        'date': 2020-07-05,
        'CALORIES': 1,545.2,
        'TOTAL_CARBS': 45.2,
      },
      {
        'date': 2020-07-07,
        'CALORIES': 1,6276.3,
        'TOTAL_CARBS': 56.9,
      }
    ];

    OR

    const transformedQueryData = [
      {
        2020-07-05: {
          'CALORIES': 1,545.2,
          'TOTAL_CARBS': 45.2,
        }
      },
      {
        2020-07-07: {
          'CALORIES': 1,6276.3,
          'TOTAL_CARBS': 56.9,
        }
      }
    ];

OR can I use json2csv utilities and config to process the queriedData as it is?

It is not .csv it is .tsv format. – Arthur Rubens Aug 05 '20 at 22:24 — Arthur Rubens, Aug 05 '20 at 22:24

Marios Nikolaou · Answer 1 · 2020-08-05T22:58:22.140

I have created a function that splits the date value to 3 elements: month, day, year and then replace the date value with the custom one.

var ex = [
      {
        "date": "July 5, 2020",
        "name": "Calories",
        "symbol": "CALORIES",
        "value": "1,545.2"
      },
      {
        "date": "July 7, 2020",
        "name": "Total Carbs (g)",
        "symbol": "TOTAL_CARBS",
        "units": "g",
        "value": 45.2
      }
    ];

function getMonth(val){
  switch(val){
    case "January": return 1; break;
    case "February": return 2; break;
    case "March": return 3; break;
    case "April": return 4; break;
    case "May": return 5; break;
    case "June": return 6; break;
    case "July": return 7; break;
    case "August": return 8; break;
    case "Semptember": return 9; break;
    case "Octomber": return 10; break;
    case "November": return 11; break;
    case "December": return 12; break;
  }
     
}

//iterates for each subarray of json array
ex.forEach(function(record) {
    if (record.date != null) {
        let temp = record.date.replace(',',''); //remove ',' from string
        let arr = temp.split(" "); //split to month,day,year
        
        let month = getMonth(arr[0]);
        let day = arr[1].length == 2 ? arr[1] : '0'+arr[1]; //add 0 in front if day has one digit

        let strBuilder = arr[2]+"-"+day+"-"+month; //concatenate the values

        record.date = strBuilder; //replace the date value with the custom one

    }
});

console.log(ex);

I think it will help.

How to correctly structure json and configure json2csv to output correct columns and rows?

1 Answers1