Automatic Excel template data backfilling method based on LLM semantic comprehension technology

Abstract

The invention relates to the technical field of computer data processing, in particular to an automatic Excel template data backfilling method based on an LLM semantic understanding technology, which is based on the application of a large language model LLM, a natural language processing NLP, a natural language to NL2SQL and an automatic data filling technology. Through semantic understanding and the NL2SQL technology, efficient and accurate extraction and filling of template data are achieved, the problems that an existing method is low in efficiency and prone to making mistakes are solved, the working efficiency and data quality under the scenes of grassroots social governance and the like are improved, and the burden of workers is relieved.

Description

Technical Field

[0001] The present invention relates to the technical field of computer data processing, in particular to an automatic backfilling method for Excel template data based on LLM semantic understanding technology, and is based on the application of large language model LLM, natural language processing NLP, natural language conversion NL2SQL and automatic data filling technology. Background Art

[0002] In grassroots social governance scenarios, staff usually need to complete a large amount of data reporting work. These tasks usually require extracting data from existing systems and filling in the Excel template format issued by superiors. Current data reporting methods mainly rely on manual input or simple automation tools, which have the following problems: Manual input: staff are required to query and extract data from the system and manually fill in the Excel template, which is a cumbersome and error-prone process. Simple automation tools: such as VBA macros or Python scripts, although they can partially automate data filling, they require high technical skills of personnel and are difficult to adapt to changing templates and data sources. Special software: Some commercial data processing software can fill in data, but they usually lack flexibility, are difficult to adapt to different templates and data sources, and are expensive.

[0003] In recent years, with the development of large language models and natural language processing technology, intelligent data processing based on semantic understanding has become possible. Large language models can understand and generate natural language texts. Through Prompt technology and NL2SQL technology, complex data query and filling tasks can be achieved. The present invention combines these advanced technologies to propose a method for Excel template data backfilling with higher automation and higher accuracy, which effectively solves the shortcomings of existing methods. Summary of the invention

[0004] The present invention aims to provide a method for automatic backfilling of Excel template data based on LLM semantic understanding technology. Through semantic understanding and NL2SQL technology, efficient and accurate extraction and filling of template data can be achieved, the problems of low efficiency and easy errors in existing methods can be solved, work efficiency and data quality in scenarios such as grassroots social governance can be improved, and the burden on staff can be reduced.

[0005] The method for automatically backfilling Excel template data based on LLM semantic understanding technology includes the following steps: S01: Excel template analysis based on LLM By reading the Excel template file uploaded by the user, metadata such as the header information is obtained to provide basic data for the subsequent processing process; Python's pandas library is used to read the Excel file; for the Excel template file, if its header is [c1, c2, ..., cn], it is represented as a header vector: vector H = [h1, h2, ..., hn]; S02: Intelligent prompt generation based on template information The header information parsed in step S01 and the query conditions entered by the user are embedded into the predefined prompt word template to generate a detailed prompt; the prompt is passed as input to the NL2SQL model, and the corresponding SQL query statement is generated using the Zhipu GLM-4-9B model; The Excel vector is H, the query condition is q, and the context mask of the SQL statement that LLM needs to generate is provided. Then Prompt can be expressed as: Prompt = [H; q; mask] = [h1, h2, ..., hn; q; mask] S03: NL2SQL conversion for Excel population tasks The system passes the generated prompt to the NL2SQL model, which generates SQL query statements through semantic parsing and context understanding. The NL2SQL model uses a Transformer-based neural network structure to process the input natural language through an encoder-decoder architecture and generate corresponding SQL statements. S04: SQL verification and optimization based on template features Syntax verification: a. Use the SQL parser to perform syntax check on the generated SQL to ensure the grammatical correctness of the query and capture and correct basic syntax errors, such as misspellings of keywords, mismatched brackets, etc.; b. Adapt according to the database type specified by the template and support multiple SQL dialect verification; c. Perform static code analysis to discover potential syntax problems and anti-patterns in advance; Template field mapping validation: Verify that the fields in the SQL query accurately correspond to the fields in the Excel template, including checking the compatibility of field names and data types, and handling possible naming differences; a. Fuzzy matching: Use fuzzy matching algorithm to handle misspellings or synonyms of field names; b. Data type conversion: Use data type conversion rules to handle data type differences between different database systems; c. Cell format analysis: Analyze Excel cell formats and infer the data type and format requirements of the fields; Data type consistency check: According to the data type of the field in the Excel template, check whether the data operation in the SQL query is type compatible; a. Numerical range check: verify whether the value of a numeric field is within a reasonable range; b. Date format verification: perform strict date format verification and support multiple date formats; c. Custom function verification: Verify the use of custom functions to ensure that the function parameters and return value types are correct; Query logic verification: Analyze the logical structure of SQL queries to ensure that they meet user intent and template requirements, including checking whether the combination of WHERE clause conditions is reasonable and whether the JOIN operation is correct; a. Semantic analysis: Perform deeper semantic analysis on query statements through natural language processing technology; b. Dependency analysis: Analyze the dependency relationship between query statements; c. Outlier detection: perform outlier detection on query results; Performance optimization: query optimization based on template features.

