MariaDB Import CSV

Question

Afternoon Folks

I am trying to import a CSV into a table on MariaDB

The columns on the table are

MariaDB [snipeit]> show columns from locations;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name       | text             | YES  |     | NULL    |                |
| city       | varchar(191)     | YES  |     | NULL    |                |
| state      | varchar(191)     | YES  |     | NULL    |                |
| country    | varchar(191)     | YES  |     | NULL    |                |
| created_at | timestamp        | YES  |     | NULL    |                |
| updated_at | timestamp        | YES  |     | NULL    |                |
| user_id    | int(11)          | YES  |     | NULL    |                |
| address    | varchar(191)     | YES  |     | NULL    |                |
| address2   | varchar(191)     | YES  |     | NULL    |                |
| zip        | varchar(10)      | YES  |     | NULL    |                |
| deleted_at | timestamp        | YES  |     | NULL    |                |
| parent_id  | int(11)          | YES  |     | NULL    |                |
| currency   | varchar(10)      | YES  |     | NULL    |                |
| ldap_ou    | varchar(191)     | YES  |     | NULL    |                |
| manager_id | int(11)          | YES  |     | NULL    |                |
| image      | varchar(191)     | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

The CSV looks like

id  name    city    state   country created_at  updated_at  user_id address address2    zip deleted_at  parent_id   currency    ldap_ou manager_id  image
testname NULL   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL

id is blank

But the warning shows

MariaDB [snipeit]> show warnings;
+---------+------+------------------------------------------------------------------+
| Level   | Code | Message                                                          |
+---------+------+------------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: '' for column 'id' at row 1             |
| Warning | 1265 | Data truncated for column 'created_at' at row 1                  |
| Warning | 1265 | Data truncated for column 'updated_at' at row 1                  |
| Warning | 1366 | Incorrect integer value: 'NULL' for column 'user_id' at row 1    |
| Warning | 1265 | Data truncated for column 'deleted_at' at row 1                  |
| Warning | 1366 | Incorrect integer value: 'NULL' for column 'parent_id' at row 1  |
| Warning | 1366 | Incorrect integer value: 'NULL' for column 'manager_id' at row 1 

Any idea why this would be? I thought if a field has NULL I can use NULL on it?

The command I used to import is

 load data local infile '/media/Share1/CSV_Files/test.csv' into TABLE locations fields terminated by ',' ignore 1 lines;

Cheers

Answer 1

There are lots of mistakes :

1. Your csv specify 17 columns and your second line (first row of data) has 16 values which means "testname" is used to match the first column ID You should remove "id".
2. Your command indicates "fields terminated by ','" but your fields aren't separated by ','. You should separate them with comma.
3. Columns in your csv file don't match columns you have shown to us with "show columns from locations"

Then try to load your file again and tell us if there are other errors.

What you might want to do is :

name,city,state,country,created_at,updated_at,user_id,address,address2,zip,deleted_at,parent_id,currency,ldap_ou, manager_id,image;
testname,,,,,,,,,,,,,,,;

And use the following command :

load data local infile '/media/Share1/CSV_Files/test.csv' into TABLE locations fields terminated by ',' LINE TERMINATED BY ";" ignore 1 lines;

EDIT : Your command should specify columns that appears in your csv file like this (in the right order !!):

load data local infile '/media/Share1/CSV_Files/test.csv' into TABLE locations(name,city,state,country,created_at,updated_at,user_id,address,address2,zip,deleted_at,parent_id,currency,ldap_ou,manager_id,image) fields terminated by ',' LINE TERMINATED BY ";" ignore 1 lines;

EDIT 2 :

When reading data with LOAD DATA, empty or missing columns are updated with ''. To load a NULL value into a column, use \N in the data file. The literal word NULL may also be used under some circumstances. See Section 13.2.7, “LOAD DATA Syntax”.

from https://dev.mysql.com/doc/refman/8.0/en/problems-with-null.html

So your csv should look like :

name,city,state,country,created_at,updated_at,user_id,address,address2,zip,deleted_at,parent_id,currency,ldap_ou, manager_id,image;
testname,\N,\N,\N,\N,\N,\N,\N,\N,\N,\N,\N,\N,\N,\N,\N;

EDIT 3 : It may be the final solution. Do not specify id field in the csv file as just above in EDIT 2. And use the following command :

load data local infile '/media/Share1/CSV_Files/test.csv' 
into table locations
fields terminated by ',' 
line terminated by ";" 
ignore 1 lines
(name,city,state,country,created_at,updated_at,user_id,address,address2,zip,deleted_at,parent_id,currency,ldap_ou,manager_id,image)
set id = null;

Answer 2

Since id is an auto_increment field it is generated automatically. You need to remove id column from the CSV file.