Complex data format file processing system and method

Abstract

The invention relates to a complex data format file processing system and method, a file preprocessing module preprocesses and divides a to-be-stored data file into data blocks, and each data block is distributed in an independent table sub-page; the data preprocessing module classifies and identifies the table sub-pages, marks classification identifiers, and then preprocesses data contents in the table sub-pages to form new table sub-pages; and the data storage module maps the table data content in the newly formed table sub-page into a database table according to a pre-configured mapping table. According to the method, preprocessing and storage of the data files can be efficiently realized, technical support is provided for data systematic arrangement and structured storage, the efficiency of data file processing is effectively improved, the problems that an existing data processing application system needs to manually modify a data storage template corresponding to the files and the fault tolerance is relatively low are solved, and the method is easy to realize and high in practicability. And the method has a wider application range.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular to a system and method for processing files in complex data formats. Background Art

[0002] In data processing, the data to be processed is generally in the form of files. Data file processing and storage is the process of systematically organizing and storing the collected data for subsequent analysis and query. The existing processing method generally defines a set of data storage templates. For example, for capital data, data file templates such as bank account opening information, account information, and transaction details are defined. Data processing personnel manually organize data files obtained from multiple channels according to the corresponding template files to make the data files conform to the format of the template, and then read the fixed header data through the application or software for storage processing. This method requires manual adjustment of the data file format by comparing the data template, which is inefficient.

[0003] Due to the wide range of data file sources, multiple data types, and the lack of a unified format for data files, manually modifying the corresponding data template for storage is labor-intensive and prone to errors. For example, the data sources for fund transactions include third parties such as banks, UnionPay, Alipay, and Tenpay. Some of these files are not in standard table data format, but data tables with different titles distributed in the same table subpage, and the table content often contains errors or missing items that are necessary for fund data. If the processing logic is incomplete, incomplete and abnormal data will be generated when the data is stored. How to intelligently split data tables with complex content formats and verify the necessary items of fund data is currently a technical problem. In addition, for the same type of data, data files from different sources have different field names, and it is necessary to quickly map similar data from different sources to a standard database. Summary of the invention

[0004] In order to solve the problem that existing data file processing tools and software cannot efficiently process multi-source heterogeneous data files and have low fault tolerance, the present invention innovatively provides a complex data format file processing system and method.

[0005] A complex data format file processing system, comprising:

[0006] The file preprocessing module is used to preprocess the incoming data files and split them into data blocks. Each data block is distributed in an independent table subpage.

[0007] The data preprocessing module is connected to the file preprocessing module and is used to classify and identify the table subpages and mark them with classification marks, and then preprocess the data content in the table subpages to form new table subpages;

[0008] The data storage module is connected to the data preprocessing module and is used to map the table data content in the newly formed table subpage to the database table according to the pre-configured mapping table.

[0009] Furthermore, the file preprocessing module includes:

[0010] A file verification unit is used to verify the data files to be stored and select the data files to be stored that meet the system processing conditions;

[0011] A file conversion unit, connected to the file verification unit, is used to convert the to-be-stored data files that meet the system processing conditions into table data files;

[0012] The file block division unit is connected to the file conversion unit and is used to perform data block processing on the table data file to form data blocks, and each data block is distributed in an independent table sub-page.

[0013] Furthermore, the data preprocessing module includes:

[0014] A data identification unit, used to match a table subpage with a preset data resource type, and mark the table subpage with a corresponding classification mark according to the matched data resource type;

[0015] A data normalization unit, connected to the data identification unit, is used to normalize the table data content in the table subpage with the classification identification;

[0016] The data completion unit is connected to the data normalization unit and is used to complete the missing items of the table data in the table subpage after normalization to form a new table subpage.

[0017] Furthermore, the data storage module includes:

[0018] A mapping configuration unit, used to pre-configure the mapping relationship between the data fields and the data table headers in the database table to form a mapping table;

[0019] A data reading unit, used to read the classification identification, data header and table data content of the table subpage;

