CN111813849A - Data extraction method, device and device, and storage medium - Google Patents

Abstract

The present invention provides a data extraction method, device, device, and storage medium. The method includes: acquiring a target form template matching a form to be processed from a configured form template, each form template having a corresponding database table, and The form template includes field information corresponding to at least one field of the form header in the database table; the data corresponding to the field information is determined from the form according to the field information in the target form template, and the target data is obtained; The target data is extracted to the database table corresponding to the target form template. There is no need to configure the corresponding extraction method for each form, which reduces the workload required for configuration.

Description

Data extraction method, device and device, and storage medium

technical field

The present invention relates to the technical field of data processing, and in particular, to a data extraction method, device and equipment, and a storage medium.

Background technique

With the continuous advancement and deepening of my country's informatization construction, the construction of government agencies, enterprise groups and business systems in various industries has reached a certain level. Data is also stored in a variety of ways, either in different types of databases or in files. For some files such as Excel files, there is at least one form in the file, and the data in the form is generally filled in manually. In the same business system, the form format may be different due to different regions or departments, and there will be massive differences. Structured Excel file.

When dealing with some business requirements, it is necessary to store the data in a large number of Excel files with different structures into the corresponding database tables by category. For example, meteorological data extracted from various Excel files with different structures is stored in a database table. , extract vegetable data from various Excel files of different structures and store it in another database table, which can be realized by ETL technology. ETL is a landing technology in the field of data integration. Different from traditional data exchange, ETL provides easier and more convenient and easy-to-use data conversion (that is, data processing on demand) under the premise of completing basic data exchange (extraction, transmission, and loading). Stronger support enables data to flow between different businesses, while ensuring that the data obtained by each business is accurate, timely, and in line with business needs.

In the existing ETL technology, most of the extraction schemes of Excel files with different structures need to manually identify each form format in the Excel file. For each form, configure the corresponding extraction method according to the form format. It is time-consuming and labor-intensive to extract the data.

SUMMARY OF THE INVENTION

In view of this, the present invention provides a data extraction method, device and device, and storage medium, which eliminates the need to configure a corresponding extraction method for each form, and can reduce the workload required for configuration.

A first aspect of the present invention provides a data extraction method, comprising:

Obtain a target form template matching the form to be processed from the configured form template, each form template has a corresponding database table, and the form template contains field information corresponding to at least one field of the form header in the database table;

Determining data corresponding to the field information from the form according to the field information in the target form template to obtain target data;

Extracting the target data into a database table corresponding to the target form template.

According to an embodiment of the present invention, obtaining a target form template matching the form to be processed from the configured form template includes:

Iterate over each row of cells in the sheet:

Find a form template that contains field information that matches the content of the cell in the row in the configured form template;

If found, the row of cells is determined as the form header of the form, and the found form template is determined as the target form template.

According to an embodiment of the present invention, the number of form headers of the form is one; the method further includes:

When it is determined that the row of cells is the form header of the form, the traversal of the form is ended.

According to an embodiment of the present invention, the method further includes:

If not found, check whether the current traversal times of the form reaches the maximum traversal times, if so, end the traversal of the form, otherwise continue to traverse the form.

According to an embodiment of the present invention, searching the configured form template for a form template whose field information matches the content of the row of cells is further:

When the data type of each cell in the row is a text type, the configured form template is searched for a form template that contains field information that matches the content of the cell in the row.

According to an embodiment of the present invention,

The field information includes at least a field name;

Search the configured form templates for form templates that contain field information that matches the content of the cells in the row, including:

For each of the configured form templates:

determining a reference cell from the row of cells, the content of the reference cell matching any field name in the form template;

According to the number of the reference cells, it is determined whether the form template is a form template that contains field information that matches the content of the row of cells.

According to an embodiment of the present invention, the content of the reference cell matches any field name in the form template means:

The content of the reference cell is the same as any field name in the form template;

Alternatively, the content of the reference cell and any field name in the form template are synonyms or synonyms, and the synonyms or synonyms are determined by a set matching algorithm.

According to an embodiment of the present invention,

The form template further includes a set matching degree;

Determine whether the form template is a form template with field information that matches the content of the row of cells according to the number of the reference cells, including:

Calculate the ratio between the number of the reference cells and the number of fields corresponding to the field information in the form template;

If the ratio is greater than the set matching degree in the form template, it is determined that the form template is a form template whose field information matches the content of the cell in the row.

According to an embodiment of the present invention,

The field information includes at least a field name;

The form template further includes a field sequence corresponding to each field information and a conversion rule corresponding to at least one field information, where the field sequence is determined according to the form header of the database table corresponding to the target form template;

According to the field information in the target form template, the data corresponding to the field information is determined from the form, and the target data is obtained, including:

Sort the column where each target cell in the form is located according to the field order in the target form template, the target cell belongs to the form header of the form, and the content of the target cell is the same as the target form template matches any of the field names in;

For each row in the form that is located after the form header, the cell content in the row where each target cell is located is determined as a piece of target data.

According to an embodiment of the present invention,

Before determining the content of the cells in the row located in the columns where the target cells are located, the method further includes: if there is a merged cell in the row, splitting the merged cell, and dividing the merged cell Fill in the contents of all split cells;

After determining the cell content in the row where each target cell is located, the method further includes: converting the determined at least one cell content according to the conversion rule in the target form template, and converting the obtained cell content The cell content is used as a piece of target data.

According to an embodiment of the present invention, the method further comprises:

When the form template configuration needs to be performed on any database table, check whether there is a corresponding relationship between the database table and the form template to be configured in the specified database. The corresponding relationship between the database table and the form template is stored in the specified database.

