MySQL Import CSV File in Database/Table

How can we import CSV files in MySQL database or table?

MySQL has a feature that allows us to import the CSV file into a database or table. A CSV stands for comma-separated values, which is a plain text file that contains the list of data and allows us to save it in a tabular format. It is mainly used to exchange data between different applications. Sometimes it is also called as character separated values. Generally, it is used with the comma character to delimit (separate) data, but can also use other characters, like semicolons.

This file is very beneficial when we need to export complex data from one application to a CSV file, and then import the file data into another application. We can use the CSV file with a various spreadsheet program like MS Excel or Google Spreadsheets. A CSV file cannot allow saving formulas in this format.

Structure of CSV File

The structure of the CSV file contains a list of data that is separated by a comma operator. For example, the below data structure explains it more clearly:

To import a CSV file, we will use the LOAD DATA INFILE statement. This statement is used to read a text file and import it into a database table very quickly.

We have to ensure the following things before importing a file into the database table:

• An empty table where the data from the file will be imported.
• A CSV file that matches the order, number of columns, and data type in each column of the table.
• The user account connected with the database server in MySQL has a FILE and INSERT privileges.

Suppose we have a database named mytestdb. Now, we will create an empty table named Address_Book in this database using the below query:

Now, we can verify whether the newly created table is empty or not using the SELECT statement that can be shown in the below output:

Next, we will create a CSV file. The following address_book.csv file contains the first line as column headings, and other lines are the data to be inserted into the table.

Finally, the following statement allows us to import data from the address_book.csv to the address_book table.

From the above,

File Location: It specifies the location of the CSV file into the system.

FIELD TERMINATED BY ',': It is used to indicate the field of the file, which is terminated by a comma.

ENCLOSED BY '"': It is used to specify the field of the file enclosed by double quotation marks.

LINES TERMINATED BY '\r\n': It is used to terminate each line of the CSV file.

IGNORE 1 ROWS: It is used to ignore the first line of the CSV file, which has column headings that are not required to import into the table.

We can now verify whether the newly created table is filled with the imported data or not using the SELECT statement that can be shown in the below output.

Transforming data while importing

Sometimes, while importing the CSV file into the database table, the data format does not match the corresponding column in the table. In such a case, we can transform that column by using the SET clause with the LOAD DATA INFILE statement.

Suppose the date column of a CSV file is in the format of "dd/mm/yy", and it does not match the corresponding column of the target table while importing. To overcome this issue, we need to transform it into a MySQL date format using STR_TO_DATE() function as follows:

Importing CSV file from Client to MySQL Server

The LOAD DATA INFILE statement also allows us to import CSV file form client (local system) to a remote MySQL Server.

When we add the LOCAL clause in the LOAD DATA INFILE statement, the client program can read the CSV file on the local system and sends it to the MySQL database server. This file will then be uploaded into the temporary folder of the database server operating system such as C:\windows\temp on Windows and /tmp on Linux OS. These folders are not determined or configurable by MySQL.

The following statement explains it more clearly:

In the above, we can see that we have only added the LOCAL option in the statement. But while importing the large CSV file with the LOCAL option, make the file loading a little slower. It is because this option takes time to transfer the file on the database server.

When we use the LOCAL option, the MySQL user account does not need to have the FILE privilege for importing it.

The LOAD DATA LOCAL INFILE statement has some security issues while importing the file from local to a remote server. In such a case, we should be aware of to avoid potential security risks.

Importing CSV file using MySQL Workbench

MySQL Workbench has an in-built tool that allows us to import the CSV file data into a table. It also helps us to edit data before making changes in the database or table.

The following are the steps that show to import CSV file data into a database table:

Step 1: First, we need to create a table in the desired database. It ensures that the table has the same number of columns as in our CSV file. Suppose, we have a table named "address_book" that does not contain any data shown in the below image:

Step 2: Once we open the table, we can import the file using the import button shown in the red color box of the below screen.

Click on the import button, and it will pop up the window screen where we need to choose a CSV file and then click on the Next button.

Step 3: In the next wizard, we need to select the destination table and click on the Next button. Here, we have two options one is to use an existing table, or we can create a new table.

Step 4: In the next wizard, we need to configure the import settings and click on Next.

Step 5: In the next wizard, we have to monitor the execution process and click on Next->Next>Finish to complete the process.

Step 6: We can verify the resulting weather the data imported successfully or not by executing the SELECT query.

NOTE: From the above techniques, we can also load data from other text file formats.