[0020] The matching storage unit is connected to the data reading unit and the mapping configuration unit respectively, and is used to match the corresponding database table according to the classification identifier of the table subpage, and map the table data content in the table subpage to the corresponding database table according to the mapping table between the data table header and the data field in the database table.

[0021] Furthermore, the matching storage unit includes:

[0022] The automatic storage sub-unit is used to automatically match the corresponding database table according to the classification identification of the table sub-page, and automatically map the table data content in the table sub-page to the corresponding database table according to the mapping table between the data table header and the data field in the database table;

[0023] The manual storage subunit is connected to the automatic storage subunit and is used to manually match the table data content under the data header that has not been automatically mapped in the table subpage to the database table.

[0024] A complex data format file processing method, using the aforementioned complex data format file processing system, comprises:

[0025] Step A1, pre-processing the data file to be stored into the database and dividing it into data blocks, each data block is distributed in an independent table sub-page;

[0026] Step A2, classifying and identifying the table subpages and marking them with classification marks, and then preprocessing the data content in the table subpages to form new table subpages;

[0027] Step A3: Map the newly formed table subpage to the database table according to the pre-configured mapping table.

[0028] Furthermore, step A1 includes:

[0029] Step A11, verifying the data files to be stored, and selecting the data files to be stored that meet the system processing conditions;

[0030] Step A12, converting the data files to be stored that meet the system processing conditions into table data files;

[0031] Step A13, dividing the table data file into data blocks, each data block is distributed in an independent table sub-page.

[0032] Further, step A2 includes:

[0033] Step A21, matching the table subpage with the preset data resource type, and marking the table subpage with a corresponding classification mark according to the matched data resource type;

[0034] Step A22, normalizing the table data content in the table subpage with the classification mark;

[0035] Step A23, completing the missing items of the table data in the normalized table subpage to form a new table subpage.

[0036] Further, step A3 includes:

[0037] Step A31, pre-configuring the mapping relationship between the data fields and the data headers in the database table to form a mapping table;

[0038] Step A32, reading the classification identifier, data header and table data content of the table subpage;

[0039] Step A33, matching the corresponding database table according to the classification identifier of the table subpage, and mapping the table data content in the table subpage to the corresponding database table according to the mapping table between the data table header and the data fields in the database table.

[0040] Further, step A33 includes:

[0041] Step A331, automatically matching the corresponding database table according to the classification identifier of the table subpage, and automatically mapping the table data content in the table subpage to the corresponding database table according to the mapping table between the data table header and the data field in the database table;

[0042] Step A332, manually match the table data content under the data header in the table subpage that has not been automatically mapped to the database table.

[0043] The beneficial technical effects of the present invention are as follows: from the aspects of file preprocessing, data preprocessing, data warehousing, etc., the present invention can efficiently realize the preprocessing and warehousing of data files, provide technical support for the systematic organization and structured storage of data, effectively improve the efficiency of data file processing, overcome the problem that the existing data processing application system needs to manually modify the file corresponding to the data warehousing template and has low fault tolerance, is easy to implement, and has a wider range of applications. BRIEF DESCRIPTION OF THE DRAWINGS

[0044] Figure 1-5 A module schematic diagram of a complex data format file processing system provided by the present invention;

[0045] Figure 6-10 A flowchart of the steps of a method for processing a file in a complex data format provided by the present invention. DETAILED DESCRIPTION

[0046] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0047] It should be noted that, in the absence of conflict, the embodiments of the present invention and the features in the embodiments may be combined with each other.

[0048] The present invention will be further described below in conjunction with the accompanying drawings and specific embodiments, but they are not intended to limit the present invention.

[0049] See also Figure 1 The present invention provides a complex data format file processing system, comprising:

[0050] A file preprocessing module (1), used to preprocess the data file to be stored and split it into data blocks, each data block is distributed in an independent table subpage;

[0051] A data preprocessing module (2) is connected to the file preprocessing module (1) and is used to classify and identify the table subpages and mark them with classification marks, and then preprocess the data content in the table subpages to form new table subpages;

[0052] The data storage module (3) is connected to the data preprocessing module (2) and is used to map the table data content in the newly formed table subpage into the database table according to the pre-configured mapping table.

