How to import CSV file using mysql

By Hardik Savani | March 3, 2016 | | 725 Viewer | Category : MySql


Share this post:

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.