CODEWITHSUNDEEP
angularjsjsonnode.js
Roby Sottini
Roby Sottini

Reputation: 2265

Convert JSON object to CSV and save it to a file

A user press a button and it executes a SQL query to my database using NodeJS and the result is a JSON object (one array with objects). After that the result must be saved in a file (the user need the file).

The problem: it can't be a JSON. The format is only the values separated by the simbol ";" and one object per line.

Example of the file's content that I need to create (it is like a CSV):

1;tree;green
2;car;red
3;Robert's house;white

This result query has around 18000 rows. It is big.

I am using the replace method but I have problems with values with the simbol ' like in Robert's house because the console interpreted as a begining of a string.

Then in the client side I use:

$window.open('data:text/csv,' + encodeURIComponent(myString));

And here the simbols ' start the problem.

I would like to know how to transform a JSON to that format (under NodeJS) and how to create the file.

Upvotes: 1

Views: 2066

Answers (1)

Miha Jamsek
Miha Jamsek

Reputation: 1273

Denote your string with double quote " and then you can use single quote ' inside of it. example:

var sql = "SELECT * FROM table WHERE USER='John'";

Now, to avoid reading character by character and therefore getting problems with single quotes ', use .split(";") method which will first divide your line into separate words, which you can then assign to columns in CSV

EDIT: I wrote a code that reads a .json file (you get it from database, same thing) and it outputs csv file.

var fs = require('fs');
var obj = JSON.parse(fs.readFileSync("data.json", "utf-8"));
var stream = fs.createWriteStream("data.csv");

stream.once('open', function(fd){
    for (var i = 0; i < obj.person.length; i++) {
        var string = obj.person[i].id + ";" + obj.person[i].name + ";" + obj.person[i].age + "\n";
        stream.write(string);
    }
    stream.end();
});

You can then do what you want with CSV file.

EDIT: As asked, here is how I send file in one of my projects:

router.get('/download/:name', function (req, res, next) {
    var file = './public/data/' + req.params.name; //file is saved in /public/data first
    var filename = path.basename(file);
    var mimetype = mime.lookup(file);
    res.setHeader('Content-disposition', 'attachment; filename=' + filename);
    res.setHeader('Content-type', mimetype);

    var filestream = fs.createReadStream(file);
    filestream.pipe(res);
});

Upvotes: 1

Related Questions