Import CSV file to MySQL (Query or using Workbench)

In previous article, I mentioned Import an SQL file in MySQL (Using CMD or Powershell) but now in this article, I have mentioned how we can import csv file in mysql using query or using MySQL Workbench, let's take a look at both example one by one.

Let's consider we are using below CSV file in this example

Import CSV to MySQL Using Query

Let's take a look at easy example, and here is the query to save CSV file in MySQL table

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Orders.csv' /*file location*/
INTO TABLE orderdetails.orders 
FIELDS TERMINATED BY ','  /* To make the delimiter a tab, use --fields-terminated-by='\t' */
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

You can see we have file location like 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Orders.csv' this is becasue, I moved my CSV file to 'secure_file_priv' location otherwise I was getting error

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

To get to know your MySQL secure_file_priv location, you can execute this query

SHOW VARIABLES LIKE "secure_file_priv";

Here is the gif image, which shows before running query table output and after running query

To make the delimiter a tab, use --fields-terminated-by = '\t' instead of ',' in above query.

Import CSVfile to MySQL Using MySQL Workbench

If the above method, doesn't work for you for some reason, you can also easily import CSV file using MySQL Workbench in database table. Let's take a look how we can do it step by step.

Step 1: Expand Database in MySQL Workbench and then select Table in which we want to import CSV, then right-click on it and then select "Table Data import Wizard"

Step 2: A Window will open, where we need to select cs file to import, so you can browse and select the file, as shown in the image

Step 3: Now, in the next screen, Import Wizard will ask you to choose from already created table or create new one, since we are using already created one we will choose it and click "Next"

Step 4: In the next screen, import wizard shows source (csv) file headers and destination table columns, which we can map, although it is already mapped, but we can change mapping if needed, once we are done click "Next"

Step 5: We are almost done, import wizard shows the task which will be performed, we don't need to do anything in this screen, just click on "Next"

Step 6: Import is completed in few seconds, and you will see success message in next screen, as shown below

Step 7: That's it, we are done, you can close the import window and now we can see our mysql database table, you will see CSV file is imported successfully.

So, these were the 2 methods to import CSV file in MySQL database table.

You may also like to read:

Error Code: 1175 - You are using safe update mode

Import csv into SQL server (with query OR without query using SSMS)

Convert CSV to JSON using Javascript

Serialize List to JSON in C#

How to Check Installed Powershell version

TRIM(), LTRIM(), RTRIM() Functions in SQL Server

Best Open Source Database Software (DBMS)

Get Date From Datetime in SQL Server

Microsoft SQL Server Versions List

Download and Install SQL Server Management Studio (Step by Step)

Download and Install SQL Server (Step by Step procedure)