Load csv file to a MySql database
LOAD DATA INFILE command is used to load the file's data to the database.
We have many parameters for firing this query. It differs based on our requirement (Refer MySQL official site to know more).
Query to Load a CSV ( comma separated values) file into the database:
SYNTAX : LOAD DATA INFILE <'path'> into TABLE <table name> FIELDS TERMINATED BY ',' LINE TERMINATED BY '\n';
Example : LOAD DATA INFILE 'C://User//Karthikk//Testing.csv" into TABLE employee FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
Common error on firing above query would be :
* Error code: 13 - Permission denied
This occurs when you tried to dump the local files to the local MYSQL server. To resolve this issue, append a keyword LOCAL to the query.
>> LOAD DATA LOCAL INFILE 'path' into TABLE tablename FIELDS TERMINATED BY ',' LINE TERMINATED BY '\n';
* Error code: 2 - No such file or directory
One such occurrence of this error is having improper escape characters in the specified file path. We need to escape using a forward slash.
>> LOAD DATA INFILE 'C://User//Karthik//Testing.csv" into TABLE employee FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
* warning(s): 1265 Data truncated for column 'column_name' at row 1
This occurs when we try to load the data into different columns of different data type. One Such major conflict will be date /time column. Please load the values with the same data/time format. Else the data will set to zero.
>> When we try to load "2014-12-02" data to a column with data format "dd-mm-yyyy" then the data will be stored as "00-00-0000"