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.
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
Wait a moment, and you can view the merged data in the database
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
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.
View the data again
Top comments (0)