multiple-csv-merge-to-json

multiple-csv-merge-to-json

This library reads a given list of CSV files and stores the compilation of data in a JSON file. The files are merged following a key, which can be one or several columns. Files should be given in order of retention data. If key was already in previous file, data is merged and replaced by lastest data only once. If key is found several times, a new line is added, merged with previous data.

Why ?

I had a scenario with 3 spreadsheets with different columns to merge. Instead of making crazy spreadsheet formulas, I decided to code a JS module.

Using module csvtojson for single CSV file read and convertion to JSON.

How to use

options API MultCsvMergeToJsonOptions

option	description	mandatory	type
inputDir	Directory where the csv files are	true	string
inputKeys	Unique column keys found in every CSV file	true	Array
inputFileNameList	List of filenames, in import order	true	Array
outputDir	Destination folder for generated JSON file	true	string
outputFileName	File name of generated JSON file	true	string
columnDelimiter	CSV column separator	true	string
encoding	CSV files encoding, default to 'utf8'	no	string
groupBy	Group data by key	no	{ groupByKey: string; groupedArrayProperty: string }
writeToFile	Write to file, default to false	no	boolean
replaceValues	Replace values from one file to another, default to false	no	boolean

mergeCsvFilesToJsonArray(options: MultCsvMergeToJsonOptions)

Function reads every file given in option and merges data in a JSON containing an array of objects. JSON object is saved in a file .json format.

• params : MultCsvMergeToJsonOptions
• returns : void, executes file reading and writing

mergeCsvFilesToJsonArray({
  inputDir: "./data_input_files",
  inputKeys: ["city", "region"],
  inputFileNameList: [
    "general_rates.csv",
    "premium_rates.csv",
    "danger_zones.csv",
  ],
  outputDir: "./data_output_json",
  outputFileName: "delivery_rates",
  columnDelimiter: ",",
});

getJsonArray(options: MultCsvMergeToJsonOptions)

Function finds and reads JSON file given in options, and returns its object in Javascript. File is meant to be previously generated by mergeCsvFilesToJsonArray function.

