A metadata-driven table data parsing and restructuring method

Abstract

The application discloses a metadata-driven table data analysis and reconstruction method, which comprises the following steps: S1, importing table data in a general text file into a visual editing interface; S2, manually setting a tree structure relationship reconstruction area in the visual interface; S3, using a tree data relationship reconstruction method to reconstruct the data relationship of the set tree structure relationship reconstruction area, using a general relationship reconstruction method to reconstruct the table data of the remaining area, and generating metadata of each table unit; and S4, after importing the metadata into an existing data template, associating the template metadata to the table unit. The method realizes storage of the table data analysis, reconstruction and quality check process, thereby getting rid of complex and repeated secondary development work, and realizing quick construction of the analysis and construction mode on the basis of simple configuration and modification of the reused data template.

Description

Technical Field

[0001] This invention relates to the field of tabular data processing technology, specifically to a metadata-driven method for parsing and reconstructing tabular data. Background Technology

[0002] Tables are widely used in industrial information technology due to their comprehensive, specific, and intuitive information display. By manually defining cell merging, arrangement, headers, and cell data formats, complex structured data can be "flattened" into tabular information. Each cell in a table is independent, and the relationships between data exist implicitly through manually defined merging and arrangement; the table itself does not store these relationships. Although the "flattening" process "loses" these relationships, the intuitive and comprehensive nature of tables, along with considerations for user habits, means that in practical structured data acquisition scenarios, the collected data is often directly stored in tables. To centrally store the data stored in tables into the application information system, it is necessary to parse and import the data and reconstruct the "lost" relationships. Currently, a common practice is to set up a unified, fixed data relationship import template. Technicians convert, organize, and clean the collected data tables stored in different file formats into an Excel template, then upload the Excel file to the server, where a pre-written parsing program parses and processes the data. However, existing methods have the following shortcomings:

[0003] 1) Table data in different file formats requires secondary file format conversion and manual cleaning, and the import and parsing process lacks a unified, standardized, flexible and variable data input.

[0004] 2) Fixed data relationship import templates need to be customized based on different business data, and data parsing and reconstruction programs need to be customized for different templates. In extreme cases where multiple parsing and reconstruction functions exist in different applications with inconsistent standards, it may be necessary to clean and process a single set of data multiple times and develop similar parsing and reconstruction functions multiple times. This results in repetitive and complex work and a waste of human resources.

[0005] 3) Fixed parsing and refactoring procedures are usually embedded in the server-side code, making it impossible to customize or change them during the parsing process, thus consuming a large amount of server-side computing resources. At the same time, the handling of exceptions during the data parsing and refactoring process relies on the developers' prior design and lacks flexible and effective "hot update" methods. When unexpected exceptions occur, they may cause the program to crash and may also cause unpredictable consequences to the subsequent parsing and refactoring process, affecting the overall stability and reliability of the system. Summary of the Invention

[0006] To address the aforementioned issues, the inventors have provided a method that uses metadata to achieve specific binding between table instance data and data templates, enabling the storage of the table data parsing, reconstruction, and quality verification processes, thereby eliminating the need for complex and repetitive secondary development work.

[0007] This invention provides a metadata-driven method for parsing and reconstructing tabular data, comprising the following steps:

[0008] Step S1: Import the tabular data from the general text file into the visual editing interface;

[0009] Step S2: In the visual interface, manually set the area for reconstructing the tree structure relationship;

[0010] Step S3: Use the tree-structured data relationship reconstruction method to reconstruct the data relationship in the set tree structure relationship reconstruction area, and use the general relationship reconstruction method to reconstruct the table data in the remaining areas to generate metadata for each table cell; each area contains multiple cells;

[0011] Step S4: After importing the metadata into the existing data template, associate the template metadata with the table cell.

[0012] Furthermore, in step S3, the step of reconstructing the data relationships of the designated reconstruction region using the tree-structured data relationship reconstruction method includes:

[0013] Step S31: Based on the maximum number of rows (row_max) and the maximum number of columns (col_max) of the tree-structured reconstruction region, construct a two-dimensional index array A of size row_max × col_max;