[0053] See also Figure 2 , further, the file preprocessing module (1) comprises:

[0054] A file verification unit (11) is used to verify the data files to be stored and select the data files to be stored that meet the system processing conditions;

[0055] A file conversion unit (12), connected to the file verification unit (11), is used to convert the to-be-stored data file that meets the system processing conditions into a table data file;

[0056] The file block division unit (13) is connected to the file conversion unit (12) and is used to perform data block processing on the table data file to form data blocks, each data block being distributed in an independent table subpage.

[0057] The file types of data files that meet the system processing conditions are, for example, various types of data files such as EXCEL, CSV, TXT, PDF, etc. The file types of data files that do not meet the system processing conditions are, for example, word type.

[0058] The file verification unit verifies different types of data files to be stored, and checks whether they are file types that the system supports processing. In addition, if they are file types that the system supports processing, the file verification unit is also used to check whether the data content format of the data file to be stored is a non-table data file that meets the requirements, that is, whether the file conversion can be performed. In this way, the files that can be processed by the system are screened out for subsequent processing, and the files that the system does not support processing are filtered out.

[0059] The file conversion unit converts the unstructured data files to be stored into a table data file, for example, extracts the contents in the files and converts them into an EXCEL data file through a converter.

[0060] The file segmentation unit performs data segmentation processing on the converted table data file to form data blocks, thereby extracting different data contents in the same table subpage into a single SHEET page for storage. For example, there are multiple different data tables in the same table data file, such as a personnel information table, a transaction information table, and an account information table. The personnel information table, the transaction information table, and the account information table are separated to form three data blocks of the personnel information table, the transaction information table, and the account information table. Then, the personnel information table, the transaction information table, and the account information table are distributed in independent table subpages, rather than three in the same table subpage, thereby separating the three tables. For example, when converted into an EXCEL data file, each of the personnel information table, the transaction information table, and the account information table is divided into an independent sheet page, and each sheet page is a table subpage.

[0061] Specifically, the file block unit includes a data header check for the file, and defines the rules for the data header as Chinese and English and numeric characters in the configuration item. That is, the data header is generally in Chinese. If the configuration item specifies which specific English words or numbers are allowed to be used as part of the header, the numbers or English words specified in these configuration items can also be considered as the header. For example, if the configuration item specifies "ID" and "Name", then characters such as "ID" or "Name" can appear in the header as the header field. The header cannot be empty. After detecting the data header, the file block unit continues to check whether the data content column in the table matches the header until the obtained row content is empty data or the next data header column stops checking, and extracts the previous row from the header to the header or empty data row checked when the check stops as a data block, and distributes a table subpage. For example, suppose that in the converted table data file, the first table is a personnel information table, the next table is an account information table, and the next one is a transaction information table. The file block unit checks line by line from top to bottom, first checking the data header of the personnel information table, and then checking the field content corresponding to the data header field. If the personnel information table and the account information table are not adjacent, the next line after the end of the personnel information table is a line of empty data. At this time, the previous line of empty data and the data header of the personnel information table are formed into a data block, that is, a personnel information table is extracted separately and distributed to a table sub-page. If the personnel information table and the account information table are closely adjacent, the next line after the end of the personnel information table is the account information table, such as the header of the account information table, then the previous line of the account information table header and the data header of the personnel information table are formed into a data block, that is, a personnel information table is extracted separately and distributed to a table sub-page.

[0062] See also Figure 3 , further, the data preprocessing module (2) includes:

[0063] A data identification unit (21) is used to match the table subpage with a preset data resource type and mark the table subpage with a corresponding classification mark according to the matched data resource type;

[0064] A data normalization unit (22), connected to the data identification unit (21), is used to perform normalization processing on the table data content in the table subpage with the classification mark;

[0065] The data completion unit (23) is connected to the data normalization unit (22) and is used to complete the missing items of the table data in the normalized table subpage to form a new table subpage.

[0066] The data preprocessing module processes each table subpage to form a new table subpage. The data preprocessing module reports an error for the table subpage that is not processed successfully, and forms the table subpage that is processed successfully into a storage file to be processed.