According to an embodiment of the present invention, obtaining a target form template matching the form to be processed from the configured form template, further comprising:

In the case of obtaining the type of data stored in the form, a target form template matching the form to be processed is obtained from the form template in which the database table for storing this type of data is configured.

According to an embodiment of the present invention, the form is any form in the to-be-processed file, the format of the to-be-processed file is a specified file format, and the to-be-processed file includes at least one form.

A second aspect of the present invention provides a data extraction device, comprising:

The target form template determination module is used to obtain the target form template matching the form to be processed from the configured form template, each form template has a corresponding database table, and the form template includes at least the form header in the corresponding database table. Field information for a field;

a target data determination module, configured to determine data corresponding to the field information from the form according to the field information in the target form template to obtain target data;

A data extraction module is used to extract the target data into a database table corresponding to the target form template.

According to an embodiment of the present invention, when the target form template determination module acquires the target form template matching the form to be processed from the configured form templates, it is specifically used for:

Iterate over each row of cells in the sheet:

Find a form template that contains field information that matches the content of the cell in the row in the configured form template;

If found, the row of cells is determined as the form header of the form, and the found form template is determined as the target form template.

According to an embodiment of the present invention, the number of form headers of the form is one; the target form template determination module is further configured to:

When it is determined that the row of cells is the form header of the form, the traversal of the form is ended.

According to an embodiment of the present invention, the target form template determination module is further configured to:

If not found, check whether the current traversal times of the form reaches the maximum traversal times, if so, end the traversal of the form, otherwise continue to traverse the form.

According to an embodiment of the present invention, when the target form template determination module searches the configured form template for a form template whose field information matches the content of the row of cells, it is further configured to:

When the data type of each cell in the row is a text type, the configured form template is searched for a form template that contains field information that matches the content of the cell in the row.

According to an embodiment of the present invention,

The field information includes at least a field name;

When the target form template determination module searches the configured form template for a form template whose field information matches the content of the row of cells, it is specifically used for:

For each of the configured form templates:

determining a reference cell from the row of cells, the content of the reference cell matching any field name in the form template;

According to the number of the reference cells, it is determined whether the form template is a form template that contains field information that matches the content of the row of cells.

According to an embodiment of the present invention, the content of the reference cell matches any field name in the form template means:

The content of the reference cell is the same as any field name in the form template;

Alternatively, the content of the reference cell and any field name in the form template are synonyms or synonyms, and the synonyms or synonyms are determined by a set matching algorithm.

According to an embodiment of the present invention,

The form template further includes a set matching degree;

When the target form template determination module determines whether the form template is a form template whose field information matches the content of the row of cells according to the number of the reference cells, it is specifically used for:

Calculate the ratio between the number of the reference cells and the number of fields corresponding to the field information in the form template;

If the ratio is greater than the set matching degree in the form template, it is determined that the form template is a form template whose field information matches the content of the cell in the row.

According to an embodiment of the present invention,

The field information includes at least a field name;

The form template further includes a field sequence corresponding to each field information and a conversion rule corresponding to at least one field information, where the field sequence is determined according to the form header of the database table corresponding to the target form template;

When the target data determination module determines the data corresponding to the field information from the form according to the field information in the target form template, and obtains the target data, it is specifically used for:

Sort the column where each target cell in the form is located according to the field order in the target form template, the target cell belongs to the form header of the form, and the content of the target cell is the same as the target form template matches any of the field names in;

For each row in the form that is located after the form header, the cell content in the row where each target cell is located is determined as a piece of target data.

According to an embodiment of the present invention,

Before the target data determination module determines the content of the cells in the row where each target cell is located, it is also used for: if there is a merged cell in the row, split the merged cell, and divide the merged cell into the row. The contents of the merged cell are filled into all the split cells;

After the target data determination module determines the cell content located in the column where each target cell is located in the row, it is further configured to: convert the determined at least one cell content according to the conversion rule in the target form template, Use the obtained cell content as a piece of target data.

According to an embodiment of the present invention, the apparatus further comprises:

The configuration module is used to check whether there is a corresponding relationship between the database table and the form template to be configured in the specified database when the form template configuration needs to be performed on any database table. configuration, and store the corresponding relationship between the database table and the form template in the specified database.

According to an embodiment of the present invention, when the target form template determination module obtains a target form template matching the form to be processed from the configured form template, it is further used for:

In the case of obtaining the type of data stored in the form, a target form template matching the form to be processed is obtained from the form template in which the database table for storing this type of data is configured.

According to an embodiment of the present invention, the form is any form in the to-be-processed file, the format of the to-be-processed file is a specified file format, and the to-be-processed file includes at least one form.

A third aspect of the present invention provides an electronic device, including a processor and a memory; the memory stores a program that can be called by the processor; wherein, when the processor executes the program, the implementation is as described in the foregoing embodiments data extraction method.

A fourth aspect of the present invention provides a machine-readable storage medium on which a program is stored, and when the program is executed by a processor, implements the data extraction method described in the foregoing embodiments.

The embodiment of the present invention has the following beneficial effects:

In this embodiment of the present invention, form templates corresponding to multiple database tables are preconfigured, and each form template contains field information corresponding to at least one field in the form header in the database table. Obtain the target form template matching the form from the configured form template, determine the data corresponding to the field information from the form based on the field information in the target form template, obtain the target data, and extract the target data to the target form template In the corresponding database table, in the above method, for forms with different structures, in the case of matching the same form template, the data extraction can be completed based on the same form template, and there is no need to manually identify the form format, nor do Configuring a corresponding extraction method for each form can greatly reduce the workload and time required for user configuration.

