Data import method and device, computer equipment and readable storage medium

Abstract

The invention provides a data importing method and device, computer equipment and a readable storage medium, and the method comprises the steps: in a verification preparation stage, defining an Excel template table, and defining a self-defined verification rule of each to-be-imported field and field data of each to-be-imported field in an Excel template; in a verification execution stage, analyzing the Excel template to obtain a self-defined verification rule of each field to be imported; and verifying the field data of each field to be imported based on the user-defined verification rule of each field to be imported, and inserting the verified field data into the database table to realize data importing. By adopting the scheme, the Excel data can be efficiently verified and accurately imported into the database.

Description

Technical Field

[0001] The present invention relates to the field of computer data processing, and in particular to a data import method, device, computer equipment and readable storage medium. Background Art

[0002] In the current information age, Excel, as a widely used spreadsheet software, is often used for data collection, organization and analysis. However, in the process of importing Excel data into the database system, problems such as data format mismatch, data missing, and data errors are often encountered. These problems not only increase the complexity of data processing, but may also cause serious consequences such as data inconsistency and system errors. Therefore, how to efficiently verify Excel data and accurately import it into the database has become an urgent problem to be solved. Summary of the invention

[0003] In view of this, an object of the present invention is to provide a data import method, apparatus, computer equipment and readable storage medium to efficiently verify Excel data and accurately import it into a database.

[0004] In a first aspect, an embodiment of the present application provides a data import method, the method comprising:

[0005] In the verification preparation stage, an Excel template table is defined, in which custom verification rules and field data of each field to be imported are defined;

[0006] In the verification execution phase, the Excel template is parsed to obtain custom verification rules for each field to be imported;

[0007] The field data of each field to be imported is verified based on the custom verification rules of each field to be imported, and the field data that passes the verification is inserted into the database table to realize data import.

[0008] Optionally, the custom validation rule for each field to be imported is defined in the first row or comment of the Excel template; if there are multiple custom validation rules, a separator is used to distinguish each custom validation rule.

[0009] Optionally, the method further comprises:

[0010] Define common validation rules for each field to be imported;

[0011] Verify the field data of each field to be imported based on the common verification rules of each field to be imported.

[0012] Optionally, the method further comprises:

[0013] The field data of each field to be imported is verified based on the custom verification rules and general verification rules of each field to be imported.

[0014] Optionally, inserting the verified field data into the database table includes:

[0015] Encapsulate the validated field data into the Model object;

[0016] Insert the field data in the encapsulated Model object into the database table.

[0017] In a second aspect, an embodiment of the present application provides a data import device, the device comprising:

[0018] A custom verification rule definition module is used to define an Excel template table in the verification preparation stage, in which the custom verification rules of each field to be imported and the field data of each field to be imported are defined;

[0019] A verification rule parsing module is used to parse the Excel template to obtain custom verification rules for each field to be imported during the verification execution phase;

[0020] The first data verification module is used to verify the field data of each field to be imported based on the user-defined verification rules of each field to be imported, and insert the field data that passes the verification into the database table to realize data import.

[0021] Optionally, the custom validation rule for each field to be imported is defined in the first row or comment of the Excel template; if there are multiple custom validation rules, a separator is used to distinguish each custom validation rule.

[0022] Optionally, the device further comprises:

[0023] The general validation rule definition module is used to define the general validation rules for each field to be imported;

[0024] The second data verification module is used to verify the field data of each field to be imported based on the general verification rules of each field to be imported.

[0025] Optionally, the device further comprises:

[0026] The third data verification module is used to verify the field data of each field to be imported based on the user-defined verification rules and general verification rules of each field to be imported.

[0027] Optionally, inserting the verified field data into the database table includes:

[0028] Encapsulate the validated field data into the Model object;

[0029] Insert the field data in the encapsulated Model object into the database table.

