CODEWITHSUNDEEP
mysqlcsv
fightstarr20
fightstarr20

Reputation: 12618

mySQL Command Line Import CSV Gives me NULL

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?

Upvotes: 0

Views: 254

Answers (2)

Barmar
Barmar

Reputation: 782498

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);

Upvotes: 2

HirenPatel_
HirenPatel_

Reputation: 530

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

Upvotes: 1

Related Questions