Description of drawings

1 is a schematic flowchart of a data extraction method according to an embodiment of the present invention;

2 is a schematic flowchart of a data extraction method according to another embodiment of the present invention;

3 is a structural block diagram of a data extraction apparatus according to an embodiment of the present invention;

FIG. 4 is a structural block diagram of an electronic device according to an embodiment of the present invention.

Detailed ways

Exemplary embodiments will be described in detail herein, examples of which are illustrated in the accompanying drawings. Where the following description refers to the drawings, the same numerals in different drawings refer to the same or similar elements unless otherwise indicated. The implementations described in the illustrative examples below are not intended to represent all implementations consistent with the present invention. Rather, they are merely examples of apparatus and methods consistent with some aspects of the invention as recited in the appended claims.

The terminology used in the present invention is for the purpose of describing particular embodiments only and is not intended to limit the present invention. As used in this specification and the appended claims, the singular forms "a," "the," and "the" are intended to include the plural forms as well, unless the context clearly dictates otherwise.

It should be understood that although the terms first, second, third, etc. may be used herein to describe various objects, the information should not be limited to these terms. These terms are only used to distinguish objects of the same type from each other. For example, a first object could also be referred to as a second object, and similarly, a second object could also be referred to as a first object, without departing from the scope of the present invention. Depending on the context, the word "if" as used herein can be interpreted as "at the time of" or "when" or "in response to determining."

The data extraction method according to the embodiment of the present invention will be described in more detail below, but it should not be limited thereto. In one embodiment, referring to FIG. 1 , a data extraction method, applied to an electronic device, may include the following steps:

S100: Obtain a target form template matching the form to be processed from the configured form template, each form template has a corresponding database table, and the form template includes field information corresponding to at least one field of the form header in the database table;

S200: Determine data corresponding to the field information from the form according to the field information in the target form template, and obtain target data;

S300: Extract the target data into a database table corresponding to the target form template.

In the embodiment of the present invention, the execution body of the data extraction method is an electronic device. For example, the electronic device may be a computer device or a server composed of multiple computer devices. Of course, the specific type of the electronic device is not limited to this, as long as it has a certain data processing capability.

In this embodiment of the present invention, an ETL application program can be improved, or a new ETL application program can be developed, so that the above-mentioned data extraction method can be implemented when the electronic device runs the ETL application program.

In step S100, a target form template matching the form to be processed is obtained from the configured form template, each form template has a corresponding database table, and the form template includes a field corresponding to at least one field of the form header in the database table information.

Different database tables can be used to store different types of data. For example, a database table is used to store meteorological data, and another database table is used to store vegetable data. Of course, there can be other database tables used to store other types of data, such as Personnel data, etc. And each type of data may exist in a large number of files with different structures, such as Excel files, so it is necessary to extract the corresponding data from the Excel files with different structures first.

A corresponding form template can be configured for each database table in advance, and the form template contains field information corresponding to at least one field of the form header in the database table, and the form header of the database table indicates the data to be stored in the database table. fields and the order of the fields. Each database table may correspond to one or more form templates, and in the case of corresponding to multiple form templates, for example, the form header of a database table may contain 5 fields, and the database table may store the If the data of 5 fields can be stored, and the data of 4 fields can be stored, a form template with field information of 5 fields and a form template with field information of 4 fields can be configured for it.

According to data storage requirements (such as the fields of data to be stored), the required field information can be automatically obtained from a file that stores a certain type of data, such as an Excel file that stores vegetable data, and generated based on the obtained field information. The corresponding form template. Alternatively, the required form template can be generated by the user through manual configuration.

The form template can mainly include several important attributes such as setting the matching degree Proportion, the maximum number of traversals maxTraverseTimes, and the field information Columns. Among them, the field information Columns can include the field type Type, field name Name, time format Format, field length Length, field precision Precision, etc., can refer to the following table (1), of course, it is not limited to this.

In one example, the user can sort out the fields of the data to be stored in the library table according to a certain library table, configure the corresponding field information, fill in the field information into the ETL program page, and then automatically generate a form template in JSON format , as the form template corresponding to the library table. Taking the field information Columns in Table (1) as an example, in the form template, the field information Columns can be used as the parent parameter of the field type Type, field name Name, time format Format, field length Length, and field precision Precision.

When the target form template matching the form to be processed is obtained from the configured form template, the form header of the form can be determined (which can be pre-specified or determined according to the form template), and the form header can be determined according to the field information of the fields in the form header. A matching target form template is determined from the configured form templates. For example, there are fields corresponding to all or most of the field information in the target form template in the form header of the form. Of course, it is not limited to this, and will be described in more detail in the subsequent content.

In one embodiment, the method further includes: when form template configuration needs to be performed on any database table, checking whether there is a corresponding relationship between the database table and the form template to be configured in the specified database, if not, then Continue to configure the form template, and store the corresponding relationship between the database table and the form template in the designated database.

The specified database can be any specified database, which is not specifically limited.

Store the corresponding relationship between the configured database table and the form template in the specified database. When you need to configure the corresponding form template for a database table later, you only need to check whether the database table and the form template exist in the specified database. If the corresponding relationship does not exist, it means that the corresponding form template has not been configured for the database table before. At this time, the configuration of the form template is performed, and the corresponding relationship between the database table and the form template is stored in the specified in the database.

Optionally, if there is a corresponding relationship between the database table and the form template that needs to be configured in the specified database, there is no need to repeat the configuration, and the form template can be called directly subsequently. In this way, repeated configuration of the same form template can be avoided.

