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.
- Import csv with LOAD DATA INFILE
- 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