DEV Community: ryjfgjl

Fastest way to import excel into mysql

ryjfgjl — Sun, 25 May 2025 11:24:37 +0000

This article will introduce the fastest way to import Excel data into MySQL.

Preparation

Here, we prepare an Excel table, as shown below:

New Connection

Open the DiLu Converter tool and create a new database connection firstly. Here we select MySQL database. For a detailed introduction to creating a new database connection, please refer to: Create a new database connection guide

New Import

After creating a new database connection, click New Import

Start importing

Select the Excel file to be imported and click Start.

View Results

Optimization

As you can see, the table fields created by the default are all of the varchar type, which makes the import speed as fast as possible and avoids import failures caused by inconsistency between data and data types.

We can also let the tool automatically detect data types . The advantage of this is that the field type can be more consistent with the actual data and make subsequent SQL queries more convenient.

Select Rebuild Mode - Select Auto Detect, and click Start to re-import

See again

Save the import configuration

We can save the import configuration so that we can repeat the import next time.

You can see the saved import in the object interface or under the database connection on the left

Next time you open the software, first double-click the connection name to open the connection - double-click the import name to open the saved import.

Just click to start

About DiLu Converter

DiLu Converter is a powerful automated Excel import and export tool that supports more than 10 databases such as MySQL, Oracle, SQL Server, PostgreSQL, IBM DB2, Access, and Hive. The supported file formats include xls, xlsx, xlsm, xlsb, csv, txt, xml, json, and dbf. Its native user interface brings users a comfortable experience of simplified Excel import and export, making Excel import and export easier than ever before. Whether you want one-click, batch, and personalized import and export, or want to use scheduled tasks to achieve unattended full automation, DiLu Converter can bring you unprecedented productivity improvement.

Go to DiLu Converter Official Website

How to import excel into mysql only 1 step

ryjfgjl — Fri, 23 May 2025 01:01:28 +0000

This article will introduce how to import Excel data into MySQL quickly and easily.

Preparation

Here, we prepare an Excel table, as shown below:

New Connection

New Import

After creating a new database connection, click New Import

Start importing

Select the Excel file to be imported and click Start.

View Results

Optimization

We can also let the tool automatically detect data types . The advantage of this is that the field type can be more consistent with the actual data and make subsequent SQL queries more convenient.

Select Rebuild Mode - Select Auto Detect, and click Start to re-import

See again

Save the import configuration

We can save the import configuration so that we can repeat the import next time.

You can see the saved import in the object interface or under the database connection on the left

Next time you open the software, first double-click the connection name to open the connection - double-click the import name to open the saved import.

Just click to start

About DiLu Converter

Go to DiLu Converter Official Website

How to import Excel to MySQL

ryjfgjl — Thu, 15 May 2025 22:36:46 +0000

This article will introduce how to import Excel data into MySQL quickly and easily.

Preparation

Here, we prepare an Excel table, as shown below:

New Connection

New Import

After creating a new database connection, click New Import

Start importing

Select the Excel file to be imported and click Start.

View Results

Optimization

We can also let the tool automatically detect data types . The advantage of this is that the field type can be more consistent with the actual data and make subsequent SQL queries more convenient.

Select Rebuild Mode - Select Auto Detect, and click Start to re-import

See again

Save the import configuration

We can save the import configuration so that we can repeat the import next time.

You can see the saved import in the object interface or under the database connection on the left

Next time you open the software, first double-click the connection name to open the connection - double-click the import name to open the saved import.

Just click to start

About DiLu Converter

Go to DiLu Converter Official Website

One-click import Excel to MySQL

ryjfgjl — Sun, 11 May 2025 09:24:36 +0000

This article will introduce how to import Excel data into MySQL quickly and easily.

Preparation

Here, we prepare an Excel table, as shown below:

New Connection

New Import

After creating a new database connection, click New Import

Start importing

Select the Excel file to be imported and click Start.

View Results

Optimization

We can also let the tool automatically detect data types . The advantage of this is that the field type can be more consistent with the actual data and make subsequent SQL queries more convenient.

Select Rebuild Mode - Select Auto Detect, and click Start to re-import

See again

Save the import configuration

We can save the import configuration so that we can repeat the import next time.

You can see the saved import in the object interface or under the database connection on the left

Next time you open the software, first double-click the connection name to open the connection - double-click the import name to open the saved import.

Just click to start

About DiLu Converter

Go to DiLu Converter Official Website

Import a large CSV more than 1G into a database quickly

ryjfgjl — Mon, 05 May 2025 02:16:42 +0000

This article will introduce how to quickly import csv or txt files larger than 1g into the database.

Here, we prepared a CSV file with 10 million rows x 30 columns x 4GB. At first, I tried to import it with Navicat.

The first problem I encountered was that the encoding format was unknown, which led to many attempts to find the correct encoding.

