How to import CSV file by using mysql

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.