[FIXED] How to convert xlsx to json reading it from an ftp?

Issue

I'm trying to download a .xlsx file from a ftp, and converting it to a .json using XSLX module, and then writing it to a file. All this using Node.js

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

const c = new Client();

c.connect({
  host: '***.***.***.**',
  user: '*********',
  password: '*********',
});

c.on('ready', () => {
  c.get('/market.xlsx', (err, stream) => {
    if (err) throw err;
    stream.once('close', () => c.end());
    
    let content = '';
    stream.on('data', (chunk) => {
      content += chunk;
    });
    stream.on('end', () => {
      //I guess something is wrong here
      const json = XLSX.utils.sheet_to_json(content);
      fs.writeFileSync(
        './files/market-to-json.json',
        JSON.stringify(json, null, 2),
        'utf-8'
      );
    });
  });
});

My actual output in .json

[]

I struggled with this for a week and can't find solution, please help!.

Solution

sheet_to_json requires a worksheet object, so you actually need to read the contents first, and then pass the desired worksheet

try reading the contents as a buffer, and then pass the desired sheet once the stream is finished:

  c.get('/market.xlsx', (err, stream) => {
    if (err) throw err;
    stream.once('close', () => c.end());
    
    let content = [];
    stream.on('data', (chunk) => {
      content.push(chunk);
    });
    stream.on('finish', () => {
      
      const excel = XLSX.read(Buffer.concat(content), {type:'buffer'})
      
      const json = XLSX.utils.sheet_to_json(excel.Sheets[excel.SheetNames[0]]);

      fs.writeFileSync(
        './files/market-to-json.json',
        JSON.stringify(json, null, 2),
        'utf-8'
      );
    });
  });

Answered By - traynor

Answer Checked By - Marilyn (PHPFixing Volunteer)