How to Import CSV File using MySQL?
In this post we have learn how to import CSV file using LOAD DATA INFILE statement.
this is a very simple example for a import file to the database.
Here we have provide a full sysntax for LOAD DATA INFILE statement.
Sysntax
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]
Description
Before imort any file must be follow this.
1. A database table to which the data from the file will be imported.
2. A CSV file fields match with database table's field
3. The account, which connects to the MySQL database server, has FILE and INSERT privileges.
Example
For example we have created a one table that name users
We have use CREATE TABLE Statement for create a database table
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);
Sample of CSV file
id,first_name,last_name
1,"Hardik","Savani"
2,"Harshad","Pathak"
3,"Vimal","Kashiyani"
4,"Uttam","Panara"
5,"Harsukh","Makwana"
The following statement though you have import that users.csv file to database's users table
LOAD DATA INFILE 'c:/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
LOAD DATA INFILE : this is a used for reads rows form a file into a table at a very high speed.
FIELD TERMINATED BY : This is a use for which sign use for terminated for a each fields.
ENCLOSED BY : This is a use for a which sign use for enclosed for a fields.
LINES TERMINATED BY : This is a use for a terminated a one recors or one table row.
I hope it can help you...