Bulk Uploading To A MySQL Database: A Quick Guide
Tags: sql, database, data, mysql
Published: 2020-03-18 15:21:14

I recently got this question from a friend of mine, and the first thing I thought about was [dbForge Studio for MySQL](https://www.devart.com/dbforge/mysql/studio/). So, I quickly wrote a detailed instruction, and posting it here, as it may be useful to someone else.

First of all,  let’s consider a set of files. These are sourced from the City of Los Angeles Open Data Portal and contains [anonymized data on service calls made by the public to the Los Angeles Police Department](https://data.lacity.org/browse).Each file contains a year’s worth of data.

![Bulk Uploading 01](https://dev-to-uploads.s3.amazonaws.com/i/j5gd6puxgeibq46zonkc.png)

Each file has a set of fields:

- A unique incident number
- Area where the incident occurred
- District where it was reported from
- Date of dispatch
- Dispatch time
- A call type code
- Call type description

We have created a table called LAPD_Service_Calls with these fields in a test database:

![Bulk Uploading 02](https://dev-to-uploads.s3.amazonaws.com/i/ytiywozpqzdm7nzci5ph.png)

To load a single data file, we can right click on the table and select “Import Data…” from the context menu:

![Bulk Uploading 03](https://dev-to-uploads.s3.amazonaws.com/i/dmq3xk3rizb5mkzifyhk.png)

This starts the Import Data wizard. We select the CSV file option and choose the source file and then click “Next”.

![Bulk Uploading 04](https://dev-to-uploads.s3.amazonaws.com/i/64inuib883r60jcy7q4q.png)

In the “Destination” screen, we have the database and the table already selected, so we click “Next”:

![Bulk Uploading 05](https://dev-to-uploads.s3.amazonaws.com/i/7jxpcpz0j9tobqvgmbfl.png)

In the “Options” screen, we are not changing any of the options already selected for us. dbForge Studio for MySQL is smart enough to read the file, determine the header row, field separators etc. We click “Next”:

![Bulk Uploading 06](https://dev-to-uploads.s3.amazonaws.com/i/ngqlcyx739l84k11sts9.png)

We leave the “Data formats” screen as is, and click “Next”:

![Bulk Uploading 07](https://dev-to-uploads.s3.amazonaws.com/i/69sq08o68j0320ojkiyd.png)

In the next screen, we accept the mapping already defined and click “Next”:

![Bulk Uploading 08](https://dev-to-uploads.s3.amazonaws.com/i/aglnt64opqwa93rvpmgw.png)

In the next screen, we accept the append-only and bulk insert mode of loading and click “Next”:

![Bulk Uploading 08](https://dev-to-uploads.s3.amazonaws.com/i/tw7spnaybgv1658rrhtn.png)

In the “Output” screen, we keep the option to import data directly into the database and click “Next”:

![Bulk Uploading 10](https://dev-to-uploads.s3.amazonaws.com/i/svipba529154ht41esfe.png)

In the “Errors handling” screen, we select the option to abort the load after the first error:

![Bulk Uploading 09](https://dev-to-uploads.s3.amazonaws.com/i/g7inioopfq88vbp7lbcn.png)

We can now click the “Import” button to start importing the selected file. But we don’t want to load a single file - we want to load all the files.

Most MySQL GUI tools will not have an answer for this scenario - but dbForge Studio for MySQL does. And this is possible through something called a “template”. Basically, a dbForge Studio data import or export template is a file that holds all the options you have chosen in the wizard. You can call this template from the dbForge Studio command line tool and pass it each data file name as an input parameter.

To create the template, we select the down arrow key beside the “Save” button in the final screen, 

![Bulk Uploading 11](https://dev-to-uploads.s3.amazonaws.com/i/dsdi3c24scem7boird2u.png)

and select “Save Template…” from the context menu. This will allow us to save the options as a Data Import Template (.DIT) file. Here, we are saving it in the same folder as the data files:

![Bulk Uploading 12](https://dev-to-uploads.s3.amazonaws.com/i/8cit2by20y8sgzx3q7np.png)

Next, we write a small batch file like the one shown below, and save it in the data folder as well.

```
echo off

set data_path="C:\Temp\Data_Import\*.csv"
dir /b /s %data_path% > load_file_list.txt


set apppath="C:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com"


if ERRORLEVEL 0 @(
echo on
for /F %%i in (load_file_list.txt) do @(
%apppath% /dataimport /templatefile:Load_LAP_Service_Calls_File.dit /inputfile:"%%i"
)
)
del load_file_list.txt
```
Here, we are calling a Windows directory command to get a list of the source data files and saving the output in a plain text file (load_file_list.txt). We then set the app path to the dbForge Studio for MySQL’s command line utility. Next, we start reading the file. For each source file name and path listed, we are calling dbForge Studio for MySQL’s command line tool and passing it the data import option (/dataimport). We are also specifying the template file to use (/templatefile:). In this case, it is the template file we created before. The final parameter is the input file for the template (/inputfile). We are passing the data file name here.

We then run the batch file from the command prompt. The output will show data being loaded. After several minutes, all files are successfully loaded:
```
C:\Temp\Data_Import>data_import.bat

C:\Temp\Data_Import>echo off
=====================================================================Devart dbForge Studio 2019 for MySQL version 8.1.45.0
Copyright 2020 Devart. All rights reserved.
=====================================================================

Data import started at: 15/01/2020 10:00:32 PM

Import mode: Append

Importing data from LAPD_Calls_for_Service_2010.csv to table test_database.LAPD_Service_Calls:
916240 rows imported, 100%

Data import finished at: 15/01/2020 10:03:58 PM
=====================================================================
Devart dbForge Studio 2019 for MySQL version 8.1.45.0
Copyright 2020 Devart. All rights reserved.
=====================================================================

Data import started at: 15/01/2020 10:04:03 PM

Import mode: Append

Importing data from LAPD_Calls_for_Service_2011.csv to table test_database.LAPD_Service_Calls:
891376 rows imported, 100%

Data import finished at: 15/01/2020 10:07:23 PM
…
…
…

=====================================================================
Devart dbForge Studio 2019 for MySQL version 8.1.45.0
Copyright 2020 Devart. All rights reserved.
=====================================================================

Data import started at: 15/01/2020 10:33:25 PM

Import mode: Append

Importing data from LAPD_Calls_for_Service_2019.csv to table test_database.LAPD_Service_Calls:
2059515 rows imported, 100%

Data import finished at: 15/01/2020 10:40:25 PM
```
As we just saw, [dbForge Studio for MySQL](https://www.devart.com/dbforge/mysql/studio/) successfully bulk-loaded data from a number of files into a target table with very minimal effort.