[0006] a. Including index usage analysis: based on the frequently queried fields in the template, suggest or automatically add appropriate indexes; including: creating multi-column indexes; b. Query rewriting: rewrite complex queries into more efficient forms, including: converting subqueries into JOIN operations; c. Paging optimization: For templates with large amounts of data, paging logic is automatically added; d. Execution plan analysis: analyze the query execution plan; e. Query result verification: Verify whether the query result meets the template requirements by pre-writing a piece of data, including data integrity, format correctness, uniqueness constraint, referential integrity, data range verification, logic verification, and data consistency verification; S05: Automated Data Extraction and Backfill First, the SQL query statement generated by step S04 is executed to obtain the required data from the preset data source. Then, the system formats the extracted data and fills it back into the corresponding position according to the structure of the template file; Data source connection and query execution: First, establish a secure connection with the preset data source, which is various types of databases or API interfaces; Query result analysis: Execute the SQL query generated after NL2SQL conversion and perform intelligent analysis on the returned result set, including: a. Data type processing: Automatically identify the data type of a field based on database metadata or data samples, and perform common type conversions, such as converting numbers to strings, converting dates to strings, etc.; b. Null value processing: Provides a variety of missing value filling strategies, such as filling fixed values, mean, median, etc.; c. Data cleaning: remove or merge duplicate values, and normalize data such as date format and numerical precision into a unified format; d. Complex data structure processing: processing array and list type data, processing JSON and XML nested data formats; e. Error handling: Capture and handle various abnormal situations, such as database connection failure, query timeout, and record error information; Data mapping: Establish an intelligent mapping relationship between query results and Excel template fields; Data conversion and formatting: Perform necessary conversion and formatting on data according to the requirements of each field in the Excel template, including date format adjustment, numerical precision control, conversion between units, and text encoding processing; Data validation: Perform real-time data validation during the filling process to ensure that the filled data complies with the various rules and constraints defined in the template, including data type check, range verification, and uniqueness check; Calculation field processing: For calculation fields in the template, such as sum, average, and count, the corresponding calculation logic can be identified and executed; Security assurance: Perform data encryption, access control, and sensitive information desensitization during data extraction and filling; Audit trail: record detailed operation logs; S06: Generate download file After backfilling the data in step S05, a new Excel file is generated and saved.

[0007] The beneficial effects of the present invention are: Excel template parsing based on LLM: This paper proposes an efficient template file parsing technology, which greatly improves the system's adaptability to various complex templates, making the automated data filling process more intelligent and flexible. It can not only process standardized templates, but also adapt to various non-standard, complex or irregular template structures. It can accurately read template files in Excel and other formats, and obtain metadata information such as headers and field comments.

[0008] Intelligent Prompt generation method combined with template information: The invention creatively designs a method including "Prompt template", "database schema definition template" and "query question template". It provides accurate context information for semantic understanding and SQL generation process. By integrating template structure information and user query intent, a highly customized prompt is generated, thereby improving the accuracy and efficiency of NL2SQL conversion.

[0009] NL2SQL conversion for Excel filling tasks: Convert natural language queries into structured SQL query statements to achieve accurate data extraction and filling. Users can enter query conditions in natural language without having to master professional query languages ​​such as SQL, for example, "Query the names and addresses of elderly people who are over 80 years old and in good health."

[0010] SQL validation and optimization based on template features: Ensure that the generated SQL queries are not only syntactically correct, but also efficiently meet the data requirements of a specific Excel template. This approach comprehensively considers the structural features and data characteristics of the Excel template to intelligently validate and optimize the SQL queries generated by NL2SQL.

[0011] Automated data extraction and backfilling: It is the key technical point for realizing efficient data processing in the present invention, which realizes intelligent data filling from SQL query results to Excel templates, greatly improving the efficiency and accuracy of data processing. BRIEF DESCRIPTION OF THE DRAWINGS

[0012] Figure 1 This is a flow chart of the automatic backfilling method of Excel template data based on LLM semantic understanding technology. DETAILED DESCRIPTION

[0013] In order to overcome the problem of cumbersome and error-prone data reporting in grassroots social governance, the present invention proposes an automatic backfilling method for Excel template data based on LLM semantic understanding technology. This method realizes efficient and accurate automatic data filling by combining the semantic understanding ability of LLM, NL2SQL technology and Prompt technology.

[0014] The user first uploads the Excel template file, and the system automatically parses the header information and extracts the field structure. The user enters the query conditions in natural language, and the system generates a prompt word containing the header information and query conditions after parsing. The prompt word is converted into an SQL query statement through the NL2SQL model. The system executes the query statement to obtain the required data from the preset data source and automatically fills the data back into the Excel template.

[0015] The solution greatly improves the efficiency and accuracy of data reporting through automated processing, reduces the error rate of manual operations, adapts to a variety of templates and data sources, and greatly reduces the burden on grassroots staff. The present invention is described in detail below in conjunction with the accompanying drawings, data and specific implementation methods.

[0016] like Figure 1 As shown, a method for automatically backfilling Excel template data based on LLM semantic understanding technology in an embodiment of the present invention includes the following steps: S01: Excel template analysis based on LLM By reading the Excel template file uploaded by the user, metadata such as header information is obtained to provide basic data for subsequent processing. The template file is formulated by the business demander based on system data, business requirements, business specifications, etc. The template file needs to meet the excel xls or xlsx file standards. We use the pandas library widely used in Python to read Excel files. For a worksheet, let its header be [c1, c2, ..., cn], which can be represented as a header vector: Vector H = [h1, h2, ..., hn] The detailed implementation steps are as follows: 1) Semantic understanding: The extracted header information is input into LLM, and the semantic understanding ability of the model is used to interpret the meaning of each field. For example, for fields such as "name", "age", and "date of employment", LLM can understand what type of information they represent.

[0017] 2) Data type identification: Based on the semantic understanding of LLM, the possible data type of each field is inferred. For example, the "age" field is likely to be an integer type, and the "entry date" is likely to be a date type.

[0018] 3) Field relationship analysis: Based on LLM semantic understanding, analyze the potential relationship between fields. For example, if there is a correlation between the two fields "date of birth" and "age" in the template.

[0019] 4) Parsing of comments and instructions: Templates may contain various comments or instructions. Understand the meaning of these texts and incorporate them into the overall parsing logic.

[0020] For example, the Excel table information is as follows: User ID | Name | Registration Date Output after parsing: ['User ID', 'Name', 'Registration Date'] S02: Intelligent prompt generation combined with template information (main invention point) By embedding the header information parsed (step S01) and the query conditions input by the user into the predefined prompt word template, a detailed prompt is generated. The prompt is passed as input to the NL2SQL model. In the present invention, the Zhipu GLM-4-9B model is mainly used to generate the corresponding SQL query statement.

[0021] Assuming that the Excel header information is H and the query condition is q, which is used to provide the context mask of the SQL statement that LLM needs to generate, the prompt can be expressed as: Prompt = [H; q; mask] = [h1, h2, ..., hn; q; mask] 1) Define the Prompt template The prompt words for defining the generated SQL statements mainly include three elements: task / instruction, database schema and answer. The task mainly describes the instruction content, including query conditions, query fields, etc. The database schema mainly describes the table information and field Chinese comments. The answer shows the final generated SQL query statement.

[0022] a) Prompt template: Task: You are a database expert who is familiar with writing database query statements for MySQL, PostgreSQL, Oracle, SQLite, etc. Your task is to generate an SQL query statement based on the user question: [QUESTION]{user_question}[ / QUESTION].

[0023] If SQL cannot be generated according to the given data schema, "Insufficient information, unable to generate SQL" is directly returned.

[0024] Database Schema template: The generated query will be executed on the schema defined in the following database: {table__DDL} Answer: Given the database schema, this is the SQL query generated for question [QUESTION]{user_question}[ / QUESTION] [SQL] b)Query question template Query fields: {query_fields} Query condition: {condition} Other requirements: If there is no field requirement in the query condition, all fields will be queried. If SQL cannot be generated according to the given data schema, "Insufficient information, unable to generate SQL" will be directly returned.

