What is Load Data Infile in MySQL?

In MySQL, the LOAD DATA INFILE statement is used to import data from a file into a database table. This is a useful tool for database administrators who need to add a large amount of data to a database quickly and efficiently. The statement bypasses the need for multiple INSERT statements, making it faster and more efficient.

The MySQL LOAD DATA INFILE statement reads data from a file and inserts it into a table in the MySQL database. Any delimited or fixed-width format, including CSV, TSV, is acceptable for the file. The statement also allows you to specify how the data in the file is formatted and preprocessed before inserting it into the table.

Syntax

Data from an external file can be imported into a database table by using the LOAD DATA command in MySQL. It offers a versatile and effective approach to swiftly load massive volumes of data. Let's examine each part of the LOAD DATA command individually:

• [LOCAL]: This is an optional keyword that specifies that the file is located on the client machine rather than the server. It allows for local file loading.
• INFILE 'file_name': It Specifies the name of the file containing the data to be loaded. the full path of the file needs to be entered if it is not in the same directory as the MySQL server.
• [REPLACE | IGNORE]: This is an optional clause that specifies how duplicate records should be handled. If the REPLACE keyword is used, it will replace existing rows with new data from the file if duplicate keys are found. The IGNORE keyword, on the other hand, will skip the duplicate rows and continue with the loading process.
• INTO TABLE table_name: The name of the table into which the data will be loaded. You must ensure that the table structure matches the data file format.
• [FIELDS | COLUMNS]: This is an optional clause that defines how the file's fields or columns are delimited, enclosed, or escaped.
• TERMINATED BY 'string': Defines the delimiter that will be used to separate fields in the file. A'string' can be a single character or a string of characters.
• [[OPTIONALLY] ENCLOSED BY 'char']: Specifies the character used to enclose file fields. The keyword OPTIONALLY indicates that fields may or may not be enclosed.
• ESCAPED BY 'char': The escape character used to escape special characters or the enclosure character itself is specified.

Example

Now we know what is LOAD DATA command so let's consider an example to understand how the MySQL LOAD DATA INFILE command works.

• Suppose we have a file named employees.txt file containing the names of the employees:

  

• We want to load this data in a Table named Employee with a single column named name. So let's see how the command will be for creating the Table:

• Now, let's load this given text file into the Table we just created using the LOAD DATA command:

• After the successful insertion of data, the output will be like this:

Verification

Let's verify whether the data is properly inserted into the database or not:

• For that, let's go to the Table and check what data is present in the table using the SELECT Statement:

• The output of the Table will be like the following:

  NAME
  John
  Jane
  Tom

• Hence, we have successfully imported the Data in the given table.

FIELDS and LINES

Using the FIELDS and LINES clauses, we can easily distinguish the data in the field and line terminators from the text/csv file to be uploaded:

Example

Now Let's increase the fields that we wanna import into the Tables

• Suppose we have a file named employees.txt file containing the names and ages of the employees:

  

• We want to load this data in a Table named Employee with columns named name and age. So let's see how the command will be for creating the Table:

• Now, let's load this given text file into the Table we just created using the LOAD DATA command:

• Let's understand what FIELDS and LINES clauses do here:

  • FIELDS TERMINATED BY ',': This clause specifies that the fields in the file are terminated (or separated) by a comma (','). Each comma-separated value will be considered a separate field in the table.
  • LINES TERMINATED BY '\n': This clause specifies that each line in the file is terminated by a newline character ('\n'). Each line will be treated as a separate row in the table.

• After the successful insertion of data, the output will be like this:

Verification

Let's verify whether the data is properly inserted into the database or not:

• For that, let's go to the Table and check what data is present in the table using the SELECT Statement:

• The output of the Table will be like the following:

  NAME	AGE
  John	21
  Jane	22
  Tom	28

• Hence, we have successfully imported the Data in the given table.

STARTING BY Clause

The STARTING BY clause in the LOAD DATA INFILE command allows you to specify a prefix that each line in the file must start with. This can be useful when there are header lines or comments at the beginning of the file that need to be skipped during the data load.

Also, it helps mark the start of any record.

Example

Now let's take an example of importing the data using this clause

• Suppose we have a file named employees.txt file containing the names and ages of the employees and records are started with ! to specify the records:

  

• We want to load this data in a Table named Employee with columns named name and age. So let's see how the command will be for creating the Table:

• Now, let's load this given text file into the Table we just created using the LOAD DATA command and STARTING BY Clause:

• Let's understand what STARTING clauses do here:

  • STARTING BY '!': This clause specifies that the new lines are starting by !. Hence, a new record will be created where it finds !.

• After the successful insertion of data, the output will be like this:

Verification

Let's verify whether the data is properly inserted into the database or not:

• For that, let's go to the Table and check what data is present in the table using the SELECT Statement:

• The output of the Table will be like the following:

  NAME	AGE
  John	21
  Jane	22
  Tom	28

• Hence, we have successfully imported the Data in the given table by using STARTING BY clause also.

Uploading Specific Columns from the File

Suppose, we want to upload some specific column values from the text file. In that case, you need to specify the column names in the query only.

Example

Now let's take an example of importing specific column data.

• Suppose we have a file named employees.txt file containing the names and ages of the employees.

  

• We want to load this data in a Table named Employee with columns named name, salary, and age. But here we only wanna fill in the column name and age. So let's see how the command will be for creating the Table:

• Now, let's load this given text file into the Table and only fill certain columns we have, For that let's see how well the command looks like:

• We have specified in the query only the columns we wanna add to the table.

• After the successful insertion of data, the output will be like this:

Verification

Let's verify whether the data is properly inserted into the database or not:

• For that, let's go to the Table and check what data is present in the table using the SELECT Statement:

• The output of the Table will be like the following:

  NAME	SALARY	AGE
  John	NULL	21
  Jane	NULL	22
  Tom	NULL	28

• Hence, we have successfully imported the Specified column Data in the given Table.

Input Preprocessing

Input preprocessing in the context of the LOAD DATA statement refers manipulation or transformation of the data from the input file before it is loaded into the database table. This preprocessing step allows the user to modify the data according to their needs or to make sure that the data is compatible with the table structure.

Example

Now let's take an example of importing the data by preprocessing it.

• Suppose we have a file named employees.txt file containing the names and salaries of the employees.

  

• We want to load this data in a Table named Employee with columns named name, salary, bonus, and Total. So let's see how we will calculate the field Total by using Input Preprocessing. Let's create a Table First:

• Now, let's load this given text file into the Table and calculate the total salary, For that let's see how well the command looks like:

• In the above command, we have calculated the Total column before loading the data into the Table.

• After the successful insertion of data, the output will be like this:

Verification

Let's verify whether the data is properly inserted into the database or not:

• For that, let's go to the Table and check what data is present in the table using the SELECT Statement:

• The output of the Table will be like the following:

  NAME	SALARY	BONUS	TOTAL
  John	10000	5000	15000
  Jane	20000	3000	23000
  Tom	25000	2000	27000

• Hence, we have successfully imported the preprocessed Data in the given Table.