A method for parallel analysis of complex batch Excel into warehouse

Abstract

This invention discloses a method for parallel parsing and importing complex batch Excel files into a database. The method includes: S1, configuring a corresponding XML configuration file based on the correspondence between specific fields in the Excel file and a database list; S2, preprocessing the Excel file using the XML configuration file as an auxiliary parsing file; S3, distributing the Excel file across multiple processes based on the preprocessing results and parsing the Excel file within each process; S4, encapsulating the parsed data into data objects to be imported into the database; S5, creating database tables in the database based on the description information of the database fields in the XML configuration file; and S6, storing the data objects in the corresponding database tables according to the reading method specified in the XML configuration file. The method provided by this invention significantly improves the import rate of complex batch Excel files, increases data extraction speed and import efficiency, and does not place a significant processing burden on the server.

Description

Technical Field

[0001] This invention relates to the field of computer technology. More specifically, it relates to a method for parallel parsing and importing complex batches of Excel files into a database. Background Technology

[0002] Information management systems are currently an important method and means of information management, and databases are a crucial component of such systems. In actual production processes and real-time communication scenarios, detection or process data is typically stored and recorded in Excel files. The header formats vary widely, and the format of an Excel file can take many forms depending on user conventions. In most common Excel formats, the first row is the header, and subsequent rows contain the content corresponding to the header. However, some complex Excel files exist, such as vertically oriented tables with the first column as the header; sheets containing multiple smaller tables; or dynamic header columns whose number varies depending on the specific situation. These complex Excel file formats also create inconvenience for data integration and management.

[0003] In developing this invention, the inventors discovered at least the following problems in the prior art: While databases bring convenience to information management, they also present data conversion challenges, such as converting complex Excel data into a database. Although most mainstream databases have common data conversion tools, these tools provide conversion functions within certain limits and constraints, exhibiting poor compatibility with complex Excel data conversions, making it difficult for ordinary users to import data independently. Furthermore, the efficiency of data conversion and import from heterogeneous data sources is not ideal, placing significant processing pressure on servers for large batches of Excel files.

[0004] Therefore, a method is needed to parse complex batches of Excel files in parallel and insert them into a database. Summary of the Invention

[0005] The purpose of this invention is to provide a method for parallel parsing and importing complex batches of Excel files into a database, so as to solve at least one of the problems existing in the prior art.

[0006] To achieve the above objectives, the present invention adopts the following technical solution:

[0007] The first aspect of this invention provides a method for parallel parsing and importing complex batch Excel files into a database, the method comprising:

[0008] S1. Configure the corresponding XML configuration file according to the correspondence between the specific fields of the Excel file and the database list;

[0009] S2. The XML configuration file is used as an auxiliary parsing file to preprocess the Excel file;

[0010] S3. Distribute the Excel file through multiple processes based on the preprocessing results and parse the Excel file within each process;

[0011] S4. Encapsulate the parsed data into data objects that need to be stored in the database;

[0012] S5. Create a database table in the database according to the information in the database field of the XML configuration file;

[0013] S6. Store the data object in the corresponding database table according to the reading method in the XML configuration file.

[0014] Optionally, step S2 includes performing decomposition preprocessing on the Excel files based on the XML configuration file corresponding to each Excel file in the Excel pool, and determining the range of Excel files that each process needs to process.

[0015] Optionally, the range of Excel files includes determining whether a corresponding XML configuration file exists based on the files in the Excel pool: for all Excel files with a configuration file, the number of files parsed by each process is calculated based on the number of files and the number of processes; if no configuration file exists, the Excel file is not counted, thereby obtaining the range of the number of corresponding XML configuration files in the Excel pool.

[0016] Optionally, the step of performing multi-process distribution of files based on preprocessing results includes distributing the Excel files and corresponding XML configuration files to each process based on the preprocessing results of the files in the Excel pool, and independently parsing and processing the Excel files based on the auxiliary information in the XML configuration files.

[0017] Optionally, parsing the Excel file within the process includes merging or splitting the fields configured in the XML configuration file, and forming key-value pairs based on the correspondence between the fields and database column names.

[0018] Optionally, step S4 includes encapsulating the contents of the Excel file and the database column names into data objects based on the key-value pair information, and merging the data objects to form a complete data object that needs to be entered into the database.

[0019] Optionally, the XML configuration file is used to represent the corresponding standards for fields and data entry in the Excel file, as well as the table creation rules for creating database tables.

[0020] Optionally, step S6 includes reading the data content of the data object to be stored in the database by row or column, and inserting the data into the corresponding database table.

[0021] A second aspect of the present invention provides a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the method provided in the first aspect of the present invention.

[0022] A third aspect of the present invention provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the method provided in the first aspect of the present invention.

[0023] The beneficial effects of this invention are as follows:

[0024] This invention discloses a method for parallel parsing and importing complex batches of Excel files into a database. This method configures corresponding XML configuration files based on the Excel files and database list, and processes the uploaded files in parallel, thus improving the speed of batch Excel import via XML configuration and enabling the handling of large-scale batches of Excel files. The method provided by this invention does not place a significant processing load on the server, greatly improving the import rate of complex batches of Excel files, and enhancing data extraction speed and import efficiency. Attached Figure Description

[0025] The specific embodiments of the present invention will be described in further detail below with reference to the accompanying drawings.

[0026] Figure 1 The flowchart illustrates the parallel parsing and data import method for complex batch Excel files according to the present invention.

[0027] Figure 2 This diagram illustrates the flowchart for parsing and importing batches of Excel files into the database.

[0028] Figure 3 This diagram illustrates the parallel parsing process among various processes.

[0029] Figure 4 This diagram illustrates the correspondence between Excel files, XML configuration files, and the DM database.

[0030] Figure 5(a) shows a schematic diagram of a complex Excel file containing three tables in this embodiment;

[0031] Figure 5(b) shows a schematic diagram of a complex Excel file without a header file in this embodiment;

[0032] Figure 6 This diagram illustrates a portion of the XML configuration file corresponding to the data in the two sheets in this embodiment.

[0033] Figure 7 A schematic diagram of the structure of a computer device according to an embodiment of the present invention is shown. Detailed Implementation

[0034] To more clearly illustrate the present invention, the following description, in conjunction with preferred embodiments and accompanying drawings, further explains the invention. Similar components in the drawings are indicated by the same reference numerals. Those skilled in the art should understand that the specific description below is illustrative rather than restrictive and should not be construed as limiting the scope of protection of the present invention.

[0035] Example 1

[0036] Example 1 provides a method for parallel parsing and importing complex batch Excel files into a database. Figure 1 This is a flowchart of the parallel parsing method for inserting complex batch Excel files into a database according to the present invention. The method includes:

[0037] S1. Configure the corresponding XML configuration file according to the correspondence between the specific fields of the Excel file and the database list;

[0038] The database used in this embodiment is DM Database. Combining the specific fields and content of Excel, and through the correspondence between the fields and the columns of the DM Database table, the corresponding XML configuration file is configured according to the rules to define the configuration file corresponding to the Excel file that needs to be parsed and entered into the database. The XML configuration file serves as an auxiliary parsing file, specifically storing three types of information: the location and name of the fields to be entered into the database, the fields and types of the database, and the correspondence between the fields. The file name of the XML configuration file is consistent with that of the Excel file.

[0039] S2. The XML configuration file is used as an auxiliary parsing file to preprocess the Excel file;

[0040] Users upload batches of Excel files, which are stored in an Excel pool. The system performs preprocessing on each Excel file based on its corresponding XML configuration file. Using the batch of Excel files as the basic unit, the system determines the range of Excel files each process needs to process based on the number of processes, file size, and parsing / database insertion standards. Specifically, the range of Excel files each process needs to process refers to determining the number of Excel files in the pool with corresponding XML configuration files, based on whether such files exist. The system then calculates the number of Excel files with parsing files and the number of files each process parses. If a corresponding XML configuration file exists, the system calculates the number of files each process parses for all Excel files with such a configuration file, based on the number of files and processes. If no such file exists, no calculation is performed for that Excel file. The XML configuration file represents the correspondence between fields in the Excel files and the data insertion standards, as well as the table creation rules for the database tables.

[0041] S3. Distribute the Excel file through multiple processes based on the preprocessing results and parse the Excel file within each process;

[0042] Figure 2 The flowchart illustrates the process of parsing and loading batches of Excel files into a database. It describes the implementation of multi-process data parallelism in a cluster. Based on preprocessing results, the main process distributes the Excel files and their corresponding XML configuration files to each process. Different processes then independently and isolatedly parse and process the Excel files according to auxiliary information in the XML configuration files. Multi-process data parallelism means that after each process receives its assigned Excel file, it processes the file within its own process based on the corresponding XML configuration file. The last process processes all remaining files.