[0014] Step S32: Set the data field extension direction to the main axis direction, and push the header cell data into the header queue HeadArray one by one along the main axis direction. Iterate through the first row element A[0,i] in A and process it. If A[0,i] is not empty, skip it and do not process it. Otherwise, take a header cell from the header queue HeadArray and get its column merge count c. 头 The data in this cell is repeatedly filled into A[0,i] to A[0,i+c]. 头 ] within the area;

[0015] Step S33: Push the table data cells one by one into the table data queue DataArray along the main axis;

[0016] Step S34: Set column number col = 0 and row number row = 1. Get the value fieldName of A[0,col]. If fieldName is not empty, skip this step and do not process it. Otherwise, take a table cell from the table data queue DataArray in step S33 for further processing, add attributes to the table cell, and get the row merging number r and column merging number c of the cell. 格 ;

[0017] Step S35: Compare the values ​​of A[row,col-1] and A[row-1,col-1]. If the two values ​​are equal, the value of A[row,col] is equal to the value of A[row-1,col] plus 1. Otherwise, the value of A[row,col] is equal to the value of A[row,col-1] plus one dimension, and the merged regions A[row,col] to A[row+r,col+c] are merged. 格 Assign the value SN to the element within the specified area to add the attribute to the current table cell.

[0018] Step S36: Let col = col + 1, and repeat steps S34 and S35 until col = col_max - 1;

[0019] Step S37: Let row = row + 1, and repeat steps S34, S35, and S36 until row = row_max - 1.

[0020] Furthermore, in step S3, the step of reconstructing the table data in the remaining areas using a general relation reconstruction method includes:

[0021] Set the data field extension direction to the main axis direction, and read the data name and cell merging count of the main axis row / column;

[0022] Traverse each data point along the main axis and add attributes to each cell;

[0023] Traverse each data point along the lateral axis and add attributes to each cell.

[0024] Furthermore, the data template in step S4 is a data dictionary, which includes: the structure, data type, data value, processing method, processing logic, and quality verification rules of the final required data.

[0025] Furthermore, the method also includes:

[0026] Perform quality checks according to the quality check rules in the data dictionary of each unit.

[0027] Furthermore, the method also includes:

[0028] Data that fails the data quality check is manually corrected, and the required data is extracted to form a data dictionary for storage.

[0029] Compared with the prior art, the beneficial effects of the present invention are as follows:

[0030] (1) This invention uses data template technology with data dictionary as carrier to realize the storage of the process of parsing, reconstructing and quality verification of table data, thereby getting rid of complex and repetitive secondary development work, and realizing the rapid construction of parsing construction mode by simple configuration and modification on the basis of reusing existing data template.

[0031] (2) The implicit tree structure data relationship is automatically reconstructed based on the structure of the table, without the need to repeatedly customize and develop parsing and reconstruction programs for the tree structure data in the table.

[0032] (3) Based on a general text file (including Word, Excel, etc.) parsing engine, the raw data is parsed without the need for secondary conversion and file format processing of tabular data in various file formats, which greatly reduces the dependence on data sources. Attached Figure Description

[0033] Figure 1 This is a flowchart of the metadata-driven table data parsing and reconstruction method of the present invention;

[0034] Figure 2 Flowchart of the method for reconstructing tree-like data relationships;

[0035] Figure 3 This is a diagram of a data dictionary. Detailed Implementation

[0036] The present invention will now be described in further detail with reference to specific embodiments and accompanying drawings.

[0037] Example 1

[0038] This invention provides a metadata-driven method for parsing and reconstructing table data, comprising the following steps:

[0039] Step S1: Import tabular data from common text files into the visual editing interface. Specifically, import or directly copy and paste tables from common text files such as Word and Excel into the visual interface developed based on the UEditor rich text engine. The UEditor engine parses the data into a unified HTML table format while preserving the original table style. This allows for subsequent processing of the tables using a unified data relationship reconstruction method, eliminating the need for secondary conversion and file format processing of data existing in various file formats, thus reducing dependence on data sources.

