1

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!

2

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.

  • Cheers dude, works great :D – adalal Aug 29 '14 at 15:48
  • 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! – adalal Aug 31 '14 at 16:05
  • 1
    @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. – jynus Aug 31 '14 at 20:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.