[0030] In a third aspect, an embodiment of the present application provides a computer device, comprising: a processor, a memory and a bus, wherein the memory stores machine-readable instructions executable by the processor, and when the computer device is running, the processor and the memory communicate through the bus, and when the machine-readable instructions are executed by the processor, the steps of the data import method described in any optional implementation manner of the first aspect are performed.

[0031] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium having a computer program stored thereon, and when the computer program is executed by a processor, the steps of the data import method described in any optional implementation manner in the above-mentioned first aspect are executed.

[0032] The technical solution provided by this application includes but is not limited to the following beneficial effects:

[0033] This application defines an Excel template table in the verification preparation stage, and defines the custom verification rules and field data of each field to be imported in the Excel template, which can fully cover various data constraints such as non-empty verification, mailbox format verification, and data length verification to ensure the accuracy and compliance of the data. In the verification execution stage, the Excel template is parsed to obtain the custom verification rules of each field to be imported, and then the field data of each field to be imported is verified based on the custom verification rules of each field to be imported. According to these pre-configured rules, each data to be verified can be carefully and accurately verified. Finally, the field data that passes the verification is inserted into the database table to achieve accurate data import.

[0034] Data verification and importing based on the data import method provided in this application can achieve efficient and flexible data processing based on the high configurability of its verification rules. Once the verification rules change, there is no need to deeply modify or rewrite the code of the import program. On the contrary, it is only necessary to simply update the configuration of the verification rules in the Excel template. This non-invasive modification method greatly improves the flexibility and maintainability of the system, and also avoids the need to re-release the program due to template changes. This not only significantly reduces the development workload caused by rule changes, but also improves the overall development efficiency and response speed, making the data import process more efficient and reliable.

[0035] In order to make the above-mentioned objects, features and advantages of the present invention more obvious and easy to understand, preferred embodiments are given below and described in detail with reference to the accompanying drawings. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings required for use in the embodiments are briefly introduced below. It should be understood that the following drawings only show certain embodiments of the present invention and therefore should not be regarded as limiting the scope. For ordinary technicians in this field, other related drawings can be obtained based on these drawings without creative work.

[0037] Figure 1 A flow chart of a data import method provided by the first embodiment of the present invention is shown;

[0038] Figure 2 A flow chart of a field data verification method based on first-row configuration provided by Embodiment 1 of the present invention is shown;

[0039] Figure 3 A flow chart of a field data verification method based on annotation configuration provided by Embodiment 1 of the present invention is shown;

[0040] Figure 4 A flow chart of a field data verification method provided by Embodiment 1 of the present invention is shown;

[0041] Figure 5 A flow chart of a method for inserting field data provided by the first embodiment of the present invention is shown;

[0042] Figure 6 A schematic diagram of the structure of a data import device provided in the second embodiment of the present invention is shown;

[0043] Figure 7 A schematic diagram of the structure of a computer device provided in Embodiment 3 of the present invention is shown. DETAILED DESCRIPTION

[0044] In order to make the purpose, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. The components of the embodiments of the present invention generally described and shown in the drawings here can be arranged and designed in various different configurations. Therefore, the following detailed description of the embodiments of the present invention provided in the drawings is not intended to limit the scope of the claimed invention, but merely represents selected embodiments of the present invention. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without making creative work belong to the scope of protection of the present invention.

[0045] Embodiment 1

[0046] To facilitate understanding of this application, Figure 1 The flowchart of a data import method provided in the first embodiment of the present invention is shown to describe the contents of the first embodiment of the present application in detail.

[0047] See also Figure 1 As shown, Figure 1 A flow chart of a data import method provided in the first embodiment of the present invention is shown, wherein the method comprises steps S101 to S103:

[0048] S101: In the verification preparation stage, an Excel template table is defined, and a custom verification rule for each field to be imported and field data for each field to be imported are defined in the Excel template.

[0049] Specifically, in the verification preparation stage, that is, the preprocessing stage of data import, the first row of the Excel template is configured to contain custom verification rules for the fields. These custom verification rules cover various data constraints, including but not limited to non-empty verification, email format verification, data length verification, etc., to ensure the accuracy and compliance of the data.

[0050] S102: In the verification execution phase, the Excel template is parsed to obtain a custom verification rule for each field to be imported.

[0051] Specifically, during the verification execution phase, the data import program begins to parse the custom verification rules in the Excel template so that the data in each column can be verified and checked one by one based on these pre-configured custom field verification rules.

[0052] S103: Verify the field data of each field to be imported based on the custom verification rule of each field to be imported, and insert the field data that passes the verification into the database table to realize data import.

[0053] Specifically, in the actual execution process of data import, the data content in the Excel template is first read and parsed, and then the data in each column is verified and checked in detail based on the custom verification rules of each field to be imported. For field data that passes the verification, it is inserted into the database table; for field data that fails the verification, it is filtered. This process ensures that the imported data complies with the preset specifications and standards, effectively avoiding data errors and non-compliance issues.

[0054] The above solution can significantly improve the flexibility and maintainability of data verification. Specifically, when the verification rules of the data change, there is no need to make tedious modifications and adjustments to the code of the import program. Instead, you only need to simply update the verification rule configuration of the first row in the Excel template to implement the new verification requirements for the data. This strategy greatly reduces the development workload caused by changes in verification rules and improves overall development efficiency and response speed.

[0055] In an optional implementation scheme, the custom validation rule for each field to be imported is defined in the first row or comment of the Excel template; if there are multiple custom validation rules, a separator is used to distinguish each custom validation rule.

[0056] Specifically, an underscore "|" is used as a separator between multiple validation rules.

[0057] When the custom validation rules for each field to be imported are defined in the first row of the Excel template, see Figure 2 As shown, Figure 2 A flow chart of a field data verification method based on the first row configuration provided by the first embodiment of the present invention is shown, wherein the verification method of the field agreement is first configured in advance in the first row of Excel. For example: the data cannot be empty, the data must be in the mailbox format, the data length cannot be less than N bits, etc., and the data content to be imported is filled in the Excel template to define the Excel import template file, and then the Excel import program is written to read the data template and perform data parsing. In the Excel import program, Excel data is first parsed by row, and then by column in the row. Then, in the program, the verification method of the field agreement configured in advance in the first row of Excel is used to verify all data. If the above verification passes, the data in the Excel table is encapsulated into the Model object, and then the import instruction is executed to insert the data into the database table.

[0058] When the custom validation rules for each field to be imported are defined in the comments of the Excel template, see Figure 3 As shown, Figure 3A flow chart of a field data verification method based on annotation configuration provided by Embodiment 1 of the present invention is shown, wherein the verification method of the field agreement is first configured in advance in the first row of Excel. For example: the data cannot be empty, the data must be in mailbox format, the data length cannot be less than N bits, etc., and the data content to be imported is filled in the Excel template to define the Excel import template file, and then an Excel import program is written to read the data template and perform data parsing. In the Excel import program, Excel data is first parsed by row, and then by column in the row. Then, the data verification rules pre-configured in the approval of the first row of Excel titles are extracted in the program, and the imported data is verified using these configured verification rules. If the above verification passes, the data in the Excel table is encapsulated into the Model object, and then the import instruction is executed to insert the data into the database table.

[0059] In an alternative embodiment, see Figure 4 As shown, Figure 4 A flow chart of a field data verification method provided in the first embodiment of the present invention is shown, wherein the method further includes steps S401 to S402:

[0060] S401: Define common validation rules for each field to be imported.

[0061] S401: Verify the field data of each field to be imported based on the general verification rules of each field to be imported.

[0062] Specifically, when further improving the validation mechanism of data import, a variety of general validation rules are added to cope with various complex validation scenarios that may occur during the system import process. When executing the Excel import program, the program will first parse the data in Excel, and then perform a detailed and item-by-item validation of each row or column of data fields in the data table according to the custom validation rules and / or general validation rules pre-defined in the first row of Excel.

