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?