I am passing a file to this function and i want to cnvert the data to json but for the specific column DOB 05-04-2001 to random number such as 36986. I want the exact date entered as a response from this function, How to do this ? Can anyone help me out yrr.
static convertExcelFileToJsonUsingXlsxTrimSpaces = (path: string) => {
// Read the file using pathname
const file = xlsx.readFile(path);
// Grab the sheet info from the file
const sheetNames = file.SheetNames;
const totalSheets = sheetNames.length;
// Variable to store our data
const parsedData: any[] = [];
// Loop through sheets
for (let i = 0; i < totalSheets; i += 1) {
// Convert to json using xlsx
const sheetData = xlsx.utils.sheet_to_json(file.Sheets[sheetNames[i]], { raw: true });
console.log(sheetData);
// Process each row to handle tab-separated values
const processedData: any[] = [];
sheetData.forEach((row: any) => {
const processedRow: any = {};
Object.keys(row).forEach((key) => {
processedRow[key] = row[key].toString().trim(); // Convert to string and trim whitespace
});
processedData.push(processedRow);
});
// Add the sheet's processed data to our data array
parsedData.push(...processedData);
}
return parsedData;
};
It's not random number, it's default parsing for date cells.
To get date value, add cellDates: true flag to parsing options:
Try this:
const file = xlsx.readFile(path, { cellDates: true });
see the docs:
By default, Excel stores dates as numbers with a format code that specifies date processing. For example, the date 19-Feb-17 is stored as the number 42785 with a number format of d-mmm-yy
The default behavior for all parsers is to generate number cells. Setting cellDates to true will force the generators to store dates.