For importing a csv file into mysql table, we need to execute following command:
LOAD DATA INFILE ‘/tmp/t.csv’ INTO TABLE table1 FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’;
Sometimes we may face some file not found exceptions and errors and it may persist even after giving full permissions. I got an error which said t.csv file is missing even though I have place it at correct location. The fix for this issue is to change the ownwership of t.csv to mysql using the following command.
chown mysql:mysql t.csv
DATE filed and CSV imports
I had a table table2:
create table table2(ID INT,title INT NULL,dt DATETIME NULL);
t.csv :
1, 3,10/12/2000
1000, 1223,12/12/2014
And when I executed the previopus load command, I got incorrect values in mysql table table2 and which was as follows:
ID title dt
+——+——-+———————+
1 3 0000-00-00 00:00:00
1000 1223 0000-00-00 00:00:00
In this case, we need to modify the date filed while uploading to database. This can be done using the following command:
LOAD DATA INFILE ‘/tmp/t.csv’ INTO TABLE table2 FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’ (ID, title, @var1) SET dt = STR_TO_DATE(@var1, ‘%m/%d/%Y’);
ID title dt
+——+——-+———————+
1 3 2000-10-12
1000 1223 2014-12-12
And if there are multiple fields which are to be modified,you can specify them in a comma separated way like,
LOAD DATA LOCAL INFILE '/path/to/csv/file.csv'
INTO TABLE mytable
LINES TERMINATED BY '\n'
(id, task, hoursWorked, @var1, @var2)
SET begindate = STR_TO_DATE(@var1, '%m/%d/%Y'),
enddate = STR_TO_DATE(@var2, '%m/%d/%Y');