In one embodiment, the target form template matching the form to be processed is obtained from the configured form template, and further may be: in the case of obtaining the data category stored in the form, the target form template for storing the data of the form can be obtained from The database table of the configured form template gets the target form template that matches the form to be processed. In other words, the subsequent configured form template is the form template in which the database table for storing this type of data is configured.

For example, if the form stores vegetable data, a target form template matching the form to be processed is obtained from the form template configured in the database table for storing vegetable data.

In one embodiment, the form is any form in the files to be processed, the format of the file to be processed is a specified file format, and the file to be processed includes at least one form.

The specified file format may be, for example, xls format or xlsx format. Correspondingly, the file to be processed is an Excel file. An Excel file may contain one or more sheets, and the sheet may be any sheet in the Excel file.

Optionally, the electronic device can search out the Excel files whose file names match the regular expression from the folders that save various types of files through the specified regular expression, and read out one or more Excel files each time. file (the specific number of readings can be specified, and the specific reading order is not limited), and traverse the Sheets in the read Excel file, and each traversed sheet is used as the form to be processed. In this example, the regular expression can be, for example, ".*xlsx", which means to read all Excel files with a suffix of xlsx. Of course, this is just an example, and it is not limited to this.

In step S200, data corresponding to the field information is determined from the form according to the field information in the target form template to obtain target data.

The field information may include field names, and data corresponding to the field names may be determined from the form to obtain target data. For example, each determined target data includes the field names in the target form template.

For example, the target form template can include the field names of three fields, including weather, temperature, and statistical time; the form header of the form also includes three fields of weather, temperature, and statistical time, and two other irrelevant fields. Fields a and b. Correspondingly, each piece of data contains these fields. Specifically, the form can refer to the following table (2):

For example, the following three target data can be identified:

Clear, 21.5, 2020-05-23;

Rain, 18, 2020-05-24;

Sunny, 28, 2020-05-25.

In step S300, the target data is extracted into a database table corresponding to the target form template.

After all target data is determined, all target data can be extracted to the database table corresponding to the target form template; or, each time a piece of target data is determined, the target data can be extracted to the database table corresponding to the target form template. , which is not specifically limited.

After the data is determined, the data can also be converted to a certain extent according to the set conversion rules, and then stored in the database table corresponding to the target form template. For example, the fields in the data can be sorted according to the field order of the form header in the database table, and the sorted data can be extracted into the database table. Of course, this is just an example, and the conversion rules are not limited to this. Conversion rules can be preset in form templates.

In this embodiment of the present invention, form templates corresponding to multiple database tables are preconfigured, and each form template contains field information corresponding to at least one field in the form header in the database table. Obtain the target form template matching the form from the configured form template, determine the data corresponding to the field information from the form based on the field information in the target form template, obtain the target data, and extract the target data to the target form template In the corresponding database table, in the above method, for forms with different structures, in the case of matching the same form template, the data extraction can be completed based on the same form template, and there is no need to manually identify the form format, nor do Configuring a corresponding extraction method for each form can greatly reduce the workload and time required for user configuration.

For example, another form is as follows (3):

Comparing table (2) and table (3), the structure of the two is different, specifically, the order of "weather" and "irrelevant field a" in the two are reversed, but the field to be extracted in the two is "weather". ", "temperature", and "statistical time" are the same, and can be matched to the same form template, and the data extraction is completed based on the same form template. The matching process is also automatic, and there is no need to identify table (2) and table (3) separately. format, and there is no need to set corresponding extraction methods for table (2) and table (3) respectively.

In one embodiment, in step S100, obtaining a target form template matching the form to be processed from the configured form template may include the following steps:

Iterate over each row of cells in the sheet:

Find a form template that contains field information that matches the content of the cell in the row in the configured form template;

If found, the row of cells is determined as the form header of the form, and the found form template is determined as the target form template.

In most cases, the first row of cells in the form is the form header, but there are other cases, such as the first row of cells in the form is the title of the form, etc.

In this embodiment, whether the field information contained in the form template matches the content of the cell in the row is determined in units of behavior. In the case of matching, on the one hand, it can be determined that the form template is the target form template, and on the other hand, Determining that the row of cells is the form header can save additional steps for determining the form header.

In this embodiment, each row of cells in the form may be read in a traversal manner, so as to implement the above steps. That is, for each form template, each row of cells in the form can be traversed to check whether the field information in the form template matches the content of the traversed row of cells. The matching rules are not limited, for example, it can be a form The field names contained in all the field information in the template exist in the cell in this row. If there is a match, it can be determined that the cell in the row traversed is the form header of the form, and the found form template is determined as the form template. Describe the target form template.

Optionally, the number of form headers of the form is one, and the method further includes: when it is determined that the traversed row of cells is the form header of the form, ending the traversal of the form. Since the target form template has been found and the form header has been determined, there is no need to traverse subsequent rows, so the traversal can be ended to improve processing efficiency.

Of course, if the number of form headers of the form is greater than one, when it is determined that the traversed row of cells is the form header of the form: the form can be traversed continuously; or the current traversal times of the form can be checked. Whether the maximum number of traversals is reached, if so, the traversal of the form is ended, otherwise, the traversal of the form can be continued.

Optionally, the method further includes: if no form template containing field information matching the content of the row of cells is found in the configured form template, checking whether the current number of traversal of the form reaches The maximum number of traversals, if yes, end the traversal of the form. Generally speaking, it is impossible to determine whether there is a match after traversing many times, indicating that there is a high probability of mismatching. By defining the maximum number of traversals to limit the number of traversals of the form, and ending the traversal in time, you can avoid the waste of resources caused by too many traversals.

