CN115934716A - Method for importing Excel table data into database based on metadata definition and implementation - Google Patents
Method for importing Excel table data into database based on metadata definition and implementation Download PDFInfo
- Publication number
- CN115934716A CN115934716A CN202211534099.4A CN202211534099A CN115934716A CN 115934716 A CN115934716 A CN 115934716A CN 202211534099 A CN202211534099 A CN 202211534099A CN 115934716 A CN115934716 A CN 115934716A
- Authority
- CN
- China
- Prior art keywords
- data
- metadata
- file
- user
- database
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Classifications
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Abstract
The invention provides a method for importing Excel table data into a database based on metadata definition and realization, which belongs to the technical field of computers and realizes the process of importing and managing offline original resource data into a relational database in a manner of designing, defining, configuring and analyzing metadata in a computer system; the metadata is a uniform mapping rule on the basis of different relational database storage types and different resource data model examples; the consistency of all operations of data adding, deleting, checking, importing and exporting are unified through one-time establishment of a data model and one-time constraint of a data rule. The saving transition from the offline resource data to the database data is realized, and the resource data model can be freely viewed, modified or changed when the data allows.
Description
Technical Field
The invention relates to the technical field of computers, in particular to a method for importing Excel table data into a database based on metadata definition and implementation.
Background
1) In the software development industry, the development of coding and searching for the operations of adding, deleting, modifying, importing and exporting databases is a very common and frequent task, and the following characteristics exist:
first, technical difficulties are not high and the development mode is usually very single or fixed.
Secondly, the encoding repeatability is high, and front-end and back-end encoding work which is completed once is needed to be carried out on each database table of each data resource.
Thirdly, in order to keep the relevance, dependence or limitation among the data and the dictionary coding conversion of the data consistent, manual intervention is needed when front-end display, back-end query, insertion, import and export are carried out and a database is established, a large amount of time is spent, the error rate of data import from Excel is high, and the repeated work of checking, debugging and the like is increased.
Fourth, there are currently some methods and tools for automatically generating front-end and back-end object codes from database tables. But has the following disadvantages: although the generated code is downloaded, the generated code still needs to be manually integrated into a system program for debugging and running; only basic realization of increasing, deleting, modifying and checking can be realized, and code generation of import and export and personalized query cannot be realized; the Excel data import template form needs manual creation and control; when the structure of the database table is changed, all codes need to be regenerated and integrated again or developed for the second time; the research and development difficulty is high in the face of complex table import requirements.
Disclosure of Invention
In order to solve the technical problem, the invention provides a method for importing Excel table data into a database based on metadata definition and implementation. The method solves the problems that code repeated development, data consistency are difficult to guarantee, complex Excel template data are imported, excel form data filled by a user are difficult to check and correct by self, and a data import function can be used only by development or modification work of professionals for non-software research and development professionals in the process of importing resource data into a database. The work difficulty and cost of importing the offline resource data into the database are reduced, and the data import operation is convenient, visual, flexible and easy to modify.
The technical scheme of the invention is as follows:
a method for importing Excel table data into a database based on metadata definition and implementation realizes the process of importing offline original resource data into a relational database and managing the online original resource data in a manner of designing, defining, configuring and analyzing the metadata in a computer system. The metadata is a uniform mapping rule based on different relational database storage types and different resource data model instances. The consistency of all operations of data adding, deleting, checking, importing and exporting are unified through one-time establishment of a data model and one-time constraint of a data rule. And the zero-code efficient data import work is realized by flexibly establishing and modifying a model and generating a strict import template.
The method comprises the steps of controlling a database table structure by establishing a relational data model and a universal data dictionary, and carrying out SQL (structured query language) sentences, mapping rules, coding conversion and template files for increasing, deleting, modifying, checking and importing and exporting operations on a data table, so as to provide a unified API (application program interface) interface and a standard Excel import table.
Front-end target codes such as JSON, JS and Vue are generated through analyzing a general front-end template file in an xml format, dynamic analysis is carried out by matching with the front end, and an example data management page of a routing address parameter adding model is displayed to a system menu through configuration data.
Further comprises
A user self-defines a data model and a data dictionary; configuring metadata by means of data modeling and dictionary management, generating corresponding model description metadata and a data dictionary corresponding to user requirements, and synchronously creating an entity in a database; the user continues to customize the required data operation and data display based on the defined data model; generating data operation description metadata, data display description metadata and a front end code template file according to management configuration metadata of data operation and display to store data operation required by a user and transmitting the metadata to a code generator to generate a database SQL code file and a front end object code file when the user needs to download; a user can add a data operation page to a menu of the system or add a front-end code to other systems to visually display the data operation page; transmitting the data model description metadata, the data operation description metadata and the corresponding data dictionary into an Excel template file generator to generate an Excel template file for a user to download and use and generate a corresponding data import description metadata file; acquiring an Excel data file uploaded by a user as a file to be processed; reading file data to be processed and converting the file data into intermediate data in a unified format; transmitting intermediate data to be processed and data import description metadata into an import data analyzer for analyzing and verifying to generate data analysis result data; and performing data warehousing operation or returning data verification error prompt information to a user according to the analysis result data.
The invention has the advantages that
The invention avoids the repeated coding work of software research personnel in the software function process of realizing the online importing of the resource data into the database, and only needs to pay attention to data modeling, a data dictionary and the subsequent data statistical analysis work; the data normalization and consistency are strictly ensured through closed-loop operation of management and configuration of the model, the file and the database, and the change requirements of the data model and the table structure can be easily met; the Excel import template form can support various checks, including drop-down selection, cascade selection, automatic supplement and import of complex Excel template data, so that a user can obtain an easier import template form and easily check and correct filling errors according to import feedback; a practical approach is provided for non-software research and development professionals to independently complete the storage of the original offline data to the relational database.
Drawings
FIG. 1 is a schematic flow diagram of the data processing process of the present invention;
FIG. 2 is a schematic diagram of data modeling management configuration metadata;
FIG. 3 is a data model description metadata diagram;
FIG. 4 is a schematic diagram of data operation description metadata;
FIG. 5 is a diagram of data presentation description metadata;
FIG. 6 is a diagram of data import description metadata;
fig. 7 is a functional workflow diagram of the system of the present invention.
Detailed Description
In order to make the objects, technical solutions and advantages of the embodiments of the present invention clearer and more complete, the technical solutions in the embodiments of the present invention will be described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are some, but not all, embodiments of the present invention, and based on the embodiments of the present invention, all other embodiments obtained by a person of ordinary skill in the art without creative efforts belong to the scope of the present invention.
The invention provides a method for importing Excel form data into a database based on metadata definition and realization, which completely ensures the consistency of data, greatly reduces the code research and development difficulty and saves the research and development time by performing closed-loop control on resource data modeling, database table building, unified back-end data interface, configured front-end page and Excel template file downloading and importing.
For the non-software development industry, when the requirement of importing the resource data into the database occurs, the program development or modification by professional developers is necessary.
The invention realizes that the offline resource data is imported into the system database through the Excel table based on the metadata and establishes a corresponding management configuration system. The flow chart of the whole user participation and system data processing process is shown in fig. 1, and the details are as follows:
(1) User-defined data model and data dictionary: the invention provides a universal data model management page used for creating and modifying a table structure for maintaining resource data and data integrity constraints, and provides a universal dictionary management page used for adding data dictionary types, dictionary entries and field associations.
Wherein the data dictionary comprises the following attributes as shown in table 1:
TABLE 1 general data dictionary
(2) The system configures metadata by means of data modeling and dictionary management, generates corresponding model description metadata and a data dictionary corresponding to user requirements, and synchronously creates an entity in a database. The data modeling management configuration metadata is shown in fig. 2, and includes the following functions: identifying the type and version of the adaptive database, providing a DDL statement template of the database and the data table, checking the structure of the data table read by the database, and generating data model description metadata. Wherein the data model description metadata is shown in fig. 3, and includes the following contents: model code (Id), model code (database field name), model name (front end page and Excel header show), model description, number of attributes, whether to synchronize to database, whether to synchronize to Excel template file, attribute list, creation time, modification time, maintainer. Wherein the data attributes include the following: attribute code (Id), attribute name, attribute description, data constraint, creation time, modification time, maintainer. Data constraints generally include the following: unique, index creation, data type, data length constraint (0 is not limited), data encoding format, data verification format (regular expression), data dictionary type (empty without dictionary).
(3) And the user continues to customize the required data operation and data display based on the defined data model: the present invention provides a data operation management configuration page for each data model (i.e., data sheet), the page functions including the following functional features defining each attribute in each model, as shown in table 2:
| whether or not to display | Order of arrangement | Whether or not to serve as a query condition | Query order |
| Whether or not to derive | Whether or not it is necessary to import in batches | Order of introduction | Whether it is modifiable after import |
TABLE 2 functional operation definition of data attributes
(4) The system generates data operation description metadata, data display description metadata and a front end code template file according to the management configuration metadata of data operation and display so as to store data operation required by a user, and transmits the metadata to a code generator to generate a database SQL (DML) file and a front end target code file when the user needs to download. Wherein the data operation description metadata is shown in fig. 4, and comprises: the operation type, the operation code (Id), the operation parameter area, the SQL statement template, the operation result and the display content mapping relation area.
(5) The user can add a data operation page to a menu of the system or add front-end codes to other systems to visually display the data operation page: the invention provides a uniform routing address containing parameters, which can realize the visual display and operation of the imported resource data after being configured to a system menu, including import and export, condition query, paging display, content modification and deletion. According to the invention, the target codes in the JSON format, the JS format and the Vue format can be directly displayed by the front end or downloaded and used by the user by analyzing the data display description metadata file. The data presentation description metadata is shown in fig. 5 and includes the following contents: the system comprises a screening condition description area, a dictionary translation and drop-down list description area, a table display description area, a form display description area and a paging and page rolling setting description area.
(6) The system transmits the data model description metadata, the data operation description metadata and the corresponding data dictionary into an Excel template file generator to generate an Excel template file for a user to download and use and generates a corresponding data import description metadata file. When the model is defined with import operation, automatically creating an Excel import template file and an import description metadata file, storing the Excel import template file and the import description metadata file into a system file system, and keeping synchronous update. Wherein importing the template file supports the following functions, as in table 3:
table 3Excel import template form function
(7) The system acquires an Excel data file uploaded by a user as a file to be processed: the method can be set to store the original file in a system file system and then read, and can also be set to directly read the stream data, and the large file is generally stored in a background and then read and imported step by step.
(8) The system reads the file data to be processed and converts the file data into intermediate data with a uniform format: and dividing the read file stream data according to the sheet page, the line and the column, and converting the read file stream data into intermediate data in a Map + two-dimensional array format.
(9) The system transmits intermediate data to be processed and data import description metadata into an import data analyzer for analysis and verification to generate data analysis result data: and verifying and translating matrix data in sheet page Map data to be imported, mapping the matrix data into warehousing Map data and generating error data prompt information. The data import description metadata is shown in fig. 6, and includes: the cell is a null value prompt, the cell is a null character string (containing a blank space) prompt, the cell format is regularly described, the cell maximum value, the cell minimum value, the cell data length limit and the cell corresponding data dictionary (reverse coding processing), and the check or the translation is not performed by default if the content is null.
(10) The system performs data storage operation or returns data verification error prompt information to the user according to the analysis result data: if the import contains error data, the error data is returned to the specific cell of the user, and a problem exists; and if all the verification passes, performing data warehousing according to warehousing operation in the data operation description metadata of the model, and returning result information.
The above description is only a preferred embodiment of the present invention, and is only used to illustrate the technical solutions of the present invention, and not to limit the protection scope of the present invention. Any modification, equivalent replacement, or improvement made within the spirit and principle of the present invention shall fall within the protection scope of the present invention.
Claims (8)
1. A method for importing Excel table data into a database based on metadata definition and implementation is characterized in that,
the process of importing and managing offline original resource data into a relational database is realized in a mode of designing, defining, configuring and analyzing metadata in a computer system; the metadata is a uniform mapping rule on the basis of different relational database storage types and different resource data model examples; the consistency of all operations of data adding, deleting, checking, importing and exporting are unified through one-time establishment of a data model and one-time constraint of a data rule.
2. The method of claim 1,
the method comprises the steps of controlling a database table structure by establishing a relational data model and a general data dictionary, and carrying out addition, deletion, modification, importing and exporting SQL sentences, mapping rules, coding conversion and template files of operation on a data table, thereby providing a unified API (application programming interface) and a standard Excel import table.
3. The method of claim 1,
and analyzing and generating a front-end target code through a general front-end template file in an xml format, matching with front-end dynamic analysis, and displaying an example data management page of the routing address parameter adding model to a system menu through configuration data.
4. The method of claim 3,
a user self-defines a data model and a data dictionary; configuring metadata by means of data modeling and dictionary management, generating corresponding model description metadata and a data dictionary corresponding to user requirements, and synchronously creating an entity in a database; the user continues to customize the desired data manipulation and data presentation based on the defined data model.
5. The method of claim 4,
and generating data operation description metadata, data display description metadata and a front end code template file according to the management configuration metadata of the data operation and display so as to store the data operation required by the user and transmitting the metadata into a code generator to generate a database SQL code file and a front end object code file when the user needs to download.
6. The method of claim 5,
the user can add a data operation page to a menu of the system or add front-end codes to other systems to visually display the data operation page.
7. The method according to claim 5 or 6,
and transmitting the data model description metadata, the data operation description metadata and the corresponding data dictionary into an Excel template file generator to generate an Excel template file for a user to download and use and generate a corresponding data import description metadata file.
8. The method of claim 7,
acquiring an Excel data file uploaded by a user as a file to be processed; reading file data to be processed and converting the file data into intermediate data in a unified format; transmitting intermediate data to be processed and data import description metadata into an import data analyzer for analyzing and verifying to generate data analysis result data; and performing data warehousing operation or returning data verification error prompt information to a user according to the analysis result data.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211534099.4A CN115934716A (en) | 2022-12-02 | 2022-12-02 | Method for importing Excel table data into database based on metadata definition and implementation |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211534099.4A CN115934716A (en) | 2022-12-02 | 2022-12-02 | Method for importing Excel table data into database based on metadata definition and implementation |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN115934716A true CN115934716A (en) | 2023-04-07 |
Family
ID=86648700
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202211534099.4A Pending CN115934716A (en) | 2022-12-02 | 2022-12-02 | Method for importing Excel table data into database based on metadata definition and implementation |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN115934716A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116756779A (en) * | 2023-08-17 | 2023-09-15 | 青岛诺亚信息技术有限公司 | Electronic form data objectification storage system and method |
-
2022
- 2022-12-02 CN CN202211534099.4A patent/CN115934716A/en active Pending
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116756779A (en) * | 2023-08-17 | 2023-09-15 | 青岛诺亚信息技术有限公司 | Electronic form data objectification storage system and method |
| CN116756779B (en) * | 2023-08-17 | 2023-11-17 | 青岛诺亚信息技术有限公司 | Electronic form data objectification storage system and method |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US11816100B2 (en) | Dynamically materialized views for sheets based data | |
| US11086894B1 (en) | Dynamically updated data sheets using row links | |
| US8386916B2 (en) | Systems and methods to create a multidimensional expression calculated member in a spreadsheet cell | |
| CN106598612B (en) | Operation method and system for data table in database | |
| CN109582647B (en) | Unstructured evidence file oriented analysis method and system | |
| US8145990B2 (en) | Systems and methods to dynamically recognize a database member identifier entered into a spreadsheet cell | |
| CN109766529B (en) | Report generation method and equipment | |
| CN104881275A (en) | Electronic spreadsheet generating method and device | |
| CN111752999A (en) | Configured report generation system and method | |
| CN111833981A (en) | Structured report making and compiling method | |
| CN115934716A (en) | Method for importing Excel table data into database based on metadata definition and implementation | |
| US8099663B2 (en) | Apparatus and method for document synchronization | |
| JP7339628B2 (en) | Online report creation system using Excel tools | |
| CN111401023B (en) | Report generation method, report generation device, server and storage medium | |
| CN116010439A (en) | Visual Chinese SQL system and query construction method | |
| CN110806963A (en) | Example information monitoring and visual display method based on wave cloud database | |
| JP7216377B2 (en) | Online reporting system with query binding capabilities | |
| CN116627390B (en) | ICD file substitution method and device in aviation software development | |
| CN112130841B (en) | SQL development method and device and terminal equipment | |
| CN117009422B (en) | Method for realizing data import by convenience business personnel | |
| CN117520606B (en) | Method and device for importing heterogeneous data sources into graph database | |
| CN111581212B (en) | Data storage method, system, server and storage medium of relational database | |
| CN116627935A (en) | Resource model-based data arrangement method, platform, equipment and medium | |
| US20240037325A1 (en) | Ability to add non-direct ancestor columns in child spreadsheets | |
| WO2022178931A1 (en) | Implementation method, apparatus and device for querying dynamic columns |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |