mysql - Not importing CSV

Question

I have the following scenario:

MariaDB [IOT]> DESCRIBE Arduino;
+-------------+-----------+------+-----+---------------------+-------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------+------+-----+---------------------+-------------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| Timestamp | timestamp | NO | | current_timestamp() | on update current_timestamp() |
| Temperature | int(11) | NO | | NULL | |
| Humidity | int(11) | NO | | NULL | |
+-------------+-----------+------+-----+---------------------+-------------------------------+

-rwxrwxrwx 1 pi pi 3712 Jan 28 17:21 test.csv

pi@728IOT:~ $ wc -l test.csv 
150 test.csv

pi@728IOT:~ $ head test.csv 
Timestamp Temperature,(C),Humidity
2021-01-28 15:49:22,9,13
2021-01-28 15:49:25,9,19
2021-01-28 15:49:29,9,14
2021-01-28 15:49:33,9,18
2021-01-28 15:49:36,9,10
2021-01-28 15:49:39,9,17
2021-01-28 15:49:42,9,19
2021-01-28 15:49:46,8,16
2021-01-28 15:49:49,8,12
MariaDB [IOT]> LOAD DATA LOCAL INFILE '/home/pi/test.csv' INTO TABLE Arduino FIELDS TERMINATED BY ',' LINES TERMINATED BY '
' IGNORE 1 ROWS;
Query OK, 1 row affected, 595 warnings (7.495 sec) 
Records: 149 Deleted: 0 Skipped: 148 Warnings: 595

MariaDB [IOT]> select * from Arduino;
+------+---------------------+-------------+----------+
| id | Timestamp | Temperature | Humidity |
+------+---------------------+-------------+----------+
| 2021 | 0000-00-00 00:00:00 | 13 | 0 |
+------+---------------------+-------------+----------+
1 row in set (0.002 sec)

Entire file:

