Excel data verification and import method, system and equipment based on python language and medium

Abstract

The invention discloses a python language-based Excel data verification and import method, system and device and a medium, and the method comprises the following steps: S1, obtaining an Excel file, and obtaining a corresponding import parameter; s2, reading the data of the Excel file; and storing the data in a memory. S3, verifying the data of the Excel file; and verifying each row of data one by one by utilizing a rule engine technology, and recording a verification result in a log. And if the data verification is not passed, displaying corresponding error prompt information. And S4, processing a verification result, if verification is qualified, storing Excel file data into a specified database or data table, and in the storage process, enabling fields in the data table to correspond to columns in the Excel file by a system according to a field mapping relationship set by a user, so as to ensure the correctness and integrity of the data. If the verification result is not qualified, the data of the Excel file is wrongly recorded. The method has the advantage that the efficiency and the accuracy of Excel file data processing are improved.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing and automation, and in particular to a method, system, device and medium for verifying and importing Excel data based on Python language. Background Art

[0002] Excel is a spreadsheet program developed by Microsoft Corporation. It is widely used in personal and business environments as part of the Microsoft Office suite. The main functions of Excel include data organization, calculation, chart making, data analysis, etc. It supports users to perform complex calculations and data processing by writing formulas and functions. With the improvement of informatization, Excel files have become an important tool for enterprise data communication, storage and processing. However, in actual applications, due to data entry errors, inconsistent formats and other problems, the data quality in Excel files is uneven, which brings great inconvenience to data analysis and processing. Therefore, it is particularly important to develop a system that can automatically verify and import Excel file data.

[0003] At present, there are some software or tools that can process Excel files on the market, such as POI, EasyExcel and other methods, but most of them can only perform simple data reading and importing operations, lacking effective verification mechanisms for data quality and format. When importing, developers mostly use row-by-row traversal and hard-coded verification logic to implement, which makes the existing Excel file data inefficient and low in accuracy when processing and analyzing.

[0004] Python is a high-level programming language created by Guido van Rossum in 1991. It is known for its concise, easy-to-read syntax and powerful functions, and is widely used in many fields such as data analysis, machine learning, network development, and automated scripts. Python supports multiple programming paradigms, such as object-oriented, procedural, and functional programming. Python has many advantages: 1. Concise and clear syntax: Python uses indentation to divide code blocks, reducing unnecessary brackets and keywords, making the code more readable. 2. Powerful standard library: Python provides a wealth of built-in modules and functions, which can easily implement various functions such as file operations, network programming, graphical user interfaces, etc. 3. Rich third-party libraries: Python has a large number of third-party libraries, such as NumPy, Pandas, Matplotlib, TensorFlow, etc., which can facilitate scientific computing, data processing, and machine learning. 4. Cross-platform: Python can run on multiple operating systems such as Windows, macOS, and Linux, and the code has good portability. 5. Dynamic type: Python is a dynamically typed language, and the type of the variable is automatically determined at runtime, reducing the complexity of type conversion. 6. Exception handling: Python provides an exception handling mechanism that can capture and handle errors that may occur during program execution. 7. Scalability: Python can be integrated with other programming languages ​​(such as C, C++, Java, etc.) to implement parts with higher performance requirements.

[0005] If Python is combined with Excel files and the powerful performance advantages of Python are used to analyze Excel file data, the efficiency and accuracy of Excel file data processing can be further improved.

[0006] In view of this, it is necessary to provide a method, system, device and medium for Excel data verification and importing based on Python language. Summary of the invention

[0007] The Python-based Excel data verification and import method, system, device and medium provided by the present invention effectively solve the problem that the existing Excel file data processing efficiency needs to be improved and the accuracy of data processing needs to be improved.

[0008] The technical solution adopted by the present invention is:

[0009] The Excel data verification and import method based on Python language includes the following steps:

[0010] S1. Obtain the Excel file and obtain the corresponding import parameters;

