Form file custom import method and system, electronic equipment and storage medium

Abstract

The invention provides a form file user-defined import method and system, electronic equipment and a storage medium, and relates to the technical field of data import, and the method comprises the following steps: defining an import instruction, creating an EXCEL form file import instruction, configuring a corresponding import instruction according to a to-be-imported EXCEL form file, and storing the to-be-imported EXCEL form file in the storage medium; the import instruction comprises a mapping relation between the entity attribute of the file data and the column name of the EXCEL form file; the table file is imported, an importing instruction corresponding to the EXCEL table file to be imported is selected, the EXCEL table file to be imported is read, and the content of the EXCEL table file is read and verified according to the mapping relation; if the verification is passed, analyzing the EXCEL form file, and storing the EXCEL form file into a relational database; if the verification is not passed, a prompt message is sent, and the prompt message comprises a failure reason; an import log is generated, the import log is generated according to the import result and the import progress of the EXCEL form file, and import information of data contained in the EXCEL form file can be obtained by inquiring the import log.

Description

Technical Field

[0001] The present invention relates to the technical field of data import, and in particular to a method for customizing the import of a table file. Background Art

[0002] With the development of science and technology and economy, enterprises are also growing and developing. More and more enterprises have established company-level business systems to record customer information, inventory information, and sales information. In the process of using business systems to conduct business, various enterprises often use EXCEL spreadsheet files to record data information. However, when there are too many EXCEL spreadsheet files, it is easy to fail to accurately find the required data files. The conventional processing method usually imports the EXCEL spreadsheet file data into the business system for management and subsequent retrieval and query.

[0003] However, when it is often necessary to import a large amount of EXCEL spreadsheet file data, manual entry is traditionally used, or the EXCEL spreadsheet file data is imported according to the field correspondence based on the column number, but the traditional import method has the following problems.

[0004] The import template of EXCEL spreadsheet file data is fixed-coded, that is, each EXCEL spreadsheet file has its own corresponding code, so when the data changes, the import template program code needs to be modified and adjusted, resulting in poor reusability and flexibility of the import function. In addition, the field verification rules are also fixed-coded, and after changes, the verification rules for the imported EXCEL spreadsheet file data need to be adjusted, which increases the human development and adjustment costs.

[0005] Therefore, the traditional EXCEL spreadsheet file import method has poor reusability, long processing cycle and high adjustment cost. Summary of the invention

[0006] The purpose of the present invention is to provide a method, system, electronic device and storage medium for customizing the import of table files, so as to solve the problems of poor reusability, long processing cycle and high adjustment cost when importing table files.

[0007] To solve the above problems, the present invention first provides a method for customizing the import of a table file, comprising: import instruction definition, creating an import instruction for an EXCEL table file, and configuring the corresponding import instruction according to the EXCEL table file to be imported, wherein the import instruction contains a mapping relationship between entity attributes of file data and column names of the EXCEL table file; table file import, selecting the import instruction corresponding to the EXCEL table file to be imported, reading the EXCEL table file to be imported, reading the content of the EXCEL table file according to the mapping relationship and performing verification; if the verification passes, parsing the EXCEL table file and storing it in a relational database; if the verification fails, issuing a prompt message, wherein the prompt message contains the reason for failure; generating an import log, wherein the import result and import progress of the EXCEL table file will generate an import log, and the import information of the data contained in the EXCEL table file can be obtained by querying the import log.

[0008] Furthermore, the import instruction definition includes: configuring a start row, and reading data from the EXCEL spreadsheet file corresponding to the start row for import.

[0009] Furthermore, the import instruction definition includes: filtering total rows setting, when importing the EXCEL table file, selecting whether to filter the total rows according to needs, if it is selected to filter the total rows, then importing data other than the total rows; and / or, only importing total rows setting, when importing the EXCEL table file, selecting whether to import only the total rows according to needs, if it is selected to import only the total rows, then importing the data of the total rows.

[0010] Furthermore, the import instruction definition includes: import column batch definition, the EXCEL table file to be imported imports the required columns in batches according to the mapping relationship; import column identifier definition, setting the business identifier for the EXCEL table file, and the business identifier can be referenced in the report file.

[0011] Furthermore, after creating or modifying the import instruction, the import instruction is saved in the historical record, and the import instruction saved in the configuration historical record can be selected when importing the EXCEL spreadsheet file.

[0012] The present invention also provides a table file custom import system, comprising: an instruction definition module, used for creating an import instruction of an EXCEL table file, and configuring the corresponding import instruction for the EXCEL table file to be imported, wherein the import instruction contains a mapping relationship between entity attributes of file data and column names of the EXCEL table file; an import module, used for reading the EXCEL table file to be imported, selecting the import instruction corresponding to the EXCEL table file to be imported before reading, and reading the content of the EXCEL table file according to the mapping relationship; a verification module, used for verifying the content of the EXCEL table file according to the mapping relationship, if the verification passes, parsing the EXCEL table file and storing it in a relational database; if the verification fails, issuing a prompt message, the prompt message containing the reason for failure; a log generation module, used for generating an import log with the import result and import progress of the EXCEL table file, and obtaining the import information of the data contained in the EXCEL table file by querying the import log.

[0013] Furthermore, the instruction definition module can configure the starting row, and when importing the EXCEL table file, data is read and imported from the corresponding starting row; a filter total row button is provided, and when importing the EXCEL table file, whether to filter the total row is selected according to needs. If the filter total row button is selected, data other than the total row is imported; and / or, an import only total row button is provided, and when importing the EXCEL table file, whether to import only the total row is selected according to needs. If the import only total row button is selected, the data of the total row is imported.

[0014] Furthermore, the instruction definition module includes: configuring import column batch definition, the EXCEL table file to be imported batches the required import columns according to the mapping relationship; configuring import column identifier definition, setting the business identifier for the EXCEL table file, and the business identifier can be referenced in the report file.

[0015] The present invention also provides an electronic device, comprising a processor and a memory; the memory is used to store a computer program, and the computer program is loaded and executed by the processor to implement the table file custom import method of the above technical solution.

[0016] The present invention also provides a computer-readable storage medium for storing a computer program, wherein the computer program, when executed by a processor, implements the table file custom import method of the above technical solution.

[0017] According to a custom table file import method, system, electronic device and storage medium provided by the present invention, the flexibility of importing EXCEL table files and the reusability of import rules are improved, and EXCEL table files of different formats are satisfied, thereby improving the success rate of importing EXCEL table files and optimizing the user perception and experience when importing EXCEL table files. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on the provided drawings without paying creative work.

[0019] Figure 1 The embodiment of the present invention provides DETAILED DESCRIPTION

[0020] In order to make the above-mentioned objects, features and advantages of the present invention more clearly understood, the specific embodiments of the present invention are described in detail below in conjunction with the accompanying drawings. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not used to limit the present invention.

[0021] Example 1

[0022] In order to solve the problems of poor reusability, long processing cycle and high adjustment cost when importing table files, this embodiment provides a table file custom import method, including the following steps:

[0023] S10. Import instruction definition, create an import instruction for an EXCEL spreadsheet file, and configure the corresponding import instruction according to the EXCEL spreadsheet file to be imported. The import instruction contains the mapping relationship between the entity attributes of the file data and the column names of the EXCEL spreadsheet file; after creating the import instruction, the import instruction is saved in the historical record, and if the import instruction is modified, the original and modified historical versions are saved in the historical record. When importing an EXCEL spreadsheet file, the import instruction saved in the configuration historical record can be selected;

[0024] The import instruction definition includes the ability to configure a start row, that is, to read data from the corresponding start row set in the EXCEL spreadsheet file for import;

