Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

JSON to CSV flattening nested JSON

I am trying to Flatten JSON to parse as a CSV. But the flattening is not properly flattening. When I get the json to flatten customer.addresses is filling with addresstype:r then skipping all fields city,countrycode,countycode etc. and then starting at customer.companyName. The nested JSON is not breaking up properly to show properly in excel I think my JavaScript code must be off just a little bit. Any help with this would be greatly appreciated.

JSON (this is a portion of the nested json it will not always be in the same depth is there a way to code for any type of nested json that will read at all levels)

[
  {
    "countyCode": 12,
    "customer": {
      "addresses": [
        {
          "addressType": "R",
          "city": "BRADENTON",
          "countryCode": "US",
          "countyCode": 12,
          "foreignPostalCode": null,
          "state": "FL",
          "streetAddress": "819 15th Ave Dr E",
          "zipCode": 34211,
          "zipPlus": null
        },
        {
          "addressType": "M",
          "city": "BRADENTON",
          "countryCode": "US",
          "countyCode": 12,
          "foreignPostalCode": null,
          "state": "FL",
          "streetAddress": "PO BOX 124",
          "zipCode": 34201,
          "zipPlus": 0124
        }
      ],
      "companyName": null,
      "customerNumber": 932874,
      "customerStopFlag": false,
      "customerType": "I",
      "dateOfBirth": "1936-08-05T00:00:00",
      "dlExpirationDate": "2022-08-05T00:00:00",
      "dlRenewalEligibilityFlag": true,
      "driverLicenseNumber": "B360722339284",
      "emailAddress": null,
      "feidNumber": null,
      "firstName": "David",
      "lastName": "Brierton",
      "middleName": "Hugh",
      "militaryExemptionFlag": null,
      "nameSuffix": null,
      "sex": "M"

JS

function flatObjectToString(obj) {
    var s = "";
    Object.keys(obj).map(key => {
      if (obj[key] === null) {
        s += key + ":";
      } else if (obj[key].toLocaleDateString) {
        s += key + ": " + obj[key].toLocaleDateString() + "\n";
      } else if (obj[key] instanceof Array) {
        s += key + ":\n" + listToFlatString(obj[key]);
      } else if (typeof obj[key] == "object") {
        s += key + ":\n" + flatObjectToString(obj[key]);
      } else {
        s += key + ":" + obj[key];
      }
      s += "\n";
    });
    return s;
  }

  function listToFlatString(list) {
    var s = "";
    list.map(item => {
      Object.keys(item).map(key => {
        s += "";
        if (item[key] instanceof Array) {
          s += key + "\n" + listToFlatString(item[key]);
        } else if (typeof item[key] == "object" && item[key] !== null) {
          s += key + ": " + flatObjectToString(item[key]);
        } else {
          s += key + ": " + (item[key] === null ? "" : item[key].toLocaleDateString ? item[key].toLocaleDateString : item[key].toString());
        }
        s += "\n";
      });
    });
    return s;
  }

  function flatten(object, addToList, prefix) {
    Object.keys(object).map(key => {
      if (object[key] === null) {
        addToList[prefix + key] = "";
      } else
      if (object[key] instanceof Array) {
        addToList[prefix + key] = listToFlatString(object[key]);
      } else if (typeof object[key] == 'object' && !object[key].toLocaleDateString) {
        flatten(object[key], addToList, prefix + key + '.');
      } else {
        addToList[prefix + key] = object[key];
      }
    });
    return addToList;
  }

Then I run it through the Javascript Utilities with this:

// Run the JSON string through the flattening utilities above
          var flatJSON = JSON.parse(evt.target.result).map(record => flatten(record, {}, ''));

          var csv = Papa.unparse(flatJSON);
like image 558
David Brierton Avatar asked Apr 24 '18 19:04

David Brierton


1 Answers

function obj2csv(obj, opt) {
        if (typeof obj !== 'object') return null;
        opt = opt || {};
        var scopechar = opt.scopechar || '.';
        var delimeter = opt.delimeter || ',';
        if (Array.isArray(obj) === false) obj = [obj];
        var curs, name, rownum, key, queue, values = [], rows = [], headers = {}, headersArr = [];
        for (rownum = 0; rownum < obj.length; rownum++) {
            queue = [obj[rownum], ''];
            rows[rownum] = {};
            while (queue.length > 0) {
                name = queue.pop();
                curs = queue.pop();
                if (curs !== null && typeof curs === 'object') {
                    for (key in curs) {
                        if (curs.hasOwnProperty(key)) {
                            queue.push(curs[key]);
                            queue.push(name + (name ? scopechar : '') + key);
                        }
                    }
                } else {
                    if (headers[name] === undefined) headers[name] = true;
                    rows[rownum][name] = curs;
                }
            }
            values[rownum] = [];
        }
        // create csv text
        for (key in headers) {
            if (headers.hasOwnProperty(key)) {
                headersArr.push(key);
                for (rownum = 0; rownum < obj.length; rownum++) {
                    values[rownum].push(rows[rownum][key] === undefined
                                        ? ''
                                        : rows[rownum][key]);//JSON.stringify()
                }
            }
        }
        for (rownum = 0; rownum < obj.length; rownum++) {
            values[rownum] = values[rownum].join(delimeter);

        }

          return '"' + headersArr.join('"' + delimeter + '"') + '"\n' + values.join('\n');
    }

if you you have a json like the following

{
  "_id": "template:user",
  "_rev": "11-d319c4ac632171d6f01c40fdef3164a5",
  "p": "user",
  "first_name": "first_name_000",
  "last_name": "last_name_000",
  "favorite_list": {
    "field": "value_000"
  },
  "list_kmorganisation": [
    {
      "siren": "siren_000",
      "partition": "partition_000",
      "id_role": "id_role_000",
      "is_default": "is_default_000",
      "is_managed": "is_managed_000",
      "is_banned": "is_managed_000",
      "ban_reason": "ban_reason_000",
      "ban_date": "ban_date_000",
      "last_connection": "last_connection_000"
    }
  ],
  "login": {
    "mail": "mail_000",
    "passwd": "passwd_000",
    "salt": "salt_000",
    "status": "status_000",
    "access": [
      {
        "log_date": "log_date_000",
        "os": "os_000",
        "version": "version_000",
        "ip_addr": "ip_addr_000",
        "screen": "screen_000"
      }
    ]
  }
}

the end result will be flattened like this "login__access__0__screen", "login__access__0__ip_addr", "login__access__0__version", "login__access__0__os", "login__access__0__log_date", "login__status", "login__salt", "login__passwd", "login__mail", "last_name","first_name", "list_kmorganisation__1__last_connection", "list_kmorganisation__1__ban_date", "list_kmorganisation__1__ban_reason", "list_kmorganisation__1__is_banned", "list_kmorganisation__1__is_managed", "list_kmorganisation__1__is_default", "list_kmorganisation__1__id_role", "list_kmorganisation__1__partition", "list_kmorganisation__1__cmpny_name", "list_kmorganisation__1__siren", "list_kmorganisation__0__last_connection", "list_kmorganisation__0__ban_date", "list_kmorganisation__0__ban_reason", "list_kmorganisation__0__is_banned", "list_kmorganisation__0__is_managed", "list_kmorganisation__0__is_default", "list_kmorganisation__0__id_role", "list_kmorganisation__0__partition", "list_kmorganisation__0__cmpny_name", "list_kmorganisation__0__siren", "p", "_rev", "_id" (in one line of course and the values in the following line )

like image 57
Mehdi Benkirane Avatar answered Oct 15 '22 02:10

Mehdi Benkirane