The maximum traversal times can be defined in the form template, and the maximum traversal times in different form templates can be different. Here, when checking whether the current traversal times of the form reaches the maximum traversal times, you can specifically check the current traversal times of the form. Whether the maximum number of traversals in this form template has been reached. Of course, the maximum number of traversals can also be defined in the electronic device, which is not limited here.

In one embodiment, searching the configured form template for a form template whose field information matches the content of the row of cells is further:

When the data type of each cell in the row is a text type, the configured form template is searched for a form template that contains field information that matches the content of the cell in the row.

Generally speaking, the type of each cell in the form header in the form is text type, so based on this feature, when the data type of each cell in the row is text type, then in the configured form In the template, look for the form template that contains the field information that matches the content of the cells in the row; and when the data type of at least one cell in the row is non-text type, you can directly skip the search for the cells in the row. Processing of form templates to speed up processing efficiency.

In one embodiment, the field information includes at least a field name;

Search the configured form templates for form templates that contain field information that matches the content of the cells in the row, including:

For each of the configured form templates:

determining a reference cell from the row of cells, the content of the reference cell matching any field name in the form template;

According to the number of the reference cells, it is determined whether the form template is a form template that contains field information that matches the content of the row of cells.

There can be multiple cells in a row of a form. The more cells in a row whose content matches the field name in the form template (that is, the reference cell), the more likely the cell in the row is the header of the form. The larger the value is, it can be determined according to the number of reference cells whether the form template is a form template that contains field information that matches the content of the cells in the row.

For example, when the number of reference cells reaches a set number, it can be determined that the form template is a form template that contains field information that matches the content of the cells in the row. The set number here can be defined in the form template, or can be preset in the electronic device, and the value of the set number can be determined according to needs, for example, it can be greater than 1.

Optionally, for each row of cells, when each reference cell is determined, the content and position index of the reference cell may be recorded in the cache. Optionally, the position index can be used to indicate the column where the reference cell is located. Taking table (2) as an example, the following information can be stored in the cache:

"Weather": C2

"Temp": C4

"Statistical time": C5

Among them, C2 means that the cell of "weather" is located in the second column, C4 means that the cell of "temperature" is located in the third column, and C5 means that the cell of "statistical time" is located in the fifth column. Here, C2, C4, and C5 can be It is represented by numerical values, and the specific numerical values are not limited, as long as the corresponding columns can be represented.

Further, if it is determined according to the number of reference cells that the form template is not a form template that contains field information that matches the content of the row of cells, the cache can be cleared.

In one embodiment, the content of the reference cell matches any field name in the form template means:

The content of the reference cell is the same as any field name in the form template;

Alternatively, the content of the reference cell and any field name in the form template are synonyms or synonyms, and the synonyms or synonyms are determined by a set matching algorithm.

In other words, the matching rule for the content of the reference cell to match any field name in the form template may include: exact match (ie, the same) of the string, or a synonym of the string determined based on the set matching algorithm Or synonym matching, of course, the specific matching rule is not limited to this, for example, it can also be determined based on text similarity.

The set matching algorithm may include, for example, synonym matching based on NLP (Natural Language Processing, Natural Language Processing), etc., which is not specifically limited.

In this embodiment, the matching of synonyms or synonyms is not an accurate matching method, so that the same form template can be used for data extraction of more similar data forms, which can further reduce the number of templates that need to be configured, and is also conducive to finding out more accurately. Form header.

In one embodiment, the form template further includes setting a matching degree;

Determine whether the form template is a form template with field information that matches the content of the row of cells according to the number of the reference cells, including:

Calculate the ratio between the number of the reference cells and the number of fields corresponding to the field information in the form template;

If the ratio is greater than the set matching degree in the form template, it is determined that the form template is a form template whose field information matches the content of the cell in the row.

Take the form as table (2) as an example, assuming that the fields corresponding to the field information in the form template are weather, temperature, and statistical time respectively, that is to say, the number of fields is 3. When traversing to the first row of cells, find out 3 reference cells (the contents of 3 reference cells are cached), the contents of these 3 reference cells are weather, temperature, and statistical time respectively, which correspond to the information of the 3 fields in the form template. Therefore, refer to The number of cells is also 3, then the ratio of the number of reference cells to the number of fields corresponding to the field information in the form template is 3/3=1, assuming that the matching degree set in the form template is 0.8 , since 1 is greater than 0.8, it can be determined that the first row of cells is the form header, and the form template is a form template whose field information matches the content of the row of cells.

In this embodiment, as long as the ratio is greater than the set matching degree, complete matching is not required. Such a form template can match more forms, which can further reduce the number of templates that need to be configured.

In one embodiment, if there is no target form template matching the form in the configured form template, the relevant information of the form can be output to a specified file (such as but not limited to a txt file) or a specified database table , for subsequent auditing.

Relevant information can include, for example, the file name of the file where the form is located, and the reason for the error that the target form template cannot be obtained. The reason for the error here can include, for example, errors in the field information in the form template (such as writing price as value), and the format of the file. Error (for example, it should be in html format, but it is rendered in xlsx format).

Auditors can rely on the error cause in the specified database table after specifying the file to determine whether it is necessary to synchronize the content of the form to the problem database table, modify the form template, modify the file format, etc. through manual auditing.

In one embodiment, the field information includes at least a field name;

The form template further includes a field sequence corresponding to each field information and a conversion rule corresponding to at least one field information, where the field sequence is determined according to the form header of the database table corresponding to the target form template;

In step S200, the data corresponding to the field information is determined from the form according to the field information in the target form template, and the target data is obtained, which may include the following steps:

