How to Import a CSV file into a MySQL database?

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.

Prerequisites

• Access to a command line / terminal window
• A CSV file containing the data you want to import
• A MySQL user account with FILE and INSERT privileges
• Pre-configured phpMyAdmin account (optional)

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.

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.

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.

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.

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.