Many times, we need to import CSV file into MySQL table or create table from CSV or automate CSV import into MySQL server. Here’s how to import CSV file into MySQL database in Ubuntu. We will use LOAD DATA INFILE query to import CSV into MySQL. You can also use these steps for CentOS, and other Linux systems.
How to Import CSV File into MySQL Table
Here are the steps to import CSV file in MySQL. After importing CSV file with header into MySQL, you may want to use a reporting tool to query your MySQL table and ensure everything is working well.
1. Create Table
First, you need to create a database table with same number of columns as those in your CSV file. Let’s say you want to import CSV file with 2 fields – order_date, sale
So we will create a sales(order_date, sale) table using mysql.
mysql> create table sales(order_date date, sale int);
Bonus Read : How to Calculate Conversion Rate in MySQL
2. Import CSV file into MySQL table
Next, import our CSV file into MySQL, using LOAD DATA INFILE command.
Let’s say we have the following sales.csv file. Please use single/double quotes for date values in your CSV file, else you will get error.
"2020-01-01",15 "2020-01-02",25 "2020-01-03",34 "2020-01-04",22 "2020-01-05",30
mysql> LOAD DATA INFILE 'c:/files/sales.csv'
INTO TABLE sales
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE ROWS 1;
mysql> select * from sales;
order_date | sale
------------+------
2020-01-01 | 15
2020-01-02 | 25
2020-01-03 | 34
2020-01-04 | 22
2020-01-05 | 30
Bonus Read : How to Fill Missing Dates in MySQL
In the above query, we specify delimiter using FIELDS TERMINATED BY ‘,’ and new-line delimiter for each line as LINES TERMINATED BY ‘\n’. ENCLOSED BY ‘”‘ indicates that those values enclosed by double quotes should be treated as chars or date but not numbers. You can also use single quotes or some other character instead of double quotes here but it should match what is used in your CSV file.
Since our data contains headers, we use IGNORE ROWS 1 to tell MySQL not to import line #1 in our file. If your CSV file doesn’t include headers, you can omit it from your query.
Bonus Read: SQL Query to Compare Product Sales By Month
You can use the same queries with CSV or TXT files, by changing the file extension in your query. You can also use it to import tab delimited file into MySQL, by changing delimiter to \t instead of comma.
After you import CSV file, you can use a mysql reporting tool like Ubiq to plot them in line/column charts as shown below.
Hopefully, the above steps will help you easily import CSV file into MySQL table. By the way, if you want to create charts & dashboards to monitor your business or website, you can try Ubiq. We offer a 14-day free trial.
