Multiple-csv-merge-to-json NPM

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

csv merge json multiple

csvtojson fs

@infinitebrahmanuniverse/nolb-multip @everything-registry/sub-chunk-2218

2 years ago

2 years ago

2 years ago

2 years ago