Convert XLS to CSV on the server in Node

Question

I have a client-side web application, with a very minimal node server to access some data that the client can't. One of these things is excel spreadsheets with .xls extensions.

I'm trying to get my server set up to download the xls, convert it to csv, and then send it back to the client. I've got the download part done, and I'm sure I can figure out the "send back" part, but I can't for the life of me find a good library to convert from xls to csv.

Can someone point me to a library that can do that in a simple fashion? The excel file is just one sheet, no complicated workbooks or anything.

Or is there another way of doing this I'm not thinking of?

score 44 · Answer 1 · answered Mar 09 '19 at 22:32

44

I am using this package to convert XLSX to CSV: https://www.npmjs.com/package/xlsx

XLSX = require('xlsx');

const workBook = XLSX.readFile(inputFilename);
XLSX.writeFile(workBook, outputFilename, { bookType: "csv" });

answered Mar 09 '19 at 22:32

Cassio

1,347
13
15

2

This is very elegant and an easy solution so I think this should be the default answer. – Koen Dec 26 '19 at 14:45
3

This looks elegant, but it creates a csv file with empty values from the empty cells in your excel sheet; so in case you will process the csv file (e.g. loop through its value) this will be very dramatic for your CPU and memory. You need to iterate through the length of data per sheet; hence, I consider the chosen answer more robust. – Mabu Jan 16 '20 at 16:17

score 37 · Accepted Answer · answered Dec 17 '15 at 19:44

37

There is no library that I am aware of, but you could use node-xlsx to parse the excel file, get the rows and make the CSV yourself. Here's an example:

var xlsx = require('node-xlsx');
var fs = require('fs');
var obj = xlsx.parse(__dirname + '/test.xls'); // parses a file
var rows = [];
var writeStr = "";

//looping through all sheets
for(var i = 0; i < obj.length; i++)
{
    var sheet = obj[i];
    //loop through all rows in the sheet
    for(var j = 0; j < sheet['data'].length; j++)
    {
            //add the row to the rows array
            rows.push(sheet['data'][j]);
    }
}

//creates the csv string to write it to a file
for(var i = 0; i < rows.length; i++)
{
    writeStr += rows[i].join(",") + "\n";
}

//writes to a file, but you will presumably send the csv as a      
//response instead
fs.writeFile(__dirname + "/test.csv", writeStr, function(err) {
    if(err) {
        return console.log(err);
    }
    console.log("test.csv was saved in the current directory!");
});

answered Dec 17 '15 at 19:44

heinst

8,520
7
41
77

Thanks. Could you give sample example code for writing to a CSV here rather than `console.log("test.csv was saved in the current directory!");` – Phil May 17 '16 at 15:30
2

@philipoghenerobobalogun did you try it? because it does write to a file. Thats what `fs.writeFile(...` is doing – heinst May 17 '16 at 16:51
Yh. I've tried it out...it works. Was too tired at that time to give it a shot ;) – Phil May 18 '16 at 12:32
1

Works great - just watch out it doesn't handle commas in the values, you'll need to add double quotes and escape values containing double-quotes – SliverNinja - MSFT Jun 05 '17 at 18:48
Will this work in timely fashion if we want to handle multiple requests? – Dev G Feb 28 '18 at 18:50
It take like 2GB of memory to parse a 40MB file. It s the same with the solution with xlsx lib. – Clem Jul 13 '22 at 14:30

Convert XLS to CSV on the server in Node

2 Answers2

Linked