[0063] The general validation rules are designed to ensure the accuracy and completeness of the imported data, and can cover multiple aspects such as data length, data format, and data range. For example, by setting data length validation rules, we can ensure that the number of characters in the field meets the system requirements; by using data format validation, we can verify whether the field value meets the specified format, such as the format of an email address or mobile phone number; and data range validation is used to ensure that the field value falls within a predetermined range. These comprehensive validation measures will greatly improve the reliability of data import and reduce system problems caused by data errors or non-compliance with specifications.

[0064] In an optional embodiment, the method further comprises:

[0065] The field data of each field to be imported is verified based on the custom verification rules and general verification rules of each field to be imported.

[0066] Specifically, the custom validation rules for each field defined in the first row of the Excel template include: NotNull: The validation column data value cannot be empty. This means that each cell in the column must contain data and cannot be left blank. ^[a-zA-Z]\w{3,15}$: The validation column data value needs to meet this regular expression. This is usually used to validate fields such as usernames or passwords, ensuring that they start with a letter and are followed by 3 to 15 characters (including letters, numbers, and underscores). Length=10: The validation column data value needs to be 10 characters long. This is suitable for fields that require a fixed length, such as user IDs and passwords. RANGE[1,2,3]: Indicates that the value of this column can only be one of 1, 2, and 3. This is suitable for fields with limited options, such as the status field, which may only have three options: "Employed", "Resigned", and "Vacation". Email: The validation column data must meet the format of the email address. This ensures that all email addresses are valid and can be used to send and receive emails. Mobile: The validation column data must meet the format of the mobile phone number. This depends on the specific mobile phone number format requirements, but usually includes the country code, operator code and the number itself. DATE[YYYY-MM-DD]: The validation column data must first be a date format, and the date format must be YYYY-MM-DD. This ensures that all date data is arranged in the order of year-month-day and in a unified format for easy system processing and storage.

[0067] When defining custom validation rules, ensure that the custom validation rules correspond to the column names in the Excel template and that the validation rules for each field are accurate. This ensures that when importing data, the column names in the Excel template form a one-to-one correspondence with each custom validation rule, so that the column names in the Excel template can be used to retrieve each custom validation rule, thereby automatically verifying the accuracy and compliance of the data and reducing the occurrence of errors and exceptions.

[0068] The field data of each field to be imported is checked respectively according to the custom check rules and general check rules of each field to be imported. When the field data satisfies both the custom check rules and general check rules of each field to be imported, it means that the field data has passed the check.

[0069] In an alternative embodiment, see Figure 5 As shown, Figure 5 A flow chart of a method for inserting field data provided by the first embodiment of the present invention is shown, wherein the field data that passes the verification is inserted into the database table, including steps S501 to S502:

[0070] S501: Encapsulate the verified field data into the Model object.

[0071] Specifically, once the field data successfully passes all the validation rules, each row of data will be encapsulated into the corresponding Model object one by one according to the structure of the Excel table. These Model objects, as a standardized medium for data transmission, will be accurately mapped to the field structure of the database table to ensure that the accuracy and integrity of the data are maintained.

[0072] S502: Insert the field data in the encapsulated Model object into the database table.

[0073] Specifically, the encapsulated Model object will be inserted into the database through the data persistence layer. In this process, the atomicity of the operation must be ensured, that is, the entire insertion process must ensure the principle of "either all are successfully inserted or none are inserted". This processing method ensures data consistency and avoids system data inconsistency problems caused by failure to insert some data. Through this series of strict controls and operations, it can ensure that the imported data remains complete, accurate and consistent in the database.

[0074] The data import method provided in this application can be applied to, but not limited to, the import scenario of employee information data, and can be applied in scenarios according to actual needs. When importing employee information data, first define an Excel template and prepare the data to be imported in Excel. Among them, the employee information table tbl_employee_info provides basic data format fields including user name, user number, name, status, gender, email address, mobile phone number, and birthday. For specific employee information tables, see the following table:

[0075]

[0076] After configuring the custom validation rule in the first row of the employee information table based on the custom validation rule configuration method disclosed in this application, the following table can be obtained:

[0077]

[0078] Embodiment 2

[0079] Embodiment 2 of the present invention provides a data import device, see Figure 6 As shown, Figure 6 A schematic diagram of the structure of a data import device provided in Embodiment 2 of the present invention is shown, wherein the device comprises:

[0080] A custom verification rule definition module 601 is used to define an Excel template table in the verification preparation stage, in which the custom verification rules of each field to be imported and the field data of each field to be imported are defined;

[0081] The verification rule parsing module 602 is used to parse the Excel template to obtain the custom verification rules of each field to be imported during the verification execution phase;

[0082] The first data verification module 603 is used to verify the field data of each field to be imported based on the user-defined verification rules of each field to be imported, and insert the field data that passes the verification into the database table to realize data import.

[0083] In an optional implementation scheme, the custom validation rule for each field to be imported is defined in the first row or comment of the Excel template; if there are multiple custom validation rules, a separator is used to distinguish each custom validation rule.

[0084] In an optional embodiment, the device further comprises:

[0085] The general validation rule definition module is used to define the general validation rules for each field to be imported;

[0086] The second data verification module is used to verify the field data of each field to be imported based on the general verification rules of each field to be imported.

[0087] In an optional embodiment, the device further comprises:

[0088] The third data verification module is used to verify the field data of each field to be imported based on the user-defined verification rules and general verification rules of each field to be imported.

[0089] In an optional embodiment, the field data that passes the verification is inserted into the database table, including:

[0090] Encapsulate the validated field data into the Model object;

[0091] Insert the field data in the encapsulated Model object into the database table.

[0092] Embodiment 3

[0093] Based on the same application concept, see Figure 7 As shown, Figure 7 FIG. 4 shows a schematic diagram of the structure of a computer device provided by Embodiment 3 of the present invention, wherein: Figure 7 As shown, a computer device 700 provided in Embodiment 3 of the present application includes:

[0094] A processor 701, a memory 702 and a bus 703, wherein the memory 702 stores machine-readable instructions executable by the processor 701. When the computer device 700 is running, the processor 701 communicates with the memory 702 via the bus 703. When the processor 701 is running, the machine-readable instructions execute the steps of the data import method shown in the above-mentioned embodiment 1.

[0095] Embodiment 4

[0096] Based on the same application concept, an embodiment of the present application further provides a computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, the steps of the data import method described in any one of the above embodiments are executed.

[0097] Those skilled in the art can clearly understand that, for the convenience and brevity of description, the specific working process of the system and device described above can refer to the corresponding process in the aforementioned method embodiment, and will not be repeated here.

[0098] The computer program product for data import provided in the embodiment of the present invention includes a computer-readable storage medium storing program code. The instructions included in the program code can be used to execute the method described in the previous method embodiment. The specific implementation can be found in the method embodiment, which will not be repeated here.

[0099] The voltage sampling device provided in the embodiment of the present invention can be specific hardware on the device or software or firmware installed on the device. The implementation principle and technical effects of the device provided in the embodiment of the present invention are the same as those of the aforementioned method embodiment. For the sake of brief description, the parts not mentioned in the device embodiment can refer to the corresponding contents in the aforementioned method embodiment. Those skilled in the art can clearly understand that for the convenience and simplicity of description, the specific working processes of the systems, devices and units described above can all refer to the corresponding processes in the aforementioned method embodiment, and will not be repeated here.

[0100] In the embodiments provided by the present invention, it should be understood that the disclosed devices and methods can be implemented in other ways. The device embodiments described above are only schematic. For example, the division of the units is only a logical function division. There may be other division methods in actual implementation. For example, multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some communication interfaces, and the indirect coupling or communication connection of devices or units can be electrical, mechanical or other forms.

