# CSV node: Regional settings in date, time and numeric variables
**URL:** https://discourse.nodered.org/t/csv-node-regional-settings-in-date-time-and-numeric-variables/82474
**Category:** General
**Created:** [30 October 2023 15:43 UTC](https://discourse.nodered.org/t/csv-node-regional-settings-in-date-time-and-numeric-variables/82474 "2023-10-30T15:43:48Z")
**Posts on this page:** 8
**Page:** 1
### Author:  [@jw\_te\_r](https://discourse.nodered.org/u/jw_te_r)
#### Post date: [30 October 2023 15:43 UTC](https://discourse.nodered.org/t/csv-node-regional-settings-in-date-time-and-numeric-variables/82474/1 "2023-10-30T15:43:49Z")
I made a MySQL table to save sensor data which is defined like so:
```auto
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
```auto
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?
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [30 October 2023 16:32 UTC](https://discourse.nodered.org/t/csv-node-regional-settings-in-date-time-and-numeric-variables/82474/2 "2023-10-30T16:32:39Z")
> [@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.
---
### Author:  [@jw\_te\_r](https://discourse.nodered.org/u/jw_te_r)
#### Post date: [30 October 2023 16:59 UTC](https://discourse.nodered.org/t/csv-node-regional-settings-in-date-time-and-numeric-variables/82474/3 "2023-10-30T16:59:30Z")
That's what I have been doing all day 😅
If I query:
```auto
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:
```auto
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
```auto
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:
```auto
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.
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [30 October 2023 17:05 UTC](https://discourse.nodered.org/t/csv-node-regional-settings-in-date-time-and-numeric-variables/82474/4 "2023-10-30T17:05:35Z")
Have you checked the region setting for the machine running node-red (or the Docker settings if you are using Docker for node-red)?
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [30 October 2023 17:14 UTC](https://discourse.nodered.org/t/csv-node-regional-settings-in-date-time-and-numeric-variables/82474/5 "2023-10-30T17:14:54Z")
> [@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?
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [30 October 2023 17:43 UTC](https://discourse.nodered.org/t/csv-node-regional-settings-in-date-time-and-numeric-variables/82474/6 "2023-10-30T17:43:18Z")
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:
```auto
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.
---
### Author:  [@jw\_te\_r](https://discourse.nodered.org/u/jw_te_r)
#### Post date: [30 October 2023 18:50 UTC](https://discourse.nodered.org/t/csv-node-regional-settings-in-date-time-and-numeric-variables/82474/7 "2023-10-30T18:50:06Z")
Thanks. I found it. It's a weird phenomenon, but I have it working now.
There seems to be no difference between
```auto
REPLACE(`massafactor_voor`, '.', ',')
```
and
```auto
FORMAT(massafactor_voor,1,'nl_NL')
```
However, **both** are ignored by the CSV node.
If i write
```auto
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.
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [29 December 2023 18:50 UTC](https://discourse.nodered.org/t/csv-node-regional-settings-in-date-time-and-numeric-variables/82474/8 "2023-12-29T18:50:09Z")
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.