Automatic Excel analysis result visualization method based on LLM natural language processing

Abstract

The invention discloses an automatic Excel analysis result visualization method based on LLM natural language processing. The method comprises the following steps: uploading an Excel file and proposing an analysis problem; a header and data of the Excel are analyzed; data cleaning; storing the cleaned data into an embedded database; generating a corresponding SQL query statement for the analysis problem by using LLM in combination with an NL2SQL technology; querying an embedded database; judging whether the SQL statement query result is correct or not, and if not, regenerating the SQL statement for re-query; and S8, the query data is rendered into a chart to be displayed. The invention provides a brand new solution, namely an automatic Excel file analysis and query result visualization method based on a large language model (LLM), a natural language processing (NLP) technology and an NL2SQL. The Excel data statistical process is simplified, and the reliability and usability of the system can be greatly improved.

Description

Technical Field

[0001] The present invention relates to the technical field of automatic analysis of data stored in an Excel spreadsheet, and in particular to an automatic Excel analysis result visualization method based on LLM natural language processing. Background Art

[0002] With the advent of the big data era, enterprises and individual users are facing increasing demands for data management and analysis. Traditional data processing methods often rely on professional IT technicians who need to have deep programming knowledge to effectively operate database systems. However, this approach is not only time-consuming and laborious, but also almost unfeasible for ordinary users who do not have the corresponding skills.

[0003] As an effective means of converting natural language into SQL queries, NL2SQL technology has shown great potential in data query automation. The task of natural language to structured query language (NL2SQL) is to convert natural language into the database's structured query language (SQL). This technology enables users to interact with the database through natural language without knowing SQL.

[0004] In addition, traditional data storage formats, such as Excel tables, are very difficult to query directly using SQL due to their unstructured nature. Currently, the market lacks a complete solution that can seamlessly connect natural language input, Excel data parsing, SQL generation, and visualization of query results.

[0005] Therefore, in order to solve these problems, it is necessary to provide an automated Excel file parsing and query result visualization method combining LLM with NL2SQL technology to overcome the shortcomings of the existing technology. Summary of the invention

[0006] The purpose of the present invention is to avoid the shortcomings of the prior art and provide an automated Excel parsing result visualization method based on LLM natural language processing, which can efficiently process data in Excel files, so that even non-technical users can easily perform complex data query and analysis, and can improve reliability and ease of use.

[0007] The purpose of the present invention is achieved through the following technical measures.

[0008] This paper provides an automated Excel analysis result visualization method based on LLM natural language processing, which is carried out through the following steps: S1, upload Excel files and ask analysis questions; S2, parses Excel header and data; S3, performing data cleaning on the data to obtain cleaned data; S4, storing the cleaned data into the embedded database; S5, using LLM combined with NL2SQL technology to generate the corresponding SQL query statement for the analysis problem; S6, query the embedded database; S7, judging whether the query result of the SQL statement is correct, and if not correct, regenerating the SQL statement and re-querying by using self-reflection; S8, the corresponding data obtained by the query is rendered into a chart for display.

[0009] Preferably, the above-mentioned automated Excel analysis result visualization method based on LLM natural language processing receives analysis questions expressed by users in natural language as proposed analysis questions.

[0010] Preferably, in the above-mentioned automated Excel parsing result visualization method based on LLM natural language processing, in S2, the uploaded Excel file is read through pandas to extract all the header information and data content in the Excel file.

[0011] Preferably, in the above-mentioned method for visualizing automated Excel analysis results based on LLM natural language processing, the data cleaning process includes operations of filling missing values ​​and correcting outliers.

[0012] Preferably, in the above-mentioned method for visualizing automated Excel parsing results based on LLM natural language processing, the embedded database is a DuckDB embedded database.

[0013] Preferably, the above-mentioned automated Excel parsing result visualization method based on LLM natural language processing, S5 specifically converts the natural language questions raised by users into corresponding SQL query statements through prompt engineering using a large language model (LLM) combined with natural language to SQL (NL2SQL) technology.

