Spreadsheet node numbers with comma's instead of dots

I’ve build a workflow that merges 2 .csv files into 1 Excel file.

Everything works fine, except for numbers with comma’s. With Dutch/The Netherlands as regional settings, the decimal separator is a comma instead of a dot.

The first problem arises when importing. The comma gets lost and the number is not the same. In this example 10x bigger!

Screenshot 2022-03-09 at 16.03.44

Screenshot 2022-03-09 at 16.04.06

When I ad the option raw, the value is right, but is treated as a string instead of a number.

Screenshot 2022-03-09 at 16.04.25

When I then export the merged file, these fields/columns get treated as strings as well.

Screenshot 2022-03-09 at 16.10.31

Any ideas on how to fix this (localisation) issue? What I’m really for, is an option to import numbers with a specific decimal separator and then internally converting it to number. And then of course also the option to specify number separators on export.

Hey @dickhoning, I’ll need to try this on my end first but just to clarify: Is the problem with reading the file (= is the number already wrong after reading it in n8n) or with writing to a file?

Could you share an example of a CSV file you are reading in n8n using which the problem can be reproduced and confirm which application you use to open the file?

Hi @MutedJam unless I use the option RAW, the problem already starts when reading in the file. And when I use RAW, the number is a string and therefor the output is not a number cel in Excel.

This is an example of the contents of the .csv file:

!PK ConctactNr;_gBBBPtype;Summary1;Summary2;PeriodYYYYMM;ContactCode;PriceTotalSummary;MarginSummary
3750514;Billings;Development;ecOrganizer;202112;D72330;5194,88;2202,11

And I’m reading the output with Excel on macOS with region set to Netherlands.

If you want, I can send my workflow with both .csv files.

Many thanks for confirming @dickhoning! I assume you know which fields will contain numeric values? If so, my suggestion would be to use a bit of JavaScript to manually parse these numbers to avoid running into trouble with the comma.

When reading your spreadsheet using the RAW option, you could afterwards use an Function Item node running code like this:

item.MarginSummary = parseFloat(item.MarginSummary.replace(',', '.'));
item.PriceTotalSummary = parseFloat(item.PriceTotalSummary.replace(',', '.'));

return item;

In this example the comma would first be replaced with a dot (this is what .replace(',', '.') does). Afterwards, parseFloat() would take care of the rest.

1 Like

@MutedJam :pray: that does the trick!

1 Like

Dear @MutedJam,
I wonder if I can do something similar inside a Set node, I have the same issue than @dickhoning but instead of reading form a Sheet, I need to transform the data before sending to Append on the Sheet.

Thanks in advance.