Data synchronization and analysis method from Excel machine account to ES data warehouse

Abstract

The invention discloses a data synchronization and analysis method from an Excel machine account to an ES data warehouse, relates to the technical field of data import and analysis, and adopts an advanced data integration technology to realize direct and real-time synchronization from the Excel machine account to the Elaast icsearch data warehouse, ensure the integrity and timeliness of data and lay a solid foundation for data analysis. And after the data is stored in the database, the rule engine is utilized to carry out secondary processing such as data cleaning, conversion and aggregation, so that the data quality is improved, and subsequent efficient analysis and query are facilitated. According to the method, data islanding limitation of a traditional Excel machine account is broken through, cross-table and cross-dimension data analysis is easily realized through flexible query and aggregation functions of the Elaast icsearch, and more comprehensive and deep insight is provided for decision making. The system is especially designed for the digital demand of offline standing book data, and assists enterprises to quickly complete the digital transformation of data assets through the automatic and intelligent data processing and analysis process, thereby improving the business operation efficiency and decision precision.

Description

Technical Field

[0001] The present invention relates to the technical field of data import and analysis, and in particular to a method for synchronizing and analyzing data from an Excel account book to an ES data warehouse. Background Art

[0002] Elastic Search (ES), as the industry's leading search engine, is far more than that. It is also a powerful data analysis engine. With its built-in Aggregation API, it can easily handle complex data analysis scenarios, whether it is accurately calculating the average value of a group, quickly locating the maximum value of a group, or efficiently counting group counts, ES can complete it with ease.

[0003] In the wave of digital transformation, although many industries have taken the lead in realizing the electronic and intelligent upgrade of business data, it cannot be ignored that many traditional fields, especially confidential environments, still rely on old manual account books and traditional Excel tools for data recording and analysis, which is not only inefficient, but also difficult to meet modern data-driven decision-making needs.

[0004] Therefore, it is particularly important to introduce advanced digital technology. It can not only realize the rapid digital conversion of manual account book data, but also use its powerful data analysis capabilities to deeply explore the value of data and bring unprecedented insights and competitive advantages to enterprises.

[0005] In the wave of digital transformation, although it has become an industry standard to monitor data changes in real time through automated business systems and efficiently synchronize them to ES for in-depth analysis, this process has mature and comprehensive technical support. However, in the face of specific challenges - that is, in the absence of a ready-made business system or the data has not yet been fully digitized, the data often exists in the form of traditional Excel ledgers, and the solution to this link is still insufficient. Especially when data requirements are further upgraded and involve complex data secondary processing or cross-table in-depth analysis, ES's direct support seems to be inadequate. Summary of the invention

[0006] In view of the above problems, the present invention provides a data synchronization and analysis method from an Excel ledger to an ES data warehouse for overcoming the above problems or at least partially solving the above problems.

[0007] The present invention provides the following scheme:

[0008] A data synchronization and analysis method from an Excel account book to an ES data warehouse, comprising:

[0009] Determine the definition of table metadata, wherein the table metadata includes a number of non-repeating field names and attributes corresponding to each of the field names; the field names correspond one-to-one to each business field included in the header of the Excel file to be synchronized, and the attributes are the same as the data type corresponding to the corresponding business field;

[0010] Using a library for reading and writing Excel spreadsheet files, load the Excel file to be synchronized to obtain all cell data;

[0011] Perform header parsing and field association processing, data parsing and conversion processing on all the cell data to obtain the data to be imported that can be recognized by the ES search and data analysis engine;

[0012] Import the data to be imported and / or incremental data into the new Index according to the determined import method to achieve data synchronization from the Excel ledger to the ES data warehouse;

[0013] Determine the derived fields, and synchronize the derived fields and corresponding results to the ES data warehouse; the derived fields include formula fields or assignment fields; the formula fields are used for users to process digital data, and by setting a specific formula, a new field corresponding to the formula is generated; the assignment field is used to give new meaning to the data through conditional judgment logic.

[0014] Preferably, the library for reading and writing Excel spreadsheet files includes any one of a pandas library and an openpyxl library.

[0015] Preferably: the header parsing and field association processing includes:

[0016] Read the first row of data from the Excel file to be synchronized to obtain the header data;

[0017] According to the definition of the field defined in the preset table template, the column name in the table header data is matched with the name of the field defined in the table template, and the association between the field defined in the table template and the column data is established.

[0018] Preferably: the data analysis and conversion process includes:

[0019] Starting from the row below the table header, traverse the data of each cell in the Excel file to be synchronized;

[0020] Determine the data type conversion logic according to the field type defined in the table template of the column where the data of the cell is located;

[0021] If conversion of the data of any of the cells fails, an error flag is set on the data of that cell.

[0022] Preferably: the field type includes a text type, and the conversion logic includes direct reading;

[0023] The field type includes a numeric type, and the conversion logic includes attempting to convert the text into a number, and logging an error if the attempt fails;

[0024] The field type includes a date type, and the conversion logic includes attempting to convert according to a preset date format rule, and recording an error if the conversion fails;

[0025] The field type includes a duration type, and the conversion logic includes converting the duration into milliseconds according to a preset duration format rule, and recording an error if it fails.

[0026] Preferably: after completing the parsing of all Cells, summarizing all Cell information with incorrect markings;

[0027] Generate an Excel file containing error markers based on error summary information;

[0028] If there are errors and the configuration requirements are strict, the process is terminated and the user is notified to check for errors;

[0029] If fault tolerance is allowed, continue with subsequent processing and provide an error report for user reference.

[0030] Preferably: the import method includes full import;

[0031] The full import includes creating the corresponding ES Mapping according to the table structure definition and randomly generating a unique new Index;

[0032] Write all the data into the new Index;

[0033] After the data migration is completed, point the table's Alias ​​to the new Index and safely delete the old Index.

[0034] If an error occurs during the data writing process, the new Index is deleted and the user is clearly prompted that the import failed.

[0035] Preferably: the importing method includes incremental importing;

[0036] The incremental import includes copying an existing Index as a new Index;

[0037] Write the incremental data that needs to be synchronized this time into the new Index;

[0038] After the incremental data is written successfully, point the table's Alias ​​to the new Index and clean up the old Index that is no longer needed.

[0039] If the incremental data writing fails, the new Index will be deleted and the import failure information will be clearly displayed to the user.

[0040] Preferably: after the derived fields are determined, the stock data and the newly added data are processed; for the stock data, each record is traversed, and the derived fields are calculated and generated according to the rules; for the newly added data, the rules are applied in real time during the data synchronization process.

[0041] Preferably: a target field is configured in the first table, and a common field between the first table and the second table is used as a bridge to establish an association between the first table and the second table.

[0042] According to the specific embodiments provided by the present invention, the present invention discloses the following technical effects:

[0043] The data synchronization and analysis method from Excel ledger to ES data warehouse provided in the embodiment of the present application adopts advanced data integration technology to realize direct and real-time synchronization from Excel ledger to Elasticsearch data warehouse, ensure the integrity and timeliness of data, and lay a solid foundation for data analysis. After the data is stored in the warehouse, the rule engine is used to perform secondary processing such as data cleaning, conversion, and aggregation to improve data quality and facilitate subsequent efficient analysis and query. Breaking the data island limitations of traditional Excel ledgers, through the flexible query and aggregation functions of Elasticsearch, cross-table and cross-dimensional data analysis can be easily realized, providing more comprehensive and in-depth insights for decision-making. Designed specifically for the digitalization needs of offline ledger data, it helps enterprises quickly complete the digital transformation of data assets and improve business operation efficiency and decision-making accuracy through automated and intelligent data processing and analysis processes.

[0044] Of course, any product implementing the present invention does not necessarily need to achieve all of the advantages described above at the same time. BRIEF DESCRIPTION OF THE DRAWINGS

[0045] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments are briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention, and for ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

[0046] Figure 1 This is a flow chart of a method for synchronizing and analyzing data from an Excel account book to an ES data warehouse provided by an embodiment of the present invention;

[0047] Figure 2 is a configuration template for physical fitness data provided by an embodiment of the present invention;

