Question 108506 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Excel to Nested-Structured JSON Conversion

I want to convert the following excel to JSON template using automation (either using any python or JS library).

The Excel Format that I have is as follows: https://docs.google.com/spreadsheets/d/1-vwP_R77dSQsAngfAHQJojd-SR6ncjLYWSpJrxpKbL4/edit?usp=sharing

enter image description here

The JSON output which is expected is as follows:

[
  {
    "Old Field Name": "PRIORITY",
    "Old Field Label": "Priority",
    "New Field Label": "Priority",
    "New Field Name": "Priority",
    "Conversion": [
      {
        "Method": "Mapping",
        "Mapping": [
          "1-high : High",
          "2-medium : Medium",
          "3-low : Low"
        ]
      }
    ]
  },
  {
    "Old Field Name": "REVIEWER",
    "Old Field Label": "Reviewer",
    "New Field Label": "Author",
    "New Field Name": "Author",
    "Conversion": [
      {
        "Method": "CONCAT",
        "Conversion": "Reviewer + <REVIEWER>"
      }
    ]
  },
  {
    "Old Field Name": "NAME",
    "Old Field Label": "Name",
    "New Field Label": "Head",
    "New Field Name": "Head"   
  }
]

Can someone suggest any steps to achieve the following conversion into the nested JSON as shown above, or any work around to automate the following scenario.

Note: I am looking forward to JS or Python Solution but I am fine with C# Libraries or any JAVA Library as well.

Tags:

2

Answers


  1. 0

    I’ve come up with the following as a standalone script:

    const file = process.argv[2];

const fs = require('fs');
const Papa = require('papaparse');

const rawCsv = fs.readFileSync(file, 'utf8');

const fileContents = Papa.parse(rawCsv, {
    delimiter: '', // auto-detect
    newline: '', // auto-detect
    quoteChar: '"',
    escapeChar: '"',
    header: true,
    transformHeader: true,
    dynamicTyping: false,
    preview: 0,
    encoding: '',
    worker: false,
    comments: false,
    step: undefined,
    complete: undefined,
    error: undefined,
    download: false,
    downloadRequestHeaders: undefined,
    downloadRequestBody: undefined,
    skipEmptyLines: true,
    chunk: undefined,
    chunkSize: undefined,
    fastMode: undefined,
    beforeFirstChunk: undefined,
    withCredentials: undefined,
    transform: true,
    delimitersToGuess: [';', 't', '|', ',', Papa.RECORD_SEP, Papa.UNIT_SEP],
}).data.map((details) => {
    let row = {...details};

    if (row['Conversion Type'] === 'Mapping') {
        row.Conversion = [
            {
                Method: 'Mapping',
                Mapping: row.Mapping.split('n'),
            }
        ];
    } else if (row['Conversion Type'] === 'CONCAT') {
        row.Conversion = [{
            Method: 'CONCAT',
            Conversion: row.Mapping,
        }];
    }
    
    delete row.Mapping;
    delete row['Conversion Type'];
    return row;
});

console.log(fileContents);

fs.writeFileSync(file.replace('.csv', '.json'), JSON.stringify(fileContents, null, 2));

    quick note, you don’t truly need to include all those options with Papa.parse. I did so that it works exactly the same for everyone

    In order to use this, you will need NodeJS and install papaparse using npm i papaparse.

    If possible, just for better scalability, you could consider using CONCAT or concat as the key instead of Conversion, and with that in place, we could apply a general rule of "take the conversion method as the key", and thusly write:

     if (row['Conversion Type'] === 'Mapping') {
        row.Conversion = [
            {
                Method: 'Mapping',
                Mapping: row.Mapping.split('n'),
            }
        ];
    } else {
        row.Conversion = [{
            Method: row['Conversion Type'],
            [row['Conversion Type'].toLowerCase()]: row.Mapping,
        }];
    }

    Let me know if you have any questions! 🙂

    Login or Signup to reply.
  2. 0

    Ok here’s a solution in JS/Node.js.

    First export from Google Spreadsheet as csv. (Note: If you do this in Excel the export might be slightly different, so I went from a Google Spreadsheet export).

    This is the CSV I got, saved it as MigrationTool.csv

    Old Field Name,Old Field Label,New Field Name,New Field Label,Conversion Type,Mapping
PRIORITY,Priority,Priority,Priority,Mapping,"""1-high : High"",
""2-medium : Medium"",
""3-low : Low"""
REVIEWER,Reviewer,Author,Author,CONCAT,"""Reviewer + <REVIEWER>"
NAME,Name,Head,Head,,

    Next write JavaScript code to convert, rather tricky one this actually, but here goes:

    const { readFileSync, writeFileSync } = require('fs');

function convertToJson(filePath) {
  let csv = readFileSync(filePath, 'utf-8').split('n');
  const headings = csv.shift().split(',');
  // handle special last column
  let lastNormal = 0;
  csv.forEach((x, i) => x[0] === '"' ? csv[lastNormal] += x : lastNormal = i);
  csv = csv.filter(x => x[0] !== '"');
  csv = csv.map(x => x.replace(/"",""/g, '|').replace(/"/g, ''));
  // loop through csv rows and create data for json
  let data = [];
  for (let row of csv) {
    let obj = {};
    colCount = -1;
    for (let col of row.split(',')) {
      colCount++;
      if (!headings[colCount]) { continue; }
      if (headings[colCount] === 'Conversion Type') {
        if (!col) { continue; }
        obj.Conversion = { Method: col };
        continue;
      }
      if (headings[colCount] === 'Mapping') {
        if (!col) { continue; }
        obj.Conversion[col.split('|').length > 1
          ? 'Mapping' : 'Conversion'] =
          col.split('|').length > 1 ? col.split('|') : col;
        continue;
      }
      obj[headings[colCount]] = col;
    }
    data.push(obj);
  }
  writeFileSync(
    filePath.replace(/.csv/g, '.json'),
    JSON.stringify(data, null, '  '),
    'utf-8'
  )
}

convertToJson('./MigrationTool.csv');

    Run the program and you’ll get this JSON in a file called MigrationTool.json:

    [
  {
    "Old Field Name": "PRIORITY",
    "Old Field Label": "Priority",
    "New Field Name": "Priority",
    "New Field Label": "Priority",
    "Conversion": {
      "Method": "Mapping",
      "Mapping": [
        "1-high : High",
        "2-medium : Medium",
        "3-low : Low"
      ]
    }
  },
  {
    "Old Field Name": "REVIEWER",
    "Old Field Label": "Reviewer",
    "New Field Name": "Author",
    "New Field Label": "Author",
    "Conversion": {
      "Method": "CONCAT",
      "Conversion": "Reviewer + <REVIEWER>"
    }
  },
  {
    "Old Field Name": "NAME",
    "Old Field Label": "Name",
    "New Field Name": "Head",
    "New Field Label": "Head"
  }
]

    Lastly, get paid? 😀
    No, it’s fine was a fun code challenge on a Sunday. 🙂

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search