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.