Intelligent database table structure self-adaption and data import system and method based on Excel

Abstract

The invention provides an intelligent database table structure self-adaption and data import system and method based on Excel, and the system comprises a file uploading and analyzing module which is used for receiving and analyzing an Excel file uploaded by a user, and extracting header information and a data sample; the database configuration management module is used for storing and managing connection configuration information of a plurality of databases; the intelligent table structure inference engine is used for carrying out multi-dimensional statistical analysis based on Excel data samples and automatically inferring an optimal database table structure design; the multi-database dialect adapter is used for converting the general table structure definition into SQL grammar of a specific database; and the transactional execution engine executes table establishment and insertion operations in an atomic transaction mode to ensure data consistency. According to the system and the method, the Excel spreadsheet data can be automatically imported into the database, the database table structure is intelligently inferred and created according to the data characteristics, and the system and the method are particularly suitable for enterprise data migration, data warehouse construction and data analysis preprocessing scenes.

Description

Technical Field

[0001] This invention relates to the field of data processing and database management technology, and more specifically, to an intelligent database table structure adaptation and data import system and method based on Excel. Background Technology

[0002] With the deepening of information technology development, various organizations have accumulated a large amount of business data in Excel format. This data needs to be migrated to a professional database management system to achieve efficient storage, retrieval, and analysis. Current methods for importing Excel data into databases have the following technical shortcomings: 1. Manual SQL writing method: Technicians need to manually analyze the Excel data structure, manually write CREATE TABLE statements to create the table structure, and then write a large number of INSERT statements to insert data. This method is extremely inefficient, has a high error rate, and cannot handle large-scale data.

[0003] 2. Database tool import method: such as using import wizards of tools like MySQL Workbench and Navicat. These tools require the target table to exist beforehand and have a completely matching structure, lacking automatic table creation capabilities. When the table does not exist or the structure does not match, the user needs to return to the database management interface to manually create the table, interrupting the process and making the operation cumbersome.

[0004] 3. Custom script development approach: Writing Python pandas or Java POI scripts for specific projects. While this can solve specific problems, it lacks versatility. Each time a new Excel format or different database type is encountered, the script needs to be redeveloped or modified, resulting in high maintenance costs.

[0005] 4. Existing ETL tools: Professional ETL tools such as Kettle and Talend are powerful, but they are complex to configure, have a steep learning curve, are not user-friendly for non-technical personnel, and lack intelligent processing capabilities for migrating from Excel to database. Summary of the Invention

[0006] This invention addresses the technical problems existing in the prior art by providing an intelligent database table structure adaptation and data import system and method based on Excel. By constructing an intelligent processing engine, it analyzes the statistical characteristics and semantic information of Excel data while parsing it, automatically infers the optimal database table structure design, and generates appropriate SQL statements according to the differences in the target database type. Under the premise of ensuring data integrity and consistency, it achieves a seamless experience of uploading and importing.

[0007] According to a first aspect of the present invention, an Excel-based intelligent database table structure adaptation and data import system is provided, comprising: The file upload and parsing module is used to receive Excel files uploaded by users and perform streaming parsing to extract header information and data samples; The database configuration management module is used to store and manage connection configuration information for multiple databases. The intelligent table structure inference engine performs multi-dimensional statistical analysis based on Excel data samples and automatically infers the optimal database table structure design. A multi-database dialect adapter that converts common table structure definitions into database SQL syntax; The transactional execution engine performs table creation and insertion operations in an atomic transaction manner, ensuring data consistency.

[0008] Based on the above technical solution, the present invention can also be improved as follows.

[0009] Optionally, the file upload and parsing module can integrate the Apache POI engine, supporting automatic detection and parsing of .xlsx, .xls, and .csv formats, automatic file encoding detection, parsing of large Excel files using SAX mode, and extraction of key metadata such as header information, data type distribution, and data volume statistics.

[0010] Optionally, the database configuration management module can create and destroy database connection pools and connection heartbeat detection mechanisms in real time based on configuration information, automatically rebuild failed connections, and establish an independent connection pool for each data source.