[0011] S2, read the data of the Excel file;

[0012] S3, verify the data of the Excel file;

[0013] S4, check result processing, if the check result is qualified, the Excel file data is saved; if the check result is unqualified, the Excel file data is recorded as an error;

[0014] S5. End.

[0015] Furthermore, the import parameters in step S1 include one or more of the following parameters: database connection information, target data.

[0016] Furthermore, in step S2, the pandas library is used to read the Excel file, including one or more of the following formats: .xlsx, .xls format, and supports reading multiple worksheets in the Excel file, and processing the data of each worksheet separately.

[0017] Furthermore, in step S3, the verification is performed by user-defined rules, and the user-defined rules include one or more of the following rules: data type verification, data range verification, data length verification, data format verification, data integrity verification, and data uniqueness verification.

[0018] Furthermore, in step S4, if the Excel file data is saved, it also includes generating a report for the Excel file: including the number of data items successfully imported, the number of data items failed to be imported, error information, etc.

[0019] Furthermore, if the Excel file data is recorded incorrectly in step S4, the recorded error includes one or more of the following error information: recording a log, displaying an error message.

[0020] Furthermore, when the Excel file data is imported in step S4, supported data types include but are not limited to MySQL, Oracle, and SQL Server.

[0021] Excel data verification and import system based on Python language, including:

[0022] Data reading module, used to read data from Excel files.

[0023] The data verification module is used to verify the data of the Excel file read by the data reading module;

[0024] The data import module is used to import the data that has passed the verification of the verification module into the specified database or data table.

[0025] A computer-readable storage medium stores a computer program, which, when executed by a processor, implements the steps of a Python-based Excel data verification and import method.

[0026] A computer device comprises a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory communicate with each other via the communication bus:

[0027] The memory is used to store computer programs;

[0028] The processor is used to execute the steps of the Excel data verification and importing method based on Python language by running the program stored in the memory.

[0029] Beneficial effects of the invention:

[0030] 1. The Excel file data is processed through Python language, which improves the efficiency and accuracy of Excel file data processing.

[0031] 2. Use the pandas library to read Excel files of various formats, and use the characteristics of the pandas library to quickly analyze Excel files.

[0032] 3. By generating reports from the saved Excel files, users can intuitively perform subsequent data processing and analysis based on the information in the reports.

[0033] 4. It is convenient to analyze and mark errors in Excel files by recording logs and displaying error messages. BRIEF DESCRIPTION OF THE DRAWINGS

[0034] Figure 1 A flowchart of a Python-based Excel data verification and import method provided in an embodiment of the present application. DETAILED DESCRIPTION

[0035] In order to make the above-mentioned objects, features and advantages of the present invention more obvious and easy to understand, the specific embodiments of the present invention are described in detail below with reference to the accompanying drawings.

[0036] like Figure 1 As shown, the present application provides a first embodiment, which is a method for verifying and importing Excel data based on Python language, comprising the following steps:

[0037] S1. Obtain the Excel file and obtain the corresponding import parameters;

[0038] S2. Read the data of the Excel file and store the data in the memory.

[0039] S3. Verify the data in the Excel file; use the rule engine technology to verify each row of data one by one, and record the verification results in the log. If the data verification fails, the corresponding error message will be displayed, and the user is allowed to manually modify or skip the row of data.

[0040] S4, check result processing, if the check is qualified, the Excel file data will be saved to the specified database or data table. During the saving process, the system will correspond the fields in the data table with the columns in the Excel file according to the field mapping relationship set by the user to ensure the correctness and integrity of the data. If the check result is unqualified, the Excel file data will be recorded as an error.

[0041] S5. End.

[0042] In the above design, the Excel file data is processed by Python language, which improves the efficiency and accuracy of Excel file data processing.

[0043] Specifically: the import parameters described in step S1 include one or more of the following parameters: database connection information, target data.