S201: Sort the column where each target cell in the form is located according to the order of fields in the target form template, the target cell belongs to the form header of the form, and the content of the target cell is the same as the target cell Any field name in the form template matches;

S202: For each row in the form that is located after the form header, determine the cell content in the row where each target cell is located, and obtain a piece of target data.

When the form header is determined, the content of the cell recorded in the cache is the content of the target cell, and the matching rule for the content of the target cell to match any field name in the target form template can refer to the aforementioned reference cell The matching rules for the content of the grid will not be repeated here.

The column of each target cell in the form can be determined according to the cell content and position index recorded in the cache. The order of the fields in the target form template can be the same as the order of the corresponding fields in the form header of the corresponding database table, which can reflect the target The mapping relationship between the fields in the form template and the fields in the corresponding database table, after sorting the column where each target cell in the form is based on the field order, the corresponding fields in the form can be arranged in the order of the fields in the database table.

Continue to take the form as table (2) as an example. Assuming that the fields in the target form template are sorted by statistical time, weather, and temperature in order from front to back, and the field order in the corresponding database table is also the same, then the form is sorted according to the field order. After sorting the columns in which the target cells are located in , the following table (4) is obtained:

After sorting, for each row in the form located after the form header, determine the cell content in the row where each target cell is located, and obtain a piece of target data.

For example, in the above table (4), the form header is the first row, and the rows after the form header are the second to fourth rows. These rows record the data to be extracted. Taking the second row as an example, determine The contents of the cells in the row where each target cell is located are 2020-05-23, Qing, and 21.5, respectively, as a piece of target data.

Through the above sorting, the determined cell content can be correctly recorded at the corresponding field position in the database table, so as to ensure the order consistency of the fields included in each data in the database table.

Optionally, if the name of a field (default field for short) in the target field template does not exist in the form header, that is, when the number of extracted cell contents is less than the required number of fields, you can use an empty field. The value complements the position corresponding to the default field, that is, the position corresponding to the default field in the target data can be a null value. For example, if the order of fields in the target form template is statistical time, weather, temperature, and PM2.5 values in order from front to back, after 2020-05-23, sunny, and 21.5 are determined, a null value is added after 21.5 For example, if it is 0, the target data obtained is 2020-05-23, clear, 21.5, 0.

In one embodiment, before the content of the cells in the row where each target cell is located is determined, the method further includes:

If there is a merged cell in the row, the merged cell is split, and the content of the merged cell is filled into all the split cells.

Each cell has corresponding attribute information. The attribute information can indicate whether the cell is a merged cell. If there is a merged cell in the row, the merged cell will be split, for example, into the smallest cell. And fill the contents of the merged cell into all the split cells.

For example, the form is as follows (5):

The first line is the header of the form. If the merged cells are not split, the following two data will be determined:

1. Class 8, Seventh Company, Zhang San, 202001

2. , Li Si, 202002

Obviously, the team information was lost in Li Si's data, resulting in data loss.

However, in this embodiment, when traversing to the second row, it is possible to detect that the cell where "seven consecutive 8 classes" is located is a merged cell, split the merged cell, and fill in the "seven consecutive 8 classes". Enter into all the split cells, and get the following table (6):

After the above split, the determined data are as follows:

1. Seventh consecutive eighth class, Zhang San, 202001

2. Seventh consecutive eighth class, Li Si, 202002

That is, the problem of data loss is solved.

In one embodiment, after determining the cell content in the row that is located in the column where each target cell is located, the method further includes:

Convert at least one determined cell content according to the conversion rule in the target form template, and use the obtained cell content as a piece of target data.

The corresponding cell content can be converted based on the field length, field precision, field type, and field format in the target form template. For example, for a cell whose field type is time type, convert the cell content with the predefined time format yyyy (year)-MM (month)-dd (day) in the target form template. Of course, this is just an example, and there may actually be other conversion methods.

Through the above conversion method, the format of the data output to the database table can be made more uniform, and further processing is more convenient.

The data extraction method of the present invention will be described below with reference to FIG. 2 with a more specific embodiment.

1) To obtain the Excel file, you can find the Excel file whose file name matches the regular expression from various files through the specified regular expression, and then perform step 2);

2) To obtain the sheet in the Excel file, you can traverse all the sheets in the above Excel file, the obtained sheet is the traversed sheet, and then go to step 3);

3) Traverse the configured form template, and traverse each row of cells in the sheet for the traversed form template; then perform step 4);

4) For each cell in the traversed row of cells, check whether there is field information that matches the content of the cell in the form template, and if so, then the content of the cell and the position index of the cell After recording in the cache, go to step 5); if not, go to step 5) directly;

5) Check whether the ratio of the number of cell contents in the cache to the number of fields corresponding to the field information in the form template is greater than the set matching degree, if so, go to step 7), if not, go to step 6);

6) Check whether there is the next cell in the row, if so, continue to process the next cell in the row; if not, continue to traverse the form, and at the end of the form traversal, if there is currently For form templates that have not been traversed, continue to traverse the next form template. Otherwise, output an error result indicating that the sheet cannot match any form template. The error result can include the file name, sheet page name and error reason. Return to step 2) to continue processing the next form;

7) Determine that the current row is the form header, and the form template is the target form template, and then perform step 8);

8) Sort the column where the content of the target cell in the form is located according to the field order in the target form template. The content of the target cell is the content of the cell recorded in the cache. The position of the column can be determined according to the corresponding position index, and then execute step 9);

9) Traverse the rows located after the form header in the form, extract the target data from the traversed rows according to the field information in the target form template, and output them to the corresponding database table.

The present invention also provides a data extraction apparatus. Referring to FIG. 3, the data extraction apparatus 100 includes:

The target form template determination module 101 is used to obtain a target form template matching the form to be processed from the configured form template, each form template has a corresponding database table, and the form template includes the corresponding database table in the form header. Field information of at least one field;

a target data determination module 102, configured to determine data corresponding to the field information from the form according to the field information in the target form template to obtain target data;

The data extraction module 103 is configured to extract the target data into a database table corresponding to the target form template.

In one embodiment, when the target form template determination module acquires the target form template matching the form to be processed from the configured form templates, it is specifically used for:

Iterate over each row of cells in the sheet:

Find a form template that contains field information that matches the content of the cell in the row in the configured form template;

If found, the row of cells is determined as the form header of the form, and the found form template is determined as the target form template.

In one embodiment, the number of form headers of the form is one; the target form template determination module is further used for:

When it is determined that the row of cells is the form header of the form, the traversal of the form is ended.

In one embodiment, the target form template determination module is further configured to:

If not found, check whether the current traversal times of the form reaches the maximum traversal times, if so, end the traversal of the form, otherwise continue to traverse the form.

In one embodiment, when the target form template determination module searches the configured form template for a form template whose field information matches the content of the cell in the row, the module is further configured to:

When the data type of each cell in the row is a text type, a form template containing field information that matches the content of the cell in the row is searched in the configured form template.

In one embodiment, the field information includes at least a field name;

When the target form template determination module searches the configured form template for a form template whose field information matches the content of the row of cells, it is specifically used for:

For each of the configured form templates:

determining a reference cell from the row of cells, the content of the reference cell matching any field name in the form template;

According to the number of the reference cells, it is determined whether the form template is a form template that contains field information that matches the content of the row of cells.

In one embodiment, the content of the reference cell matches any field name in the form template means:

The content of the reference cell is the same as any field name in the form template;

Alternatively, the content of the reference cell and any field name in the form template are synonyms or synonyms, and the synonyms or synonyms are determined by a set matching algorithm.

In one embodiment,

The form template further includes a set matching degree;

When the target form template determination module determines whether the form template is a form template whose field information matches the content of the row of cells according to the number of the reference cells, it is specifically used for:

Calculate the ratio between the number of the reference cells and the number of fields corresponding to the field information in the form template;

If the ratio is greater than the set matching degree in the form template, it is determined that the form template is a form template that contains field information that matches the content of the cell in the row.

In one embodiment,

The field information includes at least a field name;

The form template further includes a field sequence corresponding to each field information and a conversion rule corresponding to at least one field information, and the field sequence is determined according to the form header of the database table corresponding to the target form template;

When the target data determination module determines the data corresponding to the field information from the form according to the field information in the target form template, and obtains the target data, it is specifically used for:

Sort the column where each target cell in the form is located according to the field order in the target form template, the target cell belongs to the form header of the form, and the content of the target cell is the same as the target form template matches any of the field names in;

For each row in the form that is located after the form header, the cell content in the row where each target cell is located is determined as a piece of target data.

In one embodiment,

Before the target data determination module determines the content of the cells in the row where each target cell is located, it is also used for: if there is a merged cell in the row, split the merged cell, and divide the merged cell into the row. The contents of the merged cell are filled into all the split cells;

After the target data determination module determines the cell content located in the column where each target cell is located in the row, it is further configured to: convert the determined at least one cell content according to the conversion rule in the target form template, Use the obtained cell content as a piece of target data.

In one embodiment, the apparatus further comprises:

The configuration module is used to check whether there is a corresponding relationship between the database table and the form template to be configured in the specified database when the form template configuration needs to be performed on any database table. configuration, and store the corresponding relationship between the database table and the form template in the specified database.

In one embodiment, when the target form template determination module acquires the target form template matching the form to be processed from the configured form template, it is further used for:

In the case of obtaining the type of data stored in the form, a target form template matching the form to be processed is obtained from the form template in which the database table for storing this type of data is configured.

In one embodiment, the form is any form in the files to be processed, the format of the file to be processed is a specified file format, and the file to be processed includes at least one form.

For details of the implementation process of the functions and functions of each unit in the above device, please refer to the implementation process of the corresponding steps in the above method, which will not be repeated here.

As for the apparatus embodiments, since they basically correspond to the method embodiments, reference may be made to the partial descriptions of the method embodiments for related parts. The device embodiments described above are only illustrative, wherein 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.

The present invention also provides an electronic device, including a processor and a memory; the memory stores a program that can be called by the processor; wherein, when the processor executes the program, the data described in the foregoing embodiments is implemented extraction method.

The embodiments of the data extraction apparatus of the present invention can be applied to electronic equipment. Taking software implementation as an example, a device in a logical sense is formed by reading the corresponding computer program instructions in the non-volatile memory into the memory for operation by the processor of the electronic device where the device is located. From the perspective of hardware, as shown in FIG. 4 , FIG. 4 is a hardware structure diagram of the electronic device where the data extraction apparatus 100 according to an exemplary embodiment of the present invention is located, except for the processor 510 and the memory shown in FIG. 4 . 530 , the network interface 520 , and the non-volatile memory 540 , the electronic device where the data extraction apparatus 100 is located in the embodiment may also include other hardware generally according to the actual function of the electronic device, which will not be repeated here.

The present invention also provides a machine-readable storage medium on which a program is stored, and when the program is executed by a processor, implements the data extraction method described in the foregoing embodiments.