[0011] Optionally, the smart table structure inference engine includes: The data sampling unit uses a stratified random sampling algorithm to extract representative data samples from Excel. The type inference unit recommends the most suitable data type for each field based on the success rate of numeric parsing, the success rate of date parsing, the string length distribution, and the proportion of unique values. The constraint analysis unit automatically identifies primary key candidate fields, unique constraint fields, and NOT NULL constraint fields. The optimization suggestion unit provides indexing and partitioning suggestions based on data characteristics.

[0012] Optionally, the multi-database dialect adapter supports databases including MySQL, PostgreSQL, Oracle, and SQL Server, and implements: A data type mapping table maps generic types to specific database types. SQL syntax converter to handle syntax differences between different databases; performance optimization strategies to adjust batch size and transaction commit strategy according to the characteristics of different databases.

[0013] Optionally, the system also includes a table structure compatibility check module. When the target table already exists, the table structure compatibility check module compares the differences between the Excel structure and the existing table structure, generates a structure difference report and modification suggestions, and provides multiple processing options such as automatic conversion, manual adjustment, or recreating the table.

[0014] Optionally, the system also includes a large file processing module, which parses Excel files using SAX mode to achieve constant memory usage; supports file chunking upload and breakpoint resume; and enables real-time progress monitoring and estimated completion time calculation.

[0015] Optionally, the system provides a RESTful API as a backend service, supplemented by a frontend user interface. The frontend user interface is implemented using the Vue.js framework and is used for file upload, database configuration selection, and operation result display.

[0016] According to a second aspect of the present invention, an Excel-based intelligent database table structure adaptation and data import method is provided, comprising the following steps: Receive the user-uploaded Excel file and target database selection instructions; Parse an Excel file and extract header fields and representative data samples; Connect to the target database and check if the specified table exists. If the table does not exist, the optimal table structure is intelligently inferred and created based on statistical analysis of the data sample. If the table exists, compare and adjust the table structure to be compatible with the Excel data; Generate optimized INSERT statements and perform data import in batches; All database operations are performed within atomic transactions to ensure data consistency.

[0017] Optionally, the step of intelligently inferring and creating the optimal table structure includes: Data type inference is performed for each field, taking into account numeric, date, and string characteristics. Analyze field constraints, including uniqueness, non-nullability, and primary key candidate eligibility; Generate dialect-specific CREATE TABLE statements based on the target database type; Perform the table creation operation and verify the correctness of the table structure.

[0018] Optionally, the method also includes data quality assurance, performing data type and length validation during the import process, recording rows and reasons for import failures, supporting selective retries, and generating detailed import reports, including success rates, performance metrics, and a summary of issues.

[0019] The technical effects and advantages of this invention are as follows: This invention provides an intelligent database table structure adaptation and data import system and method based on Excel. This invention can automatically import Excel spreadsheet data into a database and intelligently infer and create the database table structure based on data characteristics. It is particularly suitable for enterprise data migration, data warehouse construction, and data analysis preprocessing scenarios. It achieves fully automated migration from Excel to database, eliminating manual intervention, intelligently inferring and creating the optimal database table structure, supporting automatic adaptation to multiple database types, providing stable and reliable transaction processing and error recovery mechanisms, lowering the barrier to entry, and enabling non-technical personnel to complete professional data migration work.

[0020] Other features and advantages of the invention will be set forth in the description which follows, and will be apparent in part from the description, or may be learned by practicing the invention. The objects and other advantages of the invention may be realized and obtained by means of the structures pointed out in the description, claims, and drawings. Attached Figure Description

[0021] Figure 1 A flowchart illustrating the Excel-based intelligent database table structure adaptation and data import method provided in this embodiment of the invention. Detailed Implementation

