BlueBallPanda BlueBallPanda - 4 years ago 82
Javascript Question

Writing float numbers into csv file in node

I am trying to create a CSV file in nodejs from json data that I get from database.

I create an array of objects as my data

var allExpenses = [];
expenses.forEach(function(expense) {
allExpenses.push({
amount: expense.amount,
identificator: expense.identificator,
person: expense.person.last_name + ' ' + expense.person.first_name,
date: moment.tz(expense.date, "Europe/Budapest").format("D.M.YYYY"),
description: expense.description || '-'
});
});


then I use json2csv module to create csv data and write it to file via fs

//create csv
var csvFields = ['amount', 'identificator', 'person', 'date', 'description'];
var csv = require('json2csv')({data: allExpenses, fields: csvFields, del: ';'});

var filename = new Date().valueOf() + '_csv_expenses.csv';
var filepath = config.path + '/reports/' + filename;
fs.writeFile(filepath, '\ufeff' + csv, 'utf-8', function(err) {
if(err) {
console.log(err);
res.status(500);
res.json({success: false, msg: 'Error creating csv'});
} else {
console.log('csv file saved');
res.status(200);
res.json({success:true, filename: filename});
}
});


This creates the csv file correctly, I can open it in excel, but floating-point number are not written correctly and cant be used for SUM and other operations AND some of them are treated as DATE

formatting these numbers in excel via CTRL + 1 -> NUMBER doesnt help either, because then the values that were treated as DATE are completely off.

I tried adding the option excelStrings: true to the json2csv module, then the amounts are written correctly but are stringyfied (="0.456") and cannot be transformed into number either.

Any tips or advice I can try ?
Any help appreciated.

Answer Source

Okay I managed to find a solution,

I just had to make string from the amount and change the floating point to 'floating comma' f.e.:

var amountFormatted = expense.amount + '';
expense.forEach(...
amount: amountFormatted.replace('.', ',')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download