Blogs

How to Import a CSV File in MySQL? {Command Line or phpMyAdmin}

Posted on by admin

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 into another database application.

This tutorial shows you how to import a CSV file into your MySQL database in a few 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
  • Preconfigured phpMyAdmin account (optional)

Import CSV file

using the command line

Step 1

: Access MySQL

Shell Access your terminal window and log in to MySQL using the following command:

mysql -u username -p

Replace username with your real username. The system will ask you to enter the password of your MySQL user. Entering the correct password gives you access to the MySQL client.

Step 2: Create a

MySQL table for CSV import

The columns in the MySQL table must match the data in the CSV file that you plan to import. If you already have a table ready for CSV import, you can skip to step 3 of the tutorial.

Select a database by typing the following command:

USE database_name;

Replace database_name with the name of the database to which you are importing data. 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 that you want to use for the table. The column_n variable represents the names of 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 coins.
  • Numbers in parentheses indicate the maximum value size and decimals.
  • column_4 is formatted for integers.

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

Step 3: Import

CSV in MySQL table

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

LOAD DATA INTO FILE ‘/home/export_file.csv’ IN THE TABLE table_name FIELDS ENDING WITH ‘,’ ENCLOSED BY ‘”‘ LINES ENDING WITH ‘\n’ IGNORE 1 ROWS;

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

  • LOAD DATA INFILE – Defines the location of the CSV file to be imported. Change the path (in quotation marks) to match the path and filename of your CSV file. If the CSV file is located on the local computer, you can use the LOAD DATA LOCAL INFILE statement instead.
  • INTO TABLE: Indicates the destination table into which you are importing the CSV file. Change the table_name to the name of the table.
  • FIELDS TERMINATED BY: By default, comma-separated value files use a comma to identify individual data values. If the export file uses a different delimiter, you can modify this value.
  • ENCLOSED BY – This specifies that a double quotation mark “ surrounds the values.
  • LINES TERMINATED BY: Use this line to specify the code for a line break.
  • IGNORE 1 ROWS; – Many CSV files are exported with column labels as the first line. This command tells MySQL to ignore the first row, as it has already created its 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 run phpMyAdmin.

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

3. Use the top menu to select Import.

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

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

6. Select Go to start the CSV import.

Conclusion

Now you know how to import CSV files to MySQL, both from the command line and using phpMyAdmin. The methods described in this walkthrough enable you to move data between systems and different database applications.

admin