[0014] Large Language Model (LLM), also known as Large Language Model, is an artificial intelligence model designed to understand and generate human language. They are trained on large amounts of text data and can perform a wide range of tasks, including text summarization, translation, sentiment analysis, and more. LLMs are characterized by their large size, which helps them learn complex patterns in language data. These models are usually based on deep learning architectures, which helps them achieve impressive performance on various NLP tasks.

[0015] Preferably, the above-mentioned automated Excel parsing result visualization method based on LLM natural language processing, S6, queries the embedded database, specifically: according to the generated SQL query statement, sends a query request to the embedded database, the embedded database executes the SQL statement and returns the query result.

[0016] Preferably, in the above-mentioned automated Excel analysis result visualization method based on LLM natural language processing, the chart in S8 is a bar chart, a line chart or a pie chart, etc.

[0017] The present invention provides an automated Excel analysis result visualization method based on LLM natural language processing, which is performed through the following steps: S1, uploading an Excel file and raising an analysis question; S2, parsing the Excel header and data; S3, cleaning the data to obtain the cleaned data; S4, storing the cleaned data in an embedded database; S5, generating a corresponding SQL query statement for the analysis question using LLM combined with NL2SQL technology; S6, querying the embedded database; S7, judging whether the SQL statement query result is correct, and if not, regenerating the SQL statement and re-querying using self-reflection; S8, rendering the corresponding data obtained by querying into a chart for display. The present invention provides a brand-new solution, namely an automated Excel file parsing and query result visualization method based on a large language model (LLM), natural language processing (NLP) technology and NL2SQL. Simplify the Excel data statistics process: by introducing LLM combined with NL2SQL technology, users can directly use natural language to express query intentions, and efficiently analyze and count the data of Excel files. By using self-reflection to handle possible errors when NL2SQL generates SQL statements, the reliability and usability of the system can be greatly improved. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] The present invention is further described with reference to the accompanying drawings, but the contents in the accompanying drawings do not constitute any limitation to the present invention.

[0019] Figure 1It is a flow chart of an automated Excel analysis result visualization method based on LLM natural language processing of the present invention.

[0020] Figure 2 It is a schematic diagram showing that the corresponding data obtained according to the query is rendered into a chart in Example 2 of the present invention. DETAILED DESCRIPTION

[0021] The present invention will be further described with reference to the following examples.

[0022] Example 1 An automated Excel parsing result visualization method based on LLM natural language processing, such as Figure 1 As shown, proceed as follows: S1, upload Excel file and raise analysis questions. The user first selects the Excel file to be analyzed and uploads it to the server through the system. At the same time, the user expresses their analysis needs or questions in natural language, such as "find the product with the highest sales volume". The analysis question expressed by the user in natural language is received as the proposed analysis question.

[0023] S2, parses Excel header and data.

[0024] Specifically, in S2, the uploaded Excel file is read through pandas to extract all the header information and data content in the Excel file. The header information is used to determine the name and type of the data column for subsequent data processing and query. Each column of the header information is converted into each field of the embedded database data table, and LLM will write SQL statements based on these fields for query in step S5.

[0025] S3, clean the data to obtain cleaned data.

[0026] The data in the Excel table may contain null values ​​or special symbols. Data cleaning is to process some values ​​that cannot be executed by SQL statements to meet the requirements of SQL statements. The data cleaning process includes operations such as filling missing values ​​and correcting outliers to ensure that the data quality meets the analysis requirements. The cleaned data will be more consistent and accurate, which is conducive to subsequent analysis and query.

[0027] S4, stores the cleaned data into the embedded database.

[0028] The cleaned data will be imported into the system's built-in database, which is the DuckDB embedded database. Compared with other embedded databases (such as SQLlite), DuckDB is usually faster than SQLite when processing large data sets and complex queries.

[0029] The advantage of using an embedded database is that it can quickly respond to query requests and also facilitates data management and maintenance.