pi@728IOT:~ $ cat test.csv 
Timestamp Temperature,(C),Humidity
2021-01-28 15:49:22,9,13
2021-01-28 15:49:25,9,19
2021-01-28 15:49:29,9,14
2021-01-28 15:49:33,9,18
2021-01-28 15:49:36,9,10
2021-01-28 15:49:39,9,17
2021-01-28 15:49:42,9,19
2021-01-28 15:49:46,8,16
2021-01-28 15:49:49,8,12
2021-01-28 15:49:52,8,16
2021-01-28 15:49:55,8,16
2021-01-28 15:49:59,8,11
2021-01-28 15:50:02,8,17
2021-01-28 15:50:05,8,9
2021-01-28 15:50:08,8,9
2021-01-28 15:50:11,8,17
2021-01-28 15:50:15,7,15
2021-01-28 15:50:18,7,13
2021-01-28 15:50:21,7,17
2021-01-28 15:50:24,7,12
2021-01-28 15:50:28,7,11
2021-01-28 15:50:31,7,14
2021-01-28 15:50:34,7,16
2021-01-28 15:50:37,7,9
2021-01-28 15:50:40,7,15
2021-01-28 15:50:44,7,12
2021-01-28 15:50:47,7,9
2021-01-28 15:50:50,7,9
2021-01-28 15:50:53,7,16
2021-01-28 15:50:57,7,17
2021-01-28 15:51:00,7,10
2021-01-28 15:51:03,6,15
2021-01-28 15:51:06,6,13
2021-01-28 15:51:09,6,10
2021-01-28 15:51:13,6,12
2021-01-28 15:51:16,6,16
2021-01-28 15:51:19,6,11
2021-01-28 15:51:22,6,14
2021-01-28 15:51:25,6,10
2021-01-28 15:51:29,6,16
2021-01-28 15:51:32,6,15
2021-01-28 15:51:35,6,11
2021-01-28 15:51:38,6,15
2021-01-28 15:51:42,6,10
2021-01-28 15:51:45,6,14
2021-01-28 15:51:48,6,16
2021-01-28 15:51:51,6,13
2021-01-28 15:51:54,6,14
2021-01-28 15:51:58,6,10
2021-01-28 15:52:01,6,8
2021-01-28 15:52:04,6,12
2021-01-28 15:52:07,6,14
2021-01-28 15:52:11,6,11
2021-01-28 15:52:14,6,9
2021-01-28 15:52:17,6,15
2021-01-28 15:52:20,6,15
2021-01-28 15:52:23,6,11
2021-01-28 15:52:27,6,12
2021-01-28 15:52:30,6,10
2021-01-28 15:52:33,6,9
2021-01-28 15:52:36,6,10
2021-01-28 15:52:40,6,7
2021-01-28 15:52:43,6,13
2021-01-28 15:52:46,6,12
2021-01-28 15:52:49,6,16
2021-01-28 15:52:52,6,7
2021-01-28 15:52:56,6,8
2021-01-28 15:52:59,5,9
2021-01-28 15:53:02,5,8
2021-01-28 15:53:05,5,15
2021-01-28 15:53:09,5,6
2021-01-28 15:53:12,5,14
2021-01-28 15:53:15,5,7
2021-01-28 15:53:18,5,14
2021-01-28 15:53:21,5,7
2021-01-28 15:53:25,5,8
2021-01-28 15:53:28,5,14
2021-01-28 15:53:31,5,8
2021-01-28 15:53:34,5,15
2021-01-28 15:53:38,5,6
2021-01-28 15:53:41,5,12
2021-01-28 15:53:44,5,6
2021-01-28 15:53:47,5,7
2021-01-28 15:53:50,5,7
2021-01-28 15:53:54,5,6
2021-01-28 15:53:57,5,15
2021-01-28 15:54:00,5,6
2021-01-28 15:54:03,5,7
2021-01-28 15:54:07,5,10
2021-01-28 15:54:10,5,7
2021-01-28 15:54:13,5,7
2021-01-28 15:54:16,5,9
2021-01-28 15:54:19,5,11
2021-01-28 15:54:23,5,8
2021-01-28 15:54:26,5,6
2021-01-28 15:54:29,5,11
2021-01-28 15:54:32,5,11
2021-01-28 15:54:36,5,8
2021-01-28 15:54:39,5,12
2021-01-28 15:54:42,4,7
2021-01-28 15:54:45,4,6
2021-01-28 15:54:48,4,13
2021-01-28 15:54:52,4,8
2021-01-28 15:54:55,4,11
2021-01-28 15:54:58,4,12
2021-01-28 15:55:01,4,11
2021-01-28 15:55:04,4,10
2021-01-28 15:55:08,4,11
2021-01-28 15:55:11,4,12
2021-01-28 15:55:14,4,13
2021-01-28 15:55:17,4,13
2021-01-28 15:55:21,4,5
2021-01-28 15:55:24,4,11
2021-01-28 15:55:27,4,9
2021-01-28 15:55:30,4,13
2021-01-28 15:55:33,4,12
2021-01-28 15:55:37,4,7
2021-01-28 15:55:40,4,10
2021-01-28 15:55:43,4,12
2021-01-28 15:55:46,4,12
2021-01-28 15:55:50,4,12
2021-01-28 15:55:53,4,13
2021-01-28 15:55:56,4,12
2021-01-28 15:55:59,4,13
2021-01-28 15:56:02,4,13
2021-01-28 15:56:06,4,13
2021-01-28 15:56:09,4,10
2021-01-28 15:56:12,4,10
2021-01-28 15:56:15,4,6
2021-01-28 15:56:19,4,8
2021-01-28 15:56:22,4,13
2021-01-28 15:56:25,4,10
2021-01-28 15:56:28,4,6
2021-01-28 15:56:31,4,14
2021-01-28 15:56:35,4,14
2021-01-28 15:56:38,4,12
2021-01-28 15:56:41,4,9
2021-01-28 15:56:44,4,7
2021-01-28 15:56:48,4,9
2021-01-28 15:56:51,4,13
2021-01-28 15:56:54,4,7
2021-01-28 15:56:57,4,13
2021-01-28 15:57:00,4,10
2021-01-28 15:57:04,4,13
2021-01-28 15:57:07,4,8
2021-01-28 15:57:10,4,7
2021-01-28 15:57:13,4,6
2021-01-28 15:57:17,4,11
2021-01-28 15:57:20,4,5

Any idea? ?

question from:https://stackoverflow.com/questions/65946394/not-importing-csv

Answer 1

Since you're not telling it which column to use, it's trying to insert into the id field.

Try: LOAD DATA LOCAL INFILE '/home/pi/test.csv' INTO TABLE Arduino FIELDS TERMINATED BY ',' LINES TERMINATED BY ' ' IGNORE 1 LINES (Timestamp, Temperature, Humidity);