[0025] 2) Build database schema information: mainly including table name, Chinese annotation of table name, field name, Chinese annotation of field name, external key association description, etc. This part is preset and configured through the system interface.

[0026] Database Schema Example: CREATE TABLE t_population ( id bigint PRIMARY KEY, -- primary key name varchar(50), -- name gender tinyint, -- gender, 0: male, 1: female phone_number varchar(20), -- mobile phone number identification_type varchar(10), -- ID type identification_no varchar(50), -- ID number ); -- Personnel information table CREATE TABLE t_population_label ( id bigint PRIMARY KEY, -- primary key population_id, -- population id label_name varchar(20), -- label name label_type varchar(50), -- label type ); -- Personnel tag table CREATE TABLE t_population_education ( id bigint PRIMARY KEY, -- primary key population_id bigint, -- population id culture_level varchar(20), -- cultural level ); -- Personnel education information table 3) Generate a complete prompt: Use the table header information obtained in step S01 as the query field and combine it with the query condition entered by the user to form a query question. Then use the query question and the optimized database schema information to replace the user_question and table__DDL variables in the prompt template to form the final prompt word.

[0027] a) Query question: Query fields: name, gender, age, ID number Search conditions: Women aged 70 and above Other requirements: If there is no field requirement in the query conditions, all fields will be queried.

[0028] b) Complete Prompt Task: You are a database expert, familiar with writing MySQL, PostgreSQL, Oracle, SQLite and other database query statements. Your task is to answer the user question: [QUESTION] Query fields: name, gender, age, ID number Search conditions: Women aged 70 and above Other requirements: If there is no field requirement in the query conditions, all fields will be queried.

[0029] [ / QUESTION]Generates a SQL query statement.

[0030] If SQL cannot be generated according to the given data schema, "Insufficient information, unable to generate SQL" is directly returned.

[0031] Database Schema: The generated query will be executed on the schema defined in the following database: CREATE TABLE t_population ( id bigint PRIMARY KEY, -- primary key name varchar(50), -- name gender tinyint, -- gender, 0: male, 1: female phone_number varchar(20), -- mobile phone number identification_type varchar(10), -- ID type identification_no varchar(50), -- ID number ); -- Personnel information table CREATE TABLE t_population_label ( id bigint PRIMARY KEY, -- primary key population_id, -- population id label_name varchar(20), -- label name label_type varchar(50), -- label type ); -- Personnel tag table CREATE TABLE t_population_education ( id bigint PRIMARY KEY, -- primary key population_id bigint, -- population id culture_level varchar(20), -- cultural level ); -- Personnel education information table Answer: Given the database schema, this is based on the question [QUESTION] Query fields: name, gender, age, ID number Search conditions: Women aged 70 and above Other requirements: If there is no field requirement in the query conditions, all fields will be queried.

[0032] [ / QUESTION]Generated SQL query statement [SQL] S03: NL2SQL conversion for Excel filling tasks (main invention point) The system passes the generated prompt to the NL2SQL model (this patent mainly uses the Zhipu GLM-4-9B large model), and the model generates SQL query statements through semantic parsing and context understanding. The NL2SQL model uses a Transformer-based neural network structure to process the input natural language through an encoder-decoder architecture and generate corresponding SQL statements. This step ensures accurate conversion from natural language to structured query language, providing a basis for subsequent data extraction. The detailed implementation steps are as follows 1) Model selection and optimization: We use an advanced pre-trained language model (this patent mainly uses the Zhipu GLM-4-9B large model) and perform specific fine-tuning for the Excel filling task. This includes training with a large number of Excel templates and related SQL query pairs, so that the model can better understand Excel-related query patterns.

[0033] 2) Context-aware processing: The NL2SQL model not only processes the user’s query but also considers the structural information of the Excel template. This context-aware capability makes the generated SQL query more in line with the requirements of the specific Excel template.

[0034] 3) Schema alignment: The model can intelligently align the entities and relationships in natural language queries with the fields and structure of the Excel template, ensuring that the generated SQL queries can be accurately mapped to the correct data columns and relationships.

[0035] 4) Complex query processing: The model is specially optimized for common complex query requirements in Excel filling tasks, such as multi-table joint query, conditional combination, aggregate calculation, etc., and can generate more complex and precise SQL statements.