[0067] The data identification unit provides a computing component for mapping data resource types. According to the preset data resource type matching rules, the data resource type of the data block in the table subpage is analyzed, that is, the data resource type. For example, if it is a personnel information table, the corresponding category is the personnel information category, the account information table belongs to the account information category, and the transaction information table belongs to the transaction information category. Then, the table subpage is marked with a classification mark according to the identified data resource type, which is convenient for subsequent search of the corresponding database table and entering the processing flow of the corresponding classification file.

[0068] The data normalization unit normalizes the table data content in the table subpage. For example, the document type, gender, nationality, etc. are normalized according to the metadata dictionary, the time field is uniformly processed in the format of year, month, day, hour, minute, and second, and the numeric types such as amount are uniformly processed with two decimal places. Normalization processing includes ordered scalar normalization and text data normalization. Ordered scalar normalization converts categorical data into numerical data for quantitative analysis, which is achieved by mapping the categorical values ​​to a set of ordered numbers. For example, for the categorical data of gender, males are converted to the numerical value 0 and females are converted to the numerical value 1 to facilitate subsequent quantitative analysis. Text data normalization involves converting text data into a unified format, including removing stop words, stemming, word form restoration, etc.

[0069] The data completion unit is used to complete the content of table data. It checks the necessary fields of the data items in the table according to the data resource type of the table subpage data block, and completes the missing data items according to the historical storage data and inference algorithm. The completion methods include special value filling, average value filling, hot card filling, nearest neighbor method, filling with all possible values, model prediction and multiple interpolation. These methods have their own advantages and disadvantages and are suitable for different data processing scenarios. It is necessary to select the appropriate completion strategy according to the data type, business needs and specific circumstances to ensure the accuracy and reliability of the data.

[0070] See also Figure 4 , further, the data storage module (3) includes:

[0071] A mapping configuration unit (31) is used to pre-configure the mapping relationship between the data fields and the data table headers in the database table to form a mapping table;

[0072] A data reading unit (32), used for reading the classification identification, data header and table data content of the table subpage;

[0073] The matching storage unit (33) is respectively connected to the data reading unit (32) and the mapping configuration unit (31), and is used to match the corresponding database table according to the classification identifier of the table subpage, and map the table data content in the table subpage to the corresponding database table according to the mapping table between the data table header and the data field in the database table.

[0074] In the mapping table, a data field in the database table may be mapped to a variety of data headers expressing content. For example, the name field in the database table may form a mapping relationship in the data header of the file to be stored, such as name, first name, title, etc. The configuration data in the mapping table can be saved using a document database.

[0075] The data reading unit reads the classification identifier, data header and data content (data under the fields of the data header) of the table subpage, and can display the data header and part or all of the data content on the display interface.

[0076] See also Figure 5 , further, the matching storage unit (33) includes:

[0077] The automatic storage sub-unit (331) is used to automatically match the corresponding database table according to the classification identifier of the table sub-page, and automatically map the table data content in the table sub-page to the corresponding database table according to the mapping table between the data table header and the data field in the database table;

[0078] The manual storage subunit (332) is connected to the automatic storage subunit (331) and is used to manually match the table data content under the data header in the table subpage that has not been automatically mapped into the database table.

[0079] After loading the data, first read the configuration items of the data field from the mapping table configuration, and automatically map the matching data header to the data field. The unmatched data header can be matched manually. In the matching field drop-down option, set the matched field to unavailable to prevent repeated selection. After the field matching is completed, the data file is entered into the warehouse, and the data entry process is completed. The matching entry unit includes manual and automatic processing. For data files with fixed data sources and formats, the header fields are automatically matched according to the mapping table and directly entered into the warehouse without manual intervention. For data files with non-fixed source formats, manual matching is used to enter the data into the warehouse.

