Method for generating traceable data based on integrated spreadsheet data and electronic equipment

Abstract

The invention is suitable for the technical field of data processing, and provides a method for generating traceable data based on integrated spreadsheet data and electronic equipment, and the method comprises the steps: obtaining a data source table of at least one spreadsheet file; determining a target field according to the field of each data source table; creating an integration table; for each data source table, according to the mapping relationship of the target fields, extracting data from the data source table as to-be-imported data, and when the to-be-imported data is extracted, obtaining traceability information corresponding to each row of the to-be-imported data; and respectively importing the to-be-imported data and the corresponding traceability information into corresponding fields of one or more integration tables according to a predetermined storage strategy so as to generate a data system with data traceability. Through the method, the reliability of the integration table can be accurately evaluated.

Description

Methods and electronic devices for generating traceable data based on integrated spreadsheet data Technical Field

[0001] This application belongs to the field of data processing technology, and in particular relates to methods, apparatus, electronic devices, computer-readable storage media, and computer program products for generating traceable data based on integrated spreadsheet data. Background Technology

[0002] In daily data management, the needs for statistical information vary across different periods and departments. Therefore, even data of the same type (such as personnel rosters, fixed asset ledgers, and bank statements) can result in spreadsheets with vastly different header structures (also known as data source tables). To quickly extract valid data from these data source tables, they are typically integrated into one or more consolidated tables to support subsequent data retrieval and report generation. These consolidated tables can be of various types, including database tables, in-memory tables, and standardized Excel summary tables.

[0003] Given that column names, number of columns, and data formats may vary across different data source tables, during the integration process, key fields are often selectively included in the integrated table to avoid bloated table structures and data redundancy, and to balance query performance with most query needs. While this approach optimizes storage and efficiency to some extent, it makes it difficult for users of the integrated table to verify the credibility of the information, thus hindering the assessment of the table's reliability. Furthermore, the lack of a systematic tracing path makes it impossible to directly and quickly locate and reference original records from the data source tables as evidence, exposing the risk of a broken verification chain during compliance checks or external audits. Additionally, because only a portion of key data is selected for import into the integrated table, potentially valuable information contained in the excluded columns is permanently ignored. This information narrowing caused by the integration strategy may prevent subsequent in-depth analysis or sudden data mining needs from being met. Summary of the Invention

[0004] This application provides a method and electronic device for generating traceable data based on integrated spreadsheet data, which can solve problems such as the difficulty in assessing the reliability of existing integrated spreadsheets.

[0005] In a first aspect, embodiments of this application provide a method for generating traceable data based on integrated spreadsheet data, comprising the following steps: obtaining at least one data source table of a spreadsheet file; determining target fields according to the fields of each of the data source tables; creating an integration table, the integration table including a data field corresponding to the target field and / or at least one traceability field for storing traceability information; for each of the data source tables, extracting data as importable data according to the mapping relationship of the target field, and obtaining traceability information corresponding to each row of the importable data when extracting the importable data, the traceability information being used to determine the location of the original data, including at least: the storage path of the spreadsheet file, the name of the data source table, and the row identifier of the importable data in the data source table; importing the importable data and its corresponding traceability information into the corresponding fields of one or more of the integration tables according to a predetermined storage strategy, to generate a data system with data traceability capabilities.

[0006] The beneficial effects of this application embodiment compared with the prior art are as follows: In this application embodiment, after obtaining at least one data source table of a spreadsheet file, the target field is determined according to the fields of each data source table, and data is extracted from each data source table as the data to be imported according to the mapping relationship of the target field. Furthermore, while extracting the data to be imported, the traceability information corresponding to each row of the data to be imported is obtained, and then the data to be imported and its corresponding traceability information are imported into the corresponding fields of the integration table. Since the traceability information includes: the storage path of the spreadsheet file, the name of the data source table, and the row identifier of the data to be imported in the data source table, after the integration table imports the data to be imported and the corresponding traceability information, the user can obtain the traceability information corresponding to the data imported by the integration table, thereby facilitating an accurate assessment of the credibility and reliability of the integration table.

[0007] Secondly, embodiments of this application provide an apparatus for generating traceable data based on integrated spreadsheet data, comprising: a data source table acquisition module for acquiring at least one data source table of a spreadsheet file; a target field filtering module for determining target fields based on fields of each of the data source tables; an integration table creation module for creating an integration table, the integration table including data fields corresponding to the target fields and / or at least one traceability field for storing traceability information; a data to be imported determination module for extracting data from each of the data source tables as data to be imported based on the mapping relationship of the target fields, and obtaining traceability information corresponding to each row of the data to be imported when extracting the data to be imported, the traceability information being used to determine the location of the original data, including at least: the storage path of the spreadsheet file, the name of the data source table, and the row identifier of the data to be imported in the data source table; and a data import module for importing the data to be imported and its corresponding traceability information into the corresponding fields of one or more of the integration tables according to a predetermined storage strategy, to generate a data system with data traceability capabilities.

[0008] Thirdly, embodiments of this application provide an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the method described in the first aspect.

[0009] Fourthly, embodiments of this application provide a computer-readable storage medium storing a computer program that, when executed by a processor, implements the method described in the first aspect.

[0010] Fifthly, embodiments of this application provide a computer program product that, when run on an electronic device, causes the electronic device to perform the method described in the first aspect.

[0011] It is understood that the beneficial effects of the second to fifth aspects mentioned above can be found in the relevant descriptions in the first aspect mentioned above, and will not be repeated here. Attached Figure Description

[0012] To more clearly illustrate the technical solutions in the embodiments of this application, the accompanying drawings used in the description of the embodiments or the prior art will be briefly introduced below.

[0013] Figure 1 is a flowchart illustrating a method for generating traceable data based on integrated spreadsheet data according to an embodiment of this application; Figure 2 is a schematic diagram of an integrated table according to an embodiment of this application; Figure 3 is a schematic diagram of locating a cell in a data source table according to an embodiment of this application; Figure 4 is a schematic diagram of locating a data row in a data source table according to an embodiment of this application; Figure 5 is a structural block diagram of an apparatus for generating traceable data based on integrated spreadsheet data according to another embodiment of this application; Figure 6 is a structural schematic diagram of an electronic device according to an embodiment of this application. Detailed Implementation

[0014] In the following description, specific details such as particular system architectures and techniques are set forth for illustrative purposes and not for limitation, in order to provide a thorough understanding of the embodiments of this application. However, those skilled in the art will understand that this application may also be implemented in other embodiments without these specific details. In other instances, detailed descriptions of well-known systems, apparatuses, circuits, and methods have been omitted so as not to obscure the description of this application with unnecessary detail.

[0015] It should be understood that, when used in this application specification and the appended claims, the term "comprising" indicates the presence of the described features, integrals, steps, operations, elements and / or components, but does not exclude the presence or addition of one or more other features, integrals, steps, operations, elements, components and / or a collection thereof.

[0016] It should also be understood that the term “and / or” as used in this application specification and the appended claims means any combination of one or more of the associated listed items and all possible combinations, and includes such combinations.

[0017] Furthermore, in the description of this application and the appended claims, the terms "first," "second," etc., are used only to distinguish descriptions and should not be construed as indicating or implying relative importance.

[0018] References to "one embodiment" or "some embodiments" in this specification mean that one or more embodiments of this application include a specific feature, structure, or characteristic described in connection with that embodiment. Therefore, the phrases "in one embodiment," "in some embodiments," "in other embodiments," "in still other embodiments," etc., appearing in different parts of this specification do not necessarily refer to the same embodiment, but rather mean "one or more, but not all, embodiments," unless otherwise specifically emphasized.

[0019] In routine data management, it may be necessary to integrate data source tables with vastly different header structures into one or more consolidated tables, allowing users to view data from multiple data source tables through a single consolidated table. However, since the consolidated table is not a data source table itself, users may find it difficult to verify the credibility of the information within it, making it challenging to assess the table's reliability. In scenarios requiring multi-party collaboration or auditing, a lack of clear data lineage can make cross-departmental verification of data meaning difficult and even impact the credibility of decisions.

[0020] To facilitate users in verifying the reliability of information in an integrated table, this application provides a method for generating traceable data based on integrated spreadsheet data. In this method, when data from multiple data source tables is imported into an integrated table, the storage path of the spreadsheet file containing the data, the name of the data source table, and the row identifier of the data in the data source table are also imported into the integrated table, thereby generating an integrated table with data traceability capabilities.

[0021] The method for generating traceable data based on integrated spreadsheet data, provided in the embodiments of this application, is described below with reference to the accompanying drawings.

[0022] Figure 1 shows a flowchart illustrating a method for generating traceable data based on integrated spreadsheet data according to an embodiment of this application. This method can be applied to electronic devices, such as tablets, desktop computers, laptops, mini PCs, etc. The method for generating traceable data based on integrated spreadsheet data according to an embodiment of this application is described in detail below: S11, obtain a data source table of at least one spreadsheet file.

[0023] The spreadsheet file described above is a digital file that stores, organizes, and processes data in a grid format consisting of rows and columns. A single spreadsheet file may include one or more data source tables.

[0024] The data source table mentioned above can contain information such as bank statements (or bank reconciliation statements), employee rosters, and fixed asset ledgers. Considering that multiple user needs, including fraud prevention (abnormal transaction alerts, money laundering pattern identification, and fraudulent activity monitoring), post-fraud tracing of fund flows and relationship analysis, risk control (credit assessment, abnormal transaction monitoring, and assessment of fund chain stability), internal management (financial auditing, tax compliance checks, and supplier and employee behavior monitoring), and customer insights (consumption habit analysis, income and funding needs forecasting, and customer segmentation), are typically related to bank statements, and since bank statements (or bank reconciliation statements) downloaded from mobile banking applications (APPs) or bank websites are usually provided in spreadsheet format, and the header structures of bank statements provided by different banking systems vary, a data source table containing bank statements can be selected as the data source table in this application embodiment. Further, in-depth information about the bank statements will be mined from multiple dimensions to serve user needs based on this in-depth information. In this application embodiment, by integrating the data source table and providing traceability functionality, multiple user needs can be met, demonstrating high practical value.

[0025] In this embodiment, the acquired data source tables are tables storing raw, unprocessed data, which are the data tables to be integrated later. Optionally, the data types recorded in each data source table are the same to improve the success rate of subsequent integration. For example, when integrating bank statement data, the data types recorded in each acquired data source table are all bank statement data.

[0026] Optionally, considering that spreadsheet files containing data source tables may have duplicate names, in order to clearly distinguish the files to which each data source table belongs and to meet the requirement of unique file names within the same folder, after obtaining a spreadsheet file containing at least one data source table, the method further includes: renaming the spreadsheet file containing the data source table according to preset rules, so as to achieve clear distinction between spreadsheet files to which different data source tables belong.

[0027] In this embodiment, after renaming the spreadsheet file containing the data source table according to a preset rule, the names of any two spreadsheet files containing the data source table will not be duplicated. For example, if the data source table records bank statements, the preset rule of "account owner's name - bank name" can be used to rename the corresponding spreadsheet file; when the account owner's name is "Sun Wukong" and the bank name is "Industrial and Commercial Bank of China", the spreadsheet file can be named "Sun Wukong - Industrial and Commercial Bank of China".

[0028] Optionally, if a user holds multiple bank cards from the same bank, the bank statements provided by that bank will include multiple bank statements for that user, each typically corresponding to one bank card. If the default naming rule of "Account Owner Name - Bank Name" is still used, it will result in duplicate names for multiple spreadsheet files corresponding to that user at that bank. To address this scenario where the same bank provides multiple bank statements for the same entity, the default rule of "Account Owner Name - Bank Name" can be combined with a distinguishing rule (which may include adding an additional serial number or information such as bank card type) to rename the corresponding spreadsheet files. This ensures that the renamed spreadsheet file names are unique. For example, if the account owner name is "Sun Wukong" and the bank name is "Industrial and Commercial Bank of China" (ICBC), and ICBC provides two bank statements for Sun Wukong, then the spreadsheet file corresponding to one bank statement can be renamed "Sun Wukong - ICBC 1," and the spreadsheet file corresponding to the other bank statement can be renamed "Sun Wukong - ICBC 2."

[0029] Renaming spreadsheet files according to the above rules helps ensure the uniqueness of each spreadsheet file name, thereby reducing the probability of conflicts in subsequent processing.

[0030] In some embodiments, after obtaining the spreadsheet file containing at least one data source table, or after renaming each spreadsheet file containing a data source table according to a preset rule, the method further includes: storing all the spreadsheet files containing data source tables that need to be integrated into a specified directory folder.

[0031] In this embodiment, the number of specified directory folders can be one or more. Considering that storing all data source tables in the same directory folder facilitates the subsequent program's automatic reading of each data source table from this fixed directory folder, and also minimizes complexity and program development difficulty, it is preferable to store all data source tables in the same specified directory folder. For example, when the data source table records bank statements, all spreadsheet files containing data source tables that need to be integrated can be stored together in a folder named "All Bank Statements".

[0032] Furthermore, since all data source tables are completely and continuously stored in this directory folder, traceable consistency between the original data and the subsequently integrated tables can be ensured, laying the foundation for data integrity, auditability, and long-term maintainability.

[0033] S12, determine the target field based on the fields of each of the above data source tables.

[0034] In this embodiment of the application, the fields of each data source table are analyzed, such as analyzing the field name and / or the data type of the field, etc., and the target field is determined based on the analysis results.

[0035] In this embodiment, fields with the same field names in the data source tables can be used as target fields. These same field names can also be directly used as the field names of the corresponding data fields in the integration table. Alternatively, these same field names can be processed before determining the corresponding data field names in the integration table. For example, assuming the same field name indicating the account holder's name is "Account Name," then "Account Name" can be processed to "Our Account Name," and then used as the field name of the corresponding data field in the integration table. That is, determining the target field based on the fields of each of the aforementioned data source tables includes: A1. Identifying common fields that exist in all data source tables and determining these common fields as target fields.

[0036] A2, and / or, based on preset field selection rules, filter out the target fields from the fields of each data source table.

[0037] The aforementioned common fields include fields with the same name, as well as fields with different names but the same actual meaning. For example, for a data source table recording bank transactions, fields necessary for basic operations such as fund flow analysis, reconciliation, and balance inquiry are required. These include fields such as "Account Ownership Subject" name, account number, transaction time, transaction amount, balance, and "Counterparty Subject" information. When fields recording this information in different data source tables have the same name or the same actual meaning, these fields are common fields across all data source tables.

[0038] In this embodiment of the application, all common fields can be used as the target fields, or only some common fields can be used as the target fields. For example, the data fields corresponding to the target fields in the integrated table determined by the common fields in the data source table that records bank statements can be shown in Table 1 below: Table 1:

[0039] Since the target field is determined based on the common fields in the data source table, and the data corresponding to the common fields are usually "repeated" elements generated by system-level, business-related, or audit requirements, that is, the data corresponding to the common fields is usually the key data in the data source table, after importing the data corresponding to the common fields in the data source table into the corresponding data fields of the integration table, the integration table contains the key data of each data source table, which helps users to quickly extract the key data of each data source table from the integration table.

[0040] In this embodiment of the application, the preset field selection rules include data type rules. When the target field is selected according to the preset field selection rules, it is identified whether there are one or more fields in each data source table whose data type conforms to the preset data type rules. If so, the field that conforms to the preset data type rules can be determined as the target field.

[0041] In this embodiment, considering that data source tables may contain fields with the same name but different data types, and these fields may be of different types, the target field can also be determined based on the field name and data type to improve the accuracy of the determined target field. That is, determining the target field based on the fields of each data source table includes: identifying common fields present in all data source tables; identifying whether the data type of the common field matches a preset data type rule; and if so, determining the target field based on the common field whose data type matches the preset data type rule.

[0042] The above-mentioned preset data type rule can be: the data type of the field is a specified data type.

[0043] In this embodiment, the system analyzes whether there are common fields in the fields of each data source table. If so, the common fields in these data source tables are identified. The system then analyzes whether the data types corresponding to these common fields are the same or the same specified data type, and all or some of the common fields with the same data type can be used as the target fields. Since determining the target field considers not only the field name but also the data type, and when two fields are identical, their field names and data types are usually the same, determining the target field based on the field name and data type helps improve the accuracy of the obtained target fields.

[0044] In the above description, the target field is determined based on the common fields. In some embodiments, the target field can also be determined based on the non-common fields. That is, the above determination of the target field based on the fields of each data source table includes: B1, identifying the non-common fields in all data source tables.

[0045] B2. Determine the target field based on the above non-public fields.

[0046] In this embodiment, different data source tables may contain not only common fields but also non-common fields. For example, when the data source table is an electronic spreadsheet file containing bank statements (or bank statements) downloaded from a mobile banking app or bank's official website, the header structure (i.e., the set of fields) of the data source tables containing bank statements provided by different banking systems may differ: some banking systems may provide data source tables containing fields corresponding to a summary and remarks, while others may provide fields such as transaction description channel, branch name, and counterparty's bank. Although these fields are not common fields contained in various data source tables, they contain additional information about bank transactions. Therefore, if needed, users can use all or some of the non-common fields as target fields. In this case, the field name of the data field corresponding to the target field in the integrated table is the same as the field name of the non-common field. Alternatively, after adjusting the field name of the non-public field, the adjusted field name can be used as the field name of the data field corresponding to the target field in the integration table, and the field value of the non-public field in the data source table can be used as the field value of the data field corresponding to the target field in the integration table. If a data source table does not have this non-public field, a preset value (such as null, an empty string, etc.) can be used to fill the data field corresponding to the target field in the integration table. When determining the target field based on the non-public field, the user can select the non-public field to be used as the target field through the settings page of the integration table provided by the electronic device. The electronic device then determines the field name of the data field corresponding to the target field in the integration table based on the field name of the non-public field selected by the user. In this way, the data of the non-public field selected by the user can be imported into the data field corresponding to the target field in the integration table. Of course, the target field can also not be determined based on the non-public field, that is, the data corresponding to the non-public field is not imported into the data field corresponding to the target field in the integration table. This is to control the number of fields in the integration table and avoid the table structure of the integration table becoming too bloated, so as to comply with the "single responsibility" and "high cohesion" principles of database design.

[0047] It should be noted that in practice, the target field can be determined based on common fields, non-common fields, or a combination of both. This will not be elaborated further here.

[0048] S13, Create an integration table, which includes a data field corresponding to the target field and / or at least one traceability field for storing traceability information.

[0049] The aforementioned integrated table is an intermediate layer table situated between the original data source and the data application.

[0050] The above-mentioned traceability fields are fields not included in the data source table. The traceability information of a certain row stored in the traceability fields of the integrated table is the traceability information corresponding to the data in that row.

[0051] In this embodiment of the application, the number of fields in the data field corresponding to the target field is greater than or equal to 1, and the number of fields in the tracing field can also be greater than or equal to 1.

[0052] In this embodiment of the application, the number of integrated tables is greater than or equal to 1. When the number of integrated tables is greater than 1, the types of these integrated tables may include: pure business tables, which only have data fields; pure traceability tables, which only have traceability fields; and hybrid tables, which include both data fields and traceability fields.

[0053] It should be noted that, in addition to the data fields that must be included in the target field and / or the traceability field, the integrated table in this application embodiment may also include other auxiliary fields that are conventionally added by those skilled in the art to achieve data management, such as auto-incrementing primary keys (ID), globally unique identifiers (GUID), data creation timestamps, etc. The existence of these auxiliary fields does not affect the implementation of the core traceability function in this application embodiment.

[0054] S14. For each of the above data source tables, data is extracted from them as data to be imported according to the mapping relationship of the target fields. When extracting the data to be imported, the source information corresponding to each row of the data to be imported is obtained. The source information is used to determine the location of the original data and includes at least: the storage path of the spreadsheet file, the name of the above data source table, and the row identifier of the data to be imported in the above data source table.

[0055] The mapping relationship of the target field mentioned above includes the mapping relationship between the field identifier of the target field and the data fields of the integrated table. The field identifier of the target field may include the field name, or it may include the field number, etc. Furthermore, the mapping relationship of the target field may also include the mapping relationship between the field identifier of the target field and the data under that target field.

[0056] Optionally, the above-mentioned extraction of data from the target field based on the mapping relationship of the target field as the data to be imported includes: establishing a mapping relationship between the fields of the data source table and the data fields of the integration table; and extracting data of the corresponding fields from the data source table based on the mapping relationship as the data to be imported.

[0057] In this embodiment, a mapping relationship is established between the fields of the data source table and the data fields of the integration table based on the correspondence between the data fields and the target fields of the integration table when the integration table is created. Based on this mapping relationship, the data corresponding to the target field is found, and the found data is used as the data to be imported into the data field of the integration table corresponding to the target field (i.e., the data to be imported). For example, assuming that the field named "Household Name" in the data source table is the target field, and the row of data stored under "Household Name" is "Diao Chan", then "Diao Chan" is used as the data to be imported into the data field of the integration table corresponding to "Household Name".

[0058] It should be noted that the core essence of the step "obtaining traceability information corresponding to each row of the data to be imported during the extraction of the data to be imported" in this invention lies in determining and binding a set of traceability information specific to each row of the data to be imported, so as to ensure the accuracy of traceability. Those skilled in the art should understand that this statement emphasizes the logic of establishing a precise, one-to-one correspondence between the traceability information and the data to be imported. As long as the acquisition of the traceability information is specifically performed for that row of the data to be imported and is used to establish a direct association with that row of data, regardless of whether the acquisition operation is precisely synchronized with the data reading in time, the technical essence required by "acquiring during extraction" has been achieved.

[0059] In this embodiment, the storage path of the spreadsheet file refers to the complete folder hierarchy sequence from the root directory of the storage device to the location of the spreadsheet file, used to accurately locate the spreadsheet file. For example, if the spreadsheet file "Diaochan-ICBC.xlsx" is stored in the "All Bank Transactions" folder on drive C, then the storage path of the spreadsheet file is "C:\All Bank Transactions\Diaochan-ICBC.xlsx".

[0060] The name of the data source table mentioned above refers to the worksheet name corresponding to that data source table within its respective spreadsheet file. For example, if the worksheet containing the data to be imported is named "Sheet1" and is located in the file "C:\All Bank Transactions\Diaochan-ICBC.xlsx", then the name of the data source table corresponding to the data to be imported is "Sheet1". It should be noted that a spreadsheet file may contain multiple worksheets. When a spreadsheet file can contain a maximum of 255 worksheets, then that spreadsheet file may contain 255 worksheets, and each worksheet has a unique name.

[0061] The row identifier of the data to be imported in the data source table includes the row number of the data to be imported in the data source table.

[0062] In this embodiment, for each data source table, the corresponding data is extracted as the data to be imported based on the mapping relationship of the target field. For example, a single thread can be used to extract the data to be imported row by row, and obtain the traceability information corresponding to the data to be imported. Alternatively, multiple threads can be started, with each thread extracting one row of data to be imported and the traceability information corresponding to the data to be imported. For data to be imported from the same data source table, the storage path of the obtained spreadsheet file and the name of the data source table are the same, but the row identifiers of the obtained data to be imported are different. For data to be imported from different data source tables, the names of the obtained data source tables are different, but the storage path of the obtained spreadsheet file and the row identifiers of the data to be imported may be the same.

[0063] In this embodiment of the application, the traceability information includes: the storage path of the spreadsheet file, the name of the data source table, and the row identifier of the data to be imported in the data source table. Optionally, the row identifier may include an identifier indicating the entire data row, or it may include an identifier indicating a cell in a data row.

[0064] S15, according to the predetermined storage strategy, import the above-mentioned data to be imported and its corresponding traceability information into the corresponding fields of one or more of the above-mentioned integrated tables to generate a data system with data traceability capability.

[0065] In this embodiment, considering that the data source table may contain dirty data, such as missing values, duplicate values, outliers (e.g., a person's age is 350 years old, exceeding the normal lifespan), and logical errors (e.g., a birth date of 2025 with an age of 20), the data to be imported from the data source table can be preprocessed as needed before importing it into the integration table. That is, before importing the data to be imported into the corresponding data fields of the integration table, the process further includes: data cleaning and / or validation and / or format standardization of the extracted data to be imported.

[0066] Data cleaning refers to the process of identifying and correcting errors, incompleteness, inaccuracies, or irrelevant parts of data, with the aim of transforming "dirty data" into "clean, consistent, and usable data." For example, in the data cleaning process, duplicate orders detected by data validation are deleted.

[0067] Data validation refers to the process of checking whether data conforms to predefined rules, standards, or constraints, with the aim of ensuring the accuracy, consistency, integrity, and legality of the data.

[0068] Format standardization refers to the process of changing data from one format, structure, or representation to another, with the aim of making the data more suitable for the target system or analytical needs. For example, in the process of format standardization, order dates are converted from "MM / DD" to "YYYY-MM-DD".

[0069] Since data cleaning and / or validation and / or format standardization processes can improve the accuracy of the processed data, performing at least one of these processes on the data to be imported before importing it into the integrated table can improve the accuracy of the data in the integrated table.

[0070] Optionally, to improve the accuracy of the data obtained after processing the imported data, corresponding prompts can be output during the processing. For example, during verification, error prompts can be output; during cleaning, prompts can be output regarding errors requiring cleaning, and the cleaning action can only be performed after receiving confirmation from the user; during format standardization, prompts can be output regarding errors requiring format standardization, and the format standardization action can only be performed after receiving confirmation from the user. Because corresponding prompts are output before data cleaning and / or correction and / or format standardization, and the corresponding actions are only performed after confirming that the user needs to perform cleaning and / or correction and / or format standardization actions, the processed actions are matched to the user's needs, thereby improving the accuracy of the data obtained after processing.

[0071] In this embodiment of the application, when there is only one integration table, this integration table is used to centrally store target field data and their corresponding traceability information from all data source tables. Specifically, in the integration table, target field data and traceability information are stored in correspondence. For example, in the same row of the integration table, target field data and traceability information corresponding to the target field data are stored. Here, the target field data is the data corresponding to the target field. Figure 2 shows a schematic diagram of an integration table. In the integration table of Figure 2, the relevant data to be imported (such as the account "50808510953556497" of "Sun Wukong", the transaction time of the transaction with the account, the counterparty account, etc.) is imported into the integration table, and the traceability information of the data to be imported is also imported into the corresponding row of the traceability field. It should be noted that in Figure 2, the field name of the traceability field is "data traceability". In actual practice, the field name of the traceability field can also be other names, which are not limited here.

[0072] In this embodiment of the application, when the number of traceability fields in the integration table is one, the above storage strategy is as follows: import the data to be imported into the corresponding data field, and combine the storage path, the name of the data source table and the row identifier into a parsable string, which is stored in the unique traceability field of the integration table.

[0073] For example, when the data to be imported comes from the 9th row of the "Sheet1" worksheet of the spreadsheet file "Diaochan-ICBC.xlsx" in the "All Bank Transactions" folder on drive C, a parsable string combining the storage path of the spreadsheet file, the name of the data source table, and the row identifier can be: "C:\All Bank Transactions\Diaochan-ICBC.xlsx#Sheet1!9:9". This parsable string can then be imported into the source field of the integrated table to the data row corresponding to the data to be imported.

[0074] In this embodiment of the application, when there are multiple traceability fields, the above storage strategy is as follows: import the data to be imported into the corresponding data field, and combine at least two of the above storage path, the name of the above data source table, and the above row identifier, and store the parsable string and / or the uncombined information items in multiple traceability fields respectively.

[0075] In this context, the storage path, the name of the data source table, and the row identifier are each a different information item. If the storage path and the name of the data source table are combined, the uncombined information item is the row identifier.

[0076] In this embodiment of the application, at least two of the storage path, the name of the data source table, and the row identifier are combined, and a parsable string is generated based on the combination result. Optionally, the parsable string obtained by combination is formed in one of the following ways: (1) by directly concatenating at least two of the above storage path, the name of the above data source table, and the above row identifier.

[0077] (2) First, split at least one of the above storage path, the name of the above data source table and the above row identifier into multiple sub-information, and then connect at least one of the above sub-information with other information items.

[0078] In this embodiment, direct concatenation refers to combining two pieces of information into a single string without any intermediate conversion, according to predetermined combination rules. These combination rules include, but are not limited to, directly concatenating the two pieces of information sequentially, or concatenating them by inserting predetermined separators (such as "@", "#"). For example, assuming the storage path and the name of the data source table are combined, and the storage path is "C:\All Bank Transactions" and the data source table is worksheet "Sheet1" in the spreadsheet file "Diaochan-ICBC.xlsx", then the parsable string obtained by this combination could be "C:\All Bank Transactions\Diaochan-ICBC.xlsx#Sheet1".

[0079] In this embodiment, sub-information refers to information obtained by splitting information items. For example, the storage path "C:\All Bank Transactions" can be split into two sub-information items: "C:\" and "All Bank Transactions". Of course, in actual practice, it can also be split into other forms of sub-information, which is not limited here. It should be noted that when not all information items in the combination are split into multiple sub-information items, the remaining information items are the "other information items" mentioned above.

[0080] In the above description, the combined information items can be broken down into multiple sub-items. In practice, uncombined information items can also be broken down into multiple sub-items, which is not limited here.

[0081] In this embodiment of the application, each of the above-mentioned information items can be plaintext or ciphertext obtained by converting plaintext. The conversion process includes encoding, encryption, hashing, format rearrangement, or preset splitting rules, and these conversion processes have corresponding reverse rules that can restore or derive the corresponding plaintext from the ciphertext.

[0082] Since the traceability information includes the storage path of the spreadsheet file, the name of the data source table, and the row identifier of the data to be imported in the aforementioned data source table, after the integrated table imports the data to be imported and the corresponding traceability information, the user can obtain the traceability information corresponding to the data imported by the integrated table from the integrated table, which is conducive to accurately assessing the credibility and reliability of the integrated table.

[0083] The above description introduced how to import the data to be imported and its corresponding traceability information into a single integrated table according to a predetermined storage strategy. The following will introduce how to import the data to be imported and its corresponding traceability information into multiple integrated tables (such as more than 1) according to a predetermined storage strategy.

[0084] In this embodiment, when there are multiple integration tables, different integration tables are used to classify and store target field data and / or corresponding traceability information from all the aforementioned data source tables. At least one integration table contains a traceability field for storing traceability information. Multiple integration tables are interconnected through predefined relationships, enabling the retrieval of data stored in different integration tables and the reconstruction of complete traceability information by executing a joint query based on these relationships.

[0085] The aforementioned categorized storage includes classifying target field data and their corresponding traceability information according to business themes or data characteristics, and then storing them in the corresponding integration tables. It should be noted that, to save storage space, different integration tables can be configured to store different target field data. The predefined relationships mentioned above refer to the relationships between database tables, also known as relationship types or cardinality relationships. There are four main classic types: one-to-one, one-to-many, many-to-many, and self-referencing relationships. These relationships define how tables are connected to each other through foreign keys and are the cornerstone of relational database design.

[0086] When there is a one-to-one relationship between the consolidated tables, it means that for every record in one consolidated table (let's say table A), there is at most one corresponding record in another consolidated table (let's say table B), and vice versa. Specifically, this can be achieved by creating a foreign key in either consolidated table. This foreign key must be unique (and is usually also set as a primary key).

[0087] When the relationship between the integrated tables is one-to-many, it means that one record in table A can correspond to multiple records in table B; however, one record in table B can only correspond to one record in table A.

[0088] When the relationship between the integrated tables is many-to-many, it means that one record in table A can correspond to multiple records in table B; at the same time, one record in table B can also correspond to multiple records in table A.

[0089] When there is a self-referencing relationship between tables, it indicates that a field in table A references another field (usually the primary key) in table A. It describes the relationship between records within a single table.

[0090] In this embodiment of the application, when there are multiple integration tables but only one traceability field, the above storage strategy is as follows: import the data to be imported into the data field corresponding to the integration table, and combine the storage path, the name of the data source table and the row identifier into a parsable string, which is then stored in the unique traceability field.

[0091] In this embodiment of the application, when the number of integrated tables is greater than 1, but each integrated table has only one traceability field, for any integrated table, after importing data into the integrated table, the traceability information corresponding to the imported data is also imported into the traceability field of the integrated table.

[0092] When there are multiple integration tables and multiple traceability fields, the above storage strategy is as follows: import the data to be imported into the corresponding data field of the integration table, and combine at least two of the storage path, the name of the above data source table, and the above row identifier, and store the parsable string and / or uncombined information items in multiple of the above traceability fields respectively.

[0093] The generation of the parsable string obtained by the above combination can refer to the generation method when the integration table is a single table, which will not be repeated here.

[0094] Since the number of integration tables is greater than 1, the corresponding number of integration tables can be selected according to actual needs, thereby improving the flexibility of the generated integration tables.

[0095] In this embodiment of the application, after generating an integrated table with data traceability capabilities, the data in the integrated table can be output to a Word document, a PDF document, a webpage in a browser, or a data table in a newly created file, so that users can perform corresponding applications based on the Word document, PDF document, webpage in a browser, or data table.

[0096] That is, after generating the data system with data traceability capabilities as described above, it may also include: responding to a data query request by querying the data system to obtain a subset of data that meets the query conditions.

[0097] The aforementioned data subset is exported to a new spreadsheet file to form a new traceable data table; wherein the new traceable data table contains the aforementioned data subset and maintains its traceable association with the original traceability information.

[0098] In this embodiment of the application, the data query request described above can be written using Structured Query Language (SQL) and submitted to the database for execution. Optionally, the data query request includes request identification information (such as the timestamp of the request initiation) and the query subject (such as a data entity). In addition, the data query request may also include query conditions, which will not be elaborated here.

[0099] In this embodiment, the electronic device retrieves the data corresponding to the data query request and the traceability information from the integrated table based on the received data query request, thus obtaining a data subset. This data subset refers to a collection of data that meets the specific requirements indicated by the data query request, obtained by performing data operations on the integrated table. It can be in the form of a single data table, a data view, or a dataset.

[0100] For example, suppose a query request indicates that the data in the first cell (i.e., the first row and first column) of an integrated table is requested. Then, a data subset is determined based on the data in that first cell and the corresponding traceability information. Subsequently, the data in this subset, along with the traceability information, is exported to a newly created data table. Because the newly created data table stores the traceability information of the data, it is a traceable data table.

[0101] In some embodiments, considering that hyperlinks facilitate users' quick data tracing, corresponding hyperlinks can be generated based on the tracing information. That is, after exporting the aforementioned data subset to a new spreadsheet file, the method further includes: generating hyperlinks that can directly locate the corresponding data position in the original spreadsheet file based on the tracing information in the aforementioned data subset; associating the hyperlinks with the corresponding data in the new traceable data table, so that users can directly locate and access the original data source by manipulating the hyperlinks.

[0102] In this embodiment, the hyperlinks are generated based on traceability information. Specifically, the traceability information corresponding to a piece of data can be combined into a string, and then a hyperlink can be generated based on that string. Alternatively, the traceability information corresponding to a piece of data can be split into multiple strings, and then a corresponding number of hyperlinks can be generated based on each of those multiple strings. That is, in this embodiment, the number of hyperlinks is greater than or equal to 1.

[0103] In this embodiment, a clickable hyperlink is generated based on the string corresponding to the traceability information, which can directly locate the corresponding position in the original spreadsheet file. This clickable hyperlink is then associated with the corresponding data in the data table of the newly created spreadsheet file. The corresponding data in the data table can be the data corresponding to any field in that data table. For example, assuming that row numbers are represented by numbers (e.g., 1, 2, 3...) and column numbers are represented by letters (e.g., A, B, C...), and if the row identifier in the traceability information corresponding to the data to be imported indicates the identifier of the cell corresponding to column "A" in the 5th data row, such as "C:\All Bank Transactions\Zhubajie-ICBC.xlsx#Sheet1!A5", then the generated hyperlink could be " C:\All Bank Statements\ Pigsy -ICBC.xlsx#Sheet1!A5Alternatively, if the row identifier in the traceability information corresponding to the data to be imported is an identifier indicating the 5th data row, such as "C:\All Bank Transactions\Zhubajie-ICBC.xlsx#Sheet1! 5:5", then the generated hyperlink can be " C:\All Bank Statements\Zhubajie-ICBC.xlsx#Sheet1! 5: 5 After generating the hyperlink, it is associated with the corresponding data in the data table of the newly created spreadsheet file. Specifically, if the data table in the new spreadsheet file includes data columns A, B, and C, and the data to be imported is in row 3 of the data table, then the hyperlink can be associated with the cell data in row 3 of data column A. In this case, when the user clicks the hyperlink on that cell data in row 3, they will access the… C:\All Bank Statements\ Zhu Bajie - ICBC.xlsx#Sheet1!A5 ",or" C:\All Bank Statements\Zhubajie-ICBC.xlsx#Sheet1! 5:5 When accessing " C:\All Bank Statements\Zhubajie-ICBC.xlsx#Sheet1!A5 When “”, the resulting schematic diagram is shown in Figure 3, and when “” is accessed C:\All Bank Statements\Zhubajie-ICBC.xlsx#Sheet1! 5:5 The resulting diagram is shown in Figure 4. Because users can quickly locate the data source table corresponding to the data based on the hyperlinks associated with the data in the newly created spreadsheet file, it helps users quickly and accurately determine the reliability of the data. When it is necessary to cite original data to support report conclusions, the original records in the data source table can be directly and quickly located and cited as evidence. Furthermore, users can view potentially valuable information contained in data columns that were not included, thus meeting the needs of in-depth data mining.

[0104] Of course, the hyperlink can also be associated with the corresponding cell data in data column B (or data column C) and / or any data column (such as data column Q). In addition, if a source field is added to the data table in the newly created spreadsheet file, the hyperlink can also be directly stored in the source field.

[0105] Optionally, considering that users are more accustomed to understanding the meaning through text, the value of the aforementioned "tracing source" field can be modified to a target word. This target word reflects the meaning of tracing source and can be words such as "tracing source," "backtracking," or "tracing back." The hyperlink can then be associated with the target word (e.g., "tracing source"). In this way, users can access the corresponding hyperlink by clicking the target word. Since the words users view in the data table of the newly created spreadsheet file are the target words, and these target words contain tracing source information, users can understand their meaning more quickly after viewing the target word. Therefore, when users need to trace the source of data, they can quickly trace the corresponding data based on the target word.

[0106] It should be understood that the sequence number of each step in the above embodiments does not imply the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of this application.

[0107] The following examples illustrate how to create "integrated tables" based on different storage strategies to build a data system with complete data traceability capabilities. These examples collectively demonstrate that the concept of "integrated tables" can cover multiple types: pure business tables (containing only data fields), pure traceability tables (containing only traceability fields), and hybrid tables (containing both).

[0108] In typical application scenarios of bank transaction data integration, the spreadsheet files to be integrated usually contain the following core business fields: Account Name, Account Number, Transaction Time, Outgoing Amount, Incoming Amount, Balance, Counterparty Account Number, and Counterparty Account Name. To achieve accurate traceability, the traceability information obtained for each row of data should at least include: the storage path of the source file (FilePath), the name of the data source worksheet (SheetName), and the row identifier (RowNumber) of that row in the source worksheet.

[0109] (I) Single-table centralized storage (hybrid table, single-field traceability) This embodiment demonstrates that all business data and traceability information are centrally stored in a single integrated table, and the three traceability information items are combined and stored in a single traceability field.

[0110] 1. Table structure creation: Create an integrated table named ConsolidatedAll, which is a typical hybrid table.

[0111] string createTableSql = @"CREATE TABLE ConsolidatedAll (ID INT IDENTITY PRIMARY KEY,-- Auxiliary technical fields: auto-incrementing primary key local account name NVARCHAR(100), local account number NVARCHAR(50), transaction time DATETIME, outgoing amount DECIMAL(18,2), incoming amount DECIMAL(18,2), balance DECIMAL(18,2), counterparty account number NVARCHAR(50), counterparty account name NVARCHAR(100), TraceInfo NVARCHAR(MAX)-- Single traceability field)"; 2. Traceability information combination and storage: During the data import process, the three traceability information items are combined into a parsable string according to predetermined rules and then stored as a whole.

[0112] / / Example of combination method: Construct a pointing string using a specific delimiter string traceString = $"{src.Path}#{src.Sheet}!A{row.RowNumber()}"; / / Example value stored in the TraceInfo field: C:\All Bank Transactions\Diaochan-ICBC.xlsx#Sheet1!A93. Query operation: When querying, you can directly obtain the complete business record and its corresponding combined trace source string.

[0113] SELECT LocalAccountName, LocalAccountNumber, TransactionTime, TraceInfo FROM ConsolidatedAllWHERE LocalAccountNumber = 'TargetAccount'; -- In the result set, the TraceInfo field can be directly used to parse and locate the original data source.

[0114] (II) Single-table centralized storage (hybrid table, multi-field traceability) This embodiment also uses a single table, but stores the three traceability information in independent fields to achieve a clearer structure.

[0115] 1. Table structure creation: string createTableSql = @"CREATE TABLE ConsolidatedAll_SeparateTrace (ID INT IDENTITY PRIMARY KEY,-- Business fields are the same as above, omitted...FilePath NVARCHAR(500),-- Source field: storage path SheetName NVARCHAR(100),-- Source field: worksheet name RowNumber INT-- Source field: row identifier)"; 2. Source information storage: When importing data, the original, uncombined source information items are directly stored in the corresponding fields.

[0116] Query operation: The query results are presented in a flat structure, making business data and various traceability information clear at a glance.

[0117] SELECT LocalAccountName, TransactionTime, FilePath, SheetName, RowNumberFROM ConsolidatedAll_SeparateTraceWHERE OutgoingAmount>10000; -- The result set contains both business data and separately stored complete traceability information.

[0118] (III) Multi-table relational storage (classified by business field, one-to-many relationship) In this embodiment, data is classified and stored in multiple integrated tables according to business logic, and one-to-many relationship is established through business keys to reflect the distributed storage architecture.

[0119] 1. Table structure creation: Create two hybrid tables to store basic account information and transaction details, respectively.

[0120] / / Account Information Table (AccountInfo) string createTable1Sql = @"CREATE TABLE AccountInfo (RecordID INT IDENTITY PRIMARY KEY, Local Account Name NVARCHAR(100), Local Account Number NVARCHAR(50) UNIQUE,-- Business Primary Key, used to associate with Trace_Account NVARCHAR(500)-- The trace field of this table)"; / / Transaction Information Table (TransactionInfo), associated with AccountInfo through 'Local Account' string createTable2Sql = @"CREATE TABLE TransactionInfo (RecordID INT IDENTITY PRIMARY KEY, Local Account Number NVARCHAR(50),-- Foreign Key Transaction Time DATETIME, Outgoing Amount DECIMAL(18,2), Incoming Amount DECIMAL(18,2), Balance DECIMAL(18,2), Counterparty Account Number NVARCHAR(50), Counterparty Account Name NVARCHAR(100), Trace_Transaction NVARCHAR(500), -- This table's traceability field FOREIGN KEY (this party's account) REFERENCES AccountInfo (this party's account)); 2. Data import and query: Data is imported into different tables according to categories. Through joint queries, complete business records and scattered traceability information can be reconstructed.

[0121] -- Related query to reorganize data and traceability information SELECT a.AccountName, a.AccountName, t.TransactionTime, t.AmountPaid, a.Trace_Account, t.Trace_TransFROM AccountInfo aINNER JOIN TransactionInfo t ON a.AccountName = t.AccountName WHERE a.AccountName = 'Target Account'; -- The result set contains business fields from two tables, as well as some traceability information related to each table.

[0122] (iv) Multi-table relational storage (one-to-one precise association based on shared key) This embodiment demonstrates a more rigorous one-to-one multi-table relational scheme based on shared globally unique identifiers (GUIDs), which is suitable for row-level precise traceability.

[0123] 1. Table structure creation: The two tables share the same LinkID as the primary key, forming a mandatory one-to-one relationship.

[0124] CREATE TABLE AccountInfo_Exact (LinkID UNIQUEIDENTIFIER PRIMARY KEY,-- Shared primary key Local account name NVARCHAR(100), Local account number NVARCHAR(50), Trace_Account NVARCHAR(500)); CREATE TABLE TransactionInfo_Exact (LinkID UNIQUEIDENTIFIER PRIMARY KEY,-- Shared primary key and foreign key Transaction time DATETIME,-- ... Other transaction fields...Trace_Trans NVARCHAR(500),FOREIGN KEY (LinkID) REFERENCES AccountInfo_Exact(LinkID) ON DELETECASCADE); 2. Data import: Generate a unique LinkID for each row of source data, and insert it into two tables based on this.

[0125] Guid linkId = Guid.NewGuid(); / / Insert into the account table and transaction table respectively, using the same linkId. string insertAccountSql = @"INSERT INTO AccountInfo_Exact (LinkID, Local Account Name, Trace_Account) VALUES (@LinkID, @Name, @TraceAcc)"; string insertTransSql = @"INSERT INTO TransactionInfo_Exact (LinkID, Transaction Time, Trace_Trans) VALUES (@LinkID, @Time, @TraceTrans)"; Query operation: Perform precise correlation queries through LinkID to ensure that the reorganized data strictly comes from the same row source data.

[0126] SELECT a.*, t.* FROM AccountInfo_Exact a INNER JOIN TransactionInfo_Exact t ON a.LinkID = t.LinkID WHERE a.本方账号 LIKE '%8888%'; (5) Completely separated storage of business and traceability. In this embodiment, all traceability information is centrally stored in an independent and purely traceable integration table, separated from the pure business integration table, which is the most thorough decoupling architecture.

[0127] 1. Table structure creation: Create business data tables and traceability information tables respectively.

[0128] / / Pure business table (BizData) string createBizTableSql = @"CREATE TABLE BizData (DataID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(), -- Association key本方户名 NVARCHAR(100),本方账号 NVARCHAR(50),交易时间 DATETIME,出账金额 DECIMAL(18,2)--... Other business fields, without any traceability fields...)"; / / Pure traceability table (TraceData), specifically for storing traceability information string createTraceTableSql = @"CREATE TABLE TraceData (TraceID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),DataID UNIQUEIDENTIFIER,-- Foreign key, associated with the business tableFilePath NVARCHAR(500),SheetName NVARCHAR(100),RowNumber INT,FOREIGN KEY (DataID) REFERENCES BizData(DataID))"; 2. Data import and query: Business data and traceability information are associated through DataID. During query, through join operations, the business data and its complete traceability information are recombined.

[0129] -- By using a relational query, business data is recombined with its independent traceability information. SELECT b.Our Account, b.Transaction Time, t.FilePath, t.SheetName, t.RowNumber FROM BizData bINNER JOIN TraceData t ON b.DataID = t.DataID WHERE b.Payment Amount>5000; (VI) Formation of Parsable Strings and Split Storage of Traceability Information This embodiment further explains the construction method of the "parsable string" in the claims, and the specific practice of splitting traceability information into sub-information for storage.

[0130] 1. Methods for forming parsable strings: / / Original trace information string filePath = src.Path; string sheetName = src.Sheet; int rowNum = row.RowNumber(); / / Method (1): Direct concatenation string delimiter = "|#|"; string traceDirect = string.Join(delimiter, new[] { filePath,sheetName, rowNum.ToString()}); / / Example: C:\Data.xlsx|#|Sheet1|#|15 / / Method (2): Concatenation after splitting (to prevent formatting issues) string fileName = System.IO.Path.GetFileName(filePath); / / Sub-information: filename string traceSplit = $"{fileName}@@{sheetName}@@{rowNum}"; / / Concatenate with "other information items" (table name, row number) / / Example: The above string, "Bank of China_202310.xlsx@@Transaction Record@@15", can be stored as the value of traceability fields such as TraceInfo and Trace_Account. It can be restored by parsing the corresponding rules during querying.

[0131] 2. Traceability information is split into sub-information for storage: / / The "storage path" is split into multiple sub-information items string drive = System.IO.Path.GetPathRoot(src.Path); / / Sub-information: drive letter string directory = System.IO.Path.GetDirectoryName(src.Path); / / Sub-information: directory string fileName = System.IO.Path.GetFileName(src.Path); / / Sub-information: filename / / These sub-information items are stored as "uncombined information items" in different database fields, such as worksheet name and row number. The corresponding table structure may contain multiple fields such as Trace_Drive, Trace_Directory, Trace_FileName, Trace_Sheet, and Trace_Row. During querying, these fields together constitute the complete traceability information.

[0132] The above description is merely a preferred embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any equivalent substitutions or modifications made by those skilled in the art within the scope of the technology disclosed in the present invention, based on the technical solution and inventive concept of the present invention, should be covered within the scope of protection of the present invention.

[0133] Corresponding to the method for generating traceable data based on integrated spreadsheet data described in the above embodiments, Figure 5 shows a structural block diagram of an apparatus for generating traceable data based on integrated spreadsheet data provided in an embodiment of this application. For ease of explanation, only the parts related to the embodiments of this application are shown.

[0134] Referring to Figure 5, the device 5 for generating traceable data based on integrated spreadsheet data is applied to an electronic device and includes: a data source table acquisition module 51, a target field filtering module 52, an integrated table creation module 53, a data to be imported determination module 54, and a data import module 55, wherein: the data source table acquisition module 51 is used to acquire the data source table of at least one spreadsheet file.

[0135] The target field filtering module 52 is used to determine the target field based on the fields of each of the above-mentioned data source tables.

[0136] The integration table creation module 53 is used to create an integration table, which includes a data field corresponding to the target field and / or at least one traceability field for storing traceability information.

[0137] The data to be imported determination module 54 is used to extract data from each of the above-mentioned data source tables according to the mapping relationship of the target fields as the data to be imported, and to obtain the source information corresponding to each row of the data to be imported when extracting the data to be imported. The source information is used to determine the location of the original data and includes at least: the storage path of the spreadsheet file, the name of the above-mentioned data source table, and the row identifier of the data to be imported in the above-mentioned data source table.

[0138] The data import module 55 is used to import the above-mentioned data to be imported and its corresponding traceability information into one or more fields of the above-mentioned integrated table according to a predetermined storage strategy, so as to generate a data system with data traceability capability.

[0139] In this embodiment, after obtaining at least one data source table of a spreadsheet file, target fields are determined based on the fields of each data source table. Data is extracted from each data source table as import data based on the mapping relationship of the target fields. During the extraction of import data, traceability information corresponding to each row of import data is obtained. The import data and its corresponding traceability information are then imported into the corresponding fields of the integration table. Since the traceability information includes the storage path of the spreadsheet file, the name of the data source table, and the row identifier of the import data in the aforementioned data source table, after the integration table imports the import data and its corresponding traceability information, the user can obtain the traceability information corresponding to the data imported by the integration table, thereby facilitating an accurate assessment of the credibility and reliability of the integration table.

[0140] Optionally, the target field filtering module 52 includes: identifying common fields that exist in all data source tables and determining the common fields as target fields; and / or, filtering target fields from the fields of each data source table according to preset field selection rules.

[0141] Optionally, when the above-mentioned data to be imported determination module 54 extracts data from the target field according to the mapping relationship of the target field as the data to be imported, it is specifically used to: establish a mapping relationship between the fields of the above-mentioned data source table and the data fields of the above-mentioned integration table; and extract the data of the corresponding fields from the above-mentioned data source table as the data to be imported based on the above-mentioned mapping relationship.

[0142] Optionally, the apparatus 5 for generating traceable data based on integrated spreadsheet data provided in this application embodiment further includes: a preprocessing module, used to perform data cleaning and / or verification and / or format standardization processing on the extracted data to be imported before importing the data to be imported and its corresponding traceability information into the corresponding fields of one or more of the integrated tables according to a predetermined storage strategy.

[0143] Optionally, the number of the above-mentioned integration table is one, which is used to centrally store the target field data and its corresponding traceability information from all the above-mentioned data source tables.

[0144] Optionally, the number of the above-mentioned traceability fields is one; the above-mentioned storage strategy is as follows: import the above-mentioned data to be imported into the corresponding above-mentioned data fields, and combine the above-mentioned storage path, the name of the above-mentioned data source table and the above-mentioned row identifier into a parsable string, and store it in the above-mentioned traceability field of the above-mentioned integrated table.

[0145] Optionally, there are multiple traceability fields; the storage strategy is as follows: import the data to be imported into the corresponding data field, and combine at least two of the storage path, the name of the data source table, and the row identifier, and store the parsable string and / or uncombined information items in multiple traceability fields respectively.

[0146] Optionally, there are multiple integration tables; different integration tables are used to classify and store target field data and / or corresponding traceability information from all the above data source tables; wherein, at least one of the above integration tables contains a traceability field for storing traceability information; multiple integration tables are interconnected through predefined association relationships, so that by executing a joint query based on the above association relationships, data stored in different integration tables can be obtained, and complete traceability information can be reconstructed.

[0147] Optionally, the number of the above-mentioned traceability fields is one; the above-mentioned storage strategy is as follows: import the above-mentioned data to be imported into the above-mentioned data fields corresponding to the corresponding integration table, and combine the above-mentioned storage path, the name of the above-mentioned data source table and the above-mentioned row identifier into a parsable string, and store it in the unique above-mentioned traceability field.

[0148] Optionally, there are multiple traceability fields; the storage strategy is as follows: import the data to be imported into the data fields corresponding to the integrated table, and combine at least two of the storage path, the name of the data source table, and the row identifier, and store the parsable string and / or uncombined information items obtained by the combination in multiple traceability fields respectively.

[0149] Optionally, the above parsable string is formed by one of the following methods: (1) directly concatenating at least two of the above storage path, the name of the above data source table, and the above row identifier; (2) first splitting the above storage path, the name of the above data source table, and the above row identifier into multiple sub-information, and then concatenating at least one of the above sub-information with other information items.

[0150] Optionally, the uncombined information items mentioned above include: the storage path, the name of the data source table, and the sub-information obtained by splitting at least one of the row identifiers mentioned above.

[0151] Optionally, the apparatus 5 for generating traceable data based on integrated spreadsheet data provided in this application embodiment further includes: a data subset determination module, used to query the data system to obtain a data subset that meets the query conditions in response to a data query request after the above-mentioned data system with data traceability capability is generated; and a new traceable data table forming module, used to export the above-mentioned data subset to a new spreadsheet file to form a new traceable data table; wherein the above-mentioned new traceable data table contains the above-mentioned data subset and maintains its traceable association with the original traceability information.

[0152] Optionally, the apparatus 5 for generating traceable data based on integrated spreadsheet data provided in this application embodiment further includes: a hyperlink generation module, used to generate a hyperlink that can directly locate the corresponding data position in the original spreadsheet file based on the traceability information in the data subset after the above-mentioned data subset is exported to a new spreadsheet file; and a hyperlink association module, used to associate the above-mentioned hyperlink with the corresponding data in the above-mentioned new traceable data table, so that users can directly locate and access the original data source by operating the above-mentioned hyperlink.

[0153] It should be noted that the information interaction and execution process between the above-mentioned devices / units are based on the same concept as the method embodiments of this application. For details on their specific functions and technical effects, please refer to the method embodiments section, and they will not be repeated here.

[0154] Figure 6 is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. As shown in Figure 6, the electronic device 6 of this embodiment includes: at least one processor 60 (only one processor is shown in Figure 6), a memory 61, and a computer program 62 stored in the memory 61 and executable on the at least one processor 60. When the processor 60 executes the computer program 62, it implements the steps in any of the above-described method embodiments.

[0155] The electronic device 6 can be a desktop computer, laptop, handheld computer, cloud server, or other computing device. This electronic device may include, but is not limited to, a processor 60 and a memory 61. Those skilled in the art will understand that Figure 6 is merely an example of the electronic device 6 and does not constitute a limitation on the electronic device 6. It may include more or fewer components than shown, or combine certain components, or different components, such as input / output devices, network access devices, etc.

[0156] The processor 60 may be a Central Processing Unit (CPU), or it may be other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. A general-purpose processor may be a microprocessor or any conventional processor.

[0157] In some embodiments, the memory 61 may be an internal storage unit of the electronic device 6, such as a hard disk or memory of the electronic device 6. In other embodiments, the memory 61 may be an external storage device of the electronic device 6, such as a plug-in hard disk, smart media card (SMC), secure digital (SD) card, flash card, etc., equipped on the electronic device 6. Furthermore, the memory 61 may include both internal and external storage units of the electronic device 6. The memory 61 is used to store the operating system, applications, bootloader, data, and other programs, such as the program code of the computer program. The memory 61 can also be used to temporarily store data that has been output or will be output.

[0158] Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the above-described division of functional units and modules is merely an example. In practical applications, the above functions can be assigned to different functional units and modules as needed, that is, the internal structure of the device can be divided into different functional units or modules to complete all or part of the functions described above. The functional units and modules in the embodiments can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit. Furthermore, the specific names of the functional units and modules are only for easy differentiation and are not intended to limit the scope of protection of this application. The specific working process of the units and modules in the above system can be referred to the corresponding process in the foregoing method embodiments, and will not be repeated here.

[0159] This application also provides a network device, which includes: at least one processor, a memory, and a computer program stored in the memory and executable on the at least one processor, wherein the processor executes the computer program to implement the steps in any of the above method embodiments.

[0160] This application also provides a computer-readable storage medium storing a computer program that, when executed by a processor, can implement the steps in the above-described method embodiments.

[0161] This application provides a computer program product that, when run on an electronic device, enables the electronic device to implement the steps described in the various method embodiments above.

[0162] If the integrated unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, all or part of the processes in the methods of the above embodiments of this application can be implemented by a computer program instructing related hardware. The computer program can be stored in a computer-readable storage medium, and when executed by a processor, it can implement the steps of the various method embodiments described above. The computer program includes computer program code, which can be in the form of source code, object code, executable files, or certain intermediate forms. The computer-readable medium can include at least: any entity or device capable of carrying computer program code to a photographic device / electronic device, a recording medium, a computer memory, a read-only memory (ROM), a random access memory (RAM), an electrical carrier signal, a telecommunication signal, and a software distribution medium. Examples include USB flash drives, portable hard drives, magnetic disks, or optical disks. In some jurisdictions, according to legislation and patent practice, computer-readable media cannot be electrical carrier signals or telecommunication signals.

[0163] In the above embodiments, the descriptions of each embodiment have different focuses. For parts that are not described in detail or recorded in a certain embodiment, please refer to the relevant descriptions of other embodiments.

[0164] Those skilled in the art will recognize that the units and algorithm steps of the various examples described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, or a combination of computer software and electronic hardware. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of this application.

[0165] In the embodiments provided in this application, it should be understood that the disclosed apparatus / network devices and methods can be implemented in other ways. For example, the apparatus / network device embodiments described above are merely illustrative. For instance, the division of modules or units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some interfaces; the indirect coupling or communication connection between devices or units may be electrical, mechanical, or other forms.

[0166] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0167] It should be noted that the information collection process (such as the facial image collection process, fingerprint information collection process, etc.) / feature extraction process involved in this application is carried out with the user's knowledge and permission. That is, the information collection process / feature extraction process complies with the requirements of laws and regulations and does not constitute an act that harms the public interest.

Claims

1. A method for generating traceable data based on integrated spreadsheet data, characterized in that, Includes the following steps: Obtain at least one data source table from a spreadsheet file; determine the target field based on the fields of each of the data source tables; Create an integration table, which includes a data field corresponding to the target field and / or at least one traceability field for storing traceability information; for each data source table, extract data from it as data to be imported according to the mapping relationship of the target field, and when extracting the data to be imported, obtain the traceability information corresponding to each row of the data to be imported, the traceability information being used to determine the location of the original data, including at least: the storage path of the spreadsheet file, the name of the data source table, and the row identifier of the data to be imported in the data source table; The data to be imported and its corresponding traceability information are imported into the corresponding fields of one or more of the integrated tables according to a predetermined storage strategy to generate a data system with data traceability capabilities.

2. The method according to claim 1, characterized in that, The step of determining the target field based on the fields of each of the data source tables includes: identifying common fields that exist in all data source tables and determining the common fields as target fields; and / or, filtering the target fields from the fields of each data source table according to preset field selection rules.

3. The method according to claim 1, characterized in that, The step of extracting data from the target field as import data based on the mapping relationship includes: establishing a mapping relationship between the fields of the data source table and the data fields of the integration table; and extracting data of the corresponding fields from the data source table as import data based on the mapping relationship.

4. The method according to claim 1, characterized in that, Before importing the data to be imported and its corresponding traceability information into the corresponding fields of one or more of the integrated tables according to a predetermined storage strategy, the method further includes: performing data cleaning and / or verification and / or format standardization processing on the extracted data to be imported.

5. The method according to claim 1, characterized in that, The number of integrated tables is one, and the integrated table is used to centrally store the target field data and its corresponding traceability information from all the data source tables.

6. The method according to claim 5, characterized in that, The number of traceability fields is one; the storage strategy is as follows: the data to be imported is imported into the corresponding data field, and the storage path, the name of the data source table and the row identifier are combined into a parsable string and stored in the unique traceability field of the integration table.

7. The method according to claim 5, characterized in that, The number of traceability fields is multiple; the storage strategy is as follows: import the data to be imported into the corresponding data field, and combine at least two of the storage path, the name of the data source table, and the row identifier, and store the parsable string and / or the uncombined information items in multiple traceability fields respectively.

8. The method according to claim 1, characterized in that, The number of integration tables is multiple; different integration tables are used to classify and store target field data and / or corresponding traceability information from all the data source tables; wherein, at least one integration table contains a traceability field for storing traceability information; multiple integration tables are interconnected through predefined association relationships, so that by executing a joint query based on the association relationship, data stored in different integration tables can be obtained, and complete traceability information can be reconstructed.

9. The method according to claim 8, characterized in that, The number of traceability fields is one; the storage strategy is as follows: the data to be imported is imported into the data field corresponding to the corresponding integration table, and the storage path, the name of the data source table and the row identifier are combined into a parsable string and stored in the unique traceability field.

10. The method according to claim 8, characterized in that, The number of traceability fields is multiple; the storage strategy is as follows: the data to be imported is imported into the data field corresponding to the corresponding integration table, and at least two of the storage path, the name of the data source table, and the row identifier are combined, and the parsable string and / or uncombined information items obtained by the combination are stored in multiple traceability fields respectively.

11. The method according to claim 7 or 10, characterized in that, The parsable string is formed by one of the following methods: (1) directly concatenating at least two of the storage path, the name of the data source table, and the row identifier; (2) first splitting at least one of the storage path, the name of the data source table, and the row identifier into multiple sub-information items, and then concatenating at least one of the sub-information items with other information items.

12. The method according to claim 7 or 10, characterized in that, The uncombined information items include: sub-information obtained by splitting at least one of the following: the storage path, the name of the data source table, and the row identifier.

13. The method according to claim 1, characterized in that, After generating the data system with data traceability capabilities, the method further includes: responding to a data query request by querying the data system to obtain a subset of data that meets the query conditions; exporting the subset of data to a new spreadsheet file to form a new traceable data table; wherein the new traceable data table contains the subset of data and maintains its traceable association with the original traceability information.

14. The method according to claim 13, characterized in that, After exporting the data subset to a new spreadsheet file, the method further includes: generating a hyperlink based on the traceability information in the data subset that can directly locate the corresponding data position in the original spreadsheet file; associating the hyperlink with the corresponding data in the new traceable data table, so that users can directly locate and access the original data source by operating the hyperlink.

15. An apparatus for generating traceable data based on integrated spreadsheet data, characterized in that, include: The data source table acquisition module is used to acquire the data source table of at least one spreadsheet file. The target field filtering module is used to determine the target field based on the fields of each of the data source tables; An integration table creation module is used to create an integration table, which includes a data field corresponding to the target field and / or at least one traceability field for storing traceability information; The data to be imported determination module is used to extract data from each of the data sources tables as data to be imported based on the mapping relationship of the target field, and to obtain the source information corresponding to each row of the data to be imported when extracting the data to be imported. The source information is used to determine the location of the original data and includes at least: the storage path of the spreadsheet file, the name of the data source table, and the row identifier of the data to be imported in the data source table. The data import module is used to import the data to be imported and its corresponding traceability information into one or more fields of the integrated table according to a predetermined storage strategy, so as to generate a data system with data traceability capability.

16. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the method as described in any one of claims 1 to 14.

17. A computer-readable storage medium storing a computer program, characterized in that, When the computer program is executed by a processor, it implements the method as described in any one of claims 1 to 14.

18. A computer program product, characterized in that, Includes a computer program, which, when run, causes the method as described in any one of claims 1 to 14 to be performed.

Images