Data analysis system and method based on artificial intelligence

Abstract

The invention discloses a data analysis system and method based on artificial intelligence, belongs to the technical field of artificial intelligence data analysis, and solves the problem that in the prior art, the system is low in efficiency of importing Excel large-scale data, and the system comprises a data processing module used for receiving an Excel file uploaded by a user; the data preprocessing module is used for preprocessing the data read in the Excel file, removing abnormal data and converting the data into a format input by an AI model; the AI analysis module is used for calling an AI pre-training model and carrying out natural language analysis on the processed data; the result display module is used for displaying an AI analysis result to a user; an Apache POI library is carried in the data access module, and the Apache POI library is used for reading data in the file to a system memory, so that the technical effect of improving the reading and importing efficiency of the ai system on the Excel large file is achieved.

Description

Technical Field

[0001] The present invention belongs to the technical field of artificial intelligence data analysis, and particularly relates to a data analysis system and method based on artificial intelligence. Background Art

[0002] In today's digital age, enterprises and organizations have accumulated a large amount of data stored in Excel files. These data contain information in multiple aspects such as business operations, market trends, and customer feedback, and are of great value to the decision-making and development of enterprises. However, traditional data processing and analysis methods mainly rely on structured queries and simple statistical analysis, and it is difficult to deeply mine the semantic information and potential value in the data.

[0003] At the same time, existing Excel data processing tools are inefficient in processing complex formats and large-scale data, and lack the ability to perform natural language understanding and analysis on data. Although significant progress has been made in the field of natural language processing in artificial intelligence technology, such as text classification, sentiment analysis, and semantic understanding, it has not been effectively combined with Excel data processing. Therefore, a new technical solution is needed to achieve efficient import and intelligent analysis of Excel data. Summary of the Invention

[0004] Aiming at the problem of low import efficiency of large-scale Excel data in the existing system, the present invention provides a data analysis system and method based on artificial intelligence, and its purpose is to improve the reading and import efficiency of the AI system for large Excel files.

[0005] The technical solution adopted by the present invention is as follows: A data analysis system based on artificial intelligence, comprising: A data processing module: used to receive the Excel file uploaded by the user; A data preprocessing module: used to preprocess the data read from the Excel file, remove abnormal data, and convert the data into the format input to the AI model; An AI analysis module: used to call the AI pre-trained model to perform natural language analysis on the processed data; A result display module: used to display the results of the AI analysis to the user; The data access module is equipped with an ApachePOI library, and the ApachePOI library is used to read the data in the file into the system memory, and the ApachePOI library reads the Excel file in a block-concurrent manner through the SAX event-driven method.

[0006] By adopting the above solution, through the Apache POI library of JAVA, the rapid and accurate reading of Excel files is achieved, and the import and processing of large files are supported, improving the efficiency of data access.

[0007] The AI analysis module is equipped with the spring-ai library and the ollama connection library. A data analysis method based on artificial intelligence, characterized by including: Step A: The user uploads an Excel file through the system interface, and the system stores the data in the Excel into a data structure in memory; Step B: The system cleans, converts the format, creates a temporary database table, and extracts features from the data in memory; Step C: Convert the natural language input by the user into an SQL statement for execution and return the result; Step D: Perform secondary processing based on the returned result and display the result after secondary processing to the user.

[0008] The specific steps of step A include: Step A1: The user uploads an Excel file through the system interface; Step A2: The system selects the corresponding workbook type according to the file extension through the workbooks of ApachePOI; Step A3: Read the data in the Excel file row by row and store it in a data structure in memory.

[0009] The specific steps of step B include: Step B1: Traverse the data in memory, check whether each data item is a null value, a duplicate value, or an abnormal value, and make corresponding processing; Step B2: Perform encoding processing on the text data; Step B3: Generate a temporary database table from the Excel data, and multiple tables are automatically associated; Step B4: For text data, use natural language processing technology to extract keywords and themes, and calculate the statistical features of the data.

[0010] The specific steps of step B1 include: If the value is a null value, delete the row of data or perform filling processing; if the value is a duplicate value, only keep one copy; if the value is an abnormal value, correct or delete it according to the business rules.

[0011] The specific steps of step C are: Step C1: According to the user's original question, call the AI server to generate an SQL query string; Step C2: Perform a whitelist check based on the SQL generated in Step C1, and only allow query statements starting with SELECT to be executed; Step C3: If the SQL starts with SELECT, execute the SQL statement and query the result set as a List <map>Return in this form; if the SQL statement is not a SELECT statement, interrupt the subsequent process.

[0012] The specific steps of step D are as follows: For chart data, call the JavaScript icon library for drawing; for report data, generate HTML or PDF reports; for table data, display it through HTML tables.

[0013] The specific steps of step A3 are as follows: Step A31: Traverse each worksheet in Excel; Step A32: Create a ComletableFuture asynchronous task for each worksheet and submit all asynchronous tasks to the executorService thread pool for execution; Step A33: Process the worksheet through the processSheet method.

[0014] The specific steps of step C1 are as follows: Step C11: Collect the user's question and save the question to a variable; Step C12: Set the media type entered by the user and use a preset text template as the basis for the question; Step C13: Inject the user's natural language question and DDL into the text template to form a prompt; Step C14: Send the prompt to the AI and wait for the AI to generate SQL.

[0015] In summary, due to the adoption of the above technical solutions, the beneficial effects of the present invention are as follows: 1. Utilizing the ApachePOI library of Java, it realizes the fast and accurate reading of Excel files, supports the import and processing of large files, and improves the efficiency of data access.

[0016] 2. Combining AI natural language processing technology, it can deeply explore the semantic information and potential value in Excel data, providing more comprehensive and accurate analysis results.

[0017] 3. Adopting modern front-end technologies, it presents the analysis results to users in an intuitive and easy-to-understand manner, facilitating users to make decisions and analyze.

[0018] 4. The system architecture design is flexible, supports the integration and extension of different types of AI models, and can adapt to the needs of different business scenarios. Description of the Drawings

[0019] The present invention will be described by way of examples with reference to the accompanying drawings, where: Figure 1 is the workflow diagram of this system; Figure 2 is the schematic diagram of natural language input of this system; Figure 3 is the schematic diagram of statistical table generation of this system. Detailed implementation manners

[0020] All features disclosed in this specification, or steps in all methods or processes disclosed, except for mutually exclusive features and / or steps, can be combined in any way.

[0021] The following combines Figure 1 to make a detailed description of the present invention. Embodiment

[0022] A data analysis system based on artificial intelligence, comprising: Data processing module: used to receive the Excel file uploaded by the user; Data preprocessing module: used to preprocess the data read from the Excel file, remove abnormal data and convert the data into the format input for the AI model; AI analysis module: used to call the AI pre-trained model to perform natural language analysis on the processed data; Result display module: used to display the results of AI analysis to the user; The data access module is equipped with the ApachePOI library, and the ApachePOI library is used to read the data in the file into the system memory.

[0023] The AI analysis module is equipped with the spring-ai library and the ollama connection library A data analysis method based on artificial intelligence, characterized by comprising: Step A: The user uploads an Excel file through the system interface, and the system stores the data in the Excel in a data structure in the memory; Step B: The system cleans, converts the format, creates a database temporary table and extracts features from the data in the memory; Step C: Convert the natural language input by the user into an SQL statement for execution and return the result; Step D: Perform secondary processing on the returned result and display the secondary processed result to the user.

[0024] The specific steps of step A include: Step A1: The user uploads an Excel file through the system interface; Step A2: The system selects the corresponding workbook type according to the file extension through the workbooks of ApachePOI; Step A3: Read the data in the Excel file line by line and store it in a data structure in memory.

[0025] The specific steps of step B include: Step B1: Traverse the data in memory, check whether each data item is a null value, a duplicate value or an outlier, and make corresponding processing; Step B2: Perform encoding processing on the text data; Step B3: Generate a database temporary table from the Excel data, and automatically associate multiple tables; Step B4: For the text data, use natural language processing technology to extract keywords and themes, and calculate the statistical features of the data.

[0026] The specific steps of step B1 include: if the value is a null value, delete the row of data or perform filling processing; if the value is a duplicate value, only keep one copy; if the value is an outlier, correct or delete it according to business rules.

[0027] The specific steps of step C are: Step C1: According to the user's original question, call the AI server to generate an SQL query string; Step C2: According to the SQL generated in step C1, perform a whitelist check, and only allow query statements starting with SELECT to be executed; Step C3: If the SQL starts with SELECT, execute the SQL statement and query the result set as a List <map>Return in the form; if the SQL statement is not a SELECT statement, interrupt the subsequent process.

[0028] The specific steps of step D are as follows: For chart data, call the JavaScript chart library for drawing; for report data, generate HTML or PDF reports; for table data, display it through an HTML table.

[0029] The specific steps of step A3 are as follows: Step A31: Traverse each worksheet in Excel; Step A32: Create a CompletableFuture asynchronous task for each worksheet and submit all asynchronous tasks to the executorService thread pool for execution; Step A33: Process the worksheet through the processSheet method.

[0030] In actual use, pre-install the Java Development Kit (JDK) in the system. In this embodiment, use JDK 17 and above versions to build a Java development environment, and then reference the relevant dependencies of Apache POI in the project and manage them through Maven or Gradle; For the AI natural language processing tool, in this embodiment, install the spring-ai library and the ollama connection library, download the required pre-trained models, and use mysql or sqlserver as the middle layer of the database to handle statistical requirements.

[0031] Among them, the processing method of processSheet is: (1) Obtain the table name: Obtain the worksheet name and convert it into a legal database table name; (2) Obtain the column names: Obtain the first row of the worksheet, traverse each cell, extract the column names and legalize them; (3) Infer the column types: In this embodiment, default all column types to VARCHAR(255); (4) Create a table: Call dropTableIfExists to delete the existing table with the same name; construct the CREATE TABLE statement (DDL), use the extracted column names and types; execute the DDL statement through JdbcTemplate to create a table; finally, write the DDL statement to a file; (5) Traverse all data rows in the worksheet starting from the second row, process each cell according to its type (number, Boolean value, string, formula, etc.), and convert it into a Java object; construct an INSERT statement and use batchUpdate to batch insert all data to improve efficiency.

[0032] In this embodiment, when reading a large Excel file, POI's SXSSFWorkbook write cache technology is used to segment and cache the read data and recycle it. The specific process includes: 1. Segment processing: (1) Batch reading: read 500 rows from the source worksheet each time and create corresponding rows in the target worksheet; (2) Cell copy: copy data row by row and cell by cell; (3) Memory release: After processing a batch of data, the JVM is prompted to recycle objects that are no longer in use.

[0033] 2. Memory Mapping and Caching (1) Check whether the file fragment at the specified location has been cached; (2) If the cache misses, use FileChannel.map to map the fragment at the specific location of the file into memory; (3) Store the mapping results in memoryCache and allow the JVM to automatically recycle when memory is insufficient.

[0034] 3. Cache monitoring and cleaning (1) After processing each worksheet, check the memory usage; (2) If the usage rate exceeds 80%, clean the cache and trigger GC; specifically, traverse the memoryCache and remove weak references that have been reclaimed by GC.

[0035] Among them, memoryCache is a memory-mapped cache used to temporarily store memory-mapped data of file fragments to improve the reading efficiency when processing large files; 4. Write to target file and release resources (1) Write the processed data in SXSSWorkbook to the target file; (2) Ensure that all resources are closed in the finally block; (3) Call targetWorkbook.dispose to delete temporary files and free up disk space.

[0036] Among them, the SXSSWorkbook stores all the data that is ultimately required. The SXSSWorkbook will combine the 100 rows of data retained in memory with the historical data in the disk temporary file to form complete data.

[0037] The above four steps are the technology for copying large Excel files into the memory of this system through POI. By means of segmented caching and data recycling, the phenomenon of memory overflow is avoided. In this embodiment, several thresholds are set, specifically including: The maximum number of rows retained by the SXSSF workbook in memory is 100 rows, and the excess is written to disk; The maximum number of file segments cached is 10; Cleaning is triggered when the cache quantity or memory usage rate reaches 80%. The above values are the specific values selected in this embodiment and can be adjusted according to actual usage.

[0038] In this embodiment, a data intermediate layer is set up to automatically complete the data format conversion process from reading by POI to the input tensor of AI, that is, it can efficiently convert the user's natural language question into SQL, greatly improving the degree of end-to-end data processing automation. Its specific working process is as follows: (1) Collect the user's question and save the question to a variable; (2) Set the media type of the user input and use a preset text template as the basis for the question; (3) Inject the user's natural language question and DDL into the text template to form a prompt; (4) Send the prompt to AI and wait for AI to generate SQL.

[0039] Subsequently, the system can query the actual database through the generated SQL to obtain the results that the user needs to query. Embodiment

[0040] In this embodiment, a specific Excel containing a list of persons in charge and the number of vehicles is input. Multiple databases are generated according to the table headers and the used model qwen2.5:32b, and DDL is returned; At this time, the system will include these tables and record the record id of this conversation, and perform natural language statistics on the system. In this embodiment, when asking the system "How many vehicles does each person in charge of the Communication Construction Division have", the system automatically processes the above natural language and conducts data statistics. Its process is as Figure 2 shown.

[0041] Send a request to the system to generate a statistical table based on the information. In this embodiment, a bar chart is used as an example. The completed bar chart of the statistics is as Figure 3 shown.

[0042] The above-described embodiments merely represent specific implementation manners of the present application. The description is relatively specific and detailed, but it should not be construed as a limitation on the protection scope of the present application. It should be noted that for those of ordinary skill in the art, without departing from the concept of the technical solution of the present application, several modifications and improvements can still be made, and these all fall within the protection scope of the present application.< / map> < / map>

Claims

1. An artificial intelligence-based data analysis system, characterized in that, It includes: Data access module: Used to receive the Excel files uploaded by users; Data preprocessing module: Used to preprocess the data read from the Excel files, remove abnormal data, and convert the data into the format for input to the AI model; AI analysis module: Used to call the AI pre-trained model to perform natural language analysis on the processed data; Result display module: Used to display the results of AI analysis to users; The data access module is equipped with the ApachePOI library, and the ApachePOI library is used to read the data in the file into the system memory. The ApachePOI library reads the Excel file in chunks and concurrently through the SAX event-driven method.

2. The data analysis system based on artificial intelligence according to claim 1, wherein The AI analysis module is equipped with the spring-ai library and the ollama connection library.

3. A method of applying the artificial intelligence-based data analysis system of claim 1 or 2, characterized in that, It includes: Step A: The user uploads an Excel file through the system interface, and the system stores the data in the Excel in the data structure in the memory; Step B: The system cleans, converts the format, creates a temporary database table, and extracts features from the data in the memory; Step C: Convert the natural language input by the user into an SQL statement for execution and return the result; Step D: Perform secondary processing based on the returned result and display the result after secondary processing to the user.

4. The data analysis system based on artificial intelligence according to claim 3, wherein, The specific steps of Step A include: Step A1: The user uploads an Excel file through the system interface; Step A2: The system selects the corresponding workbook type according to the file extension through the workbooks of ApachePOI; Step A3: Read the data in the Excel file row by row and store it in the data structure in the memory.

5. The data analysis system based on artificial intelligence according to claim 3, characterized in that, The specific steps of Step B include: Step B1: Traverse the data in the memory, check whether each data item is a null value, a duplicate value, or an abnormal value, and make corresponding processing; Step B2: Perform encoding processing on the text data; Step B3: Generate a temporary database table from the Excel data, and multiple tables are automatically associated; Step B4: For text data, use natural language processing technology to extract keywords and themes, and calculate the statistical features of the data.

6. The data analysis system based on artificial intelligence according to claim 5, characterized in that The specific steps of Step B1 include: If the value is a null value, delete the row of data or perform filling processing; if the value is a duplicate value, only keep one copy; if the value is an abnormal value, correct or delete it according to the business rules.

7. The data analysis system based on artificial intelligence according to claim 3, wherein, The specific steps of Step C are: Step C1: According to the user's original question, call the AI server to generate an SQL query string; Step C2: Perform whitelist verification on the SQL generated in Step C1, and only allow the execution of query statements starting with SELECT; Step C3: If the SQL starts with SELECT, execute the SQL statement and query the result set as a List <map>Return in form; < / map> If the SQL is not a SELECT statement, interrupt the subsequent process.

8. The data analysis system based on artificial intelligence according to claim 3, wherein The specific steps of Step D are: For chart data, call the JavaScript icon library for drawing; for report data, generate an HTML or PDF report; for table data, display it through an HTML table.

9. The data analysis system based on artificial intelligence according to claim 4, wherein The specific steps of Step A3 are: Step A31: Traverse each worksheet in the Excel; Step A32: Create a CompletableFuture asynchronous task for each worksheet and submit all asynchronous tasks to the executorService thread pool for execution; Step A33: Process the worksheet through the processSheet method.

10. The data analysis system based on artificial intelligence according to claim 7, characterized in that, The specific steps of step C1 are as follows: Step C11: Collect the user's question and save the question in a variable; Step C12: Set the media type entered by the user and use a preset text template as the basis for the question; Step C13: Inject the user's natural language question and DDL into the text template to form a prompt; Step C14: Send the prompt to the AI and wait for the AI to generate SQL.

Images