Import multiple CSV fields into one MySQL field

Question

I'm trying to import our CSV data log into our MySQL.

The first two fields of the CSV are Date and Time, and I would like to combine that into one date time (which can be searched) onto MySQL.

CSV:

Date,Time,ALARM_01,PT001,TEMP_AMB
14/08/2014,14:23:21,0,130.00,20
14/08/2014,14:23:25,101,128.09,20.1

MySQL Table:

----------------------------------------------
|DateTime           |ALARM_01|PT001 |TEMP_AMB|
----------------------------------------------
|2014-08-14 14:23:21|0       |130.00|20      |
|2014-08-14 14:23:25|101     |128.09|20.1    |
----------------------------------------------

Could you please let me know how this is possible?

Cheers!

Answer 1

The following LOAD DATA sentence loads the CSV file, ignoring the first line and inserting into the desired fields, but changing the format of the Date and Time columns into a proper datetime type:

mysql> LOAD DATA INFILE '/tmp/alarms.csv' 
       INTO TABLE alarms 
       COLUMNS TERMINATED BY ',' 
       IGNORE 1 LINES 
       (@Date, @Time, ALARM_01, PT001, TEMP_AMB) 
       SET DateTime = STR_TO_DATE(CONCAT(@Date, ' ', @Time), '%d/%m/%Y %H:%i:%s');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM alarms;
+----+---------------------+----------+--------+----------+
| id | DateTime            | ALARM_01 | PT001  | TEMP_AMB |
+----+---------------------+----------+--------+----------+
|  1 | 2014-08-14 14:23:21 |        0 | 130.00 |    20.00 |
|  2 | 2014-08-14 14:23:25 |      101 | 128.09 |    20.10 |
+----+---------------------+----------+--------+----------+
2 rows in set (0.00 sec)

Your fields and datatypes may vary slightly, with no effect on the import query.