[0030] S5, the analysis problem is generated by using LLM combined with NL2SQL technology to generate the corresponding SQL query statement.

[0031] S5 specifically converts natural language questions raised by users into corresponding SQL query statements through prompt engineering using large language models (LLM) combined with natural language to SQL (NL2SQL) technology. LLM is responsible for understanding the meaning of natural language, while NL2SQL is responsible for translating it into SQL code that can be executed by the database.

[0032] Large Language Model (LLM), also known as Large Language Model, is an artificial intelligence model designed to understand and generate human language. They are trained on large amounts of text data and can perform a wide range of tasks, including text summarization, translation, sentiment analysis, and more. LLMs are characterized by their large size, which helps them learn complex patterns in language data. These models are usually based on deep learning architectures, which helps them achieve impressive performance on various NLP tasks.

[0033] S6, query the embedded database.

[0034] According to the generated SQL query statement, a query request is sent to the embedded database, and the embedded database executes the SQL statement and returns the query result.

[0035] S7, determining whether the query result of the SQL statement is correct. If not, using self-reflection to regenerate the SQL statement and query again.

[0036] If the query result does not meet expectations, it may be due to a problem with the original SQL statement. At this time, the system starts the self-reflection mechanism to analyze the cause of the error and try to adjust the SQL statement to obtain the correct result again.

[0037] When the current query SQL statement is incorrect, an exception will be captured. The exception contains the cause of the error. The original erroneous SQL and the cause of the error are given to the big model, and the big model will correct the erroneous SQL and rewrite a correct SQL statement for execution.

[0038] S8, the corresponding data obtained by the query is rendered into a chart for display.

[0039] After the query is successful, the system will display the result data in the form of a chart, which is convenient for users to view and analyze the data intuitively. The selection of charts depends on the data characteristics and user preferences, and may include bar charts, line charts, pie charts and other types.

[0040] The present invention provides a new solution based on the automated Excel parsing result visualization method of LLM natural language processing, namely, an automated Excel file parsing and query result visualization method based on large language model (LLM), natural language processing (NLP) technology and NL2SQL. By introducing LLM combined with NL2SQL technology, users can directly use natural language to express query intentions, efficiently analyze and count the data of Excel files, simplify the Excel data statistics process, and make it easy for even non-technical users to perform complex data queries and analyses. By handling errors that may occur when NL2SQL generates SQL statements through self-reflection, the reliability and usability of the system can be greatly improved.

[0041] Example 2 The present invention is explained through a specific example of an automated Excel parsing result visualization method based on LLM natural language processing.

[0042] In this embodiment, the Excel file is shown in Table 1 below, and the columns contained in the Excel file are shown in Table 1.

[0043] Table 1

[0044] The automated Excel analysis result visualization method based on LLM natural language processing in this embodiment is performed through the following steps: S1, upload the Excel file of Table 1 and raise an analysis question. The analysis question is: "The total amount of reimbursement for each department". The analysis question is directly expressed by the user in natural language as the analysis question raised, which is convenient for the user to directly operate and use.

[0045] S2, reads the uploaded Excel file through pandas and extracts all the header information and data content in the Excel file. The header information is used to determine the name and type of the data column for subsequent data processing and query.

[0046] Extract each column of Excel as a data column, the result is as follows: ('Serial Number', 'BIGINT', 'YES', None, None, None) ('Expense Application ID', 'BIGINT', 'YES', None, None, None) ('Title / Specific Matter', 'VARCHAR', 'YES', None, None, None) ('amount / distribution', 'DOUBLE', 'YES', None, None, None) ('Invoice Company', 'VARCHAR', 'YES', None, None, None) ('occurrence date', 'VARCHAR', 'YES', None, None, None) ('Payment Date', 'VARCHAR', 'YES', None, None, None) ('Expense attribution', 'VARCHAR', 'YES', None, None, None) ('project', 'VARCHAR', 'YES', None, None, None) ('Reimbursement person', 'VARCHAR', 'YES', None, None, None) ('property', 'VARCHAR', 'YES', None, None, None).

[0047] S3, clean the data to obtain cleaned data.

[0048] S4, stores the cleaned data into the DuckDB embedded database.

[0049] S5, the analysis problem is generated by using LLM combined with NL2SQL technology to generate the corresponding SQL query statement.

[0050] The analysis problem is "the total amount of reimbursement for each department". LLM combined with NL2SQL technology is used to generate the corresponding SQL query statement. The result is as follows: "SELECT\"Expense attribution\", SUM(\"(amount / distribution) AS tota1-amount EROM\"excel-date\" GROUP BY\"Expense attribution\";".

[0051] S6, query the embedded database. According to the generated SQL query statement, a query request is sent to the embedded database, and the embedded database executes the SQL statement and returns the query result.

[0052] S7, determining whether the query result of the SQL statement is correct. If not, using self-reflection to regenerate the SQL statement and query again.

[0053] S8, the corresponding data obtained by the query is rendered into a chart for display. After the query is successful, the system displays the result data in the form of a chart, which is convenient for users to view and analyze the data intuitively. The final rendered chart is as follows Figure 2 shown.

[0054] The present invention provides a new solution based on the automated Excel parsing result visualization method of LLM natural language processing, namely, an automated Excel file parsing and query result visualization method based on large language model (LLM), natural language processing (NLP) technology and NL2SQL. By introducing LLM combined with NL2SQL technology, users can directly use natural language to express query intentions, efficiently analyze and count the data of Excel files, simplify the Excel data statistics process, and make it easy for even non-technical users to perform complex data queries and analyses. By handling errors that may occur when NL2SQL generates SQL statements through self-reflection, the reliability and usability of the system can be greatly improved.

[0055] Finally, it should be noted that the above embodiments are only used to illustrate the technical solution of the present invention rather than to limit the scope of protection of the present invention. Although the present invention has been described in detail with reference to the preferred embodiments, those skilled in the art should understand that the technical solution of the present invention can be modified or replaced by equivalents without departing from the essence and scope of the technical solution of the present invention.

Claims

1. An automated Excel analysis result visualization method based on LLM natural language processing, characterized in that: Proceed as follows: S1, upload Excel files and ask analysis questions; S2, parses Excel header and data; S3, performing data cleaning on the data to obtain cleaned data; S4, storing the cleaned data into the embedded database; S5, using a large language model combined with NL2SQL technology to generate the corresponding SQL query statement for the analysis problem; S6, query the embedded database; S7, judging whether the query result of the SQL statement is correct, and if not correct, regenerating the SQL statement and re-querying using introspection; S8, the corresponding data obtained by the query is rendered into a chart for display.

2. The method for visualizing automated Excel analysis results based on LLM natural language processing according to claim 1, characterized in that: An analysis question expressed by a user in natural language is received as the proposed analysis question.

3. The method for visualizing automated Excel analysis results based on LLM natural language processing according to claim 1, characterized in that: In S2, the uploaded Excel file is read through pandas to extract all the header information and data content in the Excel file.

4. The method for visualizing automated Excel analysis results based on LLM natural language processing according to claim 3, characterized in that: The data cleaning process includes filling missing values ​​and correcting outliers.

5. The method for visualizing automated Excel analysis results based on LLM natural language processing according to claim 4, characterized in that: The embedded database is the DuckDB embedded database.

6. The method for visualizing automated Excel analysis results based on LLM natural language processing according to claim 5, characterized in that: S5 specifically uses a large language model combined with natural language to SQL technology through prompt word engineering to convert natural language questions raised by users into corresponding SQL query statements.

7. The method for visualizing automated Excel analysis results based on LLM natural language processing according to claim 6, characterized in that: S6, querying the embedded database, specifically: sending a query request to the embedded database according to the generated SQL query statement, and the embedded database executes the SQL statement and returns the query result.

8. The method for visualizing automated Excel analysis results based on LLM natural language processing according to claim 7, characterized in that: The charts in S8 are bar charts, line charts or pie charts.

Images