[0048] Figure 3 It is an Excel template for entering physical fitness data provided by an embodiment of the present invention;

[0049] Figure 4 is a schematic diagram of data extraction provided by an embodiment of the present invention;

[0050] Figure 5 is a schematic diagram of generating an error report provided by an embodiment of the present invention;

[0051] Figure 6 is a schematic diagram of a data synchronization process provided by an embodiment of the present invention;

[0052] Figure 7 is a schematic diagram of a full import process provided by an embodiment of the present invention;

[0053] Figure 8 is a schematic diagram of an incremental import process provided by an embodiment of the present invention;

[0054] Fig. 9 This is a schematic diagram of adding a new formula field provided by an embodiment of the present invention;

[0055] Fig.10 This is a schematic diagram of adding a new value assignment field provided by an embodiment of the present invention;

[0056] Fig.11 is a schematic diagram of application rules provided by an embodiment of the present invention;

[0057] Fig.12 It is a schematic diagram of cross-table analysis and data entry provided by an embodiment of the present invention;

[0058] Fig.13 It is a schematic diagram of the cross-table analysis process provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0059] The technical scheme in the embodiment of the present invention will be clearly and completely described below in conjunction with the accompanying drawings in the embodiment of the present invention. Obviously, the described embodiment is only a part of the embodiment of the present invention, not all of the embodiments. Based on the embodiment of the present invention, all other embodiments obtained by ordinary technicians in this field belong to the scope of protection of the present invention.

[0060] See also Figure 1 , a data synchronization and analysis method from an Excel account to an ES data warehouse provided by an embodiment of the present invention, such as Figure 1 As shown, the method may include:

[0061] S101: Determine the definition of table metadata, the table metadata includes several non-repeating field names and attributes corresponding to each field name; the several field names correspond one-to-one to the various business fields contained in the header of the Excel file to be synchronized, and the attributes are the same as the data type corresponding to the corresponding column name; in actual application, the table metadata can be determined first, and then an Excel template can be constructed based on the table metadata to input corresponding data in each cell in the constructed Excel template. It is also possible to determine the table metadata based on the already formed Excel file and the header information of the completed Excel file. Ensure that the data type in each cell can be accurately identified during the data extraction process.

[0062] S102: Use a library for reading and writing Excel spreadsheet files to load the Excel file to be synchronized to obtain all cell data; in specific implementation, the embodiment of the present application may also provide that the library for reading and writing Excel spreadsheet files includes any one of a pandas library and an openpyxl library.

[0063] S103: Perform header parsing and field association processing, data parsing and conversion processing on all the cell data to obtain the data to be imported that can be recognized by the ES search and data analysis engine; the purpose of this step is to extract the data in the Excel file through the corresponding relationship with the Excel file established above to ensure that the data can be recognized after being imported into ES. In specific implementation, the embodiment of the present application can provide that the header parsing and field association processing includes:

[0064] Read the first row of data from the Excel file to be synchronized to obtain the header data;

[0065] According to the definition of the field defined in the preset table template, the column name in the table header data is matched with the name of the field defined in the table template, and the association between the field defined in the table template and the column data is established.

[0066] The data analysis and conversion process includes:

[0067] Starting from the row below the table header, traverse the data of each cell in the Excel file to be synchronized;

[0068] Determine the data type conversion logic according to the field type defined in the table template of the column where the data of the cell is located;

[0069] If conversion of the data of any of the cells fails, an error flag is set on the data of that cell.

[0070] The field type includes a text type, and the conversion logic includes direct reading;

[0071] The field type includes a numeric type, and the conversion logic includes attempting to convert the text into a number, and logging an error if the attempt fails;

[0072] The field type includes a date type, and the conversion logic includes attempting to convert according to a preset date format rule, and recording an error if the conversion fails;

[0073] The field type includes a duration type, and the conversion logic includes converting the duration into milliseconds according to a preset duration format rule, and recording an error if it fails.

[0074] After completing the parsing of all cells, summarize all the cell information that are marked incorrectly.

[0075] Generate an Excel file containing error markers based on error summary information;

[0076] If there are errors and the configuration requirements are strict, the process is terminated and the user is notified to check for errors;

[0077] If fault tolerance is allowed, continue with subsequent processing and provide an error report for user reference.

[0078] S104: Import the data to be imported and / or the incremental data into the new Index according to the determined import method to achieve data synchronization from the Excel ledger to the ES data warehouse; the import method includes full import;

[0079] The full import includes creating the corresponding ES Mapping according to the table structure definition and randomly generating a unique new Index;

[0080] Write all the data into the new Index;

[0081] After the data migration is completed, point the table's Alias ​​to the new Index and safely delete the old Index.

[0082] If an error occurs during the data writing process, the new Index is deleted and the user is clearly prompted that the import failed.

[0083] The import method includes incremental import;

[0084] The incremental import includes copying an existing Index as a new Index;

[0085] Write the incremental data that needs to be synchronized this time into the new Index;

[0086] After the incremental data is written successfully, point the table's Alias ​​to the new Index and clean up the old Index that is no longer needed.

[0087] If the incremental data writing fails, the new Index will be deleted and the import failure information will be clearly displayed to the user.

[0088] S105: Determine the derived fields, and synchronize the derived fields and the corresponding results to the ES data warehouse; the derived fields include formula fields or assignment fields; the formula fields are used for users to process digital data, and by setting a specific formula, a new field corresponding to the formula is generated; the assignment field is used to give new meaning to the data through conditional judgment logic. In specific implementation, the embodiment of the present application can provide the processing of existing data and newly added data after the derived fields are determined; for existing data, traverse each record, calculate and generate derived fields according to the rules; for newly added data, apply the rules in real time during the data synchronization process.

[0089] In order to further implement cross-table data analysis, the embodiment of the present application can also provide configuring a target field in the first table, using a common field between the first table and the second table as a bridge to establish an association between the first table and the second table.

[0090] The method provided in the embodiment of this application innovatively introduces Elasticsearch (ES) as the core data warehouse, aiming to efficiently realize the comprehensive digitization and analysis optimization of Excel ledger data. This solution covers the complete link from the data source (Excel ledger) to deep insight, not only realizing seamless migration and synchronization of data, but also integrating efficient data processing and cross-table analysis capabilities, giving new digital vitality to offline ledger data in specific business scenarios.

[0091] This method can improve data visibility: the ledger data originally scattered in multiple Excel files can be centrally managed, greatly improving data visibility and transparency.

[0092] It can also speed up the decision-making process: through instant and accurate data analysis, it helps companies respond quickly to market changes and make more scientific and reasonable decisions.

[0093] In summary, this method takes Elasticsearch as the core and builds an efficient and intelligent Excel ledger data digitization and analysis system, providing strong support for the digital transformation of enterprises.

[0094] Taking the physical fitness data table as an example, the data synchronization and analysis method from the Excel ledger to the ES data warehouse provided in the embodiment of the present application is described in detail below.

[0095] 1. Seamless data synchronization from Excel ledger to ES data warehouse

[0096] 1. Data table metadata definition.

[0097] Determine the definition of table metadata, the table metadata including a number of non-repeating field names and attributes corresponding to each of the field names; the field names correspond one-to-one to the business fields contained in the header of the Excel file to be synchronized, and the attributes are the same as the data type corresponding to the corresponding column name.

[0098] In specific implementation, the table metadata can be defined through the table template mechanism. Each field contains multiple attributes such as field name, field type (text, value, date, duration, etc.), whether it is required, and field order. Field names of different fields cannot be repeated.

[0099] This article takes physical fitness data as an example to introduce the process of customizing templates. Figure 2 This is a configuration template for physical fitness data. The template contains business fields such as personnel number, personnel name, 100M running time, and number of sit-ups in 1 minute.

[0100] like Figure 3 As shown in the figure, after completing the template creation, you can use the Excel template automatically generated by the system to enter the relevant data.

[0101] It is understandable that if the table metadata is determined based on the completed Excel file, the data extraction step can be performed after the table metadata is determined.

[0102] 2. Data extraction. The data extraction process is as follows: Figure 4 shown.

[0103] a. Definition of noun.

[0104] Field: Field defined in the table template, such as personnel number, personnel name, etc.;

[0105] Row: data of a row in Excel, such as Row(1) means the first row of data, i.e. the header of Excel table;

[0106] Column: data in a column in Excel, such as Column(1) means the first column of data;

[0107] Cell: The data of a cell in Excel, uniquely identified by row and column. For example, Cell(1,1) represents the data in the first row and first column.

[0108] b. Initialization phase.

[0109] Load Excel: Use mature libraries (such as pandas, openpyxl, etc.) to load Excel files and obtain all cell data.

[0110] c. Header parsing and field association.

[0111] Read header: Read header data, i.e. column name, from Excel Row(1).

[0112] Field mapping: According to the preset Field definition, try to match the column name in the table header with the Field name to establish an association between Field and Column.

[0113] Unassociated field processing:

[0114] Check if any Field is not successfully associated with Column.

[0115] If the Field is marked as required and the corresponding Column is not found, the error is recorded and the data extraction process is stopped, and an error message is displayed.

[0116] If it is not required, record this situation (optional) and ignore these fields in subsequent data extraction.

[0117] d. Data analysis and conversion.

[0118] Traverse cells: Start from the second row (assuming the first row is the table header) and traverse every Cell in Excel.

[0119] Type identification and conversion:

[0120] Determine the data type conversion logic based on the Field type of the column where the Cell is located.

[0121] Text type: Direct read.

[0122] Number Type: Attempts to convert text to a number and logs an error if this fails.

[0123] Date type: Attempt to convert according to the preset date format rules (such as YYYY-MM-DD for YYYY year MM month DD day), and log an error if it fails.

[0124] Duration type: Convert the duration to milliseconds according to the preset duration format rules (such as 12′13″ represents 12 minutes and 13 seconds). If it fails, an error is recorded.

[0125] Error mark: If the conversion of any Cell fails, an error mark will be set on the Cell (such as changing the background color, adding comments, etc.).

[0126] e. Error handling and result output.

[0127] Error summary: After completing the parsing of all cells, summarize the cell information of all error-marked cells.

[0128] Generate error report: Generate an Excel file containing error markers based on the error summary information (you can mark error cells or list all errors in a separate worksheet).

[0129] Terminate or Continue:

[0130] If there are errors and the configuration requirements are strict, the process is terminated and the user is notified to check for errors.

[0131] If a certain degree of error tolerance is allowed, subsequent processing can be continued (such as only outputting successfully parsed data), and an error report is provided for user reference. Figure 5 shown.

[0132] f. Performance optimization.

[0133] Parallel processing: Consider using multiple threads or processes to process different rows or columns of data in parallel to speed up processing.

[0134] Cache mechanism: For data that is frequently accessed but does not change much (such as Field definitions, preset format rules, etc.), use cache to reduce reading time.

[0135] 3. Data synchronization. The data synchronization process is as follows Figure 6 shown.

[0136] When importing data, two methods are supported: full import and incremental import. The following describes the data synchronization process for the two data import methods.

[0137] Glossary:

[0138] Mapping: ES data warehouse table structure mapping, corresponding to the table structure definition.

[0139] Index: In ES, data tables exist in the form of indexes, such as "Index_Physical Fitness 001" represents the physical fitness index.

[0140] Alias: An alias for convenient access set for Index. For example, if you set the alias of "Index_Physical Fitness 001" to "Index_Physical Fitness", you can directly access the data through the alias. Each data table has a unique alias.

[0141] like Figure 7 As shown, full import includes:

[0142] 1. Automatically generate a unique Index: According to the table structure definition, automatically create the corresponding ES Mapping, and randomly generate a unique Index name (such as "Index New") to ensure that it does not conflict with the existing Index.

[0143] 2. Data migration: Write all data into the newly generated Index.

[0144] 3. Seamless switching of Alias: After the data migration is completed, the Alias ​​of the table (such as "Index_Physical Fitness") is automatically pointed to the new Index (Index New), and the old Index (IndexOld, if it exists) is safely deleted.

[0145] 4. Error handling: If an error occurs during data writing, the new Index will be deleted immediately and the user will be clearly informed of the import failure to ensure data consistency.

[0146] like Figure 8 As shown, incremental import includes:

[0147] 1. Copy the old Index: First, copy the existing Index (IndexOld) as the new Index (IndexNew) to ensure data security and continuity.

[0148] 2. Incremental data writing: Write the incremental data that needs to be synchronized this time into the new Index (Index New).

[0149] 3. Alias ​​update and old index cleanup: After the incremental data is written successfully, quickly point the Alias ​​of the table to the new index and clean up the old index that is no longer needed to achieve seamless update.

[0150] 4. Error rollback: If the incremental data writing fails, the new Index will be deleted immediately and the import failure information will be clearly displayed to the user to ensure a stable data environment.

[0151] 2. Data Secondary Processing Solution

[0152] In order to solve the problem that ES cannot process relevant data, users can configure a variety of derived fields for each data table, which can include formula fields or assignment fields. These formula fields can be the calculation results based on complex formulas. Assignment fields are new fields for conditional assignment, which greatly enhances the flexibility and depth of data processing.

[0153] Formula fields allow users to deeply process numeric data. By setting a specific formula, such as (Chinese + Mathematics + English) / 3, a new "average score" field can be automatically generated. This function not only simplifies the tedious process of data processing, but also allows users to examine data from a new perspective and discover hidden patterns and trends. New formula fields such as Fig. 9 shown.

[0154] The assignment field gives new meaning to the data by setting a series of conditional judgment logic. For example, when evaluating the 100-meter running results, you can set "excellent" for less than 20 seconds, "good" for less than 30 seconds, and "fail" for the rest. In addition, it also supports multiple condition combinations, such as considering the 100-meter results and the number of sit-ups at the same time, to ensure the accuracy and comprehensiveness of the classification. New assignment fields such as Fig.10 shown.

[0155] There are many mature solutions in the industry that can handle numerical calculations and logical judgments, such as Spring Expression Language (SPEL), whose powerful functions and flexible syntax allow users to easily implement complex logic.

[0156] 1. Formula field rule definition.

[0157] The core of formula field rules lies in the perfect combination of specific formulas and fields involved in the calculation. Taking the calculation of the average score of Chinese, mathematics, and English as an example, the rule definition is as follows:

