Non-standard tabular data structuring and intelligent question answering methods, systems, and media based on large language models
By combining a large language model with a local shadow database, the problems of structuring non-standard tabular data and intelligent question answering are solved, achieving high accuracy and interpretable calculation results, and improving data security and processing efficiency for enterprise applications.
Patent Information
- Application Number
- CN202610659655.2
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2026-05-14
- Publication Date
- 2026-11-10
- Estimated Expiration
- 2046-05-14
AI Technical Summary
Existing technologies suffer from problems such as data overload, parsing rule collapse, and data black box effect when processing non-standard tabular data, resulting in low calculation accuracy and a lack of interpretability and security.
It adopts a heterogeneous processing architecture based on a large language model, uses LLM to handle inference planning and semantic understanding, and combines a local shadow database for heterogeneous processing to achieve automatic structured parsing of tables and intelligent question answering.
It significantly improves the accuracy of question and answering for non-standard table data, ensures the interpretability and security of calculation results, reduces the response time for processing complex tables, and provides financial-grade data privacy protection.
Smart Images

Figure CN122195988B_ABST
Abstract
Description
Technical Field
[0001] This application relates to the fields of artificial intelligence and data processing technology, and more specifically, to a method, system, and medium for structuring non-standard tabular data and intelligent question answering based on a large language model. Background Technology
[0002] In actual business scenarios, user-generated data tables (such as Excel files) are often non-standard formats that are "not directly analyzable". Specifically, this manifests as follows: the table header is usually not on the first row (there is a title or explanatory text at the beginning); a single sheet contains multiple independent table matrices; there are merged cells, empty rows and columns; and data columns contain mixed data types (such as a column containing numbers, text, "N / A", and numbers with units such as "1 million yuan").
[0003] In existing technologies, to achieve intelligent question answering of the aforementioned table, the table is typically converted directly into JSON or CSV text format and then used as the overall context input to a Large Language Model (LLM) for analysis. This direct connection approach, based on "brute-force data feeding," has the following serious drawbacks in practical engineering:
[0004] Context Exceeding Limits and Hallucination: Large tabular data (e.g., tens of thousands of rows, 5MB-50MB) converted to plain text can easily exceed the context window of an LLM. For example, a sales report containing 100,000 rows, after conversion to JSON, may result in millions of tokens, far exceeding the processing limits of current mainstream large models (e.g., 128k or 200k). Even with truncation strategies, a large amount of key data will be lost, causing severe bias and hallucinations when the model performs global aggregation calculations (e.g., summation, averaging), resulting in extremely low calculation accuracy.
[0005] Non-standard structures cause parsing rules to collapse: When the table header is offset downwards, or when multiple tables coexist within a single sheet, traditional hard-coding parsing rules (such as assuming the first row is always the header) will completely fail. This not only leads to the extraction of incorrect table schemas, but also incorrectly mixes descriptive text into the data columns, causing a complete failure of subsequent calculations.
[0006] Data black box effect and lack of traceability: In existing solutions, LLM is often used as an "end-to-end" calculator that directly outputs the final answer (e.g., "Total sales are 5 million"). This black box model makes it impossible to verify its calculation process and reasoning basis. In enterprise-level applications (such as financial auditing and data analysis), results lacking data security, process interpretability, and traceability are unusable. Summary of the Invention
[0007] The purpose of this application is to provide a method, system, and medium for structuring non-standard tabular data and intelligent question answering based on a large language model. It adopts a heterogeneous processing architecture that uses an LLM to handle inference planning and semantic understanding, and a local shadow database to handle exact numerical execution, thereby significantly improving the accuracy of complex tabular question answering.
[0008] This application also provides a method for structuring non-standard table data and intelligent question answering based on a large language model, including: receiving a non-standard table file, performing automatic structured parsing on the table, and obtaining standardized sub-table data; Based on the standardized sub-table data, a lightweight metadata description file is constructed, all lightweight metadata description files are integrated to obtain full standardized data, and the full standardized data is loaded into the local embedded OLAP database engine. The lightweight metadata description file is parsed and data mapping is performed to generate a global data asset summary and guiding prompts. Receive user natural language query information, match multiple guidance prompt word combinations based on user natural language query information, input the lightweight metadata description file and guidance prompt word combinations into the large language model, and generate structured execution plan information; The structured execution plan information is executed based on the local embedded OLAP database engine to generate question and answer feedback results.
[0009] Optionally, in the non-standard table data structuring and intelligent question answering method based on a large language model described in the embodiments of this application, the standardized sub-table data acquisition method includes: Obtain non-standard table files and extract the header features of the table files; Calculate the comprehensive confidence score of the header features for the first N rows of the table file, and dynamically locate the starting row of the physical header. Starting from the physical table header, a two-dimensional matrix connected component algorithm is used to identify and cut out multiple independent sub-table regions; Perform column data type inference, null value normalization, unit stripping and value restoration, and date format alignment on all sub-table areas to obtain standardized sub-table data.
[0010] Optionally, in the non-standard tabular data structuring and intelligent question answering method based on a large language model described in this application embodiment, the formula for calculating the comprehensive confidence score of the table header features is as follows:
[0011] Score(Row_i) =α*P_text +β*P_unique +γ*C_length;
[0012] P_text: The percentage of non-numeric or date data types in the valid cells of the i-th row;
[0013] P_unique: The deduplication rate of the non-empty cells in the i-th row;
[0014] C_length: The maximum number of columns that can be spanned to contain consecutive non-empty cells horizontally;
[0015] α, β, γ are empirical weight parameters extracted from a large number of samples during pre-training;
[0016] The formula for calculating the deduplication rate is as follows:
[0017] P_unique=count(distinct_values) / count(all_values);
[0018] Here, distinct_values is the set of unique values after removing duplicates from all non-empty cells in the i-th row; all_values is the set of values from all non-empty cells in the i-th row; and count() is a counting function used to count the number of elements in the set.
[0019] Optionally, in the non-standard tabular data structuring and intelligent question answering method based on a large language model described in this application embodiment, column data type inference, null value normalization, unit stripping and numerical restoration, and date format alignment are performed on all sub-table areas to obtain standardized sub-table data, specifically including:
[0020] Retrieve data from each independent sub-table and extract a sample of valid data rows for each column;
[0021] Analyze the standard underlying data type information of the valid data row samples in the column based on the majority voting mechanism and preset type matching rules;
[0022] Based on the standard underlying data type information, perform null value normalization processing, identify and replace implicit null values or placeholders, and obtain the standard underlying data type with implicit null values or placeholders removed.
[0023] Based on a pre-defined regular expression knowledge base, business units of standard underlying data types are identified. Business units that do not meet the set conditions are stripped of their units and their values restored to obtain standard business units.
[0024] Retrieve the mixed date expressions in the same column, and use the built-in date parser to map the mixed date expressions into a standard date format to obtain date format aligned data.
[0025] Standardized sub-table data is obtained by removing implicit null values or placeholders from the underlying data type, standard business units, and date format alignment data.
[0026] Optionally, in the non-standard tabular data structuring and intelligent question answering method based on a large language model described in this application embodiment, loading the full amount of standardized data into a local embedded OLAP database engine specifically includes:
[0027] Obtain data from each standardized sub-table, extract the table name, column name set, column data type, total number of rows, and sampled data of each sub-table, and obtain a lightweight metadata description file;
[0028] Create physical tables based on lightweight metadata description files;
[0029] Obtain detailed data from the physical table, clean the detailed data, and obtain fully standardized data;
[0030] Load all standardized data into the local embedded OLAP database engine.
[0031] Optionally, in the non-standard tabular data structuring and intelligent question answering method based on a large language model described in the embodiments of this application, column data type analysis is performed according to a majority voting mechanism, including:
[0032] Extract the first 100 rows of non-empty data from the target column as sample data;
[0033] If more than 80% of the samples match the numeric regular expression or contain a preset numeric unit, then the valid data row samples in the corresponding column will be forcibly labeled as numeric type.
[0034] Secondly, embodiments of this application provide a non-standard table data structuring and intelligent question-answering system based on a large language model. The system includes a memory and a processor. The memory includes a program for a non-standard table data structuring and intelligent question-answering method based on a large language model. When the program for the non-standard table data structuring and intelligent question-answering method based on a large language model is executed by the processor, it performs the following steps:
[0035] It receives non-standard table files, performs automatic structured parsing on the tables, and obtains standardized sub-table data.
[0036] Based on the standardized sub-table data, a lightweight metadata description file is constructed, all lightweight metadata description files are integrated to obtain full standardized data, and the full standardized data is loaded into the local embedded OLAP database engine.
[0037] The lightweight metadata description file is parsed and data mapping is performed to generate a global data asset summary and guiding prompts.
[0038] Receive user natural language query information, match multiple guidance prompt word combinations based on user natural language query information, input the lightweight metadata description file and guidance prompt word combinations into the large language model, and generate structured execution plan information;
[0039] The structured execution plan information is executed based on the local embedded OLAP database engine to generate question and answer feedback results.
[0040] Optionally, in the non-standard tabular data structuring and intelligent question-answering system based on a large language model described in this application embodiment, the standardized sub-table data acquisition method includes:
[0041] Obtain non-standard table files and extract the header features of the table files;
[0042] Calculate the comprehensive confidence score of the header features for the first N rows of the table file, and dynamically locate the starting row of the physical header.
[0043] Starting from the physical table header, a two-dimensional matrix connected component algorithm is used to identify and cut out multiple independent sub-table regions;
[0044] Perform column data type inference, null value normalization, unit stripping and value restoration, and date format alignment on all sub-table areas to obtain standardized sub-table data.
[0045] Optionally, in the non-standard tabular data structuring and intelligent question answering system based on a large language model described in this application embodiment, the formula for calculating the comprehensive confidence score of the table header features is as follows:
[0046] Score(Row_i) =α*P_text +β*P_unique +γ*C_length;
[0047] P_text: The percentage of non-numeric or date data types in the valid cells of the i-th row;
[0048] P_unique: The deduplication rate of the non-empty cells in the i-th row;
[0049] C_length: The maximum number of columns that can be spanned to contain consecutive non-empty cells horizontally;
[0050] α, β, γ are empirical weight parameters extracted from a large number of samples during pre-training;
[0051] The formula for calculating the deduplication rate is as follows:
[0052] P_unique=count(distinct_values) / count(all_values).
[0053] Thirdly, embodiments of this application also provide a computer-readable storage medium, which includes a program for structuring non-standard table data and intelligent question answering based on a large language model. When the program for structuring non-standard table data and intelligent question answering based on a large language model is executed by a processor, it implements the steps of the method for structuring non-standard table data and intelligent question answering based on a large language model as described in any of the above claims. Attached Figure Description
[0054] To more clearly illustrate the technical solutions of the embodiments of this application, the accompanying drawings used in the embodiments of this application will be briefly introduced below. It should be understood that the following drawings only show some embodiments of this application and should not be regarded as a limitation of the scope. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort.
[0055] Figure 1 A flowchart illustrating the non-standard tabular data structuring and intelligent question answering method based on a large language model provided in this application embodiment;
[0056] Figure 2 A flowchart illustrating the standardized sub-table data acquisition process of the non-standard tabular data structuring and intelligent question-answering method based on a large language model, as provided in this application embodiment.
[0057] Figure 3 A block diagram of a non-standard tabular data structuring and intelligent question-answering system based on a large language model, provided in an embodiment of this application. Detailed Implementation
[0058] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of the embodiments. The components of the embodiments of this application described and shown in the accompanying drawings can generally be arranged and designed in various different configurations. Therefore, the following detailed description of the embodiments of this application provided in the accompanying drawings is not intended to limit the scope of the claimed application, but merely represents selected embodiments of this application. All other embodiments obtained by those skilled in the art based on the embodiments of this application without inventive effort are within the scope of protection of this application.
[0059] It should be noted that similar reference numerals and letters in the following figures indicate similar items; therefore, once an item is defined in one figure, it does not need to be further defined and explained in subsequent figures. Furthermore, in the description of this application, the terms "first," "second," etc., are used only to distinguish descriptions and should not be construed as indicating or implying relative importance.
[0060] Please refer to Figure 1 , Figure 1 This is a flowchart illustrating a method for structuring non-standard tabular data and providing intelligent question answering based on a large language model, as described in some embodiments of this application. This method, used in a terminal device, includes the following steps:
[0061] S101: Receives a non-standard table file, performs automatic structured parsing on the table, and obtains standardized sub-table data;
[0062] S102, based on standardized sub-table data, constructs a lightweight metadata description file, integrates all lightweight metadata description files to obtain full standardized data, and loads the full standardized data into the local embedded OLAP database engine;
[0063] S103, based on the lightweight metadata description file, performs parsing and data mapping processing to generate a global data asset summary and guiding prompts;
[0064] It should be noted that generating a global summary and guidance prompts (proactive interaction logic) based on the current data structure includes:
[0065] After parsing and data mapping are completed, the system does not wait for user input, but automatically generates a global data asset analysis summary based on a lightweight metadata description file (e.g., "The system has been successfully normalized and detected N independent table entities, of which Table 1 has X rows and contains key business fields: Y").
[0066] Meanwhile, the system automatically generates 3-5 "high-quality recommendation analysis questions" based on the semantic features of each column (e.g., if date and amount columns are detected, trend analysis intent is generated) through lightweight rule matching or pre-calling of a large language model, and outputs them to the front end for users to directly click and call.
[0067] S104: Receive user natural language query information, match multiple guidance prompt word combinations based on user natural language query information, input the lightweight metadata description file and guidance prompt word combinations into the large language model, and generate structured execution plan information;
[0068] It should be noted that receiving natural language queries and calling the large language model to generate a structured execution plan specifically includes:
[0069] Receive natural language query requests from users for the target table area (such as "statistics on sales in each region and sorted in descending order").
[0070] The system assembles the generated, extremely concise "lightweight metadata description file (Table Schema)" with the user's "query request" into a preset structure prompt and inputs it into the large language model.
[0071] Through strict System Instruction constraints, the large language model is restricted from outputting any final numerical calculation results. Instead, it acts as a "logic planner," outputting only standard structured query language (specific dialects such as PostgreSQL / DuckDB SQL) or data analysis operator sequences (Analysis Plan, containing key-value pairs such as table, group_by, metric, sort, limit, etc.).
[0072] S105 generates question-and-answer feedback results based on the structured execution plan information of the local embedded OLAP database engine.
[0073] Specifically, the execution engine performs precise calculations and provides multi-dimensional feedback on the results, including:
[0074] The system captures and parses the structured execution plan output by the large model.
[0075] The SQL or operator sequence can be executed safely within a locally protected embedded OLAP database engine (DuckDB).
[0076] Obtain the exact and accurate aggregate or query result data block, and return the query result data, the visualized data chart code, and the actual SQL statement used in this query (as an absolutely traceable credential) to the caller.
[0077] It should be noted that when receiving non-standard table files, the system performs automatic structured parsing (NormalizationPipeline). After receiving the data file, the system background skips the traditional hard-coded file reading mode, directly constructs a two-dimensional grid in memory, and executes a matrix scanning algorithm based on heuristic rules, specifically including:
[0078] Header Dynamic Positioning Probe: The processor extracts the first N rows of the worksheet (default N=20 or dynamically adjusted based on the number of lines in the file), and calculates the "Header Feature Confidence Score" for each row. Here, $ is the inline formula delimiter in LaTeX mathematical typesetting syntax, which can also be understood as the processor extracting the first N rows of the worksheet (default N=20 or dynamically adjusted based on the number of lines in the file). This score is calculated by weighting the following independent factors:
[0079] Text scaling factor: The proportion of plain text cells in this row (the header is mostly a descriptive string).
[0080] Duplicate value factor: The deduplication rate of the cell content in this row (valid table headers usually have unique identifiers and no duplicate column names).
[0081] Continuity factor: The system calculates the weighted sum of the scores for the continuous span of non-empty cells in the horizontal dimension, and identifies the row with the highest score that exceeds the system threshold as the starting row of the physical header, thus successfully skipping the preceding messy descriptive text.
[0082] Table Region Multipath Detection: Starting from the physical table header, the system employs a two-dimensional matrix connectivity algorithm (similar to finding connected components in image processing) to scan a continuous matrix of non-empty cells both vertically and horizontally. Matrix blocks that are physically completely isolated from each other by entirely empty rows or columns are segmented and marked as multiple independent sub-table entities (e.g., simultaneously identifying Table_1: A4:D50 as sales details and Table_2: A60:F120 as return details within a single sheet).
[0083] like Figure 2 As shown in the embodiments of this application, the standardized sub-table data acquisition method includes:
[0084] Obtain non-standard table files and extract the header features of the table files;
[0085] Calculate the comprehensive confidence score of the header features for the first N rows of the table file, and dynamically locate the physical header starting row;
[0086] Starting from the physical table header, a two-dimensional matrix connected component algorithm is used to identify and cut out multiple independent sub-table regions;
[0087] Perform column data type inference, null value normalization, unit stripping and value restoration, and date format alignment on all sub-table areas to obtain standardized sub-table data.
[0088] According to an embodiment of this application, the formula for calculating the comprehensive confidence score of the table header features is as follows:
[0089] Score(Row_i) =α*P_text +β*P_unique +γ*C_length;
[0090] P_text: The percentage of non-numeric or date data types in the valid cells of the i-th row;
[0091] P_unique: The deduplication rate of the non-empty cells in the i-th row;
[0092] C_length: The maximum number of columns that can be spanned to contain consecutive non-empty cells horizontally;
[0093] α, β, γ are empirical weight parameters extracted from a large number of samples during pre-training;
[0094] The formula for calculating the deduplication rate is as follows:
[0095] P_unique=count(distinct_values) / count(all_values);
[0096] Here, distinct_values is the set of unique values after removing duplicates from all non-empty cells in the i-th row; all_values is the set of values from all non-empty cells in the i-th row; and count() is a counting function used to count the number of elements in the set.
[0097] Technical Performance Demonstration: For example, if a row contains 90% plain text cells ($\alpha$ score is extremely high), all column names are unique ($\beta$ score is extremely high, satisfying the header uniqueness requirement), and the row spans 10 consecutive columns without breaks ($\gamma$ score is extremely high), the system will calculate an extremely high overall confidence level. This precisely avoids the fatal error of a common cross-column merged header at the top of the table (such as "XXX Company 2023 Annual Financial Report") being mistaken for a header. After calculation, the system selects the row with the highest score and consecutive subsequent data streams as the Header_Row.
[0098] If, in the data stream below the same Sheet, the system detects a new high-scoring Header_Row after physical isolation by a completely empty line, the system triggers the multi-table matrix cutting logic, records the coordinate origin (x1, y1) and endpoint boundary (x2, y2) of the current matrix, and peels and disassembles a chaotic single sheet into multiple independent and clearly structured data frames in the system memory.
[0099] According to the embodiments of this application, column data type inference, null value normalization, unit stripping and value restoration, and date format alignment are performed on all sub-table areas to obtain standardized sub-table data, specifically including:
[0100] Retrieve data from each independent sub-table and extract a sample of valid data rows for each column;
[0101] Based on the majority voting mechanism and preset type matching rules, analyze the standard underlying data type information of the valid data row samples in the column;
[0102] Based on the standard underlying data type information, perform null value normalization processing, identify and replace implicit null values or placeholders, and obtain the standard underlying data type with implicit null values or placeholders removed.
[0103] Based on a pre-defined regular expression knowledge base, business units of standard underlying data types are identified. Business units that do not meet the set conditions are stripped of their units and their values restored to obtain standard business units.
[0104] Retrieve the mixed date expressions in the same column, and use the built-in date parser to map the mixed date expressions into a standard date format to obtain date format aligned data.
[0105] Standardized sub-table data is obtained by removing implicit null values or placeholders from the underlying data type, standard business units, and date format alignment data.
[0106] It should be noted that for each independent sub-table that has been split off, the system extracts a sample of valid data rows for each column and infers the standard underlying data type (such as numeric, string, date) of that column based on the majority voting mechanism and preset type matching rules.
[0107] Null value and dirty character normalization: Identify and replace implicit null values or placeholders (convert "N / A", "-", "missing", and "space" into system-level NULL entities) to prevent errors in subsequent numerical calculations.
[0108] Unit stripping and dynamic numerical restoration: Using a pre-defined regular expression knowledge base, the system identifies embedded business units (such as "ten thousand yuan", "%", "kg") within cells. If a match is successful, the system automatically extracts the valid numerical portion and applies the corresponding multiplier factor (for example, if "1 million yuan" is matched, the system strips the "ten thousand yuan" string and executes 100 * 1,000,0 in the underlying calculation logic to convert it into a standard floating-point number, while automatically updating the column name to carry the standard unit).
[0109] Date format alignment: For messy date expressions mixed in the same column (such as 2023 / 01 / 02, 2023-1-2, January 2, 2023), the built-in date parser maps them uniformly to a standard YYYY-MM-DD structure, ensuring the effectiveness of time dimension aggregation.
[0110] According to an embodiment of this application, loading all standardized data into a local embedded OLAP database engine specifically includes:
[0111] Obtain data from each standardized sub-table, extract the table name, column name set, column data type, total number of rows, and sampled data of each sub-table, and obtain a lightweight metadata description file;
[0112] Create physical tables based on lightweight metadata description files;
[0113] Obtain detailed data from the physical table, clean the detailed data, and obtain fully standardized data;
[0114] Load all standardized data into the local embedded OLAP database engine.
[0115] It should be noted that building an AI-friendly standard data structure (Table Schema) and loading it into the execution engine includes:
[0116] Data dimensionality reduction and isolation: The system physically prevents the transmission of the cleaned full detail data to the large language model interface, thus blocking the root cause of the illusion.
[0117] Minimal Feature Extraction: For each cleaned sub-table, the system generates a highly condensed, lightweight metadata description file (JSON Schema format). This file contains only: the table name, the cleaned set of column names, column data types, the total number of rows, and $M$ rows (preset $M=3\sim5$) of data randomly sampled using a stratified sampling algorithm as an example format of the actual data form.
[0118] Database entity mapping: Synchronously, the system dynamically executes the CREATE TABLE operation to create physical tables based on the above schema in the embedded OLAP database engine (preferably DuckDB or SQLite) on the local machine or in the cloud, and injects the cleaned detailed data into the local space without loss.
[0119] According to embodiments of this application, column data type analysis is performed based on a majority voting mechanism, including:
[0120] Extract the first 100 rows of non-empty data from the target column as sample data;
[0121] If more than 80% of the samples match the numeric regular expression or contain a preset numeric unit, then the valid data row samples in the corresponding column will be forcibly labeled as numeric type.
[0122] like Figure 3As shown, in a second aspect, embodiments of this application provide a non-standard table data structuring and intelligent question-answering system based on a large language model. The system includes a memory and a processor. The memory includes a program for a non-standard table data structuring and intelligent question-answering method based on a large language model. When the program is executed by the processor, it performs the following steps:
[0123] It receives non-standard table files, performs automatic structured parsing on the tables, and obtains standardized sub-table data.
[0124] Based on the standardized sub-table data, a lightweight metadata description file is constructed. All lightweight metadata description files are integrated to obtain the full standardized data, and the full standardized data is loaded into the local embedded OLAP database engine.
[0125] The lightweight metadata description file is parsed and the data is mapped to generate a global data asset summary and guiding prompts.
[0126] Receive user natural language query information, match multiple guide prompt word combinations based on the user natural language query information, input the lightweight metadata description file and guide prompt word combinations into the large language model, and generate structured execution plan information;
[0127] Based on the structured execution plan information of the local embedded OLAP database engine, question and answer feedback information is generated.
[0128] It should be noted that the system's internal module architecture includes the following components:
[0129] File receiving and scheduling module (Upload Service): Used to receive various heterogeneous file streams and trigger subsequent standardized parsing pipelines.
[0130] Advanced table parsing module (Excel Parser & Table Detection): includes a sliding window header probe unit, a two-dimensional matrix boundary cutting unit, and a heuristic type inference unit.
[0131] Data Cleaning Engine: Includes a regular expression pattern matcher, unit conversion logic gates with arithmetic restoration logic, a date normalizer, and a missing value safe filler.
[0132] The DuckDB Executor is used to map cleaned standard data blocks in memory, supporting complex and efficient queries such as multi-table joins, multi-dimensional aggregations, and window functions.
[0133] LLM Planner: Responsible for dynamically assembling token-intensive prompts and calling models, and has a built-in syntax validator to verify the SQL syntax security and dialect compatibility of the LLM output.
[0134] Result Generator: Encapsulates the underlying exact execution result data, dynamically generates corresponding data charts (such as line charts and bar charts), and presents a traceable SQL query path.
[0135] According to the embodiments of this application, the methods for obtaining standardized sub-table data include:
[0136] Obtain non-standard table files and extract the header features of the table files;
[0137] Calculate the comprehensive confidence score of the header features for the first N rows of the table file, and dynamically locate the physical header starting row;
[0138] Starting from the physical table header, a two-dimensional matrix connected component algorithm is used to identify and cut out multiple independent sub-table regions;
[0139] Perform column data type inference, null value normalization, unit stripping and value restoration, and date format alignment on all sub-table areas to obtain standardized sub-table data.
[0140] According to an embodiment of this application, the detection scanning window range is set to Row 1 to Row 20 of the worksheet (this depth is sufficient to cover 99% of the header area of Chinese enterprise reports). The formula for calculating the comprehensive confidence score of header features is as follows:
[0141] Score(Row_i) =α*P_text +β*P_unique +γ*C_length;
[0142] P_text: The percentage of non-numeric or date data types in the valid cells of the i-th row;
[0143] P_unique: The deduplication rate of the non-empty cells in the i-th row;
[0144] C_length: The maximum number of columns that can be spanned to contain consecutive non-empty cells horizontally;
[0145] α, β, γ are empirical weight parameters extracted from a large number of samples during pre-training;
[0146] The formula for calculating the deduplication rate is as follows:
[0147] P_unique=count(distinct_values) / count(all_values).
[0148] The logic for data type inference and dynamic unit stripping is as follows:
[0149] In real-world business tables, the most critical numerical columns are often cluttered with redundant characters due to human reading habits. This application's data cleaning engine employs the following robust cleaning logic:
[0150] Large Sample Type Voting: The first 100 non-empty cells of the target column are read as the sample cluster. If more than 80% of the samples can be matched by the basic regular expression ^[0-9\.\,\-\+]+$, or have a string with a known numerical unit in the dictionary as a suffix, the system will break the "Text" type that the original software may misjudge and force the column to be classified as a Numeric numerical structure.
[0151] Dirty data isolation and masking: Non-numerical noise text (such as manually entered "None", "Missing", "Unknown", "N / A") is accurately recorded in the null_values dictionary of the column's metadata, and these noises are uniformly replaced with safe SQL NULL values when writing to the shadow database at the underlying level, completely eliminating type crash errors during subsequent summation and averaging operations.
[0152] Numerical restoration executor: Pre-built expandable unit dictionary and arithmetic map.
[0153] If the system detects that the column feature data contains the character "ten thousand yuan" at the end, it executes regular expression capture to extract floating-point features and directly executes Value * 1,000,0 in the memory data channel.
[0154] If it contains "%", extract the floating-point feature and perform Value / 100 to convert it into a directly calculable decimal.
[0155] Automatic semantic update: After the conversion is completed, in order to avoid the model from misunderstanding the numerical units, the system will automatically add a standardized unit hint suffix to the original column name (such as "Amount this month") (renamed to "Amount this month_Yuan") and write it into the final schema so that the LLM can more accurately understand the data units.
[0156] Miniaturized representations for large language models (Prompt Engineering specification):
[0157] The core architectural concept of this system is to "limit the LLM to function only as a high-level logical planner, rather than a haphazard arithmetic calculator." Since enterprise-level files can reach 50MB or even exceed 100,000 lines, the system strictly adheres to the following compressed JSON template structure when interacting with the LLM to assemble input prompts:
[0158] {
[0159] "system_instruction": "You are a rigorous data analysis and SQL planning engine. Directly answering questions or fabricating data is prohibited. Please strictly adhere to the Table Schema provided below to generate standard DuckDB SQL statements that accurately answer user questions."
[0160] "table_schema": [
[0161] {
[0162] "table_name": "sales_table_1",
[0163] "row_count": 12000,
[0164] "columns": [
[0165] {"name": "date", "type": "date"},
[0166] {"name": "customer", "type": "string"},
[0167] {"name": "Amount_Yuan", "type": "float"}
[0168] ],
[0169] "sample_rows": [
[0170] {"Date": "2024-01-01", "Client": "Company A", "Amount in Yuan": 1,000,000},
[0171] {"Date": "2024-01-02", "Client": "Company B", "Amount in Yuan": 800,000} ]
[0173] }
[0174] ],
[0175] "user_query": "Calculate the total sales revenue for each region and identify the top three regions with the highest sales revenue."
[0176] }
[0177] Technical Results: By using this miniaturized representation structure, transmitting only the schema skeleton and a very small number of sample_rows data probes, the system successfully reduced the text processing load of large language models from O(N) (where N is the total number of data rows that can easily reach tens of thousands) to a constant level of O(1). This not only significantly reduces API call costs but also completely solves the inevitable problems of attention forgetting and computational illusion that arise when processing extremely long texts.
[0178] Local shadow database (DuckDB) acceleration mechanism:
[0179] For SQL execution plans generated by large models (e.g., SELECT region, SUM(amount_yuan) FROM sales_table_1 GROUP BY region ORDER BY 2 DESC LIMIT 3), the system does not rely on external heavyweight database middleware, but instead dynamically starts an embedded DuckDB instance directly in the same process memory of the processing server.
[0180] Zero-copy high-speed mapping: Traditional relational databases typically require cumbersome processes such as table creation, data file export and import (significant I / O overhead), and index building when handling such ad-hoc queries, which are time-consuming and disk-intensive. This system leverages DuckDB's native support for columnar storage and vectorized execution to directly register the data matrix (such as Pandas DataFrame or Apache Arrow format) residing in memory through the pre-cleaning pipeline as a virtual table in the database with zero copying.
[0181] The engine instantly executes the planned SQL code, supporting not only extremely fast basic queries, but also seamless support for complex JOIN operations across sub-tables and in-depth window function aggregation analysis.
[0182] The system captures the execution results and formats them into a standard JSON data packet for return. Crucially, the return packet not only contains the result data but also explicitly appends the complete SQL statement generated and successfully executed by the LLM. This endows the system with unparalleled "interpretability" and "tamper-proof auditing capabilities," allowing both end users and front-end caller systems to clearly and transparently understand how the underlying statistical metrics were precisely executed.
[0183] In summary, the beneficial effects of this application are as follows:
[0184] Significantly enhanced anti-interference and generalization capabilities: Overcoming the technical bottleneck of traditional table parsers that can only handle the ideal state of "strictly regular and clean data". Through pioneering matrix scanning and multi-factor feature scoring algorithms, the system can highly adaptively parse, segment, and normalize complex, heterogeneous, header-off, and out-of-order non-standard Excel tables commonly found in Chinese enterprise scenarios.
[0185] The computational accuracy and precision approach 100%: an innovative heterogeneous processing architecture was established, in which "LLM dominates semantic task planning and an embedded OLAP database takes over large-scale numerical computation." By leveraging its strengths and avoiding its weaknesses, the inherent "illusion generation" and "logical discontinuity" phenomena of large parameter models when handling mathematical addition, subtraction, multiplication, and division operations and massive contextual location retrieval were completely eliminated.
[0186] Breakthrough processing performance and absolute data security: Even massive Excel report files with millions of data rows and tens of megabytes in size can be handled by large language models by clever prompt engineering. The model only needs to read and analyze the core metadata structure (schema) of a few hundred tokens. This drastically reduces the processing response cycle, which originally took minutes or even failed due to timeouts, to seconds. At the same time, massive amounts of detailed data containing trade secrets are always kept locked in a secure sandbox within the local computing domain, ensuring financial-grade user data privacy.
[0187] Excellent User Experience (UX) Design for Cost Reduction and Efficiency Improvement: By proactively generating a global data asset summary and intelligently recommended question-and-answer sequences after the parsing cycle, the system significantly lowers the data BI analysis threshold for non-technical personnel. This not only effectively resolves the predicament of users facing a cold start platform who "don't know where to start or can't accurately describe fields," but also avoids invalid question requests beyond the scope of the current table data from the outset, greatly improving the business conversion rate and commercial usability of AI products in actual enterprise engineering implementation.
[0188] A third aspect of this application provides a computer-readable storage medium including a program for structuring non-standard table data and intelligent question answering based on a large language model. When executed by a processor, the program implements the steps of the method described above.
[0189] This application discloses a method, system, and medium for structuring non-standard tabular data and intelligent question answering based on a large language model. The method involves receiving a non-standard tabular file, performing automatic structured parsing to obtain standardized sub-table data, constructing a lightweight metadata description file, integrating all lightweight metadata description files to obtain full standardized data, and loading the full standardized data into a local embedded OLAP database engine. Based on the lightweight metadata description file, parsing and data mapping processing are performed to generate a global data asset summary and guiding prompts. The method also involves receiving user natural language query information, matching multiple guiding prompt combinations based on the user's natural language query information, inputting the lightweight metadata description file and guiding prompt combinations into a large language model to generate structured execution plan information, executing the structured execution plan information according to the local embedded OLAP database engine, and generating question-and-answer feedback results. By analyzing the guiding prompt combinations through the large language model, the method accurately obtains question-and-answer feedback results, thereby improving question-and-answer accuracy.
[0190] In the several embodiments provided in this application, it should be understood that the disclosed devices and methods can be implemented in other ways. The device embodiments described above are merely illustrative. For example, the division of units is only a logical functional division, and in actual implementation, there may be other division methods, such as: multiple units or components can be combined, or integrated into another system, or some features can be ignored or not executed. In addition, the coupling, direct coupling, or communication connection between the various components shown or discussed can be through some interfaces, and the indirect coupling or communication connection between devices or units can be electrical, mechanical, or other forms.
[0191] The units described above as separate components may or may not be physically separate. The components shown as units may or may not be physical units. They may be located in one place or distributed across multiple network units. Some or all of the units may be selected to achieve the purpose of this embodiment according to actual needs.
[0192] In addition, each functional unit in the various embodiments of this application can be integrated into one processing unit, or each unit can be a separate unit, or two or more units can be integrated into one unit; the integrated unit can be implemented in hardware or in the form of hardware plus software functional units.
[0193] Those skilled in the art will understand that all or part of the steps of the above method embodiments can be implemented by hardware related to program instructions. The aforementioned program can be stored in a readable storage medium. When the program is executed, it performs the steps of the above method embodiments. The aforementioned storage medium includes various media capable of storing program code, such as mobile storage devices, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.
[0194] Alternatively, if the integrated units described above in this application are implemented as software functional modules and sold or used as independent products, they can also be stored in a readable storage medium. Based on this understanding, the technical solutions of the embodiments of this application, or the parts that contribute to the prior art, can be embodied in the form of a software product. This software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the methods described in the various embodiments of this application. The aforementioned storage medium includes various media capable of storing program code, such as mobile storage devices, ROM, RAM, magnetic disks, or optical disks.
Claims
1. A method for structuring non-standard tabular data and intelligent question answering based on a large language model, characterized in that, include: It receives non-standard table files, performs automatic structured parsing on the tables, and obtains standardized sub-table data. Based on the standardized sub-table data, a lightweight metadata description file is constructed, all lightweight metadata description files are integrated to obtain full standardized data, and the full standardized data is loaded into the local embedded OLAP database engine. The process of loading all standardized data into the local embedded OLAP database engine includes: obtaining data from each standardized sub-table, extracting the table name, column name set, column data type, total number of rows, and sampled data of each sub-table to obtain a lightweight metadata description file; creating physical tables based on the lightweight metadata description file; obtaining detailed data from the physical tables, cleaning and processing the detailed data to obtain all standardized data; and loading the all standardized data into the local embedded OLAP database engine. The lightweight metadata description file is parsed and data mapping is performed to generate a global data asset summary and guiding prompts. Receive user natural language query information, match multiple guidance prompt word combinations based on user natural language query information, input the lightweight metadata description file and guidance prompt word combinations into the large language model, and generate structured execution plan information; Based on the structured execution plan information executed by the local embedded OLAP database engine, question and answer feedback results are generated. The methods for obtaining standardized sub-table data include: Obtain non-standard table files and extract the header features of the table files; Calculate the comprehensive confidence score of the header features for the first N rows of the table file, and dynamically locate the starting row of the physical header. Starting from the physical table header, a two-dimensional matrix connected component algorithm is used to identify and cut out multiple independent sub-table regions; Perform column data type inference, null value normalization, unit stripping and value restoration, and date format alignment on all sub-table areas to obtain standardized sub-table data.
2. The method for structuring non-standard tabular data and intelligent question answering based on a large language model according to claim 1, characterized in that, The formula for calculating the comprehensive confidence score of the header feature is as follows: Score(Row_i) =α*P_text +β*P_unique +γ*C_length; P_text: The percentage of non-numeric or date data types in the valid cells of the i-th row; P_unique: The deduplication rate of the non-empty cells in the i-th row; C_length: The maximum number of columns that can be spanned to contain consecutive non-empty cells horizontally; α, β, γ are empirical weight parameters extracted from a large number of samples during pre-training; The formula for calculating the deduplication rate is as follows: P_unique=count(distinct_values) / count(all_values); Here, distinct_values is the set of unique values in all non-empty cells in the i-th row after removing duplicates; all_values is the set of values in all non-empty cells in the i-th row; and count() is a counting function used to count the number of elements in the set.
3. The method for structuring non-standard tabular data and intelligent question answering based on a large language model according to claim 2, characterized in that, Perform column data type inference, null value normalization, unit stripping and value restoration, and date format alignment on all sub-table ranges to obtain standardized sub-table data, specifically including: Retrieve data from each independent sub-table and extract a sample of valid data rows for each column; Analyze the standard underlying data type information of the valid data row samples in the column based on the majority voting mechanism and preset type matching rules; Based on the standard underlying data type information, perform null value normalization processing, identify and replace implicit null values or placeholders, and obtain the standard underlying data type with implicit null values or placeholders removed. Based on a pre-defined regular expression knowledge base, business units of standard underlying data types are identified. Business units that do not meet the set conditions are stripped of their units and their values restored to obtain standard business units. Retrieve the mixed date expressions in the same column, and use the built-in date parser to map the mixed date expressions into a standard date format to obtain date format aligned data. Standardized sub-table data is obtained by removing implicit null values or placeholders from the underlying data type, standard business units, and date format alignment data.
4. The method for structuring non-standard tabular data and intelligent question answering based on a large language model according to claim 3, characterized in that, Column data type analysis based on majority voting mechanism includes: Extract the first 100 rows of non-empty data from the target column as sample data; If more than 80% of the samples match the numeric regular expression or contain a preset numeric unit, then the valid data row samples in the corresponding column will be forcibly labeled as numeric type.
5. A non-standard tabular data structuring and intelligent question-answering system based on a large language model, characterized in that, The system includes a memory and a processor. The memory contains a program for non-standard tabular data structuring and intelligent question answering based on a large language model. When the program for non-standard tabular data structuring and intelligent question answering based on a large language model is executed by the processor, it performs the following steps: It receives non-standard table files, performs automatic structured parsing on the tables, and obtains standardized sub-table data. Based on the standardized sub-table data, a lightweight metadata description file is constructed, all lightweight metadata description files are integrated to obtain full standardized data, and the full standardized data is loaded into the local embedded OLAP database engine. The process of loading all standardized data into the local embedded OLAP database engine includes: obtaining data from each standardized sub-table, extracting the table name, column name set, column data type, total number of rows, and sampled data of each sub-table to obtain a lightweight metadata description file; creating physical tables based on the lightweight metadata description file; obtaining detailed data from the physical tables, cleaning and processing the detailed data to obtain all standardized data; and loading the all standardized data into the local embedded OLAP database engine. The lightweight metadata description file is parsed and data mapping is performed to generate a global data asset summary and guiding prompts. Receive user natural language query information, match multiple guidance prompt word combinations based on user natural language query information, input the lightweight metadata description file and guidance prompt word combinations into the large language model, and generate structured execution plan information; Based on the structured execution plan information executed by the local embedded OLAP database engine, question and answer feedback results are generated. The methods for obtaining standardized sub-table data include: Obtain non-standard table files and extract the header features of the table files; Calculate the comprehensive confidence score of the header features for the first N rows of the table file, and dynamically locate the starting row of the physical header. Starting from the physical table header, a two-dimensional matrix connected component algorithm is used to identify and cut out multiple independent sub-table regions; Perform column data type inference, null value normalization, unit stripping and value restoration, and date format alignment on all sub-table areas to obtain standardized sub-table data.
6. The non-standard tabular data structuring and intelligent question-answering system based on a large language model according to claim 5, characterized in that, The formula for calculating the comprehensive confidence score of the header feature is as follows: Score(Row_i) =α*P_text +β*P_unique +γ*C_length; P_text: The percentage of non-numeric or date data types in the valid cells of the i-th row; P_unique: The deduplication rate of the non-empty cells in the i-th row; C_length: The maximum number of columns that can be spanned to contain consecutive non-empty cells horizontally; α, β, γ are empirical weight parameters extracted from a large number of samples during pre-training; The formula for calculating the deduplication rate is as follows: P_unique=count(distinct_values) / count(all_values).
7. A computer-readable storage medium, characterized in that, The computer-readable storage medium includes a program for structuring non-standard tabular data and intelligent question answering based on a large language model. When the program is executed by a processor, it implements the steps of the method for structuring non-standard tabular data and intelligent question answering based on a large language model as described in any one of claims 1 to 4.
Citation Information
Patent Citations
Table knowledge storage method and device in intelligent question and answer scene, medium and equipment
CN118332065A
LLM-based complex database query method, apparatus and device, and storage medium
CN119201971A
