How To Import CSV Into MySQL?

How to Import CSV into MySQL?

MySQL supports a variety of file formats that are used to store tabular data. Out of these, one of the most used file formats is CSV (comma-separated value).

A CSV (comma-separated value) is a plain text file format that is used to store tabular data, the delimiter (separator) of a CSV file is a comma (‘, ’) which is used to separate data in one column to data in another column, These files can be edited with Microsoft Excel, Google Spreadsheet or Notepad.

Let's see how to import CSV into MySQL,

MySQL offers several ways to import a CSV file into a MySQL database or table. Some of them are listed below:

Importing the data via Command Line
Importing the data via MySQL Workbench

In the further section, we will talk about importing CSV files into manually created tables and also talk about “How to transform the data while importing CSV files”.

How can We Import CSV Files in a MySQL Database or Table?

Method 1:

Importing Data via the Command Line

These are the following pre-requisites that we should have before importing the data -

There should be an empty table in the database where we want to import the file.
This empty table should have the same structure of columns as our CSV file has.

Note: We have to manually create the table structure in the MySQL command line using the CREATE TABLE statement.

Suppose we have to create a database “Employee” and import a CSV file ‘Employee_Details’ into it.

First, we will create a new database named “Employee” using the given SQL query -

Now, to start using that database, we'll have to write the following query -

Then we’ll create an empty table named 'Employee_Details' inside that database using the given query -

Run the following queries in the MySQL command line.

mysql command

This will create an empty table as per our requirement.

Now we have to use the LOAD DATA INFILE statement to load the data inside the CSV file into the newly created table.

Syntax for the following statement:

Parameters of the Query:

LOAD DATA INFILE: This command is used to specify the location of the CSV file that we want to import.

INTO TABLE: This command is used to declare the table name in which we want to import the data.

FIELDS TERMINATED BY: It is the delimiter used to separate different columns from each other. In the case of a CSV file, it’s a comma ','.

ENCLOSED BY: It is used to mark the field(columns) in the file that we want to import, enclosed by double quotation marks.

LINES TERMINATED BY: It represents a line break in the file.

IGNORE 1 ROWS: This command is used to declare the number of lines to be skipped from the top, the purpose of this statement is to deal with the column headers. In our case, the number of lines to be ignored is 1.

Now with the help of the following query we can import our CSV file into the table, we just have created:

This query will return the following result set:

output

So by this method we can import CSV into MySQL.

Note: We've covered "Method 2: Importing Data using MySQL Workbench GUI" in the later section of this article.

Structure of a CSV File

A CSV (comma-separated value) is a plain text file format that is used to store tabular data, the delimiter (separator) of a CSV file is a comma (‘, ’) which is used to separate data in one column to data in another column.

The CSV file format is widely used in the data industry because of the several advantages that it offers.

Simplicity: These are simple text files where each row represents a single record and each comma-separated value represents a data point for a column.
Size: They're smaller in size than other file formats such as xlsx and JSON, making them easier to store and transfer without using large computational resources.
Versatility:: CSV files are widely used across different domains such as Database Management Systems, Data Analytics, and Back-end Development.
Mouldability:: They can be converted into other file formats very easily without making much effort.

The CSV file used for the above example is -

Employee_Id,First_name,City,Age,Date

1,”John”,”Jaipur”,24,"11/21/2021"

2,”Jenny”,”Jaisalmer”,54,"10/13/2022"

3,”Rohit”,”Lucknow,33,"08/08/2019"

4,Ayush,Jodhpur”,29,"04/11/2018"

5,”Ajay”,”Chennai”,32,"05/02/2015"

Transforming Data while Importing

MySQL offers a variety of transformations that we can perform while importing our files in a database or table.

Let’s understand this with the help of an example.

Suppose we have to change a column’s datatype from the string to date while importing the file.

We can do this by using the SET clause in MySQL.

Note: Here the SET clause converts the string in the Date column to date. Also, the date format is defined as MM/DD/YYYY.

We can do all these transformations while importing CSV into MySQL.

Importing a CSV File from the Client to MySQL Server

MySQL also allows us to import files from the local system to the remote MySQL server. We can do this using the LOCAL statement along with the LOAD DATA INFILE command.

Syntax for the following statement:

Please note that we have already discussed this query previously in this article. Only the LOCAL keyword is newly introduced.