[0043] Internal Excel parsing refers to the system reading the configuration file and processing the Excel file. Based on the field name 'a' and coordinate O1 configured in the XML configuration file, the actual coordinate O2 of 'a' in the actual Excel file is found, obtaining the actual offset 'n' of each field. Following the splitting or merging instructions for each field in the XML configuration file, the fields are merged or split to obtain the final field information. Finally, key-value pairs are formed based on the correspondence between the fields and database column names.

[0044] Specifically:

[0045] (1) O1 is the position coordinate (X1, Y1) configured in the XML configuration file for this field, and O2 (X2, Y2) is the position information obtained by searching the file based on this field, with an offset n of (X1-X2, Y1-Y2);

[0046] (2) For each field, performing merging or splitting operations means processing the fields in the Excel file. For example, if the hours, minutes, and seconds in Excel are stored in 3 columns and correspond to a time field in the database, then the hours, minutes, and seconds need to be merged, and according to the corresponding merging symbols in the XML configuration file, a new field is formed.

[0047] (3) Key-value pair information refers to the correspondence between the fields in Excel and the column names in the database. For example, the content in the field attribute needs to be stored in the column named ATTRIBUTE, and the key-value pair information is <ATTRIBUTE, "attribute">.

[0048] S4. Package the parsed data into data objects to be stored in the database.

[0049] In each process, split and read the content information in the Excel file. According to the key-value pair information, package the content and the database column names into data objects. The main process merges the packaged data objects in sequence according to the process ID to form a complete data object to be stored in the database. The data object to be stored in the database refers to the data structure that can be stored in the database after being processed through the above steps, including its field names and corresponding data, which is temporarily stored in memory waiting for the next call.

[0050] S5. Create a database table in the database according to the description information of the database fields in the XML configuration file.

[0051] Refer to the description information of the database fields in the XML configuration file. According to the database name, field name, and type, create the corresponding database table in the DM database.

[0052] S6. Store the data object into the corresponding database table according to the reading method in the XML configuration file.

[0053] According to the data object, read the data content in sequence according to the reading method in the configuration, that is, horizontally or vertically of the table, and store it into the corresponding database table. Reading the data content in sequence according to the reading method in the configuration means reading the generated data object in row or column mode to read the content to be stored in the database, and inserting the data into the corresponding database table in sequence.

[0054] Specifically, Figure 3 The schematic diagram showing parallel parsing between processes. Through the multi-process parallel method, the speed of bulk Excel storage through XML parsing is improved, enabling it to process large-scale bulk Excel. The process of parallel processing includes reading multiple Excel files into the Excel pool, parallel grouping, intra-group splitting and parsing, and forming storage objects.

[0055] Users upload Excel files that need to be parsed to the pool through the system. The system analyzes the file extension and places the files into different waiting queues based on their file types (xls, xlsx, csv, etc.).

[0056] Specifically, taking the xls file queue as an example, under the premise of load balancing, the Excel files in the queue that correspond to parsed XML files are divided. After calculating the number of groups based on the derivation, the main process distributes the corresponding files to the other processes, and the group number of the last process additionally includes all the files remaining after equal distribution;

[0057] This step involves parsing the Excel file separately within each process, breaking it down into the smallest parsing unit (sheet), forming logical relationships between fields and columns based on the parsing method configured for each sheet in the XML, reading the specific content of the Excel file, and assembling the corresponding data object.

[0058] Within each process, data objects are merged into a final database object in column order. The one-dimensional coordinate represents the file index, and the corresponding two-dimensional data stores the index of the current group in the sheet of the Excel file. Then, the data content of the database object is read and stored in the corresponding database table.

[0059] Figure 4 This diagram illustrates the correspondence between an Excel file, an XML parsing file, and the DM database. The Excel file serves as the basis, while the XML parsing file represents the correspondence between fields in the Excel file and the data entry standards, as well as the table creation rules for the database tables.

[0060] Specifically, Figure 5 illustrates the situation of complex Excel files. In Figure 5(a) on the left, sheet1 contains three tables with three different table structures. Since the sheet is the smallest unit, the three tables are entered into the same database table. In Figure 5(b) on the right, sheet2 contains a table without a header. For the handling of files without headers, the HeadName field, which is the first field name located in the XML parsing, is marked as "no header" to identify that the Excel file does not have a header field. Therefore, the corresponding data can be directly parsed and entered into the database.

[0061] Figure 6 This represents the corresponding XML parsing files for the two sheets in the Excel file shown in Figure 5, with keywords listed in Table 1.

[0062] Table 1. Meaning of XML Keywords

[0063]

[0064]

