How to Import Excel to MySQL or MariaDB Database

Microsoft Excel's spreadsheet format is immensely popular for several reasons. Firstly, it is a widely used tool in organizations of all sizes, making it a familiar resource for various data-related tasks, including data import and export, analysis, and reporting. Furthermore, Excel offers a plethora of data validation and formatting functions, ensuring data consistency and accuracy. In many practical scenarios, importing data from Excel spreadsheets into databases is the default choice.

However, while this may be a standard procedure, it can become complex without the assistance of specialized tools. If you rely on MySQL databases in your work, as is often the case, and need to update your database with new data from XLS spreadsheets, dbForge Studio for MySQL can perform this essential task of importing data into databases along with providing comprehensive support for all aspects of data development, management, and administration.

Let us see how the Studio copes with the data imported from Excel into MySQL databases.

Connecting Microsoft Excel to MySQL database in different ways

MySQL experts typically employ two main approaches for importing data into databases. The first approach is to use the SQL LOAD DATA INFILE command, while the second involves specialized graphical user interface (GUI) tools.

The LOAD DATA INFILE command comes with a significant limitation in the context of importing data from Excel to MySQL: it doesn't directly support this type of data import. To use this method, XLS/XLSX files must be converted into CSV format first. Then, a query is composed to import data from the CSV file. This process requires manual specification of the filename, file path, and detailed data format, making it less than ideal for importing Excel data into MySQL.

On the other hand, GUI tools, like dbForge Studio for MySQL, address this limitation and simplify the entire configuration process. The Studio features a user-friendly graphical interface complete with icons and buttons, enabling quicker and more secure task setup without the need for coding. Additionally, dbForge Studio for MySQL offers several advantages, enhancing the overall ease and efficiency of the data import process.

• Direct data import into MySQL and MariaDB databases
• Compatibility with Excel versions 97-2003 and Excel 2007
• Data Import wizard for easy visual task configuration
• Detailed configuration options covering all aspects of the data import process
• Support for large files
• Various data import modes
• Templates for recurring data import tasks
• Capability to save data import scripts
• Automation of regular tasks through CLI

Next, we will explore how to configure the task of importing data from an Excel spreadsheet into a MySQL/MariaDB database using dbForge Studio for MySQL's GUI functionality.

Import Excel to a new or existing MySQL table using GUI tools

dbForge Studio for MySQL includes the Data Import wizard that allows you to import data into either a new or existing database table. This section focuses on configuring the task for importing data into a new table.

To start, open the Data Import wizard.

To import data into a new table, navigate to the Database menu and select Tasks > Import Data.

To import data into an existing table, first select the required database, expand the database node, and select the particular table. Right-click on that table and choose Import Data.

Upon selection, the wizard will launch, guiding you through the task configuration process.

On the Source file tab, select XLS (or XLSX) as the data format and browse to the necessary Excel data file. Click Next.

On the Destination tab, you are supposed to specify the target database and the table in that database.

If you are going to import data into a new table, set the connection name and provide the name of the target database and the required table in it. Click Next.

If you selected the option to import data into an existing table by right-clicking on it as described earlier, dbForge Studio for MySQL will fill in the Connection, Database, and Table details itself. Or, you can choose the Existing table option and specify the necessary table manually.

On the Options tab, preview the source data and configure additional settings if necessary:

• If the source data is in several sheets, specify which of them to choose.
• Specify the data range to import, it is highlighted with green in the Preview section. If you want to import a part of the data only, uncheck Detect a range automatically and enter Start Cell and End Cell values. Or, click the button next to these fields and double-click a cell in the Preview grid.
• Specify if you want to import data in rows or columns.
• Specify the header position if required. It will be highlighted in blue in the Preview section. If you don't specify the header position, the imported columns will get default names, such as column1, column2, etc.

After validating all your settings, proceed by clicking Next.

On the Data formats tab, specify the necessary parameters and click Next.

On the Mapping tab, map the Source columns to the Target ones.

If you are importing the data into a new table, dbForge Studio for MySQL will automatically create and map all the columns. Also, note that you can edit the Target column properties by right-clicking on it and selecting Edit from the menu or simply double-clicking the necessary column in the top grid.

If you are importing data into an existing table, the Studio will map columns with the same names. You need to map the rest manually. The Target columns are located at the top, and the Source columns are at the bottom of the wizard page. Click the Source column field and select the required columns from the drop-down list.

To cancel the mapping of all columns, click Clear Mappings. To restore it, click Fill Mapping.

Note also that you need at least one column with a primary key. Otherwise, some of the import modes will be disabled. Select the Key check box for a column with a primary key and click Next.

On the Modes tab, you can specify your preferred import mode by choosing from the following options:

• Append: Add new records to the destination table.
• Update: Update existing data with new inputs.
• Delete: Remove data in the destination table that matches the source file data.
• Repopulate: Replace all data in the destination table.

Once you've selected the desired mode, click Next.

On the Output tab, you can define the data import mode. By default, Studio for MySQL imports data directly into the database. Alternatively, you can choose to generate the data import script that you can edit and save for future use.

On the Errors handling tab, define how you want the program to behave in case of errors during the import process. Once you've configured your preferences, click Import.

dbForge Studio will notify you regarding the success or failure. Details about the data import process will be available in the log, and you can access it by clicking the Show log file button.

Close the wizard by clicking Finish.

Saving data import templates for future reuse

In dbForge Studio for MySQL, you can save the data import settings for a particular task as a template and then use these settings whenever needed. It is a beneficial option if your workflow suggests regular data import from Excel into MySQL and MariaDB databases.

Click Save when the task configuration is completed and verified and select Save Template.

When you want to import data under the same scenario, select the previously saved template and specify the location of the source data file. All the settings will be applied automatically.

Solving errors during Excel data import operations

Importing data from Excel spreadsheets into databases is a routine task. Still, errors can occur from time to time. In such cases, the Data Import wizard of dbForge Studio for MySQL offers a user-friendly approach to tackle issues.

Issue	Solution
Data Format and Compatibility	To avoid issues with file formats not matching the task settings, dbForge Studio for MySQL/MariaDB displays only files compatible with the selected import format, minimizing the risk of format-related errors.
Database User Permissions	Data import failures can occur if the user lacks the necessary permissions for the target table. To resolve this, verify and adjust the user's permissions and specific privileges in the Security Manager within dbForge Studio.
Character Encoding	If your source Excel file and MySQL/MariaDB database use different encodings, this can cause issues. To address this, check the encoding type on the Options tab in the Data Import wizard.
Data Validation and Cleaning	Ensure data compatibility by reviewing data types, allowable values, column names, data ranges, etc. These settings are adjustable on the Options and Mapping tabs of the Data Import wizard.
Handling Large Data Volumes	Consider splitting the data in large datasets into smaller segments to avoid timeouts or memory problems. dbForge Studio for MySQL/MariaDB also supports batch importing, which can be an effective solution for managing large volumes of data.

With dbForge Studio for MySQL/MariaDB, you can set the system to abort transactions immediately upon encountering an error, ensuring data consistency. Additionally, reviewing log files can help identify and address specific issues that may arise during the import process.

Finally, always back up your database before importing large volumes of data. This precautionary measure safeguards your database against potential issues during the import process.

Import other formats into MySQL