How to separate a column into two different keys/values - csvtojson NodeJS

Question

I have a csv and need to convert it to json using nodejs, but parsing many things in the process to match the required fields where i need to upload the data, like hashing some info or changing the header names.

The problem I'm having is that I need to separate the column 'Name' into two different columns, one for the first name and one for the last name.

Am I having a wrong approach to the problem? Should I parse everything to match the required files like I'm trying to do or is not the best way?

CSV FILE

email,phone,Name,zipcode,country,gender,Price
[email protected],1-(650)-561-5622,Eliza Rabbit,94046,US,Female,"$15,00"

CODE

const csv=require('csvtojson');
const sha1 = require('sha1');

const csvFilePath ='filepath';

csv({
  trim: true,
  delimiter: [","," "],
  noheaders: false,
  headers: ['em', 'ph', 'fn', 'zp', 'country', 'ge', 'price'],
  colParser: {
    "ph":function(item){
    return sha1(item);
  }}})
  .fromFile(csvFilePath)
  .then((jsonObj)=>{
      console.log(jsonObj);
});

OUTPUT

[
  {
    em: '[email protected]',
    ph: '937a997da6b9f647b3b378ef27f91952eb09fd26',
    fn: 'Eliza Rabbit',
    zp: '94046',
    country: 'US',
    ge: 'Female',
    price: '$15,00'
  }
]

My first idea was to set delimiter to separate columns by commas or spaces and then define new headers manually and putting a space in the NAME column header:

  noheaders: false,
  headers: ['em', 'ph', 'firstName lastName', 'zp', 'country', 'ge', 'price'],
  delimiter: [","," "],

Dumb, I know, of course didn't work. Just logged: 'fistName lastName': 'Eliza Rabbit'

Answer 1

Using CSVParse for Node.js. It makes life easier.

CSV file

email,phone,Name,zipcode,country,gender,Price
[email protected],1-(650)-561-5622,Eliza Rabbit,94046,US,Female,"$15,00"
[email protected],1-(650)-734-4321,Tom Cruise,07324,US,Male,"$32,00"

Code

const fs = require("fs");
const sha1 = require('sha1');
const { parse } = require("csv-parse");

const converted_data = [];
fs.createReadStream("./data.csv")
    .pipe(parse({
        delimiter: ",",
        from_line: 2   // skip head row
    }))
    .on("data", (row) => {
        // split first name and last name
        const names = row[2].split(" ");
        converted_data.push({
            'em': row[0],      // email
            'ph': sha1(row[1]),    // phone number
            'firstName': names[0], // first name
            'lastName': names[1],  // last name
            'zp': row[3],      // Zip code
            'country': row[4], // Country
            'ge': row[5],      // gender
            'price': row[6],   // Price
        });
    })
    .on("error", (error) => {
        console.log(error.message);
    })
    .on("end", () => {
        console.log("finished");
        console.log(converted_data);
    });

Install libraries

$ npm install fs sha1 csv-parse

Result

$ node convert-csv.js
finished
[
  {
    em: '[email protected]',
    ph: '937a997da6b9f647b3b378ef27f91952eb09fd26',
    firstName: 'Eliza',
    lastName: 'Rabbit',
    zp: '94046',
    country: 'US',
    ge: 'Female',
    price: '$15,00'
  },
  {
    em: '[email protected]',
    ph: '068132acfc7bc6c876b84721463de310c5e0aa6c',
    firstName: 'Tom',
    lastName: 'Cruise',
    zp: '07324',
    country: 'US',
    ge: 'Male',
    price: '$32,00'
  }
]

Answer 2

Thank you so much for your answer. This is great and will try it. Came back to post the solution I found to do it using the libraries I had, not sure it's the best way to do it, probably not haha, but when logging the data it shows ok.

    const converter = csv({
       noheaders: false,
       headers: ['em','email2', 'email3', 'ph', 'external_id', 'fullname', 'zp', 
       'country', 'ge', 'event_name', 'event_time', 'value' ],
       colParser: {
          "em": function (item) {
             return sha1(item)
          },
          "ph":function(item){
             return sha1(item)
          },
          "external_id":function(item){
             return sha1(item)
          },
          "fullname":function(item){
             return item
          },
          "zp":function(item){
             return sha1(item)
          },
          "country":function(item){
             return sha1(item)
          },
          "ge":function(item){
             return sha1(item)
          },
          "event_name":function(item){
             return item
          },
          "event_time":function(item){
             return item
          },
          "value":function(item){
             return parseFloat(item.replace(/[^0-9\.,-]+/g,""))
          }}
       },
       )
       .fromFile(csvFilePath)
       .then((fileDataObject)=> {
          const finalData = fileDataObject.map( item => {
             const data = item
             const [lastName] = item.fullname.split(" ").slice(1);
             const [firstName] = item.fullname.split(" ").slice(0);
             item.fn = sha1(firstName);
             item.ln = sha1(lastName);
             return item
          })
          console.log(finalData)
          return finalData
       });

```