[0080] From the aspects of file preprocessing, data preprocessing, data warehousing, etc., combined with data item standardization processing, the present invention can efficiently realize the preprocessing and warehousing of data files, provide technical support for data systematization and structured storage, and effectively improve the efficiency of data file processing. In the warehousing processing of multi-source heterogeneous source data files, the present invention supports various types of data files in complex formats such as EXCEL, CSV, TXT, PDF, etc., and divides and classifies data files through data file inspection and segmentation, data preprocessing, matching and warehousing and other processing processes, and normalizes and completes the file data content. Finally, the warehousing processing of data files is finally completed by field mapping matching, overcoming the problem that the existing data processing application system needs to manually modify the file corresponding to the data warehousing template, and the fault tolerance is low. It is easy to implement and has a wider range of applications.

[0081] See also Figure 6 The present invention also provides a complex data format file processing method, using the aforementioned complex data format file processing system, comprising:

[0082] Step A1, pre-processing the data file to be stored into the database and dividing it into data blocks, each data block is distributed in an independent table sub-page;

[0083] Step A2, classifying and identifying the table subpages and marking them with classification marks, and then preprocessing the data content in the table subpages to form new table subpages;

[0084] Step A3: Map the newly formed table subpage to the database table according to the pre-configured mapping table.

[0085] See also Figure 7 , further, step A1 comprises:

[0086] Step A11, verifying the data files to be stored, and selecting the data files to be stored that meet the system processing conditions;

[0087] Step A12, converting the data files to be stored that meet the system processing conditions into table data files;

[0088] Step A13, dividing the table data file into data blocks, each data block is distributed in an independent table sub-page.

[0089] See also Figure 8 , further, step A2 comprises:

[0090] Step A21, matching the table subpage with the preset data resource type, and marking the table subpage with a corresponding classification mark according to the matched data resource type;

[0091] Step A22, normalizing the table data content in the table subpage with the classification mark;

[0092] Step A23, completing the missing items of the table data in the normalized table subpage to form a new table subpage.

[0093] See also Fig. 9 , further, step A3 comprises:

[0094] Step A31, pre-configuring the mapping relationship between the data fields and the data headers in the database table to form a mapping table;

[0095] Step A32, reading the classification identifier, data header and table data content of the table subpage;

[0096] Step A33, matching the corresponding database table according to the classification identifier of the table subpage, and mapping the table data content in the table subpage to the corresponding database table according to the mapping table between the data table header and the data fields in the database table.

[0097] See also Fig.10 , further, step A33 includes:

[0098] Step A331, automatically matching the corresponding database table according to the classification identifier of the table subpage, and automatically mapping the table data content in the table subpage to the corresponding database table according to the mapping table between the data table header and the data field in the database table;

[0099] Step A332, manually match the table data content under the data header in the table subpage that has not been automatically mapped to the database table.

[0100] From the aspects of file preprocessing, data preprocessing, data warehousing, etc., combined with data item standardization processing, the present invention can efficiently realize the preprocessing and warehousing of data files, provide technical support for data systematization and structured storage, and effectively improve the efficiency of data file processing. In the warehousing processing of multi-source heterogeneous source data files, the present invention supports various types of data files in complex formats such as EXCEL, CSV, TXT, PDF, etc., and divides and classifies data files through data file inspection and segmentation, data preprocessing, matching and warehousing and other processing processes, and normalizes and completes the file data content. Finally, the warehousing processing of data files is finally completed by field mapping matching, overcoming the problem that the existing data processing application system needs to manually modify the file corresponding to the data warehousing template, and the fault tolerance is low. It is easy to implement and has a wider range of applications.

[0101] The above are only preferred embodiments of the present invention, and are not intended to limit the implementation methods and protection scope of the present invention. Those skilled in the art should be aware that all solutions obtained by equivalent substitutions and obvious changes made using the description and illustrations of the present invention should be included in the protection scope of the present invention.

Claims

1. A complex data format file processing system, characterized in that: include: A file preprocessing module, used to preprocess the data file to be stored and split it into data blocks, each of which is distributed in an independent table subpage; A data preprocessing module, connected to the file preprocessing module, is used to classify and identify the table subpages and mark them with classification marks, and then preprocess the data content in the table subpages to form new table subpages; The data storage module is connected to the data preprocessing module and is used to map the table data content in the newly formed table subpage to the database table according to a preconfigured mapping table.