[0040] Step S2: In the visualization interface, manually define the area for reconstructing the tree structure relationship. Use the visualization editing tools provided by UEditor to select the cells in the area to be reconstructed, and add the `tree-item` attribute to mark them. Simultaneously, set the depth extension direction of the tree data in the table to the main axis direction. Correspondingly, add the `normal-item` attribute to other ordinary data reconstruction areas, and set the data field extension direction to the main axis direction. Taking the following table as an example, the data in the "Component 1 Inspection Table" has a clear tree structure; mark this area as the tree structure relationship reconstruction area, set the main axis direction to horizontal, and reconstruct it using the tree data relationship reconstruction method. The "Component Overview Table" is marked as an ordinary data reconstruction area, set the main axis direction to horizontal, and reconstructed using the general relationship reconstruction method.

[0041]

[0042]

[0043] Step S3: Use the tree-structured data relationship reconstruction method to reconstruct the data relationship of the set tree structure relationship reconstruction area (component 1 verification table), and use the general relationship reconstruction method to reconstruct the table data of the remaining areas (component overview table) to generate metadata for each table cell; each area contains multiple cells.

[0044] Among them, such as Figure 2 As shown, the process of reconstructing the data relationships in a defined reconstruction area using the tree-structured data relationship reconstruction method is as follows:

[0045] Step S31: Based on the maximum number of rows (row_max) and the maximum number of columns (col_max) of the tree reconstruction region, construct a two-dimensional index array A of size row_max × col_max.

[0046] Step S32: Push the header cell data one by one into the header queue HeadArray along the main axis, and iterate through the first row of A[0,i] to process it. If A[0,i] is not empty, skip it and do not process it; otherwise, take a header cell from the header queue HeadArray and get its column merge count c. 头 The data in this cell is repeatedly filled into A[0,i] to A[0,i+c]. 头 ] area.

[0047] Step S33: Push the table data cells one by one into the table data queue DataArray along the main axis.

[0048] Step S34: Set column number col = 0 and row number row = 1. Obtain the value fieldName of A[0,col]. If fieldName is not empty, skip this step and do not process it. Otherwise, retrieve a table cell from the table data queue DataArray in step S33 for further processing. Add the attribute name (with the value fieldName) to the table cell and obtain the row merging number r and column merging number c of the cell. 格 .

[0049] Step S35: Compare the values ​​of A[row,col-1] and A[row-1,col-1]. If the two values ​​are equal, the value of A[row,col] is equal to the value of A[row-1,col] plus 1. Otherwise, the value of A[row,col] is equal to the value of A[row,col-1] plus one dimension, and the merged regions A[row,col] to A[row+r,col+c] are merged. 格 Assign the value SN to the element within the area, and add the attribute sn (with the value SN) to the current table cell.

[0050] Step S36: Let col = col + 1, and repeat steps S35 and S36 until col = col_max - 1;

[0051] Step S37: Let row = row + 1, and repeat steps S34, S35, and S36 until row = row_max - 1.

[0052] Taking technical requirement 2.2 in the above inspection table as an example, the value of the structured index array before calculating the sn attribute value is shown in the following formula. The calculation process is as follows: compare whether the values ​​on the left (value 2.1) and the top left (value 1.1) of the corresponding position in A are equal. Since they are not equal, the sn value is based on the left value plus one dimension. The sn value is 2.1.1, that is, its structure index number is 2.1.1.

[0053]

[0054] The process of reconstructing the table data in the remaining areas using the general relational reconstruction method is as follows:

[0055] Read the data names and cell merge counts of the main axis rows / columns;

[0056] Traverse each data point along the main axis and add attributes to each cell;

[0057] Traverse each data point along the lateral axis and add attributes to each cell.

[0058] For example, "2.1" is the sn attribute value of the unit with main axis number 2 and side axis number 1.

[0059] The metadata generated in step S3 includes: table number, main axis number m-index, cross axis number s-index, number of merges along the main axis r, number of merges along the cross axis c, structure index sn, category name name, data type (including string, number, date), size range, precision, format, character length, enumeration value, etc. Taking the cell containing "Component 1" in the component overview table as an example, its metadata object should be {table:0,m-index:1,s-index:1,r:1,c:1,sn:1.1,name:component name,type:string,length:3}.