[0022] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0023] It should be noted that this invention constructs an intelligent processing engine that, while parsing Excel data, analyzes the statistical characteristics and semantic information of the data, automatically infers the optimal database table structure design, and generates suitable SQL statements based on the differences in the target database type. This achieves a seamless experience of uploading and importing data while ensuring data integrity and consistency. It aims to solve the following technical problems: 1. Achieve fully automated migration from Excel to database, eliminating manual intervention; 2. Intelligently infer and create the optimal database table structure; 3. Supports automatic adaptation to multiple database types; 4. Provides stable and reliable transaction processing and error recovery mechanisms; 5. Lower the barrier to entry, enabling non-technical personnel to complete professional data migration work.

[0024] Understandably, given the deficiencies in the background technology, this invention proposes an intelligent database table structure adaptation and data import system based on Excel. This system adopts a microservice architecture with front-end and back-end separation, and mainly includes the following core modules: The file upload and parsing module is used to receive Excel files uploaded by users and perform streaming parsing to extract header information and data samples; The database configuration management module is used to store and manage connection configuration information for multiple databases. A multi-database dialect adapter that converts common table structure definitions into database SQL syntax; The intelligent table structure inference engine performs multi-dimensional statistical analysis based on Excel data samples and automatically infers the optimal database table structure design. The transactional execution engine performs table creation and insertion operations in an atomic transaction manner, ensuring data consistency.

[0025] By building the above intelligent processing engine, while parsing Excel data, it analyzes the statistical characteristics and semantic information of the data, automatically infers the optimal database table structure design, and generates appropriate SQL statements according to the differences in the target database type, so as to achieve a seamless experience of uploading and importing while ensuring data integrity and consistency.

[0026] Specifically, the file upload and parsing module has the following functions: Multi-format parsing: Integrates the Apache POI engine, supporting automatic detection and parsing of .xlsx, .xls, and .csv formats; Intelligent encoding recognition: Automatically detects file encoding (UTF-8, GBK, GB2312, etc.) to avoid garbled characters; Large file streaming processing: Uses SAX mode to parse large Excel files with constant memory usage; Metadata extraction: Extract key metadata such as table header information, data type distribution, and data volume statistics.

[0027] Specifically, the database configuration management module is used to implement: Dynamic connection pool creation: Database connection pools are created and destroyed in real time based on configuration information; Connect to health monitoring: Heartbeat detection mechanism automatically rebuilds failed connections; Resource isolation: Each data source has an independent connection pool to avoid mutual interference.

[0028] Specifically, the multi-database dialect adapter supports at least four databases, including MySQL, PostgreSQL, Oracle, and SQL Server, and implements the following: A data type mapping table maps generic types to specific database types. SQL syntax converter to handle syntax differences between different databases; performance optimization strategies to adjust batch size and transaction commit strategy according to the characteristics of different databases.

[0029] Furthermore, the multi-database dialect adapter is used to implement: Multi-database syntax adaptation: Built-in syntax mapping tables for MySQL, PostgreSQL, and Oracl; Parameterized SQL generation: Uses prepared statements to prevent SQL injection attacks; Batch operation optimization: Automatically optimize batch commit size based on database type (MySQL: 1000, Oracle: 500). Transaction boundary management: Intelligently defines transaction boundaries and supports resuming interrupted downloads.

[0030] Specifically, the smart table structure inference engine includes: The data sampling unit uses a stratified random sampling algorithm to extract representative data samples from Excel. The type inference unit recommends the most suitable data type for each field based on the success rate of numeric parsing, the success rate of date parsing, the string length distribution, and the proportion of unique values. The constraint analysis unit automatically identifies primary key candidate fields, unique constraint fields, and NOT NULL constraint fields. The optimization suggestion unit provides indexing and partitioning suggestions based on data characteristics. The intelligent table structure inference engine has the following functions: Multi-dimensional sampling analysis: A stratified random sampling algorithm is used to analyze 100 rows of data from the front, middle and back sections. Data type inference: If all values ​​are integers, infer INT; if decimals exist, infer DECIMAL; for dates, DATETIME / TIMESTAMP; for others, infer VARCHAR or TEXT / CLOB based on length. Constraint analysis of the intelligent table structure inference engine: Primary key inference: Fields whose names contain "ID", "Number", or "Primary Key" and whose uniqueness is >99%. Unique constraint: Fields with a uniqueness rate > 99.9%; NOT NULL constraint: Fields with a null value ratio of <1%; Automatic constraint identification: Automatically identifies primary key, unique constraints, and NOT NULL constraints to ensure data integrity.