• params : MultCsvMergeToJsonOptions
• returns : Array array containing objects found in JSON file.

  const objectsList = await getJsonArray({
    inputDir: "./data_input_files",
    inputKeys: ["city", "region"],
    inputFileNameList: [
      "general_rates.csv",
      "premium_rates.csv",
      "danger_zones.csv",
    ],
    outputDir: "./data_output_json",
    outputFileName: "delivery_rates",
    columnDelimiter: ",",
  });
  

  Example

  Given three CSV files as input

  File 1

  city	region	deliverySchedule	rate	deliveryInstruction
  AHUACHAPAN	AHUACHAPAN	LUNES-MIERCOLES-VIERNES	3	Contacto por telefono
  APOPA	SAN SALVADOR	DE LUNES A SABADO	3
  AYUTUXTEPEQUE	SAN SALVADOR	DE LUNES A SABADO	3
  MEJICANOS	SAN SALVADOR	DE LUNES A SABADO	3
  SAN SALVADOR	SAN SALVADOR	DE LUNES A SABADO	3

  File 2

  city	region	deliverySchedule	rate
  Apopa	San Salvador	Lunes a Sabado	4
  Ayutuxtepeque	San Salvador	Lunes a Sabado	4
  San Salvador	San Salvador	Lunes a Sabado	4

  File 3

  locality	city	region	risk	deliveryInstruction
  Madre Selva	Apopa	San Salvador	Delincuencia	PUNTO DE ENCUENTRO
  Popotlan	Apopa	San Salvador	Delincuencia	PUNTO DE ENCUENTRO
  EL TIGRE	AHUACHAPAN	AHUACHAPAN	Delincuencia	PUNTO DE ENCUENTRO
  CTON EL ROSARIO	AHUACHAPAN	AHUACHAPAN	Delincuencia	PUNTO DE ENCUENTRO

  Expected MERGED data :

  city	region	locality	risk	deliveryInstruction	deliverySchedule	rate
  Apopa	San Salvador	Madre Selva	Delincuencia	PUNTO DE ENCUENTRO	DE LUNES A SABADO	4
  Apopa	San Salvador	Popotlan	Delincuencia	PUNTO DE ENCUENTRO	DE LUNES A SABADO	4
  AHUACHAPAN	AHUACHAPAN	EL TIGRE	Delincuencia	PUNTO DE ENCUENTRO	LUNES-MIERCOLES-VIERNES	3
  AHUACHAPAN	AHUACHAPAN	CTON EL ROSARIO	Delincuencia	PUNTO DE ENCUENTRO	LUNES-MIERCOLES-VIERNES	3
  Ayutuxtepeque	San Salvador				Lunes a Sabado	4
  San Salvador	San Salvador				Lunes a Sabado	4
  MEJICANOS	SAN SALVADOR				DE LUNES A SABADO	3

  Output json

  Execute :

  mergeCsvFilesToJsonArray({
    inputDir: "./data_input_files",
    inputKeys: ["city", "region"],
    inputFileNameList: [
      "general_rates.csv",
      "premium_rates.csv",
      "danger_zones.csv",
    ],
    outputDir: "./data_output_json",
    outputFileName: "delivery_rates",
    columnDelimiter: ",",
  });
  

  Result in file saved :

  [
    {
      "city": "AHUACHAPAN",
      "region": "AHUACHAPAN",
      "deliverySchedule": "LUNES-MIERCOLES-VIERNES",
      "rate": "3",
      "deliveryInstruction": "PUNTO DE ENCUENTRO",
      "locality": "EL TIGRE",
      "risk": "DELINCUENCIA"
    },
    {
      "city": "APOPA",
      "region": "SAN SALVADOR",
      "deliverySchedule": "LUNES A SABADO",
      "rate": "4",
      "deliveryInstruction": "PUNTO DE ENCUENTRO",
      "locality": "MADRE SELVA",
      "risk": "DELINCUENCIA"
    },
    {
      "city": "AYUTUXTEPEQUE",
      "region": "SAN SALVADOR",
      "deliverySchedule": "LUNES A SABADO",
      "rate": "4",
      "deliveryInstruction": ""
    },
    {
      "city": "MEJICANOS",
      "region": "SAN SALVADOR",
      "deliverySchedule": "DE LUNES A SABADO",
      "rate": "3",
      "deliveryInstruction": ""
    },
    {
      "city": "SAN SALVADOR",
      "region": "SAN SALVADOR",
      "deliverySchedule": "LUNES A SABADO",
      "rate": "4",
      "deliveryInstruction": ""
    },
    {
      "city": "APOPA",
      "region": "SAN SALVADOR",
      "deliverySchedule": "LUNES A SABADO",
      "rate": "4",
      "deliveryInstruction": "PUNTO DE ENCUENTRO",
      "updated": true,
      "locality": "POPOTLAN",
      "risk": "DELINCUENCIA"
    },
    {
      "city": "AHUACHAPAN",
      "region": "AHUACHAPAN",
      "deliverySchedule": "LUNES-MIERCOLES-VIERNES",
      "rate": "3",
      "deliveryInstruction": "PUNTO DE ENCUENTRO",
      "updated": true,
      "locality": "CTON EL ROSARIO",
      "risk": "DELINCUENCIA"
    }
  ]
  

  Output json with group by

  Execute :

  mergeCsvFilesToJsonArray({
    inputDir: "./data_input_files",
    inputKeys: ["city", "region"],
    inputFileNameList: [
      "general_rates.csv",
      "premium_rates.csv",
      "danger_zones.csv",
    ],
    outputDir: "./data_output_json",
    outputFileName: "delivery_rates",
    columnDelimiter: ",",
    groupBy: { groupByKey: "region", groupedArrayProperty: "cities" },
  });
  

  Result in file saved :

  [
    {
      "region": "AHUACHAPAN",
      "cities": [
        {
          "city": "AHUACHAPAN",
          "region": "AHUACHAPAN",
          "deliverySchedule": "LUNES-MIERCOLES-VIERNES",
          "rate": "3",
          "deliveryInstruction": "PUNTO DE ENCUENTRO",
          "locality": "EL TIGRE",
          "risk": "DELINCUENCIA"
        },
        {
          "city": "AHUACHAPAN",
          "region": "AHUACHAPAN",
          "deliverySchedule": "LUNES-MIERCOLES-VIERNES",
          "rate": "3",
          "deliveryInstruction": "PUNTO DE ENCUENTRO",
          "updated": true,
          "locality": "CTON EL ROSARIO",
          "risk": "DELINCUENCIA"
        }
      ]
    },
    {
      "region": "SAN SALVADOR",
      "cities": [
        {
          "city": "APOPA",
          "region": "SAN SALVADOR",
          "deliverySchedule": "LUNES A SABADO",
          "rate": "4",
          "deliveryInstruction": "PUNTO DE ENCUENTRO",
          "locality": "MADRE SELVA",
          "risk": "DELINCUENCIA"
        },
        {
          "city": "AYUTUXTEPEQUE",
          "region": "SAN SALVADOR",
          "deliverySchedule": "LUNES A SABADO",
          "rate": "4",
          "deliveryInstruction": ""
        },
        {
          "city": "MEJICANOS",
          "region": "SAN SALVADOR",
          "deliverySchedule": "DE LUNES A SABADO",
          "rate": "3",
          "deliveryInstruction": ""
        },
        {
          "city": "SAN SALVADOR",
          "region": "SAN SALVADOR",
          "deliverySchedule": "LUNES A SABADO",
          "rate": "4",
          "deliveryInstruction": ""
        },
        {
          "city": "APOPA",
          "region": "SAN SALVADOR",
          "deliverySchedule": "LUNES A SABADO",
          "rate": "4",
          "deliveryInstruction": "PUNTO DE ENCUENTRO",
          "updated": true,
          "locality": "POPOTLAN",
          "risk": "DELINCUENCIA"
        }
      ]
    }
  ]