This script is for converting an xlsx file to a json object.

xlsx_to_json.js

const fs = require("fs");
const xlsx = require("xlsx");

async function xlsxToJson(inputFile, outputFile) {
  return new Promise((resolve, reject) => {
    try {
      console.log(`Converting ${inputFile} to JSON...`);

      if (!fs.existsSync(inputFile)) {
        return reject(new Error(`Input file ${inputFile} does not exist`));
      }

      // Read workbook
      const workbook = xlsx.readFile(inputFile);
      const sheetNames = workbook.SheetNames;

      if (sheetNames.length === 0) {
        return reject(new Error("No sheets found in the Excel file"));
      }

      // Convert first sheet to JSON (you can loop over all sheets if needed)
      const sheet = workbook.Sheets[sheetNames[0]];
      let jsonArray = xlsx.utils.sheet_to_json(sheet, {
        raw: false, // convert numbers/dates to strings
        blankrows: false,
      });

      // Clean rows
      const cleanedArray = jsonArray.map((row) => {
        const cleanedRow = {};
        for (const key in row) {
          if (row[key]?.toString().trim()) {
            cleanedRow[key] = row[key];
          }
        }
        return cleanedRow;
      });

      // Write JSON
      fs.writeFileSync(outputFile, JSON.stringify(cleanedArray, null, 2));

      console.log(`Successfully parsed ${cleanedArray.length} records`);
      console.log(`JSON saved to ${outputFile}`);

      // Preview first 3 records
      console.log("\nPreview of first 3 records:");
      cleanedArray.slice(0, 3).forEach((record, i) => {
        console.log(`Record ${i + 1}:`, record);
      });

      resolve(cleanedArray);
    } catch (err) {
      reject(err);
    }
  });
}

// CLI usage
if (require.main === module) {
  const args = process.argv.slice(2);

  if (args.length < 1) {
    console.log("Usage: node xlsx_to_json.js <input.xlsx> [output.json]");
    console.log("Example: node xlsx_to_json.js data.xlsx converted.json");
    process.exit(1);
  }

  const inputFile = args[0];
  const outputFile = args[1] || inputFile.replace(/\.xlsx$/i, ".json");

  xlsxToJson(inputFile, outputFile)
    .then(() => console.log("Conversion completed successfully!"))
    .catch((err) => {
      console.error("Conversion failed:", err.message);
      process.exit(1);
    });
}

module.exports = { xlsxToJson };