2. A complex data format file processing system as claimed in claim 1, characterized in that: The file preprocessing module comprises: A file verification unit, used to verify the data files to be stored, and select the data files to be stored that meet the system processing conditions; A file conversion unit, connected to the file verification unit, used to convert the to-be-stored data file that meets the system processing conditions into a table data file; The file block division unit is connected to the file conversion unit and is used for performing data block processing on the table data file to form data blocks, each of which is distributed in an independent table sub-page.

3. A complex data format file processing system as claimed in claim 1, characterized in that: The data preprocessing module comprises: A data identification unit, used for matching the table subpage with a preset data resource type, and marking the table subpage with a corresponding classification mark according to the matched data resource type; A data normalization unit, connected to the data identification unit, for normalizing the table data content in the table subpage with the classification mark; The data completion unit is connected to the data normalization unit and is used to complete the missing items of the table data in the table subpage after normalization to form a new table subpage.

4. A complex data format file processing system as claimed in claim 1, characterized in that: The data storage module includes: A mapping configuration unit, used to pre-configure the mapping relationship between the data fields and the data table headers in the database table to form a mapping table; A data reading unit, used for reading the classification identification, data header and table data content of the table subpage; A matching storage unit is respectively connected to the data reading unit and the mapping configuration unit, and is used to match the corresponding database table according to the classification identifier of the table sub-page, and map the table data content in the table sub-page to the corresponding database table according to the mapping table between the data table header and the data field in the database table.

5. A complex data format file processing system as claimed in claim 4, characterized in that: The matching storage unit includes: An automatic storage sub-unit, used for automatically matching the corresponding database table according to the classification identifier of the table sub-page, and automatically mapping the table data content in the table sub-page to the corresponding database table according to the mapping table between the data table header and the data field in the database table; The manual storage subunit is connected to the automatic storage subunit and is used to manually match the table data content under the data header in the table subpage that has not completed automatic mapping to the database table.

6. A method for processing files in complex data formats, characterized in that: A complex data format file processing system as claimed in any one of claims 1 to 5, comprising: Step A1, pre-processing the data file to be stored into the database and dividing it into data blocks, each of which is distributed in an independent table sub-page; Step A2, classifying and identifying the table subpage and marking it with a classification mark, and then preprocessing the data content in the table subpage to form a new table subpage; Step A3: Map the newly formed table subpage to the database table according to a pre-configured mapping table.

7. A method for processing a file in a complex data format as claimed in claim 6, characterized in that: The step A1 comprises: Step A11, verifying the data files to be stored, and selecting the data files to be stored that meet the system processing conditions; Step A12, converting the data file to be stored that meets the system processing conditions into a table data file; Step A13, dividing the table data file into data blocks, each of which is distributed in an independent table sub-page.

8. A method for processing a file in a complex data format as claimed in claim 6, characterized in that: The step A2 comprises: Step A21, matching the table subpage with a preset data resource type, and marking the table subpage with a corresponding classification mark according to the matched data resource type; Step A22, normalizing the table data content in the table subpage with the classification mark; Step A23, completing the missing items of the table data in the normalized table subpage to form a new table subpage.

9. A method for processing a file in a complex data format as claimed in claim 6, characterized in that: The step A3 comprises: Step A31, pre-configuring the mapping relationship between the data fields and the data headers in the database table to form a mapping table; Step A32, reading the classification identifier, data header and table data content of the table subpage; Step A33, matching the corresponding database table according to the classification identifier of the table subpage, and mapping the table data content in the table subpage to the corresponding database table according to the mapping table between the data table header and the data field in the database table.

10. A method for processing a file in a complex data format as claimed in claim 9, characterized in that: The step A33 comprises: Step A331, automatically matching the corresponding database table according to the classification identifier of the table subpage, and automatically mapping the table data content in the table subpage to the corresponding database table according to the mapping table between the data table header and the data field in the database table; Step A332: manually match the table data content under the data header in the table subpage that has not been automatically mapped to the database table.

Images