How to Import Text Files Into MySQL or MariaDB Databases

Importing data from external files into databases is a routine task for database administrators and operators. Although common and standardized, this task can be time-consuming and error-prone.

This issue becomes especially challenging for professionals focused on automation, particularly when using the command line interface (CLI). In such cases, they must write, test, and schedule scripts to automatically import data at specific intervals. Fortunately, these challenges can be addressed with the help of specialized tools designed to streamline the import process.

This tutorial focuses on importing data from TXT files into MySQL databases. We'll explore various methods, including standard SQL commands and GUI clients, discuss common errors, and demonstrate how to automate the data import process.

What is a text file, and why use it for MySQL imports?

A TXT file is the most basic, universal text file format that is easily created, read, and processed by nearly every software, including database management systems. TXT files can contain any text data, both structured and unstructured, with little or no formatting or styling.

A closely related format, the CSV file, is a structured variation of the plain text file. It stores tabular data, where each line represents a row and each value is separated by a comma or another delimiter to define columns. This structured format makes CSV files especially popular for importing data into relational databases, as their organization aligns perfectly with database tables. However, there are situations where TXT files are still preferred. For example, when working with:

• Custom or non-standard delimiters
• Complex or semi-structured data
• Legacy systems that output data in TXT format
• Large-scale bulk imports that prioritize performance and compatibility

In these cases, TXT files offer advantages in speed, simplicity, portability, and compatibility with most database import utilities. That's why, when importing data into MySQL databases, it's common to encounter TXT files as the data source.

Methods to import text files into MySQL

MySQL supports several methods of importing data from TXT files. We can use standard SQL commands and GUI-based database management tools, such as the default MySQL Workbench or third-party solutions like dbForge Studio for MySQL.

Let us consider all these options.

Importing via MySQL command line

The SQL LOAD DATA INFILE command is the standard built-in tool in MySQL that allows you to import data from TXT files into your MySQL database. This command also enables flexible settings to ensure precise task performance.

The command syntax is:

LOAD DATA INFILE 'file_name.txt' INTO TABLE table_name;

There are two variants of this command:

• LOAD DATA INFILE 'file_name' - indicates that the TXT file with the data is on the same machine as the MySQL host server.
• LOAD DATA LOCAL INFILE 'file_name' - indicates that the TXT file is not on the same machine as the MySQL host server.

For instance, we want to import data from a TXT file into the Sakila database (we will use this popular MySQL test database to illustrate our use cases in this tutorial). Open the MySQL CLI and connect to MySQL with local file import enabled:

mysql -u root -p --local-infile=1

Run the LOAD DATA command:

USE sakila;
                
LOAD DATA LOCAL INFILE 'D:/SQL/film_data.txt'
INTO TABLE film
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(column1, column2, column3);

In this query, we specify:

• The full path to the text file saved locally on the computer (not some remote server).
• The fields and lines delimiters used in the TXT file (as we mentioned earlier, TXT allows various types of delimiters).
• Skipping the header line of the file with the IGNORE 1 ROWS option (omit it if your file does not include the header line).
• The order of columns to import data (optional).

Using MySQL Workbench

MySQL Workbench is the official integrated development environment (IDE) for MySQL, and it offers a visual and user-friendly way to manage databases. In particular, it supports data import from CSV and JSON files into MySQL databases.

However, MySQL Workbench does not allow importing data directly from generic TXT files. A TXT file can contain comma-separated or tab-separated values, but the wizard specifically recognizes the CSV or JSON structure.

Therefore, to import data from a TXT file into a MySQL database using Workbench, you need to convert the TXT file into CSV format first. Alternatively, you may select another GUI tool that provides the necessary functionality.

Using GUI tools like dbForge Studio

GUI-based tools are highly popular among database professionals because they simplify routine operations. Besides the default MySQL Workbench, there are various solutions with graphical interfaces that help users handle various tasks, including importing data from TXT files. Among them, dbForge Studio for MySQL is one of the most popular options.

GUI tools provide several significant advantages:

• Ease of use: You don't need extensive SQL knowledge to import data. With intuitive visual wizards, you can monitor the entire import process and quickly address any issues that arise.
• Data visualization: You can preview and analyze the structure and contents of a TXT file before importing it.
• Flexible configuration: A wide range of import settings and options allows you to fine-tune import parameters directly within the wizard.

Overall, using a GUI tool makes the process faster, more accessible, and more efficient for users of all skill levels.

Let's now take a closer look at how to import data from TXT files using dbForge Studio for MySQL, a more powerful alternative to MySQL Workbench with a more robust functionality, better flexibility, and excellent automation capacities.

Why use dbForge Studio for MySQL?

dbForge Studio for MySQL performs a wide range of database tasks in both MySQL and MariaDB, including data import from all popular file formats. The following features help you configure data import tasks and ensure their accuracy:

• Visual import wizard: Configure even the most complex and demanding data import tasks quickly and intuitively.
• Support for multiple file formats: Import data from 10 popular data formats, including TXT, CSV, XLS, XML, JSON, and more.
• Column preview and mapping: Ensure accurate data import into both new and existing database tables.
• Import templates: Save import settings as templates to reuse them for similar operations, and skip reconfiguration. Apply the saved template to configure future imports instantly.
• Custom error handling: Define how the tool should behave in case of errors and log detailed error information.
• Task automation: Automate standard tasks and schedule them for regular execution.

Import data from a text file into a new MySQL table

In this section, we'll explore how to set up a task for importing TXT data into a new database table using dbForge Studio for MySQL. Assume we have a new table in the database and need to populate it with data. In our case, we want a table with the information about the most popular films based on the users' rentals.

Open dbForge Studio for MySQL, navigate to Database > Tasks > Import Data.

The Data Import Wizard opens. Select Text from the available formats, then browse to the necessary TXT file. Click Next.

On the Destination page, select the required server connection and the database. To import the data into a new table, choose New table and enter its name. Click Next.

On the Options page, configure the formatting options for imported data and click Next.

On the Data formats page, you can provide additional details for the task. On the Common Formats tab, you can specify the formats for null strings, thousand and decimal separators, boolean variables, and date and time. Note that the Autodetect Date and Time format option is selected by default.

On the Column Settings tab, you can configure the format settings for separate columns. There are four options: Null String, Left Quote, Right Quote, and Date and Time. After customizing all required settings, click Next.

On the Mapping page, map the Source and Target columns to ensure they match. dbForge Studio will automatically create a new table with the columns and map them to the source columns. If you need to cancel the settings, click Clear Mappings on the toolbar. To restore it, click Fill Mapping.

On the Modes page, choose the suitable import mode. Also, by default, the Studio performs the task in a single transaction and uses bulk insert if necessary. However, both are optional; if your scenario requires different settings, you can uncheck those options. Click Next.

On the Output page, configure how to perform the import. You can set the Studio to generate the data import script for additional examination and editing, save that data script to a file, or import data directly to the database. Click Next.

On the Errors handling page, specify how dbForge Studio should handle errors during import and whether you want to get a log file with details about the import session. After that, click Import.

dbForge Studio for MySQL will notify you about the results. Click Finish to complete the process.

You can view the new table with columns and imported data in the Database Explorer.

Import data from a text file into an existing MySQL table

Many scenarios suggest importing data into existing tables in MySQL databases. dbForge Studio for MySQL provides this option as well. Let us see how it works.

During the data import task configuration, choose Existing table on the Destination page and specify the target table.

Proceed with configuring the task in the same way as described earlier, in the scenario of importing data into a new table. Pay attention to the settings on the Modes page, as you need to specify how you want the data added to the existing data in a target table. You may choose to Append, Update, the mixed Append/Update mode, Delete, or Repopulate the table with the data.

After performing the task, you can view the data in the Studio.

Automate text imports in MySQL

Regularly importing data into databases is a common practice in data management, analytics, and business operations. Whether you need to consolidate data from multiple sources, ensure access to the most up-to-date information, track historical changes, generate regular reports, or synchronize and back up data across environments, all of these tasks rely on consistent data imports.

To make this process more efficient, it's essential to automate data import tasks. Automation ensures accuracy and saves time. One of the vital advantages of dbForge Studio for MySQL is the automation feature: CLI support enables you to automate any tasks performed with this dbForge Studio for instant or regular scheduled performance.

Saving dbForge templates for reuse

As already mentioned, you can save the data import task settings as a template. The option is available in the Data Import Wizard: click Save and choose Save Template.

When you need to perform a similar task again, select the necessary template and apply it. It configures your data import automatically and instantly.

You can use such templates both if you want to run the data import in real-time, and if you want to automate this task via CLI.

Using batch scripts for automated import

After configuring the task with the Data Import Wizard and ensuring that the settings are correct, you can save them as an executable batch script. Click Save and choose Save Command Line.

If you want to perform the data import, execute this file, and it will run the task instantly.

This file can be used for setting the data import task for scheduled performance.

Scheduling imports via Task Scheduler

Task Scheduler is a built-in Windows utility that lets you automate and run programs, scripts, and tasks on a schedule. To run the data import tasks with dbForge Studio for MySQL regularly on schedule, launch the Windows Task Scheduler, navigate to the Task Scheduler Library, and click Create Basic Task. Enter a name for the new task and click Next.

Specify the schedule in the Trigger section: the desired day and time. Click Next.

Proceed to the Action section > Start a program, and click Next. Browse to the .bat file you saved earlier after configuring the data import task.

Complete the task setup by clicking Finish.

When the scheduled time comes, Windows will launch dbForge Studio for MySQL and perform the data import task.

Common errors when importing text files into MySQL

While importing TXT files into MySQL databases, there can be some difficulties that complicate the process and lead to undesirable consequences. This is the list of the most common issues:

Data format and structure

Incompatibility of data formats between TXT files and MySQL can impact the way the data is presented. Ensure that the data structure in files matches the structure of the necessary databases.

Character encoding

Issues with character encoding can result in the alteration or loss of data during import. Establish the correct encoding to ensure the accurate visualization of textual information.

Line breaks

Incorrect delimiters or line breaks in the rows of TXT files can disrupt the data structure and cause issues during import. This is particularly crucial when working with files created on different operating systems.

Missing columns

If the database schema includes columns that are absent in TXT files, it can lead to errors or data loss. Ensure that all necessary fields are present and correspond to the expected structure. You can do it when mapping the columns in dbForge Studio.

Large data volumes

Extensive volumes of data can influence the processing and loading of data into databases. Consider the possibility of splitting TXT files into smaller parts or using optimized loading methods.

Access rights

Issues with file or database access rights can hinder the proper execution of the import. It is important to make sure that users have the required permissions.

Conclusion

Importing data from TXT files into MySQL or MariaDB databases is a common but often time-consuming task when performed manually. It requires both technical knowledge and attention to detail. Fortunately, modern GUI tools can make this process significantly easier and faster.

dbForge Studio for MySQL takes data import to the next level. In addition to providing a simple visual interface, it allows you to automatically configure import settings using templates, automate recurring import tasks, and schedule imports to run at specified times. You can explore these and many other powerful features in dbForge Studio for MySQL. Download the fully functional 30-day trial and experience its robust capabilities in action!

FAQ

Import other data formats into MySQL databases