[0036] 5) Data type awareness: The model understands the data types of different columns in Excel (such as text, numbers, dates, etc.) and considers these types when generating SQL to ensure type compatibility of queries.

[0037] S04: SQL verification and optimization based on template features (main invention point) Ensure that the generated SQL query is not only syntactically correct, but also can efficiently meet the data requirements of the specific Excel template. This method comprehensively considers the structural characteristics and data characteristics of the Excel template, and intelligently verifies and optimizes the SQL query generated by NL2SQL. The detailed implementation steps are as follows: 1) Syntax verification: Use the SQL parser to perform syntax check on the generated SQL to ensure the grammatical correctness of the query, and capture and correct basic syntax errors, such as misspellings of keywords, mismatched brackets, etc. Adapt according to the database type specified by the template, and support verification of multiple SQL dialects (MySQL, PostgreSQL, SQL Server, etc.). Perform static code analysis to discover potential syntax problems and anti-patterns in advance.

[0038] 2) Template field mapping verification: Verify whether the fields in the SQL query accurately correspond to the fields in the Excel template, including checking the compatibility of field names and data types, and handling possible naming differences.

[0039] Fuzzy matching: Use fuzzy matching algorithm to handle misspellings or synonyms of field names. Data type conversion: Use data type conversion rules to handle data type differences between different database systems. Cell format analysis: Analyze Excel cell formats to infer the data type and format requirements of the field.

[0040] 3) Data type consistency check: Check whether the data operations in the SQL query are compatible according to the data types of the fields in the Excel template. For example, use the appropriate date function for date fields and perform correct mathematical operations for numeric fields.

[0041] Numeric range check: Verify whether the value of a numeric field is within a reasonable range. Date format check: Perform strict date format check and support multiple date formats. Custom function check: Verify the use of custom functions to ensure that the function parameters and return value types are correct.

[0042] 4) Query logic verification: Analyze the logical structure of the SQL query to ensure that it meets the user's intention and template requirements. This includes checking whether the condition combination of the WHERE clause is reasonable and whether the JOIN operation is correct.

[0043] Semantic analysis: Use natural language processing technology to conduct a deeper semantic analysis of query statements to discover potential logical errors. Dependency analysis: Analyze the dependencies between query statements to avoid circular references and deadlocks.

[0044] Outlier detection: Perform outlier detection on query results to identify data quality issues.

[0045] 5) Performance optimization: query optimization based on template features.

[0046] Including index usage analysis: Based on the frequently queried fields in the template, appropriate indexes are recommended or automatically added. Including: creating multi-column indexes to support more complex query conditions. Ensure that the index covers all fields required for the query to reduce table return operations. Analyze the selectivity of the index to avoid creating indexes with too low selectivity. Query rewrite: Rewrite complex queries into a more efficient form, including: converting subqueries into JOIN operations. Materialize commonly used subquery results into views to improve query efficiency. Cache executed query results to reduce repeated calculations. Paging optimization: For templates with large amounts of data, paging logic is automatically added to improve query efficiency. Execution plan analysis: Analyze the execution plan of the query, find performance bottlenecks, and optimize them. Query result verification: Verify whether the query results meet the requirements of the template by pre-writing a piece of data, including data integrity, format correctness, uniqueness constraints, referential integrity, data range verification, logical verification, data consistency verification, etc.

[0047] Implementation code example: a) SQL statements According to the prompt word in step S02, after processing in steps S03 and S04, the following SQL is generated: SELECT name, CASE gender WHEN 0 THEN 'Male' ELSE 'Female' END AS gender,CAST(SUBSTR(identification_no, 7, 4) AS SIGNED INTEGER) - CAST(LEFT(CURDATE(), 4) AS SIGNED INTEGER) AS age, identification_no FROM t_population WHEREgender= 1 AND CAST(SUBSTR(identification_no, 7, 4) AS SIGNED INTEGER) - CAST(LEFT(CURDATE(), 4) AS SIGNED INTEGER)>= 70; b) SQL Validation import sqlparse def validate_sql_query(sql_query): try: parsed_query = sqlparse.parse(sql_query) return True except Exception as e: print(f"SQL syntax error: {e}") return False # Example call is_valid = validate_sql_query(sql_query) print(f"SQL query statement validity: {is_valid}") S05: Automated Data Extraction and Backfill First, execute the SQL query statement generated by step S04 to obtain the required data from the preset data source. Then, the system formats the extracted data and fills it back into the corresponding position according to the structure of the template file. The detailed implementation steps are as follows: 1) Data source connection and query execution: First, establish a secure connection with the preset data source, which can be various types of databases (such as MySQL, PostgreSQL, Oracle, etc.) or API interfaces.

