How to import a CSV file in MySQL

Hi, Welcome to an interesting tutorial on MySQL where you will learn everything about how to import a CSV file in MySQL with the help of the MySQL workbench and MySQL client tool.

As a developer or database administrator, Sometimes we have a requirement to load CSV files in a MySQL database table, Believe my Guys, this will be a very tedious task If we insert records into the table one by one because it takes so much time and if we have more than one file and each file contained lakhs of records Then this will be also more tedious task.

As a data engineer, I have experienced before, when I needed to load a large CSV file into a MySQL table, I always had to insert data into the table one by one, Then one day someone told me about this MYSQL amazing features and from that time I have been using this MySQL features to load CSV file into MySQL Database table.

Instead of inserting one by one records into the table, we can load all the data together from the CSV file into the MySQL table. This is the most useful feature offered by the MySQL server because It saves lots of time for Developers, Database Administrators, Data Engineers, etc. Being a useful feature of MySQL, definitely, we should have to use this feature.

Throughout this article, we are going to see a total of two ways to load CSV files in MySQL, the file will be using the MySQL command line, and the second will be using the MySQL Workbench application.

Headings of Contents

1 What is a CSV file?
2 Importing CSV file in MySQL using Command Line
3 How to load a CSV file in MySQL workbench?
4 Summary

What is a CSV file?

A CSV stands for Comma Separated Values. It is simply a plain text file format that allows us to store data in tabular format. All the values in the CSV file are separated by the Delimiter like Common (, ), Pipe ( | ), etc.

For your understanding, I am showing you a sample CSV data so that you can understand what I mean to say.

Sample CSV Data:

first_name,last_name,age,email
Vishvajit,Rao,23,[email protected]
Harsh,Kumar,30,[email protected]
Pankaj,Singh,32,[email protected]
John,Doe,30,[email protected]

As you can see in the above CSV sample data, the first row (first_name, last_name, age, email)indicates the column name, and the rest of the data indicates the values and how all the values are separated by Commas (,).

So, let’s see those two ways to import CSB files into MySQL table.

Importing CSV file in MySQL using Command Line

In this method, we will use the MySQL command line to import a CSV file in MySQL because it is one of the most used MySQL tools. You can open the MySQL command line from directly the MySQL Command Line Client Tool or from the windows command prompt.

To import rows from a text file, MySQL provides a statement called LOAD DATA. The LOAD DATA statement is used to import and save the data into the table at a very high speed. Throughout this article, we will use this statement. As you can see below, the syntax of the LOAD DATA statement.

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]

Please follow the following steps in order to import a CSV file in MySQL.

Use the below command, if you are trying to log in with Windows Command Prompt.

mysql -u root -p

And, if you are trying to log in through MySQL Command Line Client then you will have to enter only the password.

Now you have logged in, please follow these steps.

Switch into the required database using the USE command, In my case, the database is testing.

USE testing;

Here, I am creating a table called employee just for demonstration purposes, You can ignore it if you have your own table.

CREATE TABLE employee (
  id int NOT NULL AUTO_INCREMENT,
  emp_id varchar(20) DEFAULT NULL,
  first_name varchar(50) DEFAULT NULL,
  last_name varchar(50) DEFAULT NULL,
  email varchar(100) DEFAULT NULL,
  gender varchar(10) DEFAULT NULL,
  age int DEFAULT NULL,
  salary decimal(20,2) DEFAULT NULL,
  designation varchar(100) DEFAULT NULL,
  department varchar(100) DEFAULT NULL,
  joining_date date DEFAULT NULL,
  PRIMARY KEY ('id')
)

You can use the SHOW TABLES statement to check whether the table has been created or not.

mysql> show tables;
+-------------------+
| Tables_in_testing |
+-------------------+
| employee          |
| student           |
+-------------------+
2 rows in set (0.03 sec)

In my case, A table employee has been created.

I have a CSV file called employee.csv having some records which I want to import into the employee table.
This is a statement that I will use to import CSV data into the table employee.

load data local infile 'C:\\Users\\Vishvajit Rao\\OneDrive\\Documents\\employee.csv'
INTO TABLE testing.employee
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(emp_id,first_name,last_name,email,gender,age,salary,designation,department,joining_date);

Explanation of the above statements:

