How to Import a CSV file into a MySQL database?

June 28, 2020

Introduction

A CSV (Comma Separated Values) file uses commas to separate different values within the file. The CSV file is a standard format when transferring a table to a different system or importing it to another database application.

This tutorial shows you how to import a CSV file into your MySQL database in a few short steps.

tutorial on how to import csv file in mysql using command line in Linux ubuntu

Note: If you need to export a table first, see our article on How to Export a Table from MySQL to CSV.

Prerequisites

Import CSV File Using Command Line

Step 1: Access MySQL Shell

Access your terminal window and log into MySQL using the following command:

mysql –u username –p

Replace username with your actual username. The system prompts you to enter the password for your MySQL user. Entering the correct password gives you access to the MySQL client.

example of a MySQL client shell used to import CSV file

Step 2: Create MySQL Table for CSV Import

The columns in your MySQL table need to match the data from the CSV file you plan to import. If you already have a table ready for the CSV import, you can skip to Step 3 of the tutorial.

Select a database by entering the following command:

USE database_name;

Replace database_name with the name of the database you are importing data into. Use the following command to create a new table:

CREATE TABLE table_name (
            id INT NOT NULL AUTO_INCREMENT,
            column_1 VARCHAR(255) NOT NULL,
            column_2 DATE NOT NULL,
            column_3 DECIMAL(10 , 2 ) NULL,
            column_4 INTEGER,
            PRIMARY KEY (id)
);

Replace the table_name value with the name you want to use for your table. The column_n variable represents the names for each column in your CSV file, and you should edit them accordingly.

  • In this example, column_1 is formatted for text.
  • column_2 is formatted for dates.
  • column_3 is formatted for currencies. The numbers in the parentheses indicate the maximum size of the value and the decimal places.
  • column_4 is formatted for whole numbers.

You can add, delete, or modify the data types used in this example to fit your needs. Visit the official MySQL documentation on data types to find out more.

Step 3: Import CSV into MySQL Table

Import the data from the CSV file into the MySQL database, using the following lines:

LOAD DATA INFILE '/home/export_file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Let us, in turn, explore the actions behind each of these lines:

  • LOAD DATA INFILE – Defines the location of the CSV file to be imported. Change the path (between the quotes) to match the path and filename of your CSV file. If the CSV file is located on the local machine, you can use the LOAD DATA LOCAL INFILE statement instead.
  • INTO TABLE – This indicates the destination table you are importing the CSV file into. Change the table_name to the name of your table.
  • FIELDS TERMINATED BY – By default, comma-separated value files use a comma to identify individual data values. If your export file uses a different delimiter, you can modify this value.
  • ENCLOSED BY – This specifies that a double-quote mark " surrounds values.
  • LINES TERMINATED BY – Use this line to specify the code for a line break.
  • IGNORE 1 ROWS; – Many CSV files export with the column labels as the first line. This command tells MySQL to ignore the first row as you have already created your table with the appropriate column headings. The semicolon at the end specifies the end of the command for MySQL to execute.

Import CSV File with phpMyAdmin

If your server or database uses phpMyAdmin, you can use the graphical interface to import a CSV file.

1. Access cPanel and launch phpMyAdmin.

accessing phpMyAdmin from cPanel

2. Use the left pane to select the database and table you are importing the CSV file into.

3. Use the top menu to select Import.

screenshot of import CSV file into MySQL using phpMyAdmin

4. Click Choose File and browse to the CSV file location.

5. Use the Format drop-down menu to select CSV and the Format-specific options to define options for the individual columns.

Select CSV Format and define column values

6. Select Go to initiate the CSV import.

Conclusion

You now know how to import CSV files into MySQL, both from the command line or by using phpMyAdmin. The methods outlined in this tutorial enable you to move data between systems and different database applications.

Was this article helpful?
YesNo
Vladimir Kaplarevic
Vladimir is a resident Tech Writer at phoenixNAP. He has more than 7 years of experience in implementing e-commerce and online payment solutions with various global IT services providers. His articles aim to instill a passion for innovative technologies in others by providing practical advice and using an engaging writing style.
Next you should read
How to Install phpMyAdmin on CentOS 8
June 2, 2020

The phpMyAdmin software is used for working with MySQL databases through a graphical interface. It can be...
Read more
How to Export a PostgreSQL Table to CSV
March 17, 2020

Learn how to export a PostgreSQL table to a .csv file. This feature is especially helpful when transferring...
Read more
How to Improve MySQL Performance With Tuning
January 15, 2020

The performance of MySQL databases is an essential factor in the optimal operation of your server...
Read more
How to Import and Export MySQL Databases in Linux
April 25, 2019

MySQL is a versatile application. It can be used for something as simple as a product database, or as complex...
Read more