Is xlsx to csv conversion possible ? #2789

New Issue

2022-09-13T06:30:58Z

jithinb68 commented

2022-09-13 06:30:58 +00:00

(Migrated from github.com)

Found this piece of code in the docs, but it doesn't seem to work. I have an input which is an s3 url of xlsx file
input https://modulus.s3-****/Content.xlsx

var output_file_name = "out.csv";
var stream = XLSX.stream.to_csv(worksheet);
stream.pipe(fs.createWriteStream(output_file_name));

Is this the right way to do when input is an url also if its so how to generate worksheet from xlsx file

Found this piece of code in the docs, but it doesn't seem to work. I have an input which is an s3 url of xlsx file input https://modulus.s3-****/Content.xlsx ``` var output_file_name = "out.csv"; var stream = XLSX.stream.to_csv(worksheet); stream.pipe(fs.createWriteStream(output_file_name)); ``` Is this the right way to do when input is an url also if its so how to generate worksheet from xlsx file

SheetJSDev commented

2022-09-13 06:52:31 +00:00

(Migrated from github.com)

Assuming you are running in a newer version of NodeJS, use fetch to download the data and loop through the worksheets:

const XLSX = require("xlsx"), fs = require("fs");
const url = "...";

(async() => {
  const wb = XLSX.read(await (await fetch(url)).arrayBuffer());
  wb.SheetNames.forEach((name, idx) => {
    const ws = wb.Sheets[name];
    fs.writeFileSync(`out.${idx}.csv`, XLSX.utils.sheet_to_csv(ws));
  });
})();

If the file is not public, the AWS demo in the docs has an example using the official aws-sdk module to fetch an object, parse, and log the first worksheet

Assuming you are running in a newer version of NodeJS, use `fetch` to download the data and loop through the worksheets: ```js const XLSX = require("xlsx"), fs = require("fs"); const url = "..."; (async() => { const wb = XLSX.read(await (await fetch(url)).arrayBuffer()); wb.SheetNames.forEach((name, idx) => { const ws = wb.Sheets[name]; fs.writeFileSync(`out.${idx}.csv`, XLSX.utils.sheet_to_csv(ws)); }); })(); ``` If the file is not public, the [AWS demo in the docs](https://docs.sheetjs.com/docs/demos/aws#s3-storage) has an example using the official aws-sdk module to fetch an object, parse, and log the first worksheet

👍 1

jithinb68 commented

2022-09-13 08:12:40 +00:00

(Migrated from github.com)

Thanks @SheetJSDev . This works independently.
But in my case I don't want to write into the file system. this Nodejs part which does the conversion need to send a response to the frontend and the frontend needs to download the file as csv. frontend send the url as request and backend needs to convert it and send back to UI to download. Can you please help me with that piece as well. Something similar to

Thanks @SheetJSDev . This works independently. But in my case I don't want to write into the file system. this Nodejs part which does the conversion need to send a response to the frontend and the frontend needs to download the file as csv. frontend send the url as request and backend needs to convert it and send back to UI to download. Can you please help me with that piece as well. Something similar to

SheetJSDev commented

2022-09-13 17:14:01 +00:00

(Migrated from github.com)

XLSX.utils.sheet_to_csv(ws) is a string, you can just send that in the response.

`XLSX.utils.sheet_to_csv(ws)` is a string, you can just send that in the response.

👍 1

jithinb68 commented

2022-09-14 04:33:12 +00:00

(Migrated from github.com)

Thanks @SheetJSDev , Will this work with axios as well ? It's throwing an error response.data.arrayBuffer is not a function

jithinb68 commented

2022-09-14 05:18:02 +00:00

(Migrated from github.com)

It's throws error in internal of XLSX.read ->TypeError: f.slice is not a function

`

app.get('/csv', async (req, res) => {
try {
const s3uri = req.query.s3uri
const wb = XLSX.read(await axios(s3uri), {responseType:'arraybuffer'});
const sheetName = wb.SheetNames[0]
const ws = wb.Sheets[sheetName];
const rawCsv = XLSX.utils.sheet_to_csv(ws);
res.setHeader('Content-Type', 'text/csv');
res.send(rawCsv);
} catch (error) {
res.status(500).send("Server Error");
console.error(error);
}
})`

It's throws error in internal of XLSX.read ->_TypeError: f.slice is not a function_ ` > app.get('/csv', async (req, res) => { > try { > const s3uri = req.query.s3uri > const wb = XLSX.read(await axios(s3uri), {responseType:'arraybuffer'}); > const sheetName = wb.SheetNames[0] > const ws = wb.Sheets[sheetName]; > const rawCsv = XLSX.utils.sheet_to_csv(ws); > res.setHeader('Content-Type', 'text/csv'); > res.send(rawCsv); > } catch (error) { > res.status(500).send("Server Error"); > console.error(error); > } > })`

SheetJSDev commented

2022-09-14 05:19:45 +00:00

(Migrated from github.com)

misplaced close parentheses:

const wb = XLSX.read(await axios(url, {responseType:'arraybuffer'}));

misplaced close parentheses: ```js const wb = XLSX.read(await axios(url, {responseType:'arraybuffer'})); ```

jithinb68 commented

2022-09-14 05:25:52 +00:00

(Migrated from github.com)

@SheetJSDev Still the same issue after correcting the line -> TypeError: f.slice is not a function

Corrected it, we needed response.data to be passed. Thanks @SheetJSDev

Corrected code

app.get('/csv', async (req, res) => {
try {
const s3uri = req.query.s3uri
const response = await axios(s3uri, {responseType:'arraybuffer'})
const wb = XLSX.read(response.data);
const sheetName = wb.SheetNames[0]
const ws = wb.Sheets[sheetName];
const rawCsv = XLSX.utils.sheet_to_csv(ws);
res.setHeader('Content-Type', 'text/csv');
res.send(rawCsv);
} catch (error) {
res.status(500).send("Server Error");
console.error(error);
}
})

@SheetJSDev Still the same issue after correcting the line -> TypeError: f.slice is not a function Corrected it, we needed response.data to be passed. Thanks @SheetJSDev Corrected code > app.get('/csv', async (req, res) => { try { const s3uri = req.query.s3uri const response = await axios(s3uri, {responseType:'arraybuffer'}) const wb = XLSX.read(response.data); const sheetName = wb.SheetNames[0] const ws = wb.Sheets[sheetName]; const rawCsv = XLSX.utils.sheet_to_csv(ws); res.setHeader('Content-Type', 'text/csv'); res.send(rawCsv); } catch (error) { res.status(500).send("Server Error"); console.error(error); } })

Sign in to join this conversation.