[0031] The system also includes a table structure compatibility check module. When the target table already exists, the table structure compatibility check module compares the differences between the Excel structure and the existing table structure, generates a structure difference report and modification suggestions, and provides multiple processing options such as automatic conversion, manual adjustment, or recreating the table.

[0032] The system also includes a large file processing module, which uses SAX mode to parse Excel files, achieving constant memory usage; supports file chunking upload and breakpoint resume; and enables real-time progress monitoring and estimated completion time calculation.

[0033] The system provides a RESTful API as a backend service, supplemented by a frontend user interface. The frontend user interface is implemented using the Vue.js framework and is used for file upload, database configuration selection, and display of operation results.

[0034] According to a second aspect of the present invention, an Excel-based intelligent database table structure adaptation and data import method is provided, specifically as follows: Figure 1 As shown, it includes the following steps: Step S1: Receive the Excel file uploaded by the user and the target database selection instruction; First, the user uploads an Excel file via the front-end page, selecting a target database from the configured database list. Then, the user selects a local Excel file through the web interface. The system scans the file (checking for formatting errors and size), uploads the file in chunks to the server's temporary storage area, generates a unique task ID, and initiates an asynchronous processing task.

[0035] Step S2: Parse the Excel file and extract the header fields and representative data samples; The frontend sends the file and database configuration ID to the backend. The backend parses the Excel file, retrieves the header (first row) as field names, and reads all data rows. It identifies the file encoding and Excel version, extracts the worksheet list, selects the first worksheet by default, reads the header row, performs normalization processing (removing spaces and converting special characters), and reads the data row by row in a streaming manner. At the same time, it performs statistical analysis and collects metadata such as data type distribution, null value ratio, and unique value statistics.

[0036] Step S3: Connect to the target database and check if the specified table exists. If the table does not exist, intelligently infer and create the optimal table structure based on statistical analysis of the data sample. If the table exists, compare and adjust the table structure to be compatible with Excel data. The backend connects to the target database and checks if the specified table name exists. It establishes a connection based on the user-selected database configuration, verifies the connection's validity, checks for sufficient permissions, queries whether the target table exists, and if the table exists, retrieves the existing table structure information. The system then dynamically generates and executes the corresponding INSERT INTO ... VALUES ... statements based on each row of data in the Excel file.

[0037] If the table does not exist: The system automatically infers the data type of each field based on the Excel header (for example, it attempts to parse the data as an integer or floating-point number, and if that fails, it defaults to string type), dynamically generates a CREATE TABLE statement, performs the table creation operation, and then generates and executes an INSERT statement to complete the data insertion.

[0038] Scenario A: If the table does not exist, the inference engine is started, and a CREATE TABLE statement is generated based on the sampled data analysis, taking into account the complete definitions of data type, length, constraints, indexes, etc.

[0039] Scenario B: If the table exists but the structure does not match, compare the differences between the Excel structure and the existing table structure, generate an ALTER TABLE statement or conversion suggestions, and require user confirmation before execution.

[0040] Scenario C: If the table exists and the structure is compatible, then directly generate an INSERT statement and handle the field mapping relationship (order and name differences).

[0041] The intelligent inference and creation of the optimal table structure includes: Perform data type inference for each field, taking into account numeric, date, and string characteristics; analyze field constraints, including uniqueness, non-nullability, and primary key candidate eligibility; generate dialect-specific CREATE TABLE statements based on the target database type; execute table creation operations and verify the correctness of the table structure.

