How to Bulk Upload CSV file data into MySql Table? A very fast way using LOAD DATA.

Introduction

So I was working on a small module of a project where the client needed functionality in which he can upload a certain .csv file directly into their required database.

Being a rookie, I implemented WebAPI in DotNet Core, and in the Repository Layer (DAL), I ran a loop on all the rows in CSV, Created a MySql Command, and then executed it. Code worked fine for CSV with rows less than 1000 but after that, the API started taking time in minutes.

Until I found, that MySql provides a LOAD DATA statement, which solves my problem, and voila, after using load data my TAT reduced drastically. So this tutorial will tell you how to use this MySQL statement to ease your work, lessen your code, and have a most responsive Bulk Upload Application.

What is the LOAD DATA INFILE Statement?

As per the official docs of MySQL states, the LOAD DATA statement is used to Read Data from the file in a very fast manner. It does exactly the opposite of what SELECT…INTO OUTFILE, which is used to read the data from the table to file.

mysqlimport utility provided by MySql internally calls the LOAD DATA statement on the server to import the data.

How to Use it?

Load Data Statement with all possible options

All options available for load data statements are mentioned above but we won’t be needing all of the options to upload a simple CSV into your desired table.

A simple example :

LOAD DATA INFILE ‘data.csv’ INTO TABLE db.my_table

An essential example :

Load Data Statement I used

Needless to say that your CSV file should be properly formatted for this statement to work.

This statement runs at the MySql level, hence the source files are copied from client to server in order to import them resulting in some security issues.

In MySql 8.0, the capability to use LOCAL is set as False by default. As your server and client should be configured to have LOCAL permited, some of you might get a permission error.

In case of permission error, we need to override it by enabling the local_infile:

SET GLOBAL local_infile = true;

Note: Overriding this flag is not a security solution but rather an acknowledgement for accepting the risks, you can refer this documentation for more information.

Summary

MySql LOAD DATA statement is used to read files within very less time.

LOAD DATA LOCAL copies the source file to your server via MySql hence a security measures on the server side should be implemented.

mysqlimport utility uses the LOAD DATA statement internally.

You can ignore the headers in CSV by adding IGNORE 1 LINES in the statement.