Today now in this post i will show you how to import CSV file by using Mysql. So now in this post we will learn how to import CSV file by using LOAD DATA INFILE statement. Now this is a very simple example for a import file to our database. So here we have provide a full syntax 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 import any file we must be follow this step.
1. Need a database table to which the data from the file will be imported.
2. A CSV file fields must match with database table’s field
3. The account, which connects to the MySQL database server, has FILE and INSERT privileges.
Example
Now for example we need to created a one table that name users
We must 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,"Shahriar","Sagor" 2,"Srs","shahriar" 3,"sabbi","anam" 4,"joy","shahriar" 5,"may","june"
So the following statement will though we 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 : We know this is a used for reads rows form a file into the table at a very high speed.
FIELD TERMINATED BY : This is a used for which sign use for terminated for a each of fields.
ENCLOSED BY : We know this is a use for a which sign use for enclosed for a fields.
LINES TERMINATED BY : For this is a use for a terminated a one record or one table row.
Read Also :Laravel Join with Subquery in Query Builder Example
Thanks for read. I hope it help you. For more you can follow us on facebook.
