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.
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.
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 |
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: ",",
});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: ",", });
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 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" } ]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" } ] } ]