Using XLSX package in Node to upload n read Excel file with custom object keys

Question

Need to upload an Excel file to store data in a Node/Mogo application. I am able to upload the file and extract the data. The data is in the form of an Array of Objects with each Object having keys as the column titles of the excel file. I need to use my own Object Keys to replace the keys generated from column titles. This is to map the properties of a Model in which this data is getting saved. How do I achieve this, assuming the order of the columns is known to me.

Node Controller:

    const currDirName = __dirname;
    const basePath = currDirName.slice(0, currDirName.length - 11); // removing controllers from path
    const filePath = basePath + 'assets/uploadedFiles/' + req.savedFileName
    const file = xlsx.readFile(filePath);
    let fileData = [];
    const temp = xlsx.utils.sheet_to_json(file.Sheets[file.SheetNames[0]])
    temp.forEach(res => fileData.push(res))
  
    console.log('uploaded file data: ', fileData);

console output

[{
    'Sr.\r\nNo.': 1,
    'Name and address': 'Shop No. 3, ABC Arcade,\r\n' +     
      'Opp. PKA, XYZ Lane,\r\n' +
      'J.P. Road, Andheri (West), Mumbai - 400068. Maharashtra',
    PAN: 'AAAPA1234B',
    'Authorised Person (AP) code': 'MCX/AP/6337',
    'Date of Approval': 40570,
    'Date of withdrawal of approval': 41508,
    'Name of the Member through which AP was registered': 'ABC Commodity Services Pvt. Ltd.',        
    'Name of Directors / Shareholders': 'N.A.'
},....
]

Need to have my own keys for the above objects.

Answer 1

While reading the sheet, you can add your custom headers by providing headers option which contains array of strings which will be used as headers, i.e. object properties.

Also, use .slice(1) to skip the first row with headers.

To get date value, add cellDates: true flag to .readFile options (you'll get a date istead of a number in your example 'Date of Approval': 40570, as per docs: Dates).

Try this:

const currDirName = __dirname;
const basePath = currDirName.slice(0, currDirName.length - 11); // removing controllers from path
const filePath = basePath + 'assets/uploadedFiles/' + req.savedFileName

// pass options to parse date
const file = xlsx.readFile(filePath, { cellDates: true });

// add your headers array here
const opts = {
    header: ['header1', 'header2..etc']    
};

const file = xlsx.readFile(filePath);
// slice to skip headers row
let fileData = xlsx.utils.sheet_to_json(file.Sheets[file.SheetNames[0]], opts).slice(1);

console.log('uploaded file data: ', fileData);