[0065] The XML configuration file indicates that the Excel file contains two sheets, which need to be parsed and imported into the "TEST"."A" database in the DaMeng database. Sheet1 contains two tables: table1 is read by column, and table2 is read by row. In table2, the four fields of hour, minute, second, and millisecond need to be concatenated into one field using concatenation symbols. Sheet2 contains only one table without a header; only the field value at position (0,0) needs to be located and read and parsed sequentially.

[0066] Example 2

[0067] like Figure 7 As shown, Embodiment 2 provides a computer device, it is understood that... Figure 7 The computer device 12 shown is merely an example and should not impose any limitation on the functionality and scope of use of the embodiments of the present invention.

[0068] like Figure 7 As shown, the computer device 12 is represented in the form of a general-purpose computing device. The components of the computer device 12 may include, but are not limited to: one or more processors or processing units 16, system memory 28, and a bus 18 connecting different system components (including system memory 28 and processing unit 16).

[0069] Bus 18 represents one or more of several bus architectures, including a memory bus or memory controller, a peripheral bus, a graphics acceleration port, a processor, or a local bus using any of the various bus architectures. For example, these architectures include, but are not limited to, the Industry Standard Architecture (ISA) bus, the Micro Channel Architecture (MAC) bus, the Enhanced ISA bus, the Video Electronics Standards Association (VESA) local bus, and the Peripheral Component Interconnect (PCI) bus.

[0070] Computer device 12 typically includes a variety of computer system readable media. These media can be any available media that can be accessed by computer device 12, including volatile and non-volatile media, removable and non-removable media.

[0071] System memory 28 may include computer system readable media in the form of volatile memory, such as random access memory (RAM) 30 and / or cache memory 32. Computer device 12 may further include other removable / non-removable, volatile / non-volatile computer system storage media. By way of example only, storage system 34 may be used to read and write non-removable, non-volatile magnetic media (…). Figure 7 Not shown; usually referred to as a "hard drive"). Although Figure 7Not shown, a disk drive for reading and writing to a removable non-volatile disk (e.g., a "floppy disk") and an optical disk drive for reading and writing to a removable non-volatile optical disk (e.g., a CD-ROM, DVD-ROM, or other optical media) may be provided. In these cases, each drive may be connected to bus 18 via one or more data media interfaces. Memory 28 may include at least one program product having a set (e.g., at least one) of program modules configured to perform the functions of the embodiments of the present invention.

[0072] A program / utility 40 having a set (at least one) of program modules 42 may be stored, for example, in memory 28. Such program modules 42 include, but are not limited to, an operating system, one or more application programs, other program modules, and program data. Each or some combination of these examples may include an implementation of a network environment. Program modules 42 typically perform the functions and / or methods described in the embodiments of the present invention.

[0073] Computer device 12 can also communicate with one or more external devices 14 (e.g., keyboard, pointing device, display 24, etc.), and with one or more devices that enable a user to interact with the computer device 12, and / or with any device that enables the computer device 12 to communicate with one or more other computing devices (e.g., network card, modem, etc.). This communication can be performed through input / output (I / O) interface 22. Furthermore, computer device 12 can also communicate with one or more networks (e.g., local area network (LAN), wide area network (WAN), and / or public networks, such as the Internet) through network adapter 20. Figure 7 As shown, network adapter 20 communicates with other modules of computer device 12 via bus 18. It should be understood that, although... Figure 7 As not shown, it can be used in conjunction with computer device 12 with other hardware and / or software modules, including but not limited to: microcode, device drivers, redundant processing units, external disk drive arrays, RAID systems, tape drives, and data backup storage systems.

[0074] The processor unit 16 executes various functional applications and data processing by running programs stored in the system memory 28, such as implementing the method provided in Embodiment 1 of the present invention.

[0075] Example 3

[0076] Example 3 provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the method described in Example 1.

[0077] In practical applications, the computer-readable storage medium can be any combination of one or more computer-readable media. The computer-readable medium can be a computer-readable signal medium or a computer-readable storage medium. For example, a computer-readable storage medium can be, but is not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination thereof. More specific examples of computer-readable storage media (a non-exhaustive list) include: an electrical connection having one or more wires, a portable computer disk, a hard disk, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage device, magnetic storage device, or any suitable combination thereof. In this embodiment, the computer-readable storage medium can be any tangible medium containing or storing a program that can be used by or in conjunction with an instruction execution system, apparatus, or device.

[0078] Computer-readable signal media may include data signals propagated in baseband or as part of a carrier wave, carrying computer-readable program code. Such propagated data signals may take various forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination thereof. Computer-readable signal media may also be any computer-readable medium other than computer-readable storage media, capable of sending, propagating, or transmitting programs for use by or in connection with an instruction execution system, apparatus, or device.

[0079] Program code contained on a computer-readable medium may be transmitted using any suitable medium, including but not limited to wireless, wire, optical fiber, RF, etc., or any suitable combination thereof.

[0080] Computer program code for performing the operations of this invention can be written in one or more programming languages ​​or a combination thereof, including object-oriented programming languages ​​such as Java, Smalltalk, and C++, as well as conventional procedural programming languages ​​such as "C" or similar programming languages. The program code can be executed entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In cases involving remote computers, the remote computer can be connected to the user's computer via any type of network—including a local area network (LAN) or a wide area network (WAN)—or can be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0081] In the description of this invention, it should be noted that the terms "upper," "lower," etc., indicating the orientation or positional relationship are based on the orientation or positional relationship shown in the accompanying drawings, and are only for the convenience of describing the invention and simplifying the description, and do not indicate or imply that the device or element referred to must have a specific orientation, or be constructed and operated in a specific orientation, and therefore should not be construed as a limitation of the invention. Unless otherwise expressly specified and limited, the terms "installed," "connected," and "linked" should be interpreted broadly. For example, they can refer to a fixed connection, a detachable connection, or an integral connection; they can refer to a mechanical connection or an electrical connection; they can refer to a direct connection or an indirect connection through an intermediate medium; they can refer to the internal communication between two elements. For those skilled in the art, the specific meaning of the above terms in this invention can be understood according to the specific circumstances.

[0082] It should also be noted that in the description of this invention, relational terms such as "first" and "second" are used only to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0083] Obviously, the above embodiments of the present invention are merely examples for clearly illustrating the present invention, and are not intended to limit the implementation of the present invention. For those skilled in the art, other variations or modifications can be made based on the above description. It is impossible to exhaustively list all the implementation methods here. All obvious variations or modifications derived from the technical solutions of the present invention are still within the protection scope of the present invention.

Claims

1. A warehouse-in method for parallel resolving complex batch Excel, characterized in that, The method comprises: S1, configuring a corresponding XML configuration file according to the correspondence between specific fields of an Excel file and a database list; S2, preprocessing the Excel file by taking the XML configuration file as an auxiliary parsing file; S3, distributing the Excel file in multiple processes according to the preprocessing result and parsing the Excel file in the processes; S4, encapsulating the parsed data into a data object to be stored in a database; S5, creating a database table in the database according to the information of the database field in the XML configuration file; S6, storing the data object into the corresponding database table according to the reading mode in the XML configuration file. The S2 comprises decomposing and preprocessing the Excel file according to the corresponding XML configuration file of each Excel file in an Excel pool, and determining the range of the Excel file to be processed by each process. The range of the Excel file comprises judging whether there is a corresponding XML configuration file according to the file in the Excel pool: for all Excel files with configuration files, calculating the number of files parsed by each process according to the number of files and the number of processes; if there is no configuration file, the Excel file is not calculated, thereby obtaining the range of the number of Excel files with corresponding XML configuration files in the Excel pool. The S4 comprises encapsulating the content in the Excel file and the database column name into a data object according to the key-value pair information, and merging the data objects to form a complete data object to be stored in the database.

2. The method of claim 1, wherein, The multiple-process distribution of the file according to the preprocessing result comprises distributing the Excel file and the corresponding XML configuration file to each process according to the preprocessing result of the file in the Excel pool, and independently parsing the Excel file according to the auxiliary information in the XML configuration file.

3. The method of claim 1, wherein, The parsing of the Excel file in the process comprises merging or splitting the fields configured in the XML configuration file, and forming a key-value pair information according to the correspondence between the fields and the database column names.

4. The method of claim 1, wherein, The XML configuration file is used to represent the corresponding standard of the fields in the Excel file and the data storage, and the table creation rule for establishing a database table.

5. The method of claim 1, wherein, The S6 comprises reading the data content to be stored in the database table according to the row or column of the data object, and inserting the data into the corresponding database table.

6. A computer device comprising a memory, a processor, and a computer program stored on the memory and executable on the processor, characterized in that, The processor executes the program to implement the method of any one of claims 1-5.

7. A computer-readable storage medium having stored thereon a computer program, characterized in that, The program is executed by the processor to implement the method of any one of claims 1-5.

Images