[0044] Specifically: in step S2, the pandas library is used to read the Excel file, including reading one or more files in the following formats: .xlsx, .xls format, and supports reading multiple worksheets in the Excel file, and processing the data of each worksheet separately.

[0045] It should be noted that Pandas is an extension library of the Python language for data analysis. Pandas can load data from various data sources, including CSV files, Excel tables, SQL databases, JSON files, etc., and can save the processed data in these formats. Pandas provides a wealth of functions and methods for data cleaning, processing missing values, duplicate values, outliers, etc., as well as data conversion, reshaping and merging operations. Pandas provides a variety of statistical functions and methods for data analysis tasks such as descriptive statistics, aggregation operations, grouping operations, pivot tables, etc. Pandas is combined with the Matplotlib library, which can easily visualize data and draw various statistical charts, such as line charts, scatter plots, histograms, etc.

[0046] In the above design, Excel files of various formats are read through the pandas library, and the characteristics of the pandas library itself can be used to quickly analyze Excel files.

[0047] Specifically: in step S3, the verification is performed by custom rules, and the custom rules include one or more of the following rules: data type verification, data range verification, data length verification, data format verification, data integrity verification, and data uniqueness verification.

[0048] In the above design, adaptive verification standards are applied to different tables through customized rules to ensure that different tables can have the most appropriate verification standards when being verified.

[0049] Specifically: in step S4, after the Excel file data is saved, it also includes generating a report for the Excel file: including the number of data items successfully imported, the number of data items failed to be imported, error information, etc.

[0050] In the above design, by generating a report for the saved Excel file, the user can intuitively perform subsequent data processing and analysis through the information in the report.

[0051] Specifically: in step S4, if the Excel file data is recorded incorrectly, the recorded error includes one or more of the following information: recording a log, displaying an error message.

[0052] In the above design, error analysis and marking of Excel files are facilitated by recording logs and displaying error information.

[0053] Specifically: when the Excel file data is imported in step S4, the supported data types include MySQL, Oracle, and SQL Server.

[0054] The present application also provides a second embodiment, which is a Python-based Excel data verification and import system, including:

[0055] Data reading module, used to read data from Excel files;

[0056] The data verification module is used to verify the data of the Excel file read by the data reading module;

[0057] The data import module is used to import the data that has passed the verification of the verification module into the specified database or data table.

[0058] The present application also provides a third embodiment, which is a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the steps of an Excel data verification and import method based on the Python language are implemented.

[0059] In addition, the computer-readable storage medium of this embodiment may adopt any combination of one or more computer-readable storage media, wherein the computer-readable storage medium includes electrical, optical, electromagnetic, infrared or semiconductor systems, devices or components, or any combination thereof.

[0060] The present application also provides a fourth embodiment, which is a computer device, including a processor, a communication interface, a memory, and a communication bus, wherein the processor, the communication interface, and the memory communicate with each other through the communication bus:

[0061] The memory is used to store computer programs.

[0062] The processor is used to execute the steps of the Excel data verification and importing method based on Python language by running the program stored in the memory.

[0063] As an embodiment of the present invention, the communication bus mentioned in the above terminal can be a Peripheral Component Interconnect (PCI) bus or an Extended Industry Standard Architecture (EISA) bus, etc. The communication bus can be divided into an address bus, a data bus, a control bus, etc.

[0064] As an implementation mode of the present invention, the communication interface is used for communication between the above-mentioned terminal and other devices.

[0065] As an embodiment of the present invention, the memory may include a random access memory (RAM) or a non-volatile memory, such as at least one disk memory. Optionally, the memory may also be at least one storage device located away from the aforementioned processor.

[0066] As an embodiment of the present invention, the above-mentioned processor can be a general-purpose processor, including a central processing unit (CPU), a network processor (NP), etc.; it can also be a digital signal processor (DSP), an application specific integrated circuit (ASIC), a field programmable gate array (FPGA) or other programmable logic devices, discrete gates or transistor logic devices, discrete hardware components.