[0101] The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0102] In addition, each functional unit in the embodiment provided by the present invention may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit.

[0103] If the functions are implemented in the form of software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, or the part that contributes to the prior art or the part of the technical solution, can be embodied in the form of a software product. The computer software product is stored in a storage medium, including several instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the methods described in each embodiment of the present invention. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk, etc., which can store program codes.

[0104] It should be noted that similar numbers and letters represent similar items in the following figures. Therefore, once an item is defined in one figure, it does not need to be further defined and explained in subsequent figures. In addition, the terms "first", "second", "third", etc. are only used to distinguish the description and are not to be understood as indicating or implying relative importance.

[0105] Finally, it should be noted that the above-described embodiments are only specific implementations of the present invention, which are used to illustrate the technical solutions of the present invention, rather than to limit them. The protection scope of the present invention is not limited thereto. Although the present invention is described in detail with reference to the above-described embodiments, those skilled in the art should understand that any person skilled in the art can still modify the technical solutions described in the above-described embodiments within the technical scope disclosed by the present invention, or can easily think of changes, or perform equivalent replacements on some of the technical features thereof; and these modifications, changes or replacements do not make the essence of the corresponding technical solutions deviate from the spirit and scope of the technical solutions of the embodiments of the present invention. They should all be covered within the protection scope of the present invention. Therefore, the protection scope of the present invention shall be subject to the protection scope of the claims.

Claims

1. A data import method, characterized in that: The method comprises: In the verification preparation stage, an Excel template table is defined, in which custom verification rules and field data of each field to be imported are defined; In the verification execution phase, the Excel template is parsed to obtain custom verification rules for each field to be imported; The field data of each field to be imported is verified based on the custom verification rules of each field to be imported, and the field data that passes the verification is inserted into the database table to realize data import.

2. The method according to claim 1, characterized in that The custom validation rules for each field to be imported are defined in the first row or comment of the Excel template; if there are multiple custom validation rules, a separator is used to distinguish each custom validation rule.

3. The method according to claim 1, characterized in that The method further comprises: Define common validation rules for each field to be imported; Verify the field data of each field to be imported based on the common verification rules of each field to be imported.

4. The method according to claim 3, characterized in that The method further comprises: The field data of each field to be imported is verified based on the custom verification rules and general verification rules of each field to be imported.

5. The method according to claim 1, characterized in that The field data that passes the verification is inserted into the database table, including: Encapsulate the validated field data into the Model object; Insert the field data in the encapsulated Model object into the database table.

6. A data import device, characterized in that: The device comprises: A custom verification rule definition module is used to define an Excel template table in the verification preparation stage, in which the custom verification rules of each field to be imported and the field data of each field to be imported are defined; A verification rule parsing module is used to parse the Excel template to obtain custom verification rules for each field to be imported during the verification execution phase; The first data verification module is used to verify the field data of each field to be imported based on the user-defined verification rules of each field to be imported, and insert the field data that passes the verification into the database table to realize data import.

7. The device according to claim 6, characterized in that The custom validation rules for each field to be imported are defined in the first row or comment of the Excel template; if there are multiple custom validation rules, a separator is used to distinguish each custom validation rule.

8. The device according to claim 6, characterized in that The device also includes: The general validation rule definition module is used to define the general validation rules for each field to be imported; The second data verification module is used to verify the field data of each field to be imported based on the general verification rules of each field to be imported.

9. A computer device, characterized in that: include: A processor, a memory and a bus, wherein the memory stores machine-readable instructions executable by the processor, and when the computer device is running, the processor and the memory communicate via the bus, and when the machine-readable instructions are executed by the processor, the steps of the data import method as described in any one of claims 1 to 5 are performed.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, which, when executed by a processor, executes the steps of the data import method as described in any one of claims 1 to 5.

Images