After ‘load data local infile‘ statement I have mentioned the path of the CSV file. Please replace it with the path of the file.
In the second line you have to mention the database name and table, In My case testing is the database name and employee is the table name.
FIELDS TERMINATED BY ‘,’ make sure each field is separated by commas. As you can see in the above sample CSV data, how each field is separated through commas.
ENCLOSED BY ‘”‘ ensure that sometimes a comma (,) be a part of the value, In that case, the value should be enclosed between quotes.
LINES TERMINATED BY ‘\n’ ensure that the line should be terminated by a new line.’\n’ Represents the new line always.
IGNORE 1 ROWS ensures that first shouldn’t be part of the value. It represents the name of names.
In the last line, I have mentioned all the column names in which I want to import data.

Note:- Please, replace the path of the CSV file with your path and database and table name ( testing.employee ) also. Here testing represents the name of the database and the employee name represents the name of the table.

If you are importing a CSV file into the table first time, then I am sure, you will get the following error.

ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

The above error is saying, The functionality of loading data from the file is disabled, It should be enabled.

To check, if this is disabled or enabled, issue the below command.

mysql> SHOW VARIABLES LIKE '%local%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

As you can see above, the local_infile global variable is disabled, To enable this variable use the SET command.

mysql> SET GLOBAL local_infile=True;
Query OK, 0 rows affected (0.00 sec)

After getting the Ok message, Close the MySQL session and log in with this command.

mysql --local-infile=1 -u root -p

Now, again use the above LOAD DATA statement to import CSV data.

load data local infile 'C:\\Users\\Vishvajit Rao\\OneDrive\\Documents\\employee.csv'
INTO TABLE testing.employee
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(emp_id,first_name,last_name,email,gender,age,salary,designation,department,joining_date);

Now this time, After executing the above statement, you will get an Ok message.

Now, You can use the MYSQL SELECT statement to display the records that have been inserted into the employee table from the CSV file as you can see below.

import a CSV file in MySQL — **Import a CSV file in MySQL**

So, these are the whole steps to load a CSV file in the MySQL Database table using the MySQL client tool.

Now, let’s see the process of importing CSV data to MySQL Workbench.

Note:- Load Data statement Reference:- Click Here

How to load a CSV file in MySQL workbench?

Let’s see a brief about The MySQL Workbench.

MySQL Workbench:- MySQL workbench is an official GUI application of MySQL server and It was first released by Oracle and Sun Microsystems. The first preview version of the MySQL workbench was introduced in 2005. Workbench allows us to browse and design database schemas, work with database objects insert data as well as run SQL queries to work with stored data.

You can download MySQL Workbench from here.

Now let’s explore all the necessary steps to load CSV records through the MySQL Workbench application.

Open MySQL Workbench Application.
Click on the icon to create a connection, You can ignore this step, If you have created a connection already.
- Assign the name of the connection.
- Provide hostname, Hostname will be localhost in case of the local machine.
- Provide port number 3306.
- Provide the name of the database in the Default schema section.
- Click on the Store in Vault option to save the password.
- And then click on the Test Connection option to check has been established or not. If everything is perfect then click on Ok otherwise again re-check your credentials.

After creating the connection successfully, just click on the connection that you have created.
Now, you will see, your database will be showing on the left side.
Now, you have to right-click on the table name ( In which you want to import CSV Records ) and then again click on the Table Data Import Wizard option.

Now a window will appear to select the CSV file. Choose a CSV file from your computer system and click on the Next option.

Re-check your database and table name and leave by default and click on Next.

Now, You will have to check, whether all your CSV data column names are correctly mapped to the perfect column name of the table or not. if everything is ok click on Next otherwise you can manually map the CSV column name to the table column name. In My case, things are going well.

Click on Next.
Again Click on Next.
Now, you can use MySQL SELECT statement to check whether the CSV data is inserted or not. Here you can see all the CSV data has been successfully inserted into the table employee.

So, this is how we can import a CSV file in MySQL table using the Workbench application.

Related Articles:

Summary

So, In this article, we have seen everything about how to import a CSV file in MySQL Table with the help of two ways using LOAD DATA and MySQL Workbench tool.

I would like to highly recommend you, always use raw SQL statements because, Being a developer and Database Administrators, we should have knowledge about SQL statements and we should have to use SQL statements.

But if you don’t want to use SQL statements then you can go with MySQL workbench because there you can upload your CSV records with few clicks.

Now, next time, when you will have a requirement to load a large amount of data from a CSV file to a MySQL database table, You will always use these approaches. You can go with any of them at your convenience.

If you found this article helpful, Please share and keep visiting for further interesting MySQL articles.

Thanks for your valuable time …. 🙏🙏