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.

traynor · Accepted Answer · 2023-10-25 16:53:00Z

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);

edited Oct 25, 2023 at 16:53

answered Oct 21, 2023 at 20:53

traynor

10.4k3 gold badges15 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

hemant Over a year ago

The column headers part is working fine. But the dates columns are still showing numbers. I have added cellDates: true in options. Is there any other setting?

traynor Over a year ago

right, it goes with readFile actually.. try the updated code

hemant Over a year ago

An update. Just to test something when I used slice(1) without opts, xlsx.utils.sheet_to_json(file.Sheets[file.SheetNames[0]]).slice(1); the row 2 onwards gets sliced. i.e. data is parsed from 3rd row onwards. It looks like while parsing data the header row is skipped. Therefore, I had to remove slice(1) from the code. Hope my understanding is correct and there is no other setting to this.

traynor Over a year ago

sure, you can omit that and adapt to your input file and data, but, no, nothing is skipped with these options. Not sure why, it probably depends on your input file.. Try testing some dummy file with a few rows, it should work without skipping anything, and if the first row contains headers, then without slice it should end in the results as well..

traynor Over a year ago

ah, right, you're missing object property. it's default behavior, add defval: '' to opts, it will then insert the value you provide for that header/object property when the cell is blank: If defval is not specified, null and undefined values are skipped normally. If specified, all null and undefined points will be filled with defval

|

Collectives™ on Stack Overflow

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

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related