The present invention may take the form of a computer program product embodied on one or more storage media having program code embodied therein, including but not limited to disk storage, CD-ROM, optical storage, and the like. Machine-readable storage media includes both persistent and non-permanent, removable and non-removable media, and storage of information can be implemented by any method or technology. Information may be computer readable instructions, data structures, modules of programs, or other data. Examples of machine-readable storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory Memory (ROM), Electrically Erasable Programmable Read Only Memory (EEPROM), Flash Memory or other memory technology, Compact Disc Read Only Memory (CD-ROM), Digital Versatile Disc (DVD) or other optical storage , magnetic tape cartridges, magnetic disk storage or other magnetic storage devices or any other non-transmission medium that can be used to store information that can be accessed by a computing device.

The above descriptions are only preferred embodiments of the present invention, and are not intended to limit the present invention. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of the present invention shall be included in the present invention. within the scope of protection.

Claims (16)

1. A data extraction method, comprising:

acquiring a target form template matched with a form to be processed from the configured form templates, wherein each form template is provided with a corresponding database table and comprises field information of at least one field of a form head in the corresponding database table;

determining data corresponding to the field information from the form according to the field information in the target form template to obtain target data;

and extracting the target data to a database table corresponding to the target form template.

2. The data extraction method of claim 1, wherein obtaining a target form template matching a form to be processed from the configured form templates comprises:

traversing each row of cells in the form:

searching a form template containing field information matched with the content of the row of cells in the configured form template;

if the form template is found, determining the cell of the line as the form head of the form, and determining the found form template as the target form template.

3. The data extraction method of claim 2, wherein the number of form headers of the form is 1; the method further comprises the following steps:

and when the line cell is determined to be the form head of the form, ending the traversal of the form.

4. The data extraction method of claim 2, further comprising:

if not, checking whether the current traversal times of the form reaches the maximum traversal times, if so, ending the traversal of the form, otherwise, continuing the traversal of the form.

5. The data extraction method of claim 2, wherein searching for a form template in the configured form template that contains field information that matches the content of the row of cells further comprises:

and when the data type of each cell in the line is a text type, searching a form template containing field information matched with the content of the cell in the line in the configured form template.

6. The data extraction method as claimed in claim 2,

the field information at least comprises a field name;

searching the configured form template for the form template with the field information matched with the content of the row of cells, wherein the method comprises the following steps:

for each of the configured form templates:

determining a reference cell from the row of cells, wherein the content of the reference cell is matched with any field name in the form template;

and determining whether the form template is a form template with the contained field information matched with the content of the row of cells or not according to the number of the reference cells.

7. The data extraction method of claim 6, wherein the matching of the content of the reference cell with any field name in the form template is:

the content of the reference cell is the same as the name of any field in the form template;

or the content of the reference cell and any field name in the form template are similar words or synonyms, and the similar words or synonyms are determined through a set matching algorithm.

8. The data extraction method of claim 6,

the form template also comprises a set matching degree;

determining whether the form template is a form template with the contained field information matched with the content of the row of cells according to the number of the reference cells, wherein the method comprises the following steps:

calculating the ratio of the number of the reference cells to the number of the fields corresponding to the field information in the form template;

and if the ratio is greater than the set matching degree in the form template, determining that the form template is the form template with the contained field information matched with the content of the row of cells.

9. The data extraction method as claimed in claim 2,

the field information at least comprises a field name;

the form template further comprises a field sequence corresponding to each field information and at least one conversion rule corresponding to the field information, wherein the field sequence is determined according to a form head of a database table corresponding to the target form template;

determining data corresponding to the field information from the form according to the field information in the target form template to obtain target data, wherein the method comprises the following steps:

sequencing columns of target cells in the form according to the field sequence in the target form template, wherein the target cells belong to the form head of the form, and the content of the target cells is matched with any field name in the target form template;

and determining the cell content of each row positioned in the column of each target cell in the row as a piece of target data for each row positioned behind the head of the form in the form.

10. The data extraction method as claimed in claim 9,

before determining the cell contents in the column of the target cells in the row, the method further includes: if the merged cell exists in the row, splitting the merged cell, and filling the content of the merged cell into all split cells;

after determining the cell contents in the column of the target cells in the row, the method further includes: and converting the determined at least one cell content according to the conversion rule in the target form template, and taking the obtained cell content as a piece of target data.

11. The data extraction method of claim 1, wherein the method further comprises:

when any database table needs to be configured with the form template, whether the corresponding relation between the database table and the form template needing to be configured exists in a specified database is checked, if the corresponding relation does not exist, the configuration of the form template is continued, and the corresponding relation between the database table and the form template is stored in the specified database.

12. The data extraction method of any one of claims 1-11, wherein obtaining a target form template from the configured form templates that matches the form to be processed further comprises:

and under the condition of obtaining the data category stored in the form, obtaining a target form template matched with the form to be processed from the form templates configured in the database table for storing the data.

13. The data extraction method according to claim 1, wherein the form is any one form in a file to be processed, the format of the file to be processed is a specified file format, and the file to be processed contains at least one form.

14. A data extraction apparatus, comprising:

the target form template determining module is used for acquiring a target form template matched with a form to be processed from the configured form templates, each form template is provided with a corresponding database table, and the form template comprises field information of at least one field of a form head in the corresponding database table;

the target data determining module is used for determining data corresponding to the field information from the form according to the field information in the target form template to obtain target data;

and the data extraction module is used for extracting the target data into a database table corresponding to the target form template.

15. An electronic device comprising a processor and a memory; the memory stores a program that can be called by the processor; wherein the processor, when executing the program, implements a data extraction method as claimed in any one of claims 1 to 13.

16. A machine-readable storage medium, having stored thereon a program which, when executed by a processor, implements a data extraction method as claimed in any one of claims 1 to 13.

Images