Loading large datasets into a MySQL database can be a tedious and time-consuming task. Fortunately, the LOAD DATA INFILE command provides a quick and efficient way to insert data from a CSV file into a MySQL database. In this article, we will explore how to use this command to insert selected columns from a CSV file into a MySQL database.
First, let's take a look at the syntax of the LOAD DATA INFILE command:
LOAD DATA INFILE 'file_name'
INTO TABLE table_name
FIELDS TERMINATED BY 'delimiter'
OPTIONALLY ENCLOSED BY 'enclosure'
LINES TERMINATED BY 'line_terminator'
IGNORE number LINES
(column1, column2, column3, ...)
SET column1 = value1, column2 = value2, ...
As you can see, the command takes in several parameters, such as the file name, the table name, the delimiter used in the CSV file, and the line terminator. It also allows for the optional specification of the number of lines to ignore at the beginning of the file and the set values for specific columns.
Now, let's say we have a CSV file with the following data:
employee_id, first_name, last_name, department, salary
1, John, Smith, Sales, 50000
2, Jane, Doe, Marketing, 60000
3, Bob, Johnson, Finance, 70000
4, Sarah, Williams, HR, 45000
5, David, Brown, IT, 55000
Our goal is to insert the employee_id, first_name, department, and salary columns into a MySQL table called employees. The first step is to create the table with the necessary columns:
CREATE TABLE employees (
employee_id INT,
first_name VARCHAR(50),
department VARCHAR(50),
salary INT
);
Once the table is created, we can use the LOAD DATA INFILE command to insert the data from the CSV file into the table. Since our file has a header row, we will specify to ignore the first line. We will also use a comma as the delimiter and a newline as the line terminator.
LOAD DATA INFILE 'employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
IGNORE 1 LINES
(employee_id, first_name, department, salary);
This command will insert the data from the CSV file into the specified columns of the employees table. However, what if we want to insert data into different columns or set values for specific columns?
To insert data into specific columns, we can use the SET clause in the LOAD DATA INFILE command. For example, if we want to insert the employee_id, first_name, last_name, and salary columns into the table, we can use the following command:
LOAD DATA INFILE 'employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
IGNORE 1 LINES
(employee_id, first_name, last_name, salary)
SET department = 'Unknown';
This command will insert the data from the CSV file into the specified columns, and the department column will be set to 'Unknown' for all records.
Alternatively, if we want to set different values for specific columns, we can use the SET clause with the appropriate column names and values. For example, if we want to set the department to 'Sales' for all records and the salary to 55000 for the records with an employee_id of 1 and 3, we can use the following command:
LOAD DATA INFILE 'employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
IGNORE 1 LINES
(employee_id, first_name, last_name, department, salary)
SET department = 'Sales',
salary = CASE
WHEN employee_id IN (1, 3) THEN 55000
ELSE salary
END;
This command will set the department to 'Sales' for all records and change the salary to 55000 for the records with an employee_id of 1 and 3.
In conclusion, the LOAD DATA INFILE command is a powerful tool for inserting data from CSV files into a MySQL database. With its various parameters and the ability to set values for specific columns, it provides a convenient way to load large datasets quickly and efficiently. So next time you need to insert data from a CSV file into a MySQL database, remember to use the LOAD DATA INFILE command.
