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.

• adalal: Cheers dude, works great :D
• adalal: One more bit of help, if you wouldn't mind - Is it possible to match columns somehow, if the db and the CSV headers didn't match up, and further more, if the CSV has more columns than I would require in the db. Cheers!
• jynus: @adalal Just use variables and SET the columns you want; do not use the unrequired ones. Look at how I did it with @Date and @Time above.