How to import csv file in MySQL database?

In this tutorial I will explain you two methods to import csv file into MySQL database. First this requires a MySQL database with same numbers of column as csv file.

1. Import csv with LOAD DATA INFILE
2. Import csv file with mysqlimport

What is CSV file?

CSV stands from comma separated value. It is a plain text file that have multiple records per line. each record may have one or more fields which are separated by comma (sometime tab delimited). As it is a simple text file thus it can be created or edited by any text editor.

Import csv with LOAD DATA INFILE

LOAD DATA INFILE is MySql command to load csv data in database.

LOAD DATA INFILE 'my.csv' 
INTO TABLE database 
FIELDS TERMINATED BY ',' 
ENCLOSED BY ''
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

You can use FIELDS TERMINATED BY ‘\t’ if fields are tab delimited. IGNORE 1 ROWS will ignore first rows as it has field name and should not be imported.

Import csv file with mysqlimport

mysqlimport is client provides a command-line interface to the LOAD DATA SQL statment

mysqlimport --ignore-lines=1 \
            --fields-terminated-by=, \
            --local -u root \
            -p database \
             my.csv