[0060] Step S4: After importing the step metadata into the existing data template, associate the template metadata with the table cells. The data template exists in the form of a data dictionary, which describes the structure, data type, data value, processing method, processing logic, and quality verification rules of the final required data; the data dictionary is specifically bound to the table cells through a unique structure index number (sn). Taking inspection item 1 in the above inspection table as an example, its template data dictionary is as follows: Figure 3 As shown,

[0061] Step S5: Perform quality verification according to the quality verification rules in the data dictionary of each unit.

[0062] Step S6: Manually correct the data that fails the data quality check, and extract the corrected data to form a data dictionary for storage. Extract template metadata and save it for future table data relationship reconstruction and parsing.

[0063] The above examples illustrate the present invention only to aid in understanding it and are not intended to limit the scope of the invention. Those skilled in the art can make various simple deductions, modifications, or substitutions based on the principles of this invention.

Claims

1. A metadata-driven method for parsing and reconstructing tabular data, characterized in that, Includes the following steps: Step S1: Import the tabular data from the general text file into the visual editing interface; Step S2: In the visual interface, manually set the area for reconstructing the tree structure relationship; Step S3: Use the tree-structured data relationship reconstruction method to reconstruct the data relationship in the set tree structure relationship reconstruction area, and use the general relationship reconstruction method to reconstruct the table data in the remaining areas to generate metadata for each table cell; Each region contains multiple units; Step S4: After importing the metadata into the existing data template, associate the template metadata with the table cells; In step S3, the steps of reconstructing the data relationships of the designated reconstruction area using the tree-structured data relationship reconstruction method include: Step S31: Based on the maximum number of rows in the tree-structured reconstruction region row_max and maximum number of columns col_max , build size row_max col_max Two-dimensional index array A ; Step S32: Set the data field extension direction to the main axis direction, and push the header cell data into the header queue one by one along the main axis direction. HeadArray In the middle, loop traversal processing A The first row of elements A[0,i] ,like A[0,i] If the value is not empty, skip it and do nothing; otherwise, start from the head queue. HeadArray Extract a header cell from the table and obtain its column merge count. c 头 Repeat the filling of the data in this cell into A[0,i] to A[0,i+c 头 ] within the area; Step S33: Push the table data cells one by one into the table data queue along the main axis. DataArray middle; Step S34: Let the number of columns be... col =0, number of rows row =1, get A [0, col The value of ] fieldName ,like fieldName If the value is not empty, skip the process; otherwise, retrieve the data from the table queue in step S33. DataArray Extract a table cell Cell Further processing is performed, including adding attributes to the table cell and retrieving the number of merged rows for that cell. r Combined with column c 格 ; Step S35: Comparison A [ row , col -1] and A [ row -1, col The value of -1], if the two values ​​are equal, then A [ row , col The value of ] SN equal A [ row -1, col The value of ] is incremented by 1, otherwise A [ row , col The value of ] is equal to A [ row , col Add a dimension to the value of -1, and merge regions. A [ row , col ]to A [ row + r , col + c 格 The values ​​of the elements within the region are assigned as follows: SN , is the current table cell Cell Add attributes; Step S36: Let col = col +1, repeat steps S34 and S35 until... col = col_max -1; Step S37: Let row = row +1, repeat steps S34, S35, and S36 until... row = row_max -1.

2. The method as described in claim 1, characterized in that, In step S3, the step of reconstructing the table data in the remaining areas using the general relation reconstruction method includes: Set the data field extension direction to the main axis direction, and read the data name and cell merging count of the main axis row / column; Traverse each data point along the main axis and add attributes to each cell; Traverse each data point along the lateral axis and add attributes to each cell.

3. The method as described in claim 1, characterized in that, The data template in step S4 is a data dictionary, which includes: the structure, data type, data value, processing method, processing logic, and quality verification rules of the final required data.

4. The method as described in claim 3, characterized in that, Also includes: Step S5: Perform quality verification according to the quality verification rules in the data dictionary of each unit.

5. The method as described in claim 4, characterized in that, Also includes: Step S6: Manually correct the data that fails the data quality check, and extract the corrected data to form a data dictionary for storage.

Images