[0048] 2) Query result analysis: Execute the SQL query generated after NL2SQL conversion and perform intelligent analysis on the returned result set, including: c. Data type processing: Automatically identify the data type of a field based on database metadata or data samples. Perform common type conversions (such as number to string, date to string, etc.), and consider precision loss and data overflow issues. Support user-defined data types and conversion rules.

[0049] d. Null value processing: Provides a variety of missing value filling strategies (such as filling fixed values, mean, median, etc.), and allows users to customize the filling rules.

[0050] e. Data cleaning: remove or merge duplicate values. Normalize date format, numerical precision and other data into a unified format.

[0051] f. Complex data structure processing: processing array and list type data. Processing nested data formats such as JSON and XML.

[0052] g. Error handling: Capture and handle various abnormal situations, such as database connection failure, query timeout, etc. Record error information to facilitate troubleshooting.

[0053] 3) Data mapping: Establish an intelligent mapping relationship between query results and Excel template fields, and handle the semantic similarity of field names, data type compatibility, and possible hierarchical relationships.

[0054] 4) Data conversion and formatting: According to the requirements of each field in the Excel template, necessary conversion and formatting of the data are performed, including date format adjustment, numerical precision control, conversion between units (such as meters to centimeters, Celsius to Fahrenheit, etc.), text encoding processing (such as UTF-8 to GBK), etc.

[0055] 5) Data Validation: Real-time data validation is performed during the filling process to ensure that the filled data complies with the various rules and constraints defined in the template, including data type checking, range verification, uniqueness checking, etc.

[0056] 6) Calculation field processing: For the calculation fields in the template (such as sum, average, count, etc.), it can identify and execute the corresponding calculation logic to ensure the consistency and accuracy of the data.

[0057] 7) Security assurance: Implement strict security measures during data extraction and filling, including data encryption, access control, and sensitive information desensitization.

[0058] 8) Audit tracking: Record detailed operation logs and support full tracking and auditing of the data filling process.

[0059] Implementation code example: 1) SQL query execution: The system executes the generated SQL query statement through the database connection to extract the required data from the preset data source. The pandas library can be used to easily read data from the SQL query.

[0060] import pandas as pd import sqlite3 def execute_sql_query(sql_query, db_connection): df = pd.read_sql_query(sql_query, db_connection) return df # Example call db_connection = sqlite3.connect('data_source.db') sql_query = "SELECT user_id, name, registration_date FROM user_tableWHERE YEAR(registration_date) = 2021" extracted_data = execute_sql_query(sql_query, db_connection) print(extracted_data) 2) Data backfilling: backfill the formatted data into the Excel template uploaded by the user. Using pandas' ExcelWriter, you can easily write data into an Excel file.

[0061] def fill_template_with_data(template_path, output_path, formatted_data): with pd.ExcelWriter(template_path, engine='openpyxl', mode='a')as writer: formatted_data.to_excel(writer, index=False, sheet_name='Sheet1', startrow=1) # Example call template_path = 'template.xlsx' output_path = 'filled_template.xlsx' fill_template_with_data(template_path, output_path, formatted_data) S06: Generate download file After backfilling the data in step S05, a new Excel file is generated and saved. The final file is provided to the user for download and use after confirmation.

[0062] Implementation code example: import shutil def save_filled_template(output_path, save_path): shutil.copyfile(output_path, save_path) # Example call save_path = 'final_filled_template.xlsx' save_filled_template(output_path, save_path) 2. Implementation Cases 1) Case 1 a) Prompt words Query question: Query the names and registration dates of all users registered in 2021 Database Schema: CREATE TABLE user_table ( user_id INT PRIMARY KEY, name VARCHAR(100), registration_date DATE ); b) Generate SQL SELECT name, registration_date FROM user_table WHERE YEAR(registration_date) = 2021; 2) Case 2 a) Prompt words Query problem: Get all order information for the user with user ID 1001 Database Schema: CREATE TABLE orders ( order_id INT PRIMARY KEY, user_id INT, product_name VARCHAR(100), order_date DATE, amount DECIMAL(10, 2) ); b) Generate SQL SELECT * FROM orders WHERE user_id = 1001; 3) Case 3 a) Prompt words Query question: Count the total sales for each month.

