How to Import Data From a CSV File in MySQL?

Importing data from a CSV (Comma-Separated Values) file into a MySQL database is a common task for data migration and loading purposes. CSV files are widely used for storing and exchanging tabular data. However, we cannot run SQL queries on such CSV data so we must convert it to structured tables.

In this article, we will learn about many methods to import a CSV file in MySQL with the help of implementation and so on.  

How to Import CSV Files in MySQL?

We will use the below three methods to import any CSV file into MySQL.

1. Import CSV into MySQL Using Command Line
   
2. Import CSV into MySQL Using PhpMyAdmin
   
3. Import CSV into MySQL using Workbench 

Using these methods, you can efficiently import all the data from a CSV file into a MySQL table, allowing you to use MySQL queries to manipulate and analyze the data.

Importing data significantly saves time and effort by eliminating the need for manual data entry, ensuring accuracy, and streamlining your workflow.

1. Import CSV into MySQL Using the Command Line

Follow the given steps, to import data from a CSV file into MySQL tables using the MySQL command line.

Step 1: Open the terminal window and log in to the MySQL Client using the password. Refer to the following command:

mysql -u root -p

Step 2: Create a database and then create a table inside that database. The .CSV file data will be the input data for this table:  

#To create a database
CREATE DATABASE database_name;

#To use that database
use database_name;

#To create a table
CREATE TABLE table_name(
id INTEGER,
col_1 VARCHAR(100),
col_2 INTEGER,
col_3 DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);

We create a database and a table called table_name inside it. The table contains various attributes such as id, col_1, col_2, and col_3. The col_3 attribute is of the DATETIME type, and it contains both data and time in the format YYYY-MM-DD hh:mm:ss. In addition to the integer and varchar attributes, we'll look at how to import a DateTime type attribute from a CSV file into a MySQL table.

Step 3: Now we must examine the MySQL variable "secure_file_priv". By default, the MySQL server starts with the -secure-file-priv option. When using LOAD DATA INFILE, it specifies the directory from which data files can be loaded into a given database instance. To view the configured directory, use the following command:

SHOW VARIABLES LIKE 'secure_file_priv';

Output:

The variable "secure_file_priv" will be set to a file directory configured by the MySQL server. We now have two options for making the LOAD DATA INFILE command work properly:

1. Move the input .csv file into the specified folder structure.
2. To change the configured folder structure to our needs.

We'll go over both of these approaches.

Approach 1:  

In this approach, we will need to put the input CSV file into the specified folder structure, and then only we will be able to access the file to be loaded into the database. Once we do this, now we are ready to run the command to import the CSV files to the database. Refer to the following command:

LOAD DATA INFILE '{folder_structure}/{csv_file_name}'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

1. LOAD DATA INFILE - It specifies the location of the input CSV file.
   
2. INTO TABLE - It specifies the table into which data is to be populated.
   
3. FIELDS TERMINATED BY - It specifies the delimiter through which the individual values in the file are separated.
   
4. ENCLOSED BY - It specifies the symbol which specifies the values in the CSV file.
   
5. LINES TERMINATED BY - It specifies the code for a line break.
   
6. IGNORE 1 ROWS - It specifies the number of lines to be ignored from an input CSV file which might contain column labels, etc.

Output:

Approach 2: 

In this approach, we will change the configured folder structure to our needs.

For Windows:

Step 1: The existing folder structure in my.ini must be changed. Go to  This PC -> C Drive -> ProgramData -> MySQL -> MySQL Server * -> my.ini  [The ProgramData folder is hidden. To see the folder, go to View in the top menubar and enable Hidden items.]

Step 2: Open the file and search for “secure-file-priv”. It will look something like this.

# Secure File  Priv.
secure-file-priv=”C:/ProgramData/MySQL
/MySQL Server 5.7/Uploads”

Step 3: Change the folder structure as per your choice and save the file in a different location, as the file cannot be saved at the same location. Now, copy and paste the file into its actual location. To do so, we need administrator access.

Step 4: Now, press “Ctrl + R” -> Type “services.msc” -> Press “Enter”. This will open the Services tab.

Step 5: Search for MySQL57 [The number 57 may vary on your PC]. Right-click on it and then click on the restart button.

Step 6: Log in to the MySQL Command Line client and run the following command:

