Method for realizing excel batch import interface by means of temporary table
By creating temporary tables in the database and batch insertion and verification, the problem of inefficient repeated conflict verification when batch importing data is solved, and efficient data import and system performance optimization are achieved.
Patent Information
- Application Number
- CN202411563642.2
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-11-05
- Publication Date
- 2025-03-07
AI Technical Summary
In the prior art, when importing data in batches, it is difficult to efficiently process the repeated conflict verification of data, resulting in low operational efficiency and poor data import performance.
By creating temporary tables in the database, adding uuid and number fields, batch insertion and batch verification of data, including checking whether the data conflicts with the existing data in the target data table, verifying the data format and value range, and finally batch importing the data that meets the criteria into the target table.
It significantly reduces the time consumption of data rationality verification during data import, improves the system's processing capability and data import efficiency, and avoids the timeout problem caused by processing data one by one.
Smart Images
Figure CN119577010A_ABST
Abstract
Description
Technical Field
[0001] The invention discloses a method for implementing an Excel batch import interface by means of a temporary table, and relates to the technical field of data processing and database. Background Art
[0002] In the data processing process, batch importing data is a common operation. Each piece of data needs to be verified for legality before importing to check whether it meets the import conditions, including field format, value domain legality check and data duplication conflict check. In particular, duplication conflict check requires one-by-one comparison of the duplication condition fields of the data to be imported and the database table data. Therefore, if the data to be imported is imported in batches using SQL, it is difficult to filter duplicate data in the same batch of data. However, executing SQL import statements one by one and performing duplication verification before importing will lead to low efficiency of the operation, greatly affecting the performance of data import. Summary of the invention
[0003] In view of the problems of the prior art, the present invention provides a method for implementing an Excel batch import interface with the help of a temporary table, which improves the efficiency of batch importing data, significantly reduces the time consumption of data rationality verification during the data import process, and improves the processing capacity of the system.
[0004] The specific scheme proposed by the present invention is:
[0005] The present invention provides a method for implementing an Excel batch import interface by means of a temporary table, comprising:
[0006] Step 1: Before data import begins, create a temporary table in the database. The structure of the temporary table adds uuid and number fields based on the target data table. The uuid field is used to distinguish batches of import operations, and the number field is used to record the row number of the imported data in the table.
[0007] Step 2: Batch insert the data to be imported into the temporary table;
[0008] Step 3: Use SQL statements to batch verify the data in the temporary table. The verification content includes:
[0009] Checks whether the data conflicts with existing data in the target data table.
[0010] Verify the data format,
[0011] Verify data value domain;
[0012] Step 4: Import the successfully verified data that meets the import conditions into the target table in batches;
[0013] Step 5: Generate a failure table excel for the data that failed the verification and output it;
[0014] Step 6: After the data is imported, clean up the temporary table: delete the data in the order of the uuids generated when this batch of data is inserted into the temporary table.
[0015] Furthermore, in step 3 of the method for implementing an Excel batch import interface with the help of a temporary table, it is checked whether the data conflicts with the existing data in the target data table, including: according to the duplicate detection logic, the fields of the current batch data in the temporary table and the existing data in the target table are compared, and the data that meets the non-duplicate condition is screened out. If there is duplicate data in the current batch data, only the first one of the duplicate data is inserted, and the duplicate data after the first one are all judged as duplicates and output from the failure table.
[0016] Furthermore, in step 3 of the method for implementing an Excel batch import interface with the help of a temporary table, verifying the data format includes: checking the legitimacy of the data format in the basic information, where the basic information includes a mobile phone number and an ID card number.
[0017] Furthermore, in step 3 of the method for implementing an Excel batch import interface with the help of a temporary table, verifying the data value domain includes: checking whether the value of a preset field is filled in and selected within a given range.
[0018] Furthermore, in step 5 of the method for implementing an Excel batch import interface with the help of a temporary table, the failed data is written into the failure table Excel according to the number field in the temporary table and the reason for the import failure, and a failure data prompt is generated and output to indicate the failed data and the reason for the failure to the operator.
[0019] The present invention also provides a device for implementing an Excel batch import interface by means of a temporary table, comprising a temporary table management module, a batch import module, a batch verification module and a cleaning module.
[0020] The temporary table management module creates a temporary table in the database before data import begins. The structure of the temporary table adds uuid and number fields based on the target data table. The uuid field is used to distinguish batches of import operations, and the number field is used to record the row number of the imported data in the table.
[0021] The batch import module batch-inserts the data to be imported into the temporary table;
[0022] The batch verification module uses SQL statements to perform batch verification on the data in the temporary table. The verification content includes:
[0023] Checks whether the data conflicts with existing data in the target data table.
[0024] Verify the data format,
[0025] Verify data value domain;
[0026] The batch import module imports the successfully verified data that meets the import conditions into the target table in batches;
[0027] The batch import module generates a failure table excel for the data that failed verification and outputs it;
[0028] After the data is imported, the cleaning module cleans up the temporary table: the data is deleted uniformly according to the uuid sequence generated when the data in this batch is inserted into the temporary table.
[0029] Furthermore, the batch verification module of the device of the Excel batch import interface implemented by means of a temporary table checks whether the data conflicts with the existing data in the target data table, including: performing field comparison on the current batch data in the temporary table and the existing data in the target table according to the duplicate detection logic, screening out the data that meets the non-duplicate condition, if there is duplicate data in the current batch data, only inserting the first of the duplicate data, and all the duplicate data after the first are judged as duplicates and output from the failure table.
[0030] Furthermore, the batch verification module of the device for implementing the Excel batch import interface with the help of a temporary table verifies the data format, including: checking the legality of the data format in the basic information, and the basic information includes the mobile phone number and the ID card number.
[0031] Furthermore, the batch verification module of the device for implementing the Excel batch import interface with the help of a temporary table verifies the data value domain, including: checking whether the value of the preset field is filled in and selected within a given range.
[0032] Furthermore, the device cleaning module of the Excel batch import interface implemented by means of a temporary table writes the failed storage data into the failure table Excel according to the number field in the temporary table and the reason for the import failure, generates a failed data prompt and outputs it, and indicates the failed data and the reason for the failure to the operator.
[0033] The benefits of the present invention are:
[0034] Improve efficiency: By batch inserting and batch verifying data, the number of database operations is greatly reduced and the efficiency of data import is improved.
[0035] Reduce timeouts: Avoids timeouts when processing data one by one, making large-scale data import more feasible.
[0036] Simplified logic: Data validation and data import are processed separately, which simplifies business logic and makes it easier to maintain and expand.
[0037] Optimize performance: Using temporary tables for batch operations can effectively reduce the database load and improve the overall performance of the system. BRIEF DESCRIPTION OF THE DRAWINGS
[0038] Figure 1 It is a schematic flow chart of the method of the present invention. DETAILED DESCRIPTION
[0039] The present invention is further described below in conjunction with the accompanying drawings and specific embodiments so that those skilled in the art can better understand the present invention and implement it, but the embodiments are not intended to limit the present invention.
[0040] Example 1
[0041] The present invention provides a method for implementing an Excel batch import interface by means of a temporary table, comprising:
[0042] Step 1: Before data import begins, create a temporary table in the database. The structure of the temporary table adds uuid and number fields based on the target data table. The uuid field is used to distinguish batches of import operations, and the number field is used to record the row number of the imported data in the table.
[0043] Among them, uuid is used to distinguish the batches of the import operation, so that after the storage is completed, it can be deleted according to the batch to avoid confusion with other batches of data; the latter is used to record the number of rows of data in the import table, so that it can be uniformly output in the failure table returned by the interface to help the operator locate the failed data location and prompt the reason for the failure. The other fields are consistent with the target table fields and are used to store the data to be imported into the target table. The temporary table is only used to temporarily store data and will be cleared after the import operation is completed.
[0044] Step 2: Batch insert the data to be imported into the temporary table; batch insertion can significantly reduce the number of database operations and avoid performance bottlenecks caused by inserting one by one.
[0045] Step 3: Use SQL statements to batch verify the data in the temporary table. The verification content includes:
[0046] Step 31: Check whether the data conflicts with the existing data in the target data table. For example, compare the fields of the current batch of data in the temporary table with the existing data in the target table according to the duplicate detection logic, and filter out the data that meets the non-duplicate condition. If there is duplicate data in this batch, only the first one is inserted, and the duplicate data after the first one are all judged as duplicates and need to be output from the failure table.
[0047] Step 32: Verify the data format, including: checking the legitimacy of the data format in the basic information, such as the legitimacy of the mobile phone number and ID card number.
[0048] Step 33: Verify the data value domain, for example, the value of a field can only be filled in and selected within a given range. The verification process is performed through SQL statements, which can efficiently process data and effectively filter and verify the data in the temporary table. For example, when importing self-built house information in batches, it is necessary to determine whether it is a repeated input of self-built house data based on the similarity of multiple field values. Among them, self_build_houses_temp is a temporary table, self_build_houses is the target table, enterprise, structure, fwjk, dzzh, conclusion, manage are the fields used for duplicate detection, and min(id) is to achieve that only the first record is inserted when there is duplicate data in the same batch. The code can be referred to as follows:
[0049]
[0050]
[0051]
[0052] Step 4: Batch import the successfully verified data that meets the import conditions into the target table; through SQL batch operations, the number of database operations can be reduced and the import efficiency can be improved.
[0053] Step 5: Generate a failure table excel for the data that failed verification and output it. According to the number field in the temporary table and the reason for the import failure, the failed data is written into excel to generate a failed data prompt table and output it, indicating the failed data and the reason for the failure to the operator so that it can be avoided in advance during subsequent operations to improve operational efficiency. For example, in the self-built house information import mentioned above, when the data of this batch in the temporary table has not been cleared, the remaining data in the temporary table can be filtered out by id, which is the so-called duplicate data. These data are the data to be written into the failure table, where ids is a string of data ids concatenated from the filtered data in step 3. The code can be referred to as follows:
[0054]
[0055] Step 6: After the data is imported, clean up the temporary table: delete the data in the order of the uuids generated when this batch of data is inserted into the temporary table.
[0056] Example 2
[0057] The present invention also provides a device for implementing an Excel batch import interface by means of a temporary table, comprising a temporary table management module, a batch import module, a batch verification module and a cleaning module.
[0058] The temporary table management module creates a temporary table in the database before data import begins. The structure of the temporary table adds uuid and number fields based on the target data table. The uuid field is used to distinguish batches of import operations, and the number field is used to record the row number of the imported data in the table.
[0059] The batch import module batch-inserts the data to be imported into the temporary table;
[0060] The batch verification module uses SQL statements to perform batch verification on the data in the temporary table. The verification content includes:
[0061] Checks whether the data conflicts with existing data in the target data table.
[0062] Verify the data format,
[0063] Verify data value domain;
[0064] The batch import module imports the successfully verified data that meets the import conditions into the target table in batches;
[0065] The batch import module generates a failure table excel for the data that failed verification and outputs it;
[0066] After the data is imported, the cleaning module cleans up the temporary table: the data is deleted uniformly according to the uuid sequence generated when the data in this batch is inserted into the temporary table.
[0067] As the information interaction and execution process between the modules of the above-mentioned device are based on the same concept as the embodiment of the method of the present invention, the specific contents can be found in the description of the embodiment of the method of the present invention and will not be repeated here.
[0068] Likewise, the benefits of the device of the present invention are:
[0069] Improve efficiency: By batch inserting and batch verifying data, the number of database operations is greatly reduced and the efficiency of data import is improved.
[0070] Reduce timeouts: Avoids timeouts when processing data one by one, making large-scale data import more feasible.
[0071] Simplified logic: Data validation and data import are processed separately, which simplifies business logic and makes it easier to maintain and expand.
[0072] Optimize performance: Using temporary tables for batch operations can effectively reduce the database load and improve the overall performance of the system.
[0073] It should be noted that not all steps and modules in the above-mentioned processes and device structures are necessary, and some steps or modules can be ignored according to actual needs. The execution order of each step is not fixed and can be adjusted as needed. The system structure described in the above-mentioned embodiments can be a physical structure or a logical structure, that is, some modules may be implemented by the same physical entity, or some modules may be implemented by multiple physical entities, or some components in multiple independent devices may be implemented together.
[0074] The above-described embodiments are only preferred embodiments for fully illustrating the present invention, and the protection scope of the present invention is not limited thereto. Equivalent substitutions or changes made by those skilled in the art based on the present invention are within the protection scope of the present invention. The protection scope of the present invention shall be subject to the claims.
Claims
1. A method for implementing an Excel batch import interface with the help of a temporary table, characterized by: include: Step 1: Before data import begins, create a temporary table in the database. The structure of the temporary table adds uuid and number fields based on the target data table. The uuid field is used to distinguish batches of import operations, and the number field is used to record the row number of the imported data in the table. Step 2: Batch insert the data to be imported into the temporary table; Step 3: Use SQL statements to batch verify the data in the temporary table. The verification content includes: Checks whether the data conflicts with existing data in the target data table. Verify the data format, Verify data value domain; Step 4: Import the successfully verified data that meets the import conditions into the target table in batches; Step 5: Generate a failure table excel for the data that failed the verification and output it; Step 6: After the data is imported, clean up the temporary table: delete the data in the order of the uuids generated when this batch of data is inserted into the temporary table.
2. According to claim 1, a method for implementing a batch import interface of Excel with the help of a temporary table is characterized in that In step 3, check whether the data conflicts with the existing data in the target data table, including: compare the fields of the current batch of data in the temporary table and the existing data in the target table according to the duplicate detection logic, filter out the data that meets the non-duplicate condition, and if there is duplicate data in the current batch of data, only insert the first one of the duplicate data, and all the duplicate data after the first one will be judged as duplicates and output from the failure table.
3. According to the method of claim 1, which is characterized by: Verifying the data format in step 3 includes: checking the legitimacy of the data format in the basic information, which includes the mobile phone number and ID card number.
4. The method of implementing a batch import interface of Excel with the help of a temporary table according to claim 1 is characterized in that In step 3, the data value domain is verified, including: checking whether the value of the preset field is filled in and selected within a given range.
5. The method of implementing a batch import interface of Excel with the help of a temporary table according to claim 1 is characterized in that In step 5, the failed data is written into the failure table excel according to the number field in the temporary table and the reason for the import failure, and a failure data prompt is generated and output to indicate the failed data and the reason for the failure to the operator.
6. A device for implementing an Excel batch import interface with the help of a temporary table, characterized in that Including temporary table management module, batch import module, batch verification module and cleanup module, The temporary table management module creates a temporary table in the database before data import begins. The structure of the temporary table adds uuid and number fields based on the target data table. The uuid field is used to distinguish batches of import operations, and the number field is used to record the row number of the imported data in the table. The batch import module batch-inserts the data to be imported into the temporary table; The batch verification module uses SQL statements to perform batch verification on the data in the temporary table. The verification content includes: Checks whether the data conflicts with existing data in the target data table. Verify the data format, Verify data value domain; The batch import module imports the successfully verified data that meets the import conditions into the target table in batches; The batch import module generates a failure table excel for the data that failed verification and outputs it; After the data is imported, the cleaning module cleans up the temporary table: the data is deleted uniformly according to the uuid sequence generated when the data in this batch is inserted into the temporary table.
7. The device for implementing a batch import interface of Excel by means of a temporary table according to claim 6, characterized in that The batch verification module checks whether the data conflicts with the existing data in the target data table, including: comparing the fields of the current batch data in the temporary table and the existing data in the target table according to the duplicate detection logic, and filtering out the data that meets the non-duplicate conditions. If there is duplicate data in the current batch data, only the first one of the duplicate data is inserted, and the duplicate data after the first one are all judged as duplicates and output from the failure table.
8. The device for implementing a batch import interface of Excel by means of a temporary table according to claim 6, characterized in that The batch verification module verifies the data format, including: checking the legitimacy of the data format in the basic information, which includes the mobile phone number and ID card number.
9. The device for implementing a batch import interface of Excel by means of a temporary table according to claim 6, characterized in that The batch validation module validates the data value domain, including: checking whether the value of the preset field is filled in and selected within a given range.
10. The device for implementing a batch import interface of Excel by means of a temporary table according to claim 6, characterized in that The cleanup module writes the failed data into the failure table excel according to the number field in the temporary table and the reason for the import failure, generates a failed data prompt and outputs it, indicating the failed data and the reason for the failure to the operator.