Import data in MySQL from a CSV file using LOAD DATA INFILE

One of the most common formats to record and store data are CSV(Comma Separated Values) and Excel files. As the data in these CSV files grows, it becomes important to store and maintain this data in a scalable Database Management System like MySQL. This allows organizations to manage and analyze their data from a central repository effectively. To load data from file to table in MySQL, you can either write SQL statements or use the GUI(Graphical User Interface) of MySQL Workbench, phpMyAdmin, or automated data integration tools like Hevo Data.  

In this article, you will learn how to effectively load data from file to table in MySQL using these 3 different methods.

Understanding Data Loading in MySQL Methods

To seamlessly load data from file to table in MySQL, you can go through the following methods:

Method 1: Using Hevo Data to Load Data from File to Table in MySQL

Hevo Data is the only No-code Data Pipeline solution that can cost-effectively help you move data from 150+ data sources like FTP/SFTP & Google Sheets to your desired destination, such as MySQL and other Data Warehouses, in a completely hassle-free & automated manner. Using Hevo, you can easily upload files to your MySQL database in formats such as CSV, JSON to MySQL, and XML.

To effortlessly load data from file to table in MySQL using Hevo, follow the simple steps given below:

• Step 1: Connect Hevo Data with your system by setting FTP/SFTP as a source. You can provide a unique name to your Pipeline and information such as Port Number, Username, Password, etc. Hevo currently supports CSV, JSON, and XML formats. You can specify the file format as CSV and the Delimiter as “,”.

• Step 2: For completing the process to load data from file to table in MySQL, you can start by providing your MySQL database credentials such as your authorized Username and Password, along with information about your Host IP Address and Port Number value. You will also need to provide a name for your database and a unique name for this destination.

Method 2: Using Command-Line and MySQL Workbench to Load Data from File to Table in MySQL

Using simple SQL commands you can load data from file to table in MySQL. To achieve this follow the steps given below via the Command-Line and MySQL Workbench:

• Step 1: Create Table
• Step 2: Importing Data into your Table
• Step 3: Transforming Data while Importing
• Step 4: Importing File from Client to a remote MySQL Database Server
• Step 5: Importing CSV Files using MySQL Workbench

Step 1: Create Table

To load data from file to table in MySQL, you have first to create a table in your database if there is no existing table. Consider creating a table named items containing item_id, item_name, cost, & manufacturing_date. 

Using the CREATE TABLE statement, you can easily design your new table in your MySQL Database:

CREATE TABLE items (
    item_id INT NOT NULL AUTO_INCREMENT,
    item_name VARCHAR(255) NOT NULL,
    manufacturing_date DATE NOT NULL,
    cost DECIMAL(10 , 2 ) NULL,
    PRIMARY KEY (item_id)
);

Step 2: Importing Data into your Table

Assume you have an items.csv file containing the following item details with the first line being the column headings:

item_id, item_name,manufacturing_date,cost

1,”ball pen”, 20210722,20
2,”pencil”,20220212,10
3,”paint_brush”,20210115,30

Using the following LOAD DATA INFILE commands, you can load your c:tmpitems.csv file into 

LOAD DATA INFILE 'c:/tmp/items.csv' 
INTO TABLE items 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY 'n'
IGNORE 1 ROWS;

The above statement used to load data from file to table in MySQL has the following commands:

• FIELDS TERMINATED BY: Comma(,) terminates a field of the file.
• ENCLOSED BY: Fields enclosed by double quotation marks. 
• TERMINATED BY: Each line of your CSV file is terminated by ‘n’.
• IGNORE ROWS: Since the first row of the CSV file has the filed headers, you can mention IGNORE ROWS 1 to only start loading data from the second row.

Step 3: Transforming Data while Importing

When you want to load data from file to table in MySQL, you will often notice that the format of the incoming data doesn’t accurately match the target table. For instance, the manufacturing date format in the items.csv file is “yyyy/mm/dd” and the target table format is “mm/dd/yyyy”. 

Using the SET clause in the LOAD DATA INFILE statement, you can transform the CSV file date format to the one present in the destination table in MySQL. You can use the SET clause & the str_to_date() function while performing the operation to load data from file to table in MySQL. 

LOAD DATA INFILE 'c:/tmp/items.csv'
INTO TABLE items
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY 'n'
IGNORE 1 ROWS
(item_name,@manufacturing_date,cost)
SET manufacturing_date = STR_TO_DATE(@manufacturing_date, '%m/%d/%Y');

Step 4: Importing File from Client to a remote MySQL Database Server

Using the LOCAL modifier with the LOAD DATA INFILE statement, you can import files from the client (local computer) to a remote MySQL database server. Here, the client program reads the file present on the client and sends it to the desired remote MySQL Database Server. The file is generally imported to the database server operating system’s temporary folder. For instance, C:windowstemp on Windows or /tmp on Linux. Note that this folder is not configurable or determined by MySQL.

LOAD DATA LOCAL INFILE  'c:/tmp/items.csv'
INTO TABLE items
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY 'n'
IGNORE 1 ROWS;

While using LOCAL to load data from file to table in MySQL, you don’t need to have the FILE privilege to import the file. However, it has some security issues that you should be aware of to avoid potential security risks. Also, if you are trying to load a big CSV file using the LOAD DATA LOCAL INFILE statement, then it will be a slower process as it takes relatively more time to transfer the file to the database server.  

Step 5: Importing CSV Files using MySQL Workbench

An alternative approach to load data from file to table in MySQL is via MySQL Workbench. To get started with MySQL Workbench to load data from file to table in MySQL, follow these simple steps:

• Step 1: To load data from file to table in MySQL, open up your target table in MySQL Workbench and click on the Import button available on the top horizontal bar.

• Step 2: In the Import dialog box, browse through your system and go to the location where you have saved your CSV file. Select the file and click on the Open button. 

• Step 3: Your data will from the CSV file will be displayed. You can review and edit your data by clicking on the Edit button. After you are done with the changes, click on the Apply button. 

• Step 4: Finally, a “Apply SQL Script to Database” dialog box will appear on your MySQL Workbench. Click on the Apply button to confirm the import and load data from file to table in MySQL. 

Method 3: Using phpMyAdmin to Load Data from File to Table in MySQL

Another method to load data from file to table in MySQL is using an open-source tool like phpMyAdmin. It is commonly used to administer MySQL & MariaDB databases and can also be employed to load data from file to table in MySQL. By default, it allows data loading for a maximum size of 2 MB CSV files. However, it also allows you to configure the settings and increase the limit for importing large CSV files.    

• Step 1: Open phpMyAdmin and navigate to the left panel of the GUI. Either select the table from the list of existing tables or create a new table by clicking the New option. This will require you to mention the column names as well as the data types for them.
• Step 2: Once you have created or selected a table, a list of options will appear on the top horizontal bar. Click on the Import menu to load data from file to table in MySQL.  
• Step 3: Click on the Choose file button to locate your CSV file. You can choose the encoding of the file to UTF-8 and select CSV in the Format list.

To easily load data from file to table in MySQL using phpMyAdmin, follow these simple steps:

• Step 4: phpMyAdmin also allows you to mention the character such as a comma used as a delimiter in your CSV file or set the other configurations associated with your CSV file. Finally, click on the Go button to start the process to load data from file to table in MySQL.

After you have successfully completed the process to load data from file to table in MySQL, phpMyAdmin will display a confirmation message.

Understanding the Statements to Load Data from File to Table in MySQL

Here is an overview of using the following statements to Load Data from file to table in MySQL:

• LOAD DATA Statement
• INSERT Statement

LOAD DATA Statement

By using the LOAD DATA statement, you can read rows from a text file into a table very quickly. You can read this file from the server host or the client host, based on whether the LOCAL modifier is provided. The LOCAL modifier also influences operations such as data interpretation and error handling. 

Just like the SELECT … INTO OUTFILE statement is used to write data from a table to a file, the LOAD DATA statement reads the file back into the table. You can also use the mysqlimport utility that sends a LOAD DATA statement to the server to load data from file to table in MySQL. 

To understand the application of the Load Data statement, consider a table containing the following Product details:

Product ID	Item	Category	Quantity	Date of Manufacturing	Date of Expiry
1	Sandals	Footwear	1200	2021-03-22
2	Pencils	Stationery	200	2019-05-02
3	Ball Pen	Stationery	1000	2021-04-11
4	Butter	Food	300	2022-02-14	2022-08-14
5	Spoons		1000	2021-07-09

To populate your empty MySQL Table, you can first create a text file containing a row for each of your products, then load data from file to table in MySQL with a single statement.

For that, you have to create a text file product.txt containing one record per line, with all the values separated by tabs, and written in the order in which the columns were provided in your initial CREATE TABLE statement. For cases where the values are missing such as Category for Spoons or the Date of Expiry, you can use NULL values. You can use N (backslash, capital-N) to demonstrate these values. For instance, the record for your product Spoons would appear as shown below(the whitespaces represent a single tab): 

5 Spoons N 1000 2021-07-09 N

• To load data from file to table in MySQL, you can use this statement:

mysql> LOAD DATA LOCAL INFILE '/path/product.txt' INTO TABLE product; 

• You may also find files created by an editor in Windows that have rn as a line terminator. In those cases, you can use the following command:

mysql> LOAD DATA LOCAL INFILE '/path/product.txt' INTO TABLE product
LINES TERMINATED BY 'rn';

If you are an Apple user with MacOS, you would likely want to use LINES TERMINATED BY ‘r’. MySQL also provides complete flexibility by allowing you to mention the column value separator and end-of-line marker explicitly in the LOAD DATA statement. However, the default values i.e. tab and linefeed are sufficient for the statement to read the file product.txt easily.

To understand the LOAD DATA statement in detail, you can go through the following aspects:

• Index Handling
• Duplicate-Key and Error Handling
• Input File Location
• Security Requirements
• Local vs Non-Local Operation

Index Handling

You can ignore foreign key constraints during the data loading process by issuing the SET Foreign_Key_Checks = 0 statement before running the LOAD DATA command. 

Whenever you run the LOAD DATA statement for a completely empty MyISAM table, then all non-unique indexes are created in a separate batch. This usually makes the LOAD DATA command much faster when there are many indexes. For some scenarios, you can also create an index even faster by disabling the index using the ALTER TABLE … DISABLE KEYS statement before starting the data loading process into the table and rebuilding the index with ALTER TABLE … ENABLE KEYS command after loading the file.

Duplicate-Key and Error Handling

To effectively handle & control cases where the new rows you want to input have the same unique key values(PRIMARY KEY or UNIQUE index values) as the existing table rows, you can use the following modifiers: 

• REPLACE: Using the REPLACE modifier, you can replace the existing rows with new rows with the same unique key values. 
• IGNORE: Using the IGNORE modifier, you can discard the new rows that duplicate an existing row on a unique key value. The LOCAL modifier has the same effect as IGNORE. This is because the server has no way to stop the transfer of files in the middle of the process. 

For cases when none of REPLACE, IGNORE, or LOCAL is specified, an error will occur if duplicate key values ​​are detected and the rest of the text file is ignored. 

Apart from duplicate keys, IGNORE and LOCAL also affect error handling as described below:

• Without IGNORE or LOCAL, the operation ends with a data interpretation error. 
• Using IGNORE or LOCAL will warn you of a data interpretation error and will continue loading even if SQL mode is restricted. 

Input File Location

The following LOCAL modifier rules can identify the location of the LOAD DATA input file:

• If the LOCAL modifier is not mentioned, the file must be present on the server host. The server reads the file directly and finds it as follows:
  •  If you have named your files as an absolute path name, the server will use the file as specified. 
  •  If the file name is a relative path name containing the first component, the server will search for a file associated with the data directory. 
  • If the filename does not contain a predecessor component, the server searches for the file in the database directory of the default database. 
• If the LOCAL modifier is mentioned, the file must be present on the client host. The client program reads the file and finds it as follows:
  • If the file name is an absolute path name, the client program will use the file as specified. 
  • If the file name is a relative path name, the client program searches for the file based on the calling directory. 

 If you are using the LOCAL modifier, the client program reads the file and transfers its contents to the server. Now, the server will make a copy of the file in the directory that contains the temporary files. Note that the LOAD DATA LOCAL statement can fail if there is not enough space for a copy of this directory. 