[0067] The present invention also provides a fifth embodiment, which is a method for verifying and importing Excel data based on Python language, comprising the following steps:

[0068] S1. Obtain an Excel file and corresponding import parameters; the import parameters include one or more of the following parameters: database connection information and target data.

[0069] S2. Use the pandas library to read one or more Excel files in the following formats: .xlsx, .xls, and store the data in memory. It also supports reading multiple worksheets in Excel files and processing the data of each worksheet separately.

[0070] S3. Verify the data in the Excel file; the system creates a rule table through custom verification rules, uses rule engine technology to verify each row of data one by one, and records the verification results in the log. If the data verification fails, the corresponding error message is displayed, and the user is allowed to manually modify or skip the row of data.

[0071] S4, verification result processing, if the verification is qualified, the Excel file data will be saved to the specified database or data table. During the saving process, the system will correspond the fields in the data table to the columns in the Excel file according to the field mapping relationship set by the user to ensure the correctness and integrity of the data. When importing Excel file data, the supported data types include MySQL, Oracle, and SQL Server. After the Excel file data is saved, it also includes generating a report for the Excel file: including the number of successfully imported data, the number of failed data, error information, etc. If the verification result is unqualified, the Excel file data is recorded as an error. The recorded error includes one or more of the following information: recording logs, displaying error information.

[0072] S5. End.

[0073] To further explain in detail, it should be understood that the above is only a specific embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention should be included in the protection scope of the present invention.

Claims

1. The Excel data verification and import method based on Python language includes the following steps: S1. Obtain the Excel file and obtain the corresponding import parameters; S2, read the data of the Excel file; S3. Verify the data in the Excel file: Use the rule engine technology to verify each row of data in the Excel file one by one, and record the verification results in the log; S4, verification result processing: if the verification is qualified, the Excel file data is saved to the specified database or data table; If the verification result fails, the Excel file data will be recorded as incorrect.

2. The Excel data verification and import method based on Python language according to claim 1, characterized in that: The import parameters described in step S1 include one or more of the following parameters: database connection information, target data.

3. The Excel data verification and import method based on Python language according to claim 1, characterized in that: In step S2, the pandas library is used to read the Excel file, including one or more of the following formats: .xlsx, .xls, and supports reading multiple worksheets in the Excel file, and processing the data of each worksheet separately.

4. The Excel data verification and import method based on Python language according to claim 1, characterized in that: In step S3, the verification is performed by user-defined rules, and the user-defined rules include one or more of the following rules: data type verification, data range verification, data length verification, data format verification, data integrity verification, and data uniqueness verification.

5. The Excel data verification and import method based on Python language according to claim 1, characterized in that: In step S4, if the Excel file data is saved, it also includes generating a report for the Excel file: including but not limited to the number of data items imported successfully, the number of data items that failed to be imported, and error information.

6. The Excel data verification and import method based on Python language according to claim 1, characterized in that: If the Excel file data is recorded incorrectly in step S4, the recorded error includes one or more of the following error information: log, display error information.

7. The Excel data verification and import method based on Python language according to claim 1, characterized in that: When the Excel file data is imported in step S4, the supported data types include: MySQL, Oracle, and SQL Server.

8. Excel data verification and import system based on Python language, characterized by: include, Data reading module, used to read data from Excel files; The data verification module is used to verify the data of the Excel file read by the data reading module; The data import module is used to import the data that has passed the verification of the verification module into the specified database or data table.

9. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the steps of the Excel data verification and import method based on Python language according to any one of claims 1 to 7 are implemented.

10. A computer device, characterized in that: It includes a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory communicate with each other through the communication bus: The memory is used to store computer programs; The processor is used to execute the steps of the Excel data verification and import method based on Python language in any one of claims 1 to 7 by running the program stored in the memory.

Images