LOAD DATA LOCAL INFILE '{file_location}'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Following the steps outlined above, we can import a CSV file to a MySQL table in Windows OS without encountering any errors.

For Linux:

Step 1: We need to add the new folder structure of our choice in my.cnf file. The file is located in /etc/mysql/ folder.  Open my.cnf file using the editor of your choice and add the following lines to the file:

[mysqld]
secure-file-priv={new_folder_structure}

Step 2: Save the file after making the changes and restart the MySQL service. Refer to the following command for that:

sudo systemctl restart mysql

Step 3: Log in to the MySQL client again using the following command:

mysql -u root -p --local-infile

Step 4: Once you log in, run the following command:

set global local_infile = 1;
SHOW VARIABLES LIKE 'local_infile';

Output:

Step 5: Now, we are set to import the CSV file data to the MySQL table. Refer to the following command:

LOAD DATA LOCAL INFILE '{file_location}'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Output:

Following the steps outlined above, we can import a CSV file to a MySQL table in Linux OS without encountering any errors.

The following command can be used to determine whether or not the dob attribute with timestamp type has been properly imported. In this way, we can ensure that the attribute in the table corresponds to the actual DateTime datatype of MySQL:

SELECT * FROM table_name WHERE col_3 
= TIMESTAMP('{YYYY-MM-DD HH:MM:SS}');

Optional Step: Transforming Data while Importing

When you want to load data from a file into a MySQL table, you might notice that the incoming datatime format does not exactly match the target table. As an example, consider the col_3 timestamp format in the items.

If the target table format is "yyyy/mm/dd %H:%i:%s.%f" and the csv file format is "dd/mm/yyyy %H:%i:%s.%f". The SET clause in the LOAD DATA INFILE statement can be used to convert the CSV file datetime format to the one used by the destination table in MySQL. While performing the operation to load data from file to table in MySQL, you can use the SET clause in the end along with the str to date() function. Refer to the following command:

LOAD DATA INFILE '{file_location}'
INTO TABLE table_name
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
IGNORE 1 ROWS 
(id, col_1, col_2, @col_3) 
SET col_3 = timestamp(str_to_date(@col_3,'%d-%m-%Y %H:%i:%s.%f'));

2. Import CSV into MySQL Using phpMyAdmin

phpMyAdmin is a free PHP-based software tool designed to handle MySQL administration over the Internet.

Let's look at how we can use PhpMyAdmin to import a CSV file into a MySQL database.

Step 1: When you launch PhpMyAdmin, you will see various databases and tables on the left side of the interface. Create a table into which you want to import the CSV file data. The columns and datatypes must match the corresponding data in the CSV file.

Step 2: Select the table and then click on the import button from the top side menu bar.

Step 3: Click the "choose file" button and navigate to your local storage to find the input CSV file. On the same page, you can also specify the number of lines to skip in the file.

Step 4: Scroll down the page to choose the file format, which is csv in our case. Similarly to the command line approach, we have several Format-specific options here. When we're finished, click the "Go" button.  

Step 5:The CSV file gets imported into the MySQL table. When the import is successful, we see the following messages.

Step 6: We can now return to the table and examine the data imported from the CSV file. 

3. Import CSV into MySQL Using MySQL Workbench

MySQL Workbench is a visual database design tool that combines SQL development, administration, database design, creation, and maintenance for the MySQL database system into a single integrated development environment.

Let's look at how we can use MySQL Workbench to import a CSV file into a MySQL database.

Step 1: When you launch MySQL Workbench, you will see various databases and tables on the left side of the interface. Create a table into which you want to import the CSV file data. The columns and datatypes must match the corresponding data in the CSV file.

Step 2: Right-click on the table and select the option of “Table Data Import Wizard”. Browse your local storage to select the input CSV file.

Step 3: Select the file encoding and match the source and destination columns to configure the import settings. When you're finished, click the next button.

Step 4: The input CSV file data will then be imported into the MySQL table. We can then check the imported data on the MySQL workbench interface.

Conclusion

Overall, this article provided three methods to import CSV files into MySQL such as using the Command Line, phpMyAdmin, and MySQL Workbench. By leveraging these tools, you can efficiently import data from CSV files into MySQL, saving time and ensuring accuracy.