The non-local rule means that the server reads the file named  ./product.txt associated with the data directory and the file named product.txt from the database directory of the default database. For instance, if you run the following LOAD DATA statement when FirrstDB is the default database, the server will read the test.txt file from the firstdb database directory, even if the statement explicitly loads the file into a table in the seconddb database.

LOAD DATA INFILE 'test.txt' INTO TABLE seconddb.new_table;

Security Requirements

For Non-Local load operations, the server reads text files present on the server host and must meet the following security requirements: 

• You must have the FILE privileges provided by MySQL.
• Operations must follow the settings of the secure_file_priv system variable:
  • If the variable value is a non-empty directory name, the file must exist in that directory. 
  • If the variable value is empty (which is not safe), the file should only be readable by the server. 

While you are performing the LOCAL load operation, the client program reads the text file present on the client host. The contents of the file are sent by the client over the connection to the server, so letting the server access the file directly may be a faster method than using LOCAL. However, with LOCAL you don’t need the FILE permissions. The file can be placed in any directory accessible by the client program.

Local vs Non-Local Operation

When you start applying the LOCAL modifier for the LOAD DATA operations, you will notice the following changes compared to non-LOCAL operations: 

•  It modifies the expected location of the input file. 
•  It modifies the security requirements of the statement.
• A LOCAL modifier produces the same effect as the IGNORE modifier on interpreting the contents of the input file and handling errors. 

It is to be noted that the LOCAL works only if both the server and the client are configured to allow it. For instance, if you start mysqld with the local_infile system variable disabled, LOCAL will generate an error.

INSERT Statement

Apart from the LOAD DATA statement to load data from file to table in MySQL, you can use the INSERT statement to insert new rows into an existing table. You can use the INSERT statement to load data from file to table in MySQL in the following forms:

• INSERT… VALUES
• INSERT…VALUES ROWS()
• INSERT…SET
• INSERT…SELECT
• INSERT…TABLE
• INSERT…ON DUPLICATE KEY UPDATE

The INSERT statement is the simplest method to insert new single rows one at a time by providing values for each column, in the order in which the columns were listed in the CREATE TABLE statement. For instance, for adding a new product to your product table you can use the following command:

mysql> INSERT INTO product
VALUES ('Permanent Marker','Stationery','1000','2021-06-06',NULL);

As shown above, you can directly use NULL instead of the N used in LOAD DATA statements. However, when you have multiple entries to load data from file to table in MySQL, you have to execute several INSERT statements as compared to a single LOAD DATA command. Generally, when you want to load data from a text file, it is suggested to use the LOAD DATA command as it is 20 times faster than the INSERT command.

It is recommended to use the INSERT statements with multiple VALUES lists to insert several rows at a time when you need to insert several rows from the same client. While using the INSERT command you can also configure the bulk_insert_buffer_size variable for faster data insertion, especially when you want to add data to a non-empty table. 

Additional Reads:

• Creating MySQL Composite Primary Keys Simplified
• How To Export MySQL Database using Command Prompt?
• mysqldump: Exporting Databases and Tables Made Easy

Conclusion

In this article, you have learned how to effectively load data from file into table MySQL using 3 different methods. You can use the Command line in MySQL to load from file to table in MySQL via SQL statements such as LOAD DATA and INSERT. For a more user-friendly interface, you can use the MySQL Workbench GUI to import your CSV files. You can also employ an open-source database administration tool like phpMyAdmin to load data from file to table in MySQL.

All these manual methods are a good choice if you are rarely required to load data from file to table in MySQL with little to no data transformation. However, for frequently performing complex transformations & loading large-sized CSV files in real-time you can opt for a No-Code Automated Data Integration platform like Hevo Data! 

Visit our Website to Explore Hevo

If you are using MySQL as your Database Management System and searching for a no-fuss alternative to manual data integration, then Hevo can effortlessly automate this for you. Hevo allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiffy.

Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your Data Integration process. Do check out the pricing details to understand which plan fulfills all your business needs.

Tell us about your experience of completing the process to load data from file to table in MySQL! Share your thoughts with us in the comments section below.