Merge multiple Excel into a database table

This article will introduce how to batch merge the data of multiple excel files into a database table.

Prepare data

As shown in the figure, we need to merge the data of 4 excel files into one.

The headers of the files are the same, but the data is different. Even if the headers are different, we can choose to ignore or add different columns

Start merging

Open the DiLu Converter tool, select the file to be merged, select Specified for the target table, and enter the table name or select a table that already exists in the database

View the results

You can see that 4 excel files were merged in 2 seconds, with a total of 40,000 rows

Continue to optimize

Although the data here is merged, if you want to distinguish which file each row of data comes from, how to do it?

Select Override in the import mode, then switch to the database options interface, and fill in the field to save the Excel name

After re-importing the data, you can see that a new source field has been added to the table

Here, by default, the name of the Excel workbook and worksheet is added to the end of each row of data. If you only want to save some characters in the file name, such as the date here, how to deal with it?

Select Rebuild in the import mode, save the Excel table name (regular extraction) to the column, select the Date (YYYYMMDD), and fill in the data_date in the field name. If you want to extract other characters, you can also write a regular expression to extract it yourself

View the data again