[0158] Formula: (#Chinese+#Mathematics+#English) / 3

[0159] Fields: ["Chinese", "Mathematics", "English"]

[0160] Formula checking: Using SPEL's powerful engine, strictly check the grammatical correctness of the formula to ensure that there are no missing brackets, division operations have clear dividends, etc., so as to avoid calculation errors.

[0161] Formula execution: During the execution phase, the system automatically extracts the values ​​of the above fields from the data entity, and performs precise calculations based on the formula to generate the required average score field.

[0162] 2. Define the assignment field rules.

[0163] The design of the assignment field rule is to give specific meaning to the data through conditional judgment. Taking the 100-meter running score as an example, different score levels can be set according to the running time.

[0164] Default value: Fail

[0165] Rule Group:

[0166] Rule Group 1:

[0167] Group name: Excellent

[0168] Formula rules:

[0169] Formula: #100M score < 20

[0170] Field: ["100M score"]

[0171] Rule Group 2:

[0172] Group name: Good

[0173] Formula rules:

[0174] Formula: 20 <= #100M score < 30

[0175] Field: ["100M score"]

[0176] Formula check: When defining the assignment rules, we not only check the correctness of each formula definition, but also pay special attention to the conflicts between rule groups. Through detailed boundary value checks, we ensure that the same data does not meet multiple rules at the same time, ensuring the accuracy and uniqueness of the classification. For example, in the above example, we check whether the rules of group 1 and group 2 are met at the same time when the 100M score is 20 or 30.

[0177] Formula execution: During execution, the system extracts the corresponding field values ​​from the data entity, and performs conditional judgment based on the formula definition of each rule group, and finally assigns the correct grade to the data.

[0178] 3. Apply the rules.

[0179] After the rule definition is completed, the system will automatically process the existing data and the newly added data. For the existing data, the system will traverse each record, calculate and generate the derived fields according to the rules; for the newly added data, the rules will be applied in real time during the data synchronization process to ensure the accuracy and consistency of the data. Fig.11 shown.

[0180] 3. Cross-table data analysis plan.

[0181] The realization of cross-table data analysis depends on accurately extracting key fields from the source table for comprehensive analysis. Imagine that there are two tables: one is the first table, which can be a basic information table of personnel (including personnel number, name, height, weight, etc.), and the other is the second table, which can be a personnel physical performance table (recording each person's 100-meter running score, sit-up score, etc.). In order to more comprehensively evaluate physical fitness, we need to add a key dimension to the physical performance table - weight, and capture this data from the basic information table of personnel.

[0182] The specific operation is to configure a new field "weight" in the physical performance table, and then use "personnel number" as a bridge to establish a connection with the basic information table of personnel. This process is like paving a data highway for data analysis, so that the physical performance table can directly "borrow" the weight data in the basic information table of personnel. Fig.12 shown.

[0183] Import rule definitions.

[0184] Target table: A table that explicitly receives new data, i.e., the physical performance table.

[0185] Base key: A unique identifier used in the target table to match external data, i.e., the person number in the physical performance table.

[0186] Source table base key: In the source table (personnel basic information table), the field corresponding to the base key and having the same business meaning is also the personnel number.

[0187] Imported fields: The fields extracted from the source table and added to the target table, i.e. weight.

[0188] Rule check: Ensure that all fields involved in the operation exist in their respective tables without omissions.

[0189] Data import: By accurately matching the base key with the source table base key, ensure that each person's weight data is accurately imported into the physical performance table, completing the intelligent derivation of the field. Fig.13 shown.

[0190] In short, the data synchronization and analysis method from Excel ledger to ES data warehouse provided in this application adopts advanced data integration technology to achieve direct and real-time synchronization from Excel ledger to Elasticsearch data warehouse, ensuring the integrity and timeliness of data, and laying a solid foundation for data analysis. After the data is stored in the warehouse, the rule engine is used to perform secondary processing such as data cleaning, conversion, and aggregation to improve data quality and facilitate subsequent efficient analysis and query. Breaking the data island limitations of traditional Excel ledgers, through the flexible query and aggregation functions of Elasticsearch, cross-table and cross-dimensional data analysis can be easily realized, providing more comprehensive and in-depth insights for decision-making. Designed specifically for the digitalization needs of offline ledger data, it helps enterprises quickly complete the digital transformation of data assets and improve business operation efficiency and decision-making accuracy through automated and intelligent data processing and analysis processes.

[0191] It should be noted that, in this article, relational terms such as first and second, etc. are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, the elements defined by the sentence "comprise a ..." do not exclude the existence of other identical elements in the process, method, article or device including the elements.

[0192] It can be known from the description of the above implementation methods that those skilled in the art can clearly understand that the present application can be implemented by means of software plus a necessary general hardware platform. Based on such an understanding, the technical solution of the present application can be essentially or partly embodied in the form of a software product that contributes to the prior art. The computer software product can be stored in a storage medium such as ROM / RAM, a magnetic disk, an optical disk, etc., and includes several instructions for enabling a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods described in the various embodiments of the present application or certain parts of the embodiments.

[0193] Each embodiment in this specification is described in a progressive manner, and the same or similar parts between the embodiments can refer to each other, and each embodiment focuses on the differences from other embodiments. In particular, for the system or system embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant parts can refer to the partial description of the method embodiment. The system and system embodiments described above are merely schematic, wherein the units described as separate components may or may not be physically separated, and the components displayed as units may or may not be physical units, that is, they may be located in one place, or they may be distributed on multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the scheme of this embodiment. Ordinary technicians in this field can understand and implement it without creative work.

[0194] The above description is only a preferred embodiment of the present invention and is not intended to limit the protection scope of the present invention. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention are included in the protection scope of the present invention.

Claims

1. A data synchronization and analysis method from Excel ledger to ES data warehouse, characterized in that: include: Determine a definition of table metadata, where the table metadata includes a number of non-repeating field names and attributes corresponding to each of the field names; The field names correspond one-to-one to the business fields contained in the header of the Excel file to be synchronized, and the attributes are the same as the data types corresponding to the corresponding business fields; Using a library for reading and writing Excel spreadsheet files, load the Excel file to be synchronized to obtain all cell data; Perform header parsing and field association processing, data parsing and conversion processing on all the cell data to obtain the data to be imported that can be recognized by the ES search and data analysis engine; Import the data to be imported and / or incremental data into the new Index according to the determined import method to achieve data synchronization from the Excel ledger to the ES data warehouse; Determine the derived fields, and synchronize the derived fields and corresponding results to the ES data warehouse; the derived fields include formula fields or assignment fields; the formula fields are used for users to process digital data, and by setting a specific formula, a new field corresponding to the formula is generated; the assignment field is used to give new meaning to the data through conditional judgment logic.

2. The data synchronization and analysis method from Excel account to ES data warehouse according to claim 1 is characterized in that: The library for reading and writing Excel spreadsheet files includes any one of a pandas library and an openpyxl library.

3. The data synchronization and analysis method from Excel account to ES data warehouse according to claim 1 is characterized in that: The header parsing and field association processing includes: Read the first row of data from the Excel file to be synchronized to obtain the header data; According to the definition of the field defined in the preset table template, the column name in the table header data is matched with the name of the field defined in the table template, and the association between the field defined in the table template and the column data is established.

4. The data synchronization and analysis method from Excel account to ES data warehouse according to claim 3 is characterized in that: The data analysis and conversion process includes: Starting from the row below the table header, traverse the data of each cell in the Excel file to be synchronized; Determine the data type conversion logic according to the field type defined in the table template of the column where the data of the cell is located; If conversion of the data of any of the cells fails, an error flag is set on the data of that cell.

5. The data synchronization and analysis method from Excel account to ES data warehouse according to claim 4 is characterized in that: The field type includes a text type, and the conversion logic includes direct reading; The field type includes a numeric type, and the conversion logic includes attempting to convert the text into a number, and logging an error if the attempt fails; The field type includes a date type, and the conversion logic includes attempting to convert according to a preset date format rule, and recording an error if the conversion fails; The field type includes a duration type, and the conversion logic includes converting the duration into milliseconds according to a preset duration format rule, and recording an error if it fails.

6. The data synchronization and analysis method from Excel account to ES data warehouse according to claim 4 is characterized in that: After completing the parsing of all cells, summarize all incorrectly marked cell information; Generate an Excel file containing error markers based on error summary information; If there are errors and the configuration requirements are strict, the process is terminated and the user is notified to check for errors; If fault tolerance is allowed, continue with subsequent processing and provide an error report for user reference.

7. The data synchronization and analysis method from Excel account to ES data warehouse according to claim 1, characterized in that: The import method includes full import; The full import includes creating the corresponding ES Mapping according to the table structure definition and randomly generating a unique new Index; Write all the data into the new Index; After the data migration is completed, point the table's Alias ​​to the new Index and safely delete the old Index. If an error occurs during the data writing process, the new Index is deleted and the user is clearly prompted that the import failed.

8. The data synchronization and analysis method from Excel account to ES data warehouse according to claim 1, characterized in that: The import method includes incremental import; The incremental import includes copying an existing Index as a new Index; Write the incremental data that needs to be synchronized this time into the new Index; After the incremental data is written successfully, the table's Alias ​​points to the new Index and the old Index that is no longer needed is cleared; If the incremental data writing fails, the new Index will be deleted and the import failure information will be clearly displayed to the user.

9. The data synchronization and analysis method from Excel account to ES data warehouse according to claim 1, characterized in that: After the derived fields are determined, the existing data and the newly added data are processed; for the existing data, each record is traversed, and the derived fields are calculated and generated according to the rules; for the newly added data, the rules are applied in real time during the data synchronization process.

10. The data synchronization and analysis method from Excel account to ES data warehouse according to claim 1, characterized in that: A target field is configured in the first table, and a common field between the first table and the second table is used as a bridge to establish an association between the first table and the second table.

Images