XLSX nodejs does not recognize comma ',' as a separator rather takes it as a whole number

I have a node js service to read and process data from a xls or a xlsx file.

private fromBankStatementFileToManualJournalRowsJSONFromTTK = (file: any) => {
    const fileJSON = [];
    const sheet = this.readBankStatement(file);

    const description = sheet['C2']?.v;

    const unformatedDate = sheet['A2']?.v;
    const splittedDatePart = unformatedDate?.split('.');
    const formattedDate = `${splittedDatePart[2]}-${splittedDatePart[1]}-${splittedDatePart[0]}`;

    let data = XLSX.utils.sheet_to_json(sheet, { header: 1 });

    let start_index = null;
    let end_index = null;

    // Find the index of 'должи' and 'Вкупно:'
    data.forEach((sublist: any, index: any) => {
      if (sublist.includes('Должи')) {
        start_index = index;
      } else if (sublist.includes('Вкупно:')) {
        end_index = index;
      }
    });

    // Process data between 'должи' and 'Вкупно:'
    if (start_index !== null && end_index !== null) {
      const extractedData = data.slice(start_index, end_index + 0);

      data = extractedData;
    }

    for (let i = 1; i < data.length; i++) {
      const debit = data[i][5];
      const credit = data[i][4];
      const partner = data[i][1]; // Check if debit and credit are numeric values
      const isDebitNumeric = !isNaN(parseFloat(debit));
      const isCreditNumeric = !isNaN(parseFloat(credit));

      if (isDebitNumeric && isCreditNumeric && partner !== undefined) {
        // Round only if the values are numeric
        const roundedDebit = parseFloat(debit).toFixed(2);
        const roundedCredit = parseFloat(credit).toFixed(2);

        fileJSON.push({
          description,
          transactionDate: formattedDate,
          debit: roundedDebit,
          credit: roundedCredit,
          partnerNameFromFile: partner.toString(),
        });
      }
    }

    return fileJSON;
  };

The debit and credit in the file is as such: 700,00.

I think the issue is in when I get read the file and save it to the data variable.

This is the data array object:

"translationOfFile": [
            {``your text``
                "description": "[AutoProv]Надомест за водење на сметка за 9.2023",
                "transactionDate": "2023-09-30",
                "debit": "70000.00",
                "credit": "0.00",
                "partnerNameFromFile": "Company"
                "clientPartner": null
            },
            {
                "description": "[AutoProv]Надомест за водење на сметка за 9.2023",
                "transactionDate": "2023-09-30",
                "debit": "5000.00",
                "credit": "0.00",
                "partnerNameFromFile": "Company",
                "clientPartner": null
            },

I don't want to add a dot at the last 2 numbers like such:

70000
700.00

Because I fear this won't always be as such.

EDIT: since the ',00' is always static in my country i ended up removing the last 2 zeros and then adding them with a .00.

I think we using "Macedonian" language. I convert by English and answer for your question.

This is your input data.xlsx

This is looking for output data

[
  {
    "description": "Compensation 1",
    "transactionDate": "2023-09-30",
    "debit": "70000.00",
    "credit": "0.00",
    "partnerNameFromFile": "Company",
    "clientPartner": "null"
  },
  {
    "description": "Compensation 2",
    "transactionDate": "2023-09-30",
    "debit": "5000.00",
    "credit": "0.00",
    "partnerNameFromFile": "Company",
    "clientPartner": "null"
  },
  {
    "description": "Another Desc",
    "transactionDate": "2023-09-30",
    "debit": "3000.00",
    "credit": "0.00",
    "partnerNameFromFile": "Another Company",
    "clientPartner": "null"
  },
  {
    "description": "Yet Another Desc",
    "transactionDate": "2023-09-30",
    "debit": "1000.00",
    "credit": "0.00",
    "partnerNameFromFile": "Yet Another Comp",
    "clientPartner": "null"
  }
]

This is my demo code using "xlsx" library

Demo code

Save as "read-excel.js"

const XLSX = require('xlsx');
const fs = require('fs');

const fromBankStatementFileToManualJournalRowsJSONFromTTK = function (file) {
  const fileJSON = [];
  const workbook = XLSX.readFile(file);
  const sheet = workbook.Sheets[workbook.SheetNames[0]];

  // Loop through each row in the Excel sheet
  for (let i = 2; ; i++) { // Start from row 2 (assuming the header is in row 1)
    const descriptionCell = sheet['A' + i];
    const transactionDateCell = sheet['B' + i];
    const debitCell = sheet['C' + i];
    const creditCell = sheet['D' + i];
    const partnerNameFromFileCell = sheet['E' + i];
    const clientPartnerCell = sheet['F' + i];

    // Check if we have reached the end of the data
    if (!descriptionCell) {
      break;
    }

    const description = descriptionCell.v;
    const transactionDate = transactionDateCell ? transactionDateCell.v : '';
    const debit = debitCell ? debitCell.v.toString().replace(',', '.') : ''; // Replace commas with dots 
    const credit = creditCell ? creditCell.v.toString().replace(',', '.') : ''; // Replace commas with dots 
    const partnerNameFromFile = partnerNameFromFileCell ? partnerNameFromFileCell.v : '';
    const clientPartner = clientPartnerCell ? clientPartnerCell.v : '';

    // Check if transactionDate, debit, and credit are non-empty strings
    if (transactionDate !== '' && debit !== '' && credit !== '') {
      fileJSON.push({
        description,
        transactionDate,
        debit,
        credit,
        partnerNameFromFile,
        clientPartner,
      });
    }
  }

  return fileJSON;
};

const inputFilePath = 'data.xlsx';

const outputJSON = fromBankStatementFileToManualJournalRowsJSONFromTTK(inputFilePath);

fs.writeFileSync('output.json', JSON.stringify(outputJSON, null, 2), 'utf-8');
console.log('Processing complete. Output saved as output.json');

Install dependency

npm install xlsx

Run it

node read-excel.js

Result