[0063] Database Schema: CREATE TABLE sales ( sale_id INT PRIMARY KEY, sale_date DATE, amount DECIMAL(10, 2) ); c) Generate SQL SELECT DATE_FORMAT(sale_date, '%Y-%m') AS month, SUM(amount) AStotal_sales FROM sales GROUP BY month; Compared with the prior art, the present invention has the following beneficial effects: (1) Improved efficiency: This patent greatly improves data reporting efficiency through automated processing. It can process multiple templates at the same time and realize batch data reporting. It supports real-time data updates to ensure that reports always reflect the latest information. Under normal circumstances, people manually query data in multiple systems and then manually fill in the data into Excel model files. This patent automatically uploads template files, greatly reducing the manual operations of grassroots staff and improving data reporting efficiency.

[0064] (2) Reduce errors: Eliminating human errors can ensure data consistency between different data reporting files and avoid differences caused by multiple inputs. It can automatically identify and mark abnormal data, perform data verification in real time, ensure that the entered data complies with predefined rules and formats, and improve data quality. It can provide detailed data processing logs to facilitate tracking and correcting potential problems. Under normal circumstances, people manually fill in data into Excel model files, and when the amount of data reported is large, problems such as filling in the wrong row may occur.

[0065] (3) Flexible adaptability: It can adapt to various complex Excel template structures, including multiple table headers, and supports multiple database types and API interfaces. New task templates can adapt to different types of businesses by simply configuring the data source and extracting the database DDL, realizing data extraction or automatic filling of Excel templates. It has strong flexibility and scalability.

[0066] (4) Reducing burden: Simplifying data operation and reporting processes has greatly reduced the burden on grassroots staff, reduced the pressure caused by data errors, improved job satisfaction, and enabled them to devote more energy to core business.

[0067] (5) Advanced technology: Combining the most advanced LLM, prompt word engineering, NL2SQL and other technologies, it reflects the cutting-edge level of current natural language processing and data processing technology.

[0068] Glossary: LLM (Large Language Model) is a natural language processing model based on deep learning technology, with billions or even hundreds of billions of parameters. LLM can understand and generate natural language text by pre-training on massive text data, and has strong semantic understanding and language generation capabilities. Typical LLMs include OpenAI's GPT-3 and GPT-4, Google's BERT and T5, etc.

[0069] NL2SQL (Natural Language to SQL) is a technology that converts natural language into structured query language (SQL). Through natural language processing and machine learning technology, the NL2SQL model can understand the natural language queries entered by users and convert them into corresponding SQL query statements. The core of NL2SQL is to use pre-trained large language models (such as the Transformer architecture) to perform semantic analysis on natural language and generate grammatically and semantically correct SQL statements. This technology is widely used in database query and management systems, allowing users to query and operate data without having to master complex SQL syntax.

[0070] Prompt Engineering is a technique for designing and optimizing input prompts for natural language processing models (such as large language models) to obtain desired outputs. Prompt Engineering guides the model to generate the desired results by carefully designing input content, including task descriptions, context information, instructions, etc. In the present invention, prompt engineering includes generating prompts for NL2SQL conversion, which specifically includes task descriptions, database schemas, and answer parts. Through clear and detailed prompts, the model can understand user needs more accurately and generate correct SQL query statements. Prompt Engineering plays a vital role in improving the quality and performance of model output.

[0071] Preset data source: a preconfigured data repository in the system, such as a database, data interface, or other data storage system.

Claims

1. The Excel template data automatic backfilling method based on LLM semantic understanding technology is characterized by The steps include: S01: Excel template analysis based on LLM By reading the Excel template file uploaded by the user, metadata such as header information is obtained to provide basic data for subsequent processing; Python's pandas library is used to read the Excel file; for the Excel template file, if its header is [c1, c2, ..., cn], it is represented as a header vector: vector H = [h1, h2, ..., hn]; S02: Intelligent prompt generation based on template information The header information parsed in step S01 and the query conditions entered by the user are embedded into the predefined prompt word template to generate a detailed prompt; the prompt is passed as input to the NL2SQL model, and the corresponding SQL query statement is generated using the Zhipu GLM-4-9B model; The Excel vector is H, the query condition is q, and the context mask of the SQL statement that LLM needs to generate is provided. Then Prompt can be expressed as: Prompt = [H; q; mask] = [h1, h2, ..., hn; q; mask] S03: NL2SQL conversion for Excel population tasks The system passes the generated prompt to the NL2SQL model, which generates SQL query statements through semantic parsing and context understanding. The NL2SQL model uses a Transformer-based neural network structure to process the input natural language through an encoder-decoder architecture and generate corresponding SQL statements. S04: SQL verification and optimization based on template features Syntax verification: a. Use the SQL parser to perform syntax check on the generated SQL to ensure the grammatical correctness of the query and capture and correct basic syntax errors, such as misspellings of keywords, mismatched brackets, etc.; b. Adapt according to the database type specified by the template and support multiple SQL dialect verification; c. Perform static code analysis to discover potential syntax problems and anti-patterns in advance; Template field mapping validation: Verify that the fields in the SQL query accurately correspond to the fields in the Excel template, including checking the compatibility of field names and data types, and handling possible naming differences; a. Fuzzy matching: Use fuzzy matching algorithm to handle misspellings or synonyms of field names; b. Data type conversion: Use data type conversion rules to handle data type differences between different database systems; c. Cell format analysis: Analyze Excel cell formats and infer field data types and format requirements; Data type consistency check: According to the data type of the field in the Excel template, check whether the data operation in the SQL query is type compatible; a. Numerical range check: verify whether the value of a numeric field is within a reasonable range; b. Date format verification: perform strict date format verification and support multiple date formats; c. Custom function verification: Verify the use of custom functions to ensure that the function parameters and return value types are correct; Query logic verification: Analyze the logical structure of SQL queries to ensure that they meet user intent and template requirements, including checking whether the combination of WHERE clause conditions is reasonable and whether the JOIN operation is correct; a. Semantic analysis: Perform deeper semantic analysis on query statements through natural language processing technology; b. Dependency analysis: Analyze the dependency relationship between query statements; c. Outlier detection: perform outlier detection on query results; Performance optimization: query optimization based on template features; a. Including index usage analysis: based on the frequently queried fields in the template, suggest or automatically add appropriate indexes; including: creating multi-column indexes; b. Query rewriting: rewrite complex queries into more efficient forms, including: converting subqueries into JOIN operations; c. Paging optimization: For templates with large amounts of data, paging logic is automatically added; d. Execution plan analysis: analyze the query execution plan; e. Query result verification: Verify whether the query result meets the template requirements by pre-writing a piece of data, including data integrity, format correctness, uniqueness constraint, referential integrity, data range verification, logic verification, and data consistency verification; S05: Automated Data Extraction and Backfill First, the SQL query statement generated by step S04 is executed to obtain the required data from the preset data source. Then, the system formats the extracted data and fills it back into the corresponding position according to the structure of the template file; Data source connection and query execution: First, establish a secure connection with the preset data source, which is various types of databases or API interfaces; Query result analysis: Execute the SQL query generated after NL2SQL conversion and perform intelligent analysis on the returned result set, including: a. Data type processing: Automatically identify the data type of a field based on database metadata or data samples, and perform common type conversions, such as converting numbers to strings, converting dates to strings, etc.; b. Null value processing: Provides a variety of missing value filling strategies, such as filling fixed values, mean, median, etc.; c. Data cleaning: remove or merge duplicate values, and normalize data such as date format and numerical precision into a unified format; d. Complex data structure processing: processing array and list type data, processing JSON and XML nested data formats; e. Error handling: Capture and handle various abnormal situations, such as database connection failure, query timeout, and record error information; Data mapping: Establish an intelligent mapping relationship between query results and Excel template fields; Data conversion and formatting: Perform necessary conversion and formatting on data according to the requirements of each field in the Excel template, including date format adjustment, numerical precision control, conversion between units, and text encoding processing; Data validation: Perform real-time data validation during the filling process to ensure that the filled data complies with the various rules and constraints defined in the template, including data type check, range verification, and uniqueness check; Calculation field processing: For calculation fields in the template, such as sum, average, and count, the corresponding calculation logic can be identified and executed; Security assurance: Perform data encryption, access control, and sensitive information desensitization during data extraction and filling; Audit trail: record detailed operation logs; S06: Generate download file After backfilling the data in step S05, a new Excel file is generated and saved.

Images