How to import CSV into MySQL
Importing data from CSV (Comma Separated Values) files into a MySQL database is a common task in data management and analysis. This process allows you to efficiently load large datasets into MySQL tables for further analysis, reporting, or storage. In this Answer, we will explore different approaches to importing CSV data into MySQL e.g. using the command line, MySQL Workbench, and automation techniques.
Prepare the CSV File
Before importing the CSV file, ensure that it is properly formatted. Check for any inconsistencies, missing values, or incorrect delimiters.
It’s best practice to ensure the CSV file has a header row that describes the column names.
Example
id,name,salary1,John Doe,5000.002,Jane Smith,6000.503,Michael Johnson,4500.75
Create a table in MySQL
First, you need to create a table in MySQL that matches the structure of your CSV file. You can either use an existing table or create a new one specifically for the import. Ensure that the table columns have the correct data types and order to align with the CSV file’s columns.
Example
Let’s assume we have a CSV file called the employees.csv with columns id, name, and salary. To create a corresponding table in MySQL, execute the following SQL statement:
CREATE TABLE employees (id INT,name VARCHAR(255),salary DECIMAL(10,2));
Import the CSV File
MySQL provides several ways to import CSV files. Here, we will explore a few of them one by one.
Using the command-line (mysqlimport)
MySQL provides the mysqlimport command-line utility to efficiently import CSV data into a MySQL database. Here are the steps to do that.
Ensure your CSV file is well-formed and contains the data you want to import.
Open the command prompt or terminal on your system.
Use the
mysqlimportcommand as follows:
mysqlimport -u your_username -p --local your_database_name /path/to/your/csv/file.csv
Explanation
-uspecifies the MySQL username.-pprompts for the MySQL password.--localindicates that the file is located on the local machine.your_database_nameis the name of the target database./path/to/your/csv/file.csvis the path to your CSV file.
Using MySQL Workbench
If you prefer a graphical user interface, you can use MySQL Workbench, a popular database administration tool. It allows you to import CSV files.
Example
Open MySQL Workbench and connect to your MySQL server.
Select your target table, right-click on that, and select "Table Data Import Wizard".
Now, browse to select your CSV file.
Configure import settings as needed.
Click “Start Import” to initiate the CSV import process.
Using LOAD DATA INFILE
If you frequently import CSV data into MySQL, you can automate the process using the LOAD DATA INFILE SQL statement. The LOAD DATA INFILE statement allows you to directly import the CSV file into the MySQL table. This method is efficient for large datasets. Here are the steps to do that.
Ensure your CSV file is located on the MySQL server (upload it if necessary).
Connect to your MySQL server using a MySQL client or MySQL Workbench.
Execute the
LOAD DATA INFILEstatement to import the CSV data into the target table.
LOAD DATA INFILE '/path/to/your/csv/file.csv'INTO TABLE your_table_nameFIELDS TERMINATED BY ','ENCLOSED BY '"'LINES TERMINATED BY '\n'IGNORE 1 LINES;
Explanation
INTO TABLE your_table_namespecifies the target table where the data will be imported.FIELDS TERMINATED BY ','indicates that the fields in the CSV file are separated by commas.ENCLOSED BY '"'specifies that fields containing special characters are enclosed within double quotes.LINES TERMINATED BY '\n'sets the line terminator to a newline character.IGNORE 1 LINESskips the first line (header) of the CSV file during the import.
Conclusion
Importing CSV data into MySQL is important for managing databases efficiently. In this Answer, we covered various methods to achieve this, including using the command line, MySQL Workbench, and automating the process with the LOAD DATA INFILE statement. Each method offers its own advantages and can be chosen based on your preference.
Free Resources