mySQL Command Line Import CSV Gives me NULL

Question

I am used to using PHPmyadmin to manage my mySQL databases but I am starting to use the command line a lot more. I am trying to import a CSV file into a table called source_data that looks like this...

+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| code      | varchar(10) | YES  |     | NULL    |                |
| result    | char(1)     | YES  |     | NULL    |                |
| source    | char(1)     | YES  |     | NULL    |                |
| timestamp | varchar(30) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

And my CSV file looks like this...

code,result,source,timestamp
123 ABC,,,
456 DEF,,,
789 GHI,,,
234 JKL,,,
567 MNO,,,
890 PQR,,,

I am using this command..

LOAD DATA INFILE '/home/user1/data.csv' INTO TABLE source_data FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

This inserts the correct number of rows but each one just says NULL, where am I going wrong?

This is happening due to auto_increment column in your table I believe. Please see this thread on how to import data with auto_increment column in the table: http://stackoverflow.com/questions/2463542/how-to-import-text-file-to-table-with-primary-key-as-auto-increment — Harsh, Mar 09 '16 at 21:49

Barmar · Accepted Answer · 2016-03-09T22:00:24.693

2

Since the CSV file doesn't have all the table columns (it's missing the id column), you need to specify the columns that they should be written into explicitly.

LOAD DATA INFILE '/home/user1/data.csv' 
INTO TABLE source_data 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' 
IGNORE 1 ROWS
(code, result, source, timestamp);

edited Mar 09 '16 at 22:00

answered Mar 09 '16 at 21:53

Barmar

741,623
53
500
612

I am getting an error trying to run that query, right syntax to use near 'FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS' at line 1 – fightstarr20 Mar 09 '16 at 21:59
Sorry, the column list has to be at the end. – Barmar Mar 09 '16 at 22:00

score 1 · Answer 2 · answered Mar 09 '16 at 21:50

1

Well, I hope this wouldn't be a case still, You said table name is source_data and in command the name is data

answered Mar 09 '16 at 21:50

HirenPatel_

532
1
5
19

1

Then why is it inserting the correct number of rows? – Barmar Mar 09 '16 at 21:53
Ya, Add comma before CODE column in csv, Right now you are entering only 4 columns rather than 5. The first column should be ID, whicha can be empty(cause its auto increment) but it should be there to load the data. – HirenPatel_ Mar 09 '16 at 21:55
1

try @Barmar 's solution – HirenPatel_ Mar 09 '16 at 21:57

mySQL Command Line Import CSV Gives me NULL

2 Answers2