This article will introduce how to schedule upload Excel data to the database.
Prepare data
As shown in the figure, we have a table called product information, which records all the company's product information. When there is a new product, new data will be added to the table. When there is a change in product information, the table data may also be updated. Now, we need to synchronize the data of this table to the database, and when the Excel data is updated, the data in the database can be updated regularly.
Initialization
The DiLu Converter tool we use imports Excel to the database table with one click and imports a copy of the initialization data.
Determine the update method
There are generally two ways to update data: full update and add update
Full update: that is, delete all the data in the database table, and then import all the data in the Excel table. This method is simple and crude, ensuring the complete consistency of Excel and database data. It is suitable for scenarios where there is full data in Excel.
Add update: that is, based on the existing data in the database table, only the newly added or modified data in Excel is updated. This method requires each row of data to have a fixed unique identifier to distinguish the data, such as the product id here. This is suitable for scenarios where there is only part of the data in the Excel table and there is a unique identifier in the data.
Note: Add updates cannot update physically deleted data
Below we introduce two update methods
Full update
First, save the import just now and name it Product Information, select Overwrite for the import mode, and click Save
In the software toolbar-Tasks Schedule -Add Tasks, add a scheduled task called Product Information-Full Update (here we keep the default settings of the scheduled tasks first, and make supplements later)
You can see the running status of the task in the task schedule interface
Edit excel, delete, add, and modify a data test effect respectively. The following figure shows the data in the database table before modification. We will delete product 10, change the unit price of product 1 to 88, and add a new product 11
After modification, as shown in the figure, save excel
Check the database table data again, it has been updated
Add update
If a full update is in progress, stop it first.
Here, we use product id as the unique identifier of the data and set it as the primary key in the database table.
Prepare add data, as shown in the figure, a copy contains modified and added data.
Add an import, select the add data file, select the target table, select the update mode, click the + on the right, and you can see that the default is to update according to the primary key of the database table. We keep the default and click Save
Add a scheduled task called Product Information-Add Update
You can see that the data has been updated. Let's modify another data test. Here we modify the unit price of product 1 to 100 and add a new product 14
As shown in the figure:
Check the database table again, it has been updated
Scheduled task settings
Subtask type: Select a database connection, you can add saved imports, exports and jobs
Task execution time:
Month: 1-12 can be filled in
Week: 1-7 can be filled in, that is, Monday to Sunday
Day: 1-31 can be filled in
Hour: 0-23 can be filled in
Minute: 0-59 can be filled in
Second: 0-59 can be filled in
If you don't know how to fill in, you can click the Timing Settings Assistant.
Send an email reminder when the task fails: Check and fill in the email settings in the software menu bar-Settings-Email interface.
Note: Scheduled tasks require the software to run all the time. When you click x to close the software interface, the software will exit immediately and stop the scheduled task by default. You can go to the software menu bar-Tools-Settings, check to hide in the tray area instead of exiting the program directly. After hiding, you can right-click the program icon in the tray area and choose to open the main interface or exit the program.
Startup
Applications that need to run scheduled tasks are generally placed on servers that run 24 hours a day, but if it is a scheduled task running on your own computer, you may restart the computer every day. At this time, we can add the program to the startup, and the scheduled task will automatically run after starting the program.
Other timing methods
The built-in scheduled task of DiLu Converter needs to keep the program open, and the scheduled task will automatically stop running after exiting the program. If you want to run the scheduled task in the background without opening the program, you can let other special scheduled task programs call the API provided by DiLu Converter. For example, the task scheduler that comes with Windows, this method provides a background operation method without a graphical interface, and can automatically start when the computer is turned on.
API usage can refer to:
