DEV Community

ryjfgjl
ryjfgjl

Posted on

1

Merging data from multiple Excel files

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files or multiple sheets. At this point, we need to merge them into one Excel file or into a database for analysis and processing.

Example

As shown in the figure, we need to merge the data from four Excel files into one.

Image description

The headers of the files are the same, except for the data. Even if the headers are different, you can choose to ignore or add different columns

Using the ExcelToDatabase tool

Select the files to be merged, manually specify the target table, and enter the table name or select a table that already exists in the database

Image description

Wait a moment, and you can view the merged data in the database

Image description

Although the data is merged here, how can we distinguish which file each row of data comes from?
Select the Overwrite option in the import mode, switch to the database options interface, and fill in the Excel file name to be saved to the field

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

Image description

Here, the full name of the Excel file is added to the end of each row of data. What if you only want to save the date inside the file name?
Select the import mode as "reconstruction", save the Excel file name (which can be extracted using regular expressions) to the column, select the date (YYYYMMDD), and fill in the field name with the date. If you want to extract other characters, you can write your own regular expression to extract them.

Image description

View the data again

Image description

Introduction and Download of ExcelToDatabase

Image of Docusign

Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more

Top comments (0)

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay