CSV node: Regional settings in date, time and numeric variables

[jw_te_r]

I made a MySQL table to save sensor data which is defined like so:

var topic;

topic = "CREATE TABLE `datapoint` ( ";
topic = topic + "`no` INT NOT NULL AUTO_INCREMENT,";
topic = topic + "`date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,";
topic = topic + "`ppm_voor` INT NOT NULL,";
topic = topic + "`ppm_na` INT NOT NULL,";
topic = topic + "`massafactor_voor` DECIMAL(4,1) NOT NULL,";
topic = topic + "`massafactor_na` DECIMAL(4,1) NOT NULL,";
topic = topic + "`luchtomzet` INT NOT NULL,";
topic = topic + "`luchtomzet_max` INT NOT NULL,";
topic = topic + "PRIMARY KEY (no));";
msg.topic = topic;

return msg;

Saving data and executing queries works just fine.
I need to export the data to a CSV file, and tried using the CSV node. Unfortunately, here I'm running into trouble.
I Googled a lot, and tried several things, but until now without success.

If I query

SELECT no, DATE_FORMAT(date,'%Y-%m-%d') as datum, TIME(date) as tijd, ppm_voor, ppm_na, massafactor_voor, massafactor_na, luchtomzet, luchtomzet_max FROM `VOS`.`datapoint`;

I get all my numbers in the CSV. However, when importing my data in Excel, the fields of type DECIMAL(4,1) are imported using a decimal point, which means my Excel doesn't recognize them as numbers. When formatted like this, I can only import them as numbers when I set my locale to en_US, which then results in US style numbers, dates and times. Since I'm Dutch, that's not an option.

If I use 'format(massafactor_voor,1,'nl_NL')' in my query, the formatted numbers are saved as strings in the dataset (0.9 is saved as "0,9"); the csv node completely ignores these values, so Excel doesn't see them at all.

I can't decide where the problem lies.

Is this a MySQL problem, a CSV node problem, or am I doing something wrong?

[TotallyInformation]

@jw_te_r

Is this a MySQL problem, a CSV node problem, or am I doing something wrong?

Time for some intermediary debug outputs.

First from the SQL output, then from the CSV node. What do each look like? What is creating that as a full-stop rather than comma?

You perhaps also need to check and make sure that your server and your browser both have the correct country settings.

[jw_te_r]

That's what I have been doing all day
If I query:

SELECT no, DATE_FORMAT(date,'%Y-%m-%d') as datum, TIME(date) as tijd, ppm_voor, ppm_na, massafactor_voor, massafactor_na, luchtomzet, luchtomzet_max FROM `VOS`.`datapoint`;

I get this dataset from the MySQL node:

The CSV node turns it into this:

no;datum;tijd;ppm_voor;ppm_na;massafactor_voor;massafactor_na;luchtomzet;luchtomzet_max
76;2023-10-23;11:34:08;3717;472;1.3;0.9;17064;36000
77;2023-10-23;11:44:17;3717;472;1.3;0.9;17100;36000
78;2023-10-23;11:54:17;3717;472;1.3;0.9;17100;36000

etcetera

Excel imports the decimal values (1.3 and 0.9) as strings, because my pc uses comma as decimal separator, and I don't want to change that. The file is meant for somebody else, and I cannot expect them to use US settings.

If I query

SELECT no, DATE_FORMAT(date,'%Y-%m-%d') as datum, TIME(date) as tijd, ppm_voor, ppm_na, format(massafactor_voor,1,'nl_NL'), format(massafactor_na,1,'nl_NL'), luchtomzet, luchtomzet_max FROM `VOS`.`datapoint`;

the MySQL node returns

and the CSV node returns this:

no;datum;tijd;ppm_voor;ppm_na;massafactor_voor;massafactor_na;luchtomzet;luchtomzet_max
76;2023-10-23;11:34:08;3717;472;;;17064;36000
77;2023-10-23;11:44:17;3717;472;;;17100;36000
78;2023-10-23;11:54:17;3717;472;;;17100;36000
79;2023-10-23;12:04:18;3717;472;;;17100;36000

etcetera

I don' understand why the formatted values are missing.

[Colin]

Have you checked the region setting for the machine running node-red (or the Docker settings if you are using Docker for node-red)?

[Colin]

@jw_te_r

I don' understand why the formatted values are missing.

Show us how you have configured the CSV node.
Are you using the latest version of node-red?

[TotallyInformation]

OK, forget all that. Some quick searching reveals that this is a SQL standard problem. The SQL standard defines that decimals are returned with a dot not a comma.

This seems to be the recommended approach. In your SQL:

SELECT REPLACE(`massafactor_voor`, '.', ',')

While it returns as a string, as you are writing to CSV, it makes no difference since CSV data HAS to quote any data containing a comma. As long as Excel is in the correct locale, it should still recognise it as a number.

If Excel fails to recognise it, possibly the easiest fix is to keep your workbook separate to your CSV file and use PowerQuery to import the CSV. PowerQuery will try to fix the imported column types but you can easily adjust that should you need to.

[jw_te_r]

Thanks. I found it. It's a weird phenomenon, but I have it working now.

There seems to be no difference between

REPLACE(`massafactor_voor`, '.', ',')

and

FORMAT(massafactor_voor,1,'nl_NL')

However, both are ignored by the CSV node.
If i write

REPLACE(`massafactor_voor`, '.', ',') as massafactor_voor

in my query, the MySQL node looks different:

If I pass this object to a CSV node, it reports the correct values, using commas as decimal separator.