The second problem is that Navicat reads and writes one by one. Although this does not take up memory, the speed is very slow.

The third problem is that halfway through the import, an error was reported because the field length was not enough! ! ! I had to reset the field length and import from 0 again. . . What's worse is that after setting one field, another error was reported. . .

Now, we use the DiLu Converter Import tool to solve these problems one by one.

Begin Import

Encoding: It can be automatically identified by default (for files with unknown encoding). If the encoding format of the file is known, you can select or enter its encoding format to speed up the parsing speed.

Chunk Size: You can set the number of rows to import at a time according to the available memory of your computer. My computer has a total of 16G memory, about 9G is available, and 500,000 is set in a batch. Here, you must set the size according to the available memory. If the setting is too large, the memory may overflow, and if the setting is too small, it will not speed up.

And The just click start

You can see that it takes about 20 seconds to import a batch of 500,000 rows, and the memory usage is less than 1G

Finally, it took about 5 minutes to import this csv file of 10 million rows x 30 columns x 4GB, and the maximum memory usage was about 1G. Because the tool imports files in batches according to batch size, no matter it is 1 file or hundreds or thousands of files, no matter a single file is 1G or 100G, as long as the batch size is set reasonably, it can be imported quickly, stably and without consuming memory.

Importing Excel with multiple headers into a database

ryjfgjl — Mon, 05 May 2025 01:20:24 +0000

This article will explain how to import this kind of complex multi-level header Excel table into the database like this:

Determine the data area

Start importing

Open the DiLu Converter, select the file, select the label of the column to be imported, fill in: C:O, fill in 3-5 for the row number of the header, fill in 4 for the number of rows to be imported, and click Start

View the results

Continue to optimize
Here, the number of rows to be imported is set to 4, which is suitable for the situation where the number of data rows is determined. If the number of data rows is uncertain, but the last row of note data needs to be excluded, the number of skipped end rows can be set to 1. If you also want to remove the blank rows in the last second row, you can also set the Delete Blank Rows option in the Data Cleaning Options interface.

Batch export database tables to Excel

ryjfgjl — Sun, 04 May 2025 06:50:59 +0000

This article will introduce how to export multiple or even thousands of database tables to Excel files in batches.

Prepare data

The following figure shows the tables in the database. We need to export them all to Excel files. Here we take MySQL database as an example

New Export

Open the DiLu Converter tool and create a new database connection. Here we take MySQL database as an example. After the connection is successful, select Export and click New Export.

Start export

Click Select Table-Select the table name we want to export, select a folder for the target Excel file, and click Start

View the results

Continue to optimize

You can see that the exported workbook name and title row name are the table name and field name of the database, respectively, both in English letters. If there are comments in the database table, the comments can be used as the workbook name and title row name.

We can also add a date suffix to the exported table name to distinguish it

You can also export in batches to csv files

View the results

Export database tables to Excel with one click

ryjfgjl — Wed, 30 Apr 2025 03:45:14 +0000

At work, we often need to export database tables to Excel. Usually we use the export function provided by tools such as database editors to export, but their export functions are usually relatively simple.

This article will introduce a simple, easy-to-use and powerful export method.

New Export

Open the DiLu Converter tool and create a new database connection. Here we take the MySQL database as an example. After the connection is successful, select Export and click New Export.

Export table

Select the table to be exported and click Start

View the results

Export query results

Enter the query sql statement in the input box and click Start

Export to CSV

Select csv as the target Excel format and click Start

Continue to optimize

When we export the table, we can see that the exported workbook name and header name are the table name and field name of the database, respectively, both in English letters. If there are comments in the database table, the comments can be used as the workbook name and header name.

View the results again

Save the export

We can save the export so that we can repeat the export next time.

One-click import of Excel to database

ryjfgjl — Sun, 06 Apr 2025 00:49:04 +0000

At work, we often need to import Excel data into the database, but for database novices, this may not be an easy task; for database experts, this may be very cumbersome.

This article will introduce how to import Excel data into a database quickly and easily.

Preparation

Here, we prepare an Excel table, as shown in the figure:

New Connection

Open the DiLu Converter tool and first create a new database connection. Here we take MySQL database as an example. For a detailed introduction to creating a new database connection, please refer to: Create a new database connection guide

New Import

After creating a new database connection, click New Import

Start importing

Select the Excel file to be imported and click Start.

View Results

Optimization

As you can see, the table fields created by the default import tool are all of the varchar type, which makes the import speed as fast as possible and avoids import failures caused by inconsistency between data and data types.

We can also let the tool automatically identify data types . The advantage of this is that the field type can be more consistent with the actual data and make subsequent SQL queries more convenient.

Select Rebuild Mode - Select Auto Detect, and click Start Re-importing

See again

Save the import configuration

We can save the import configuration so that we can repeat the import next time.

You can see the saved import in the object interface or under the database connection on the left

Next time you open the software, first double-click the connection name to open the connection - double-click the import name to open the saved import.

Just click to start

About DiLu Converter

Go to DiLu Converter Official Website

How to import excel into access only 1 step

ryjfgjl — Thu, 17 Oct 2024 13:12:57 +0000

In our work, we often need to import Excel data into Access for data analysis and processing. Usually we use database tools like Navicat or the Excel import function in the editor that comes with the database to manually import it. However, it is not easy for both professional and non-professional data processing personnel to correctly import an Excel file into a database.
Now, through years of practice, we have solved various problems through the DiLu Converter tool, achieving one-click import of Excel into the Access .

DiLu Converter is an automated tool that can import Excel files into a database in bulk.
Support for 6 common formats of Excel files (xls/xlsx/xlsm/xlsb/csv/txt) and 8 database types (mysql/oracle/sql server/postgresql/access/hive/Access/dameng).
Let's take an example of importing a product information table into a Access database.

Now, we can import it to the database with 1 step through the DiLu Converter tool.

Create a new database connection

Click on the toolbar (New Connection) to select your database type. Here, we will use Access as an example. To create other database connections, please refer to the DiLu Converter New Database Connection Guide.

Add a configuration

1 Step to import: Select the file to import and click "Start"

Finally, enter the database editor to view the import results

Introduction and Download of DiLu Converter

DiLu Converter - Automation tool for batch importing Excel files into database

How to import excel into sqlite only 1 step

ryjfgjl — Wed, 16 Oct 2024 11:23:43 +0000

In our work, we often need to import Excel data into SQLite for data analysis and processing. Usually we use database tools like Navicat or the Excel import function in the editor that comes with the database to manually import it. However, it is not easy for both professional and non-professional data processing personnel to correctly import an Excel file into a database.
Now, through years of practice, we have solved various problems through the DiLu Converter tool, achieving one-click import of Excel into the SQLite .

DiLu Converter is an automated tool that can import Excel files into a database in bulk.
Support for 6 common formats of Excel files (xls/xlsx/xlsm/xlsb/csv/txt) and 8 database types (mysql/oracle/sql server/postgresql/access/hive/sqlite/dameng).
Let's take an example of importing a product information table into a SQLite database.

Now, we can import it to the database with 1 step through the DiLu Converter tool.

Create a new database connection

Click on the toolbar (New Connection) to select your database type. Here, we will use SQLite as an example. To create other database connections, please refer to the DiLu Converter New Database Connection Guide.

Add a configuration

1 Step to import: Select the file to import and click "Start"

Finally, enter the database editor to view the import results

Introduction and Download of DiLu Converter

DiLu Converter - Automation tool for batch importing Excel files into database

How to import Excel into PostgreSQL only 1 step

ryjfgjl — Tue, 15 Oct 2024 12:34:52 +0000

In our work, we often need to import Excel data into PostgreSQL for data analysis and processing. Usually we use database tools like Navicat or the Excel import function in the editor that comes with the database to manually import it. However, it is not easy for both professional and non-professional data processing personnel to correctly import an Excel file into a database.
Now, through years of practice, we have solved various problems through the DiLu Converter tool, achieving one-click import of Excel into the PostgreSQL .

Common Problems of Manually Importing Excel to PostgreSQL

The actual length of the data exceeds the set length of the data type and cannot be imported
Too many columns in Excel cannot create a table The encoding format of the 3.csv file is incorrect, resulting in failure to import or importing messy code
The Excel header contains special characters and cannot be created
The file is too large, and manual import causes memory overflow or slow import
Too many files, too many manual import steps, very cumbersome
Excel does not have a header or multilevel header Manual import cannot handle it
The table name or field name exceeds the database limit and cannot be created
Blank cells, empty spaces at the beginning and end of data, duplicate data, missing data, data replacement, etc. Manual import cannot handle these situations 。。。

DiLu Converter is an automated tool that can import Excel files into a database in bulk.
Support for 7 common formats of Excel files (xls/xlsx/xlsm/xlsb/csv/txt/xml) and 8 database types (mysql/oracle/sql server/postgresql/access/hive/sqlite/dm).
Let's take an example of importing a product information table into a PostgreSQL database.

Now, we can import it to the database with 1 step through the DiLu Converter tool.

Create a new database connection

Click on the toolbar (New Connection) to select your database type. Here, we will use PostgreSQL as an example. To create other database connections, please refer to the DiLu Converter New Database Connection Guide.

Add a configuration

1 Step to import: Select the file to import and click "Start"

Finally, enter the database editor to view the import results

Introduction and Download of DiLu Converter

DiLu Converter - Automation tool for batch importing Excel files into database