[0025] Filter total row settings, import the EXCEL spreadsheet file and select whether to filter the total row according to the needs. If you choose to filter the total row, then import the data except the total row;

[0026] Only import total rows. When importing an EXCEL spreadsheet file, select whether to import only total rows according to your needs. If you select to import only total rows, the data in the total rows will be imported.

[0027] Import columns in batches and define them. The EXCEL table file to be imported will be imported in batches according to the mapping relationship.

[0028] Import column identifier definition and set business identifier for EXCEL table file. Business identifier can be referenced in report file.

[0029] Import column type definition, which can set the type of imported column in EXCEL spreadsheet file;

[0030] S20. Import the table file, select the import instruction corresponding to the EXCEL table file to be imported, read the EXCEL table file to be imported, and read the contents of the EXCEL table file according to the mapping relationship;

[0031] The import process supports importing various formats of EXCEL spreadsheet files, including Excel 2003 version files.xls and Excel 2007, Excel 2010 and above version files.xlsx; and when importing, the EXCEL spreadsheet file can select any import instruction saved in the configuration history record;

[0032] S30. Perform file verification. If the verification passes, the EXCEL spreadsheet file is parsed and stored in a relational database. If the verification fails, a prompt message is issued, which includes the reason for failure. Abnormal data that fails the verification can be exported.

[0033] S40. Generate an import log. The import results and import progress of the EXCEL spreadsheet file will generate an import log. By querying the import log, the import information of the data contained in the EXCEL spreadsheet file can be obtained;

[0034] Optional log query methods include querying by import instruction code and name, querying by importer and import time, querying by import status, such as success, failure, and importing, and supporting fuzzy query.

[0035] This embodiment improves the flexibility of importing EXCEL spreadsheet files by creating a custom import instruction for EXCEL spreadsheet file data, and using the import instruction configured corresponding to the EXCEL spreadsheet file to read the EXCEL spreadsheet file data to be imported; and the import instruction is saved in the historical record. If it is to be reused after creation, the import instruction saved in the historical record can be directly used, which improves the reusability of the import instruction and the efficiency of the import operation; the EXCEL file format supports diversity and reduces the workload of users' repetitive work.

[0036] In addition, the read EXCEL spreadsheet file data can be verified through the import instruction, the verified EXCEL spreadsheet file can be parsed, and the file can be stored in a relational database, which improves the success rate of importing EXCEL spreadsheet file data.

[0037] The import log of this embodiment can record the number of successfully imported EXCEL table file data, import time, the number of failed imported EXCEL table file data, failed imported EXCEL table file data and failure reasons; set the attribute value of Excel RowNum through the entity class to record the row number of each EXCEL data, and write the verification failure reason into a column in the EXCEL table file according to Excel RowNum to feedback to the user. This allows users to grasp the import status of EXCEL table file data in real time, greatly improving user experience and usage perception.

[0038] The results and progress of table file import are clearly recorded in the import log, and detailed data can be queried, which improves the convenience of users to query historical data.

[0039] Example 2

[0040] This embodiment provides a table file custom import system, which can implement the table file custom import method of embodiment 1, including:

[0041] The instruction definition module is used to create an import instruction for an EXCEL table file and configure a corresponding import instruction for the EXCEL table file to be imported. The import instruction contains a mapping relationship between entity attributes of the file data and column names of the EXCEL table file;

[0042] The import module is used to read the EXCEL table file to be imported, select the import instruction corresponding to the EXCEL table file to be imported before reading, and read the content of the EXCEL table file according to the mapping relationship;

[0043] The verification module verifies the contents of the EXCEL spreadsheet file according to the mapping relationship. If the verification passes, the EXCEL spreadsheet file is parsed and stored in the relational database; if the verification fails, a prompt message is issued, and the prompt message includes the reason for failure;

[0044] The log generation module is used to generate an import log with the import results and import progress of the EXCEL spreadsheet file, and the import information of the data contained in the EXCEL spreadsheet file can be obtained by querying the import log.

[0045] The specific import process of this system is as follows:

[0046] By clicking the Add button of the Instruction Definition Module, fill in the import rule content contained in the pop-up display interface, including the required instruction category and instruction name. The instruction category can facilitate the identification of the instruction category, such as expenses or income. It is preferably selected by radio button. The instruction can be selected by using a magnifying glass, and the corresponding selectable instruction name is loaded according to the selected instruction category. For example, the instruction data of the "Expense Business List" is loaded for expenses, and the instruction data of the "Income Business List" is loaded for income. The instruction data only loads the instruction name and instruction code.

[0047] The specific instructions include: setting the start row, using an input box to fill in the start row number of the EXCEL table file to be imported, and only positive integers are allowed to be filled in the number of rows; setting the end mark, which is not a mandatory option, preferably using a check box. When importing data, it is possible to determine whether the imported data has ended based on the mark or text. When the row where the end mark is located is read, the data in this row is stored, and subsequent data is no longer stored. For example, if the end row mark is entered as "Total", when "Total" is read, the import is completed after the total row data is stored; and non-mandatory items are allowed to be empty. If the file does not have a clear end row, the import work will automatically end after judging that there is an empty row during import. Or set the end mark column, which is not a mandatory option, using a check box to fill in the column in the Excel table where the end mark is located; for example, in Excel table column A or column B, there is a situation where the end mark cell is merged or in column A or column B.

[0048] The version number of this system is generated in automatic mode. Manual filling and modification are not allowed. The generation rules are as follows: V+YYYYMMDDXXX, where YYYY is the current system year, MM is the current system month, DD is the single-select system date, and XXX is the serial number, and duplication is not allowed; the sample is V20220812001.

[0049] The header structure of the instruction list is used to create, import and display header information. The function buttons include: Add, Edit, Delete, and Import. The default node is the root node, and report fields are added at the root node stage.

[0050] When using the new function, select a node and click the Add button to pop up the Add Node interface. Enter the node name in the node name input box. After filling in, click Save to save the node information and exit the Add interface. At the same time, node-related information is automatically generated in the node list on the left. The node name is a required item and is entered in the input box.

[0051] When using the edit function, select a node and click the edit button to pop up the edit interface, where you can edit the node name and update the relevant information after saving. At the same time, the node list on the left is also automatically updated.

[0052] When using the delete function, select a node and click the delete button to delete the node. When deleting, select "Confirm" twice, and cancel by selecting "Cancel". At the same time, the node list on the left is automatically deleted.

[0053] The import function uses a floating menu, which is divided into import and import template buttons;

[0054] (1) Click Import to import the Excel document and the validation rules. Paste the table header to be imported into the import template according to the actual format. After the table header is imported, the table header format is automatically analyzed and loaded into the structure tree. The list fields on the right are automatically generated based on the imported information.

[0055] (2) Click Import Template to download the import template. The template content includes the Excel document header.

[0056] After importing, the import field sequence value is automatically generated using positive integers, such as 1, 2, and 3. It mainly marks the number of the import field in the imported Excel document. Only the last-level node is marked. If there is a node in the next layer, it will not be marked because this level will not be stored in the database. Manual modification of the import field value is allowed according to actual conditions, but duplicate values ​​are not allowed.

[0057] Field names are automatically generated based on the names of the added nodes.

[0058] The system automatically generates a business ID, marking the business ID of each field in the rule. The business ID is mainly used to record the unique ID of the imported data cell, and is used to configure the table value and calculation when displaying. The system automatically generates a self-increment method. It is recommended to use letters such as A, B, and C. After single letters are used up, you can use AA, AB, and AC multi-letter combination formats. Duplicate business IDs are not allowed in the same instruction.

[0059] Optionally, you can set a move adjustment button to adjust the order of fields. After the adjustment, the tree structure on the right will also be updated. When adjusting the order, the field name, business ID, and field type will not change. The order of imported fields and whether to enter the database can be manually modified according to the actual import situation.

[0060] After filling in the import instruction information, save the import instruction information. Verify when saving. Verify whether the imported fields are filled in. If not filled in, it will prompt "Please configure the import field information content. The import instruction is not allowed to be empty"; when adding a new instruction, the uniqueness is determined according to the instruction code. If the code already exists and the enable flag is "Enabled", a prompt message "XXX code already exists and the enable flag is "Yes" will pop up; select "Yes" to continue saving, and the enable flag of the existing rules in the system will be changed to "No". Click No to not save; if the rule code does not exist, save the import instruction and enable the flag to "Yes".

[0061] The query methods for imported instructions include: code query, name query, category query and identification query. The query can be performed by filling in the code content, instruction name, instruction category or label in the input box, supporting precise query.

[0062] Select the file to be imported and the matching import instruction. You can select the import instructions created in the above ways or saved in the history records. Batch submission of import files is allowed, and the system background performs multi-threaded import of files.

[0063] The imported files can be selected to be imported into the database. If the data display is abnormal, it cannot be selected to be imported into the database.

[0064] After submission, the imported file will be automatically synchronized and displayed in the "Import Log Query" menu, where you can query the import status and reasons.

[0065] The import log can be queried by instruction code, instruction name, importer, import time and import status. The instruction code uses the input box method, fill in the code for query, and supports precise query; the instruction name uses the input box method, fill in the name for query, and supports fuzzy query; the importer uses the input box method, fill in the importer's name for query, and supports precise query; the import time uses the date box method, select the specific import time for query, the start date and end date are empty by default; the import status uses the drop-down box method, select the status to query the file import status, including success, failure, and importing.

[0066] Data imported through EXCEL spreadsheet files also supports querying, which can be queried through the import instruction code and import time.

[0067] Example 3

[0068] This embodiment provides an electronic device, which includes a processor and a memory; the memory is used to store a computer program, and the computer program is loaded and executed by the processor to implement the table file custom import method of embodiment 1.

[0069] Example 4

[0070] This embodiment provides a computer-readable storage medium for storing a computer program. When the computer program is executed by a processor, the table file custom import method of embodiment 1 is implemented.

[0071] According to a custom table file import method, system, electronic device and storage medium provided by the present invention, the flexibility of importing EXCEL table files and the reusability of import rules are improved, and EXCEL table files of different formats are satisfied, thereby improving the success rate of importing EXCEL table files and optimizing the user perception and experience when importing EXCEL table files.

[0072] In the description of this embodiment, it should be noted that those skilled in the art can understand that all or part of the processes in the above-mentioned embodiment method can be implemented by instructing the control device through a computer program, and the program can be stored in a computer-readable storage medium. When the program is executed, it may include the processes of the above-mentioned method embodiments, wherein the storage medium may be a memory, a disk, an optical disk, etc.

[0073] Although the present invention is disclosed as above, the present invention is not limited thereto. Any person skilled in the art can make various changes and modifications without departing from the spirit and scope of the present invention. Therefore, the protection scope of the present invention shall be subject to the scope defined by the claims.

[0074] Finally, it should be noted that, in this article, relational terms such as first and second, etc. are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, the elements defined by the sentence "comprise a ..." do not exclude the presence of other identical elements in the process, method, article or device including the elements.

[0075] The various embodiments in this specification are described in a progressive manner, and each embodiment focuses on the differences from other embodiments. The same or similar parts between the various embodiments can be referenced to each other.

[0076] The above description of the disclosed embodiments enables one skilled in the art to implement or use the present invention. Various modifications to these embodiments will be apparent to one skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the present invention. Therefore, the present invention will not be limited to the embodiments shown herein, but rather to the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. A method for customizing the import of a table file, characterized in that: include: Import instruction definition, create an import instruction of an EXCEL table file, and configure the corresponding import instruction according to the EXCEL table file to be imported, wherein the import instruction contains a mapping relationship between entity attributes of the file data and column names of the EXCEL table file; Table file import, select the import instruction corresponding to the EXCEL table file to be imported, read the EXCEL table file to be imported, read the content of the EXCEL table file according to the mapping relationship and perform verification; If the verification is passed, the EXCEL table file is parsed and stored in a relational database; If the verification fails, a prompt message will be issued, which includes the reason for failure; Generate an import log. The import result and import progress of the EXCEL spreadsheet file will generate an import log. By querying the import log, the import information of the data contained in the EXCEL spreadsheet file can be obtained.

2. The table file custom import method according to claim 1, characterized in that: The import instruction definition includes: Configure the start row, and start reading and importing data from the EXCEL spreadsheet file corresponding to the start row.

3. The table file custom import method according to claim 1, characterized in that: The import instruction definition includes: Filter total row settings, import the EXCEL table file and select whether to filter the total row according to the needs. If you choose to filter the total row, import the data except the total row; And / or, only import the total row setting, import the EXCEL spreadsheet file and select whether to import only the total row according to the needs. If you choose to import only the total row, the data of the total row will be imported.

4. The table file custom import method according to claim 1, characterized in that: The import instruction definition includes: Importing columns in batches, the EXCEL table file to be imported imports the required columns in batches according to the mapping relationship; Import the column identifier definition and set the business identifier for the EXCEL table file. The business identifier can be referenced in the report file.

5. The table file custom import method according to claim 1, characterized in that: After creating or modifying the import instruction, the import instruction is saved in the historical record. When importing the EXCEL spreadsheet file, the import instruction saved in the configuration historical record can be selected.

6. A table file custom import system, characterized in that: include: An instruction definition module, used for creating an import instruction of an EXCEL table file, and configuring the corresponding import instruction for the EXCEL table file to be imported, wherein the import instruction includes a mapping relationship between entity attributes of the file data and column names of the EXCEL table file; An import module, used for reading the EXCEL table file to be imported, selecting the import instruction corresponding to the EXCEL table file to be imported before reading, and reading the content of the EXCEL table file according to the mapping relationship; A verification module verifies the content of the EXCEL table file according to the mapping relationship. If the verification passes, the EXCEL table file is parsed and stored in a relational database; If the verification fails, a prompt message will be issued, which includes the reason for failure; The log generation module is used to generate an import log based on the import result and import progress of the EXCEL spreadsheet file, and the import information of the data contained in the EXCEL spreadsheet file can be obtained by querying the import log.

7. The table file custom import system according to claim 6, characterized in that: The instruction definition module can configure a starting row, and when importing the EXCEL table file, data is read and imported from the corresponding starting row; A filter total row button is provided. When the EXCEL table file is imported, the user can select whether to filter the total row according to the requirement. If the filter total row button is selected, the data other than the total row will be imported. And / or, click the Import Total Rows Only button, when importing the EXCEL spreadsheet file, select whether to import only the total rows according to the needs. If you select the Import Total Rows Only button, the data of the total rows will be imported.

8. The table file custom import system according to claim 6, characterized in that: The instruction definition module includes: Configure batch definition of import columns, and import the required import columns in batches according to the mapping relationship in the EXCEL table file to be imported; Configure the import column identifier definition and set the business identifier for the EXCEL table file. The business identifier can be referenced in the report file.

9. An electronic device, characterized in that: The electronic device includes a processor and a memory; the memory is used to store a computer program, and the computer program is loaded and executed by the processor to implement the custom import method for a table file as described in any one of claims 1 to 5.

10. A computer-readable storage medium, characterized in that: Used to store a computer program, which, when executed by a processor, implements the table file custom import method as described in any one of claims 1 to 5.

Images