[0042] When generating a CREATE TABLE statement, the data type of each field is automatically inferred. The inference logic includes attempting to parse the data as an integer or floating-point number, and if the parsing fails, it is set to a string type by default.

[0043] Step S4: Generate optimized INSERT statements and perform data import in batches; Create a database transaction by executing a CREATE TABLE or ALTER TABLE statement (if necessary). INSERT statements are generated and executed in batches. After each batch is committed, the execution results are checked, and the number of rows that succeeded and failed is recorded.

[0044] Step S5: Complete all database operations within an atomic transaction to ensure data consistency.

[0045] The backend returns the operation results (success, failure, and reason) to the frontend for display to the user, commits or rolls back the transaction, generates a detailed execution report, cleans up temporary files, updates the task status, and notifies the user.

[0046] Finally, data quality assurance is implemented, including: data type and length validation during the import process, recording rows and reasons for import failures, supporting selective retries, and generating detailed import reports, including success rates, performance metrics, and a summary of issues.

[0047] Compared with the prior art, the present invention has the following significant advantages: 1. Significantly improved intelligence: Automatic table structure inference: No need for manual table structure design, the system automatically generates the optimal solution based on data characteristics.

[0048] Intelligent data type matching: Selects the most suitable data type for each field through statistical analysis, saving storage space.

[0049] Automatic constraint recognition: Automatically identifies primary key, unique constraint, NOT NULL constraint, etc., to ensure data integrity.

[0050] 2. Significantly improved operational efficiency: One-click operation: The entire process from file upload to data entry is automated, reducing operation time from hours to minutes.

[0051] Parallel processing capability: Supports importing multiple files into different databases simultaneously, making full use of system resources.

[0052] Batch optimization: Intelligent batch processing strategy, import speed is 5-10 times faster than traditional methods.

[0053] 3. Enhanced versatility and scalability: Multi-database support: A unified interface supports mainstream relational databases, eliminating the need to learn import tools for different databases.

[0054] Extended development: New database types or file formats can be supported through extended development.

[0055] Configuration-based management: All rules are configurable to adapt to the needs of different business scenarios.

[0056] 4. Data quality and security assurance: Data verification mechanism: Data type verification, length verification, and constraint verification are performed during the import process.

[0057] Full transaction support: guarantees the atomicity of operations, automatically rolls back on failure, and avoids data inconsistency.

[0058] Security protection: Multiple security mechanisms including protection against SQL injection, file type verification, and access control.

[0059] 5. Lowered technical barrier: User-friendly interface: Visual operation interface, no coding required.

[0060] Intelligent guidance: Provides intelligent suggestions and explanations at key decision points.

[0061] Detailed Report: Generates a detailed report that includes statistics, performance metrics, and a summary of issues.

[0062] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs. Those skilled in the art can understand and implement this without any creative effort.

[0063] Through the above description of the embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus necessary general-purpose hardware platforms, and of course, it can also be implemented by hardware. Based on this understanding, the above technical solutions, in essence or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods described in the various embodiments or some parts of the embodiments.

[0064] Although preferred embodiments of the invention have been described, those skilled in the art, upon learning the basic inventive concept, can make other changes and modifications to these embodiments. Therefore, the appended claims are intended to be interpreted as including both the preferred embodiments and all changes and modifications falling within the scope of the invention.

[0065] Obviously, those skilled in the art can make various modifications and variations to this invention without departing from its spirit and scope. Therefore, if these modifications and variations fall within the scope of the claims of this invention and their equivalents, this invention also intends to include these modifications and variations.

[0066] Finally, it should be noted that the above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art can still modify the technical solutions described in the foregoing embodiments or make equivalent substitutions for some of the technical features. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. An intelligent database table structure adaptive and data import system based on Excel, characterized in that, include: The file upload and parsing module is used to receive and parse Excel files uploaded by users, and extract header information and data samples. The database configuration management module is used to store and manage connection configuration information for multiple databases. The intelligent table structure inference engine performs multi-dimensional statistical analysis based on Excel data samples and automatically infers the optimal database table structure design. A multi-database dialect adapter that converts common table structure definitions into database SQL syntax; The transactional execution engine performs table creation and insertion operations in an atomic transaction manner, ensuring data consistency.

2. The Excel-based intelligent database table structure adaptive and data import system according to claim 1, characterized in that, The file upload and parsing module can integrate the Apache POI engine, supporting automatic detection and parsing of .xlsx, .xls, and .csv formats, automatic file encoding detection, and parsing of large Excel files using SAX mode, as well as extracting key metadata such as header information, data type distribution, and data volume statistics.

3. The Excel-based intelligent database table structure adaptive and data import system according to claim 1, characterized in that, The database configuration management module can create and destroy database connection pools and connection heartbeat detection mechanisms in real time based on configuration information, automatically rebuild failed connections, and establish an independent connection pool for each data source.

4. The Excel-based intelligent database table structure adaptive and data import system according to claim 1, characterized in that, The smart table structure inference engine includes: The data sampling unit uses a stratified random sampling algorithm to extract representative data samples from Excel. The type inference unit recommends the most suitable data type for each field based on the success rate of numeric parsing, the success rate of date parsing, the string length distribution, and the proportion of unique values. The constraint analysis unit automatically identifies primary key candidate fields, unique constraint fields, and NOT NULL constraint fields. The optimization suggestion unit provides indexing and partitioning suggestions based on data characteristics.

5. The Excel-based intelligent database table structure adaptive and data import system according to claim 1, characterized in that, The multi-database dialect adapter supports databases including MySQL, PostgreSQL, Oracle, and SQL Server, and implements: A data type mapping table maps generic types to specific database types. SQL syntax converter to handle syntax differences between different databases; performance optimization strategies to adjust batch size and transaction commit strategy according to the characteristics of different databases.

6. The Excel-based intelligent database table structure adaptive and data import system according to claim 1, characterized in that, The system also includes: The table structure compatibility check module compares the differences between the Excel structure and the existing table structure when the target table already exists, generates a structure difference report and modification suggestions, and provides multiple processing options such as automatic conversion, manual adjustment or table reconstruction. The system includes a large file processing module that uses SAX mode to parse Excel files, achieving constant memory usage; supports file chunking uploads and resume uploads; and enables real-time progress monitoring and estimated completion time calculation.

7. The Excel-based intelligent database table structure adaptive and data import system according to claim 1, characterized in that, The system provides a RESTful API as a backend service, supplemented by a frontend user interface. The frontend user interface is implemented using the Vue.js framework and is used for file upload, database configuration selection, and display of operation results.

8. An Excel-based intelligent database table structure adaptation and data import method, used in the system described in any one of claims 1 to 7, characterized in that, The method includes the following steps: Receive the user-uploaded Excel file and target database selection instructions; Parse an Excel file and extract header fields and representative data samples; Connect to the target database and check if the specified table exists. If the table does not exist, intelligently infer and create the optimal table structure based on statistical analysis of the data sample. If the table exists, compare and adjust the table structure to be compatible with Excel data. Generate optimized INSERT statements and perform data import in batches; All database operations are performed within atomic transactions to ensure data consistency.

9. The method for adaptive intelligent database table structure and data import based on Excel according to claim 8, characterized in that, The intelligent inference and creation of the optimal table structure based on statistical analysis of data samples includes: Data type inference is performed for each field, taking into account numeric, date, and string characteristics. Analyze field constraints, including uniqueness, non-nullability, and primary key candidate eligibility; Generate dialect-specific CREATE TABLE statements based on the target database type; Perform the table creation operation and verify the correctness of the table structure.

10. The method for adaptive intelligent database table structure and data import based on Excel according to claim 8, characterized in that, The method also includes data quality assurance, performing data type and length validation during the import process, recording the rows and reasons for import failures, supporting selective retries, and generating detailed import reports, including success rates, performance metrics, and a summary of issues.

Images