WO2019179021A1

WO2019179021A1 - Method and apparatus for importing excel data into database, and computer device and storage medium

Info

Publication number: WO2019179021A1
Application number: PCT/CN2018/101991
Authority: WO
Inventors: 占锐
Original assignee: 平安科技（深圳）有限公司
Priority date: 2018-03-22
Filing date: 2018-08-23
Publication date: 2019-09-26
Also published as: CN108491510A

Abstract

Embodiments of the present application provide a method and apparatus for importing Excel data into a database, and a computer device and a storage medium. The method comprises: connecting Excel to a relational database by means of an Excel VBA program; setting a correspondence between a field in the Excel form and a field in a corresponding table of the relational database; analyzing the relevant attribute of the field in the corresponding table of the relational database; determining a conversion rule according to the correspondence and the relevant attribute of the field; utilizing the conversion rule to convert the value of the corresponding row of the field in the Excel table into the value of the corresponding row of the field in the corresponding table of the relational database according to the correspondence of the field, and displaying the conversion result in the Excel; and importing the conversion result into the relational database.

Description

Method, device, computer device and storage medium for importing Excel data into database

This application claims priority to Chinese Patent Application No. 201810241485.1, entitled "Method, Device, Computer Equipment and Storage Media for Excel Data Import Database", filed on March 22, 2018, the entire contents of which are hereby incorporated by reference. This is incorporated herein by reference.

Technical field

The present application relates to the field of data processing technologies, and in particular, to a method, an apparatus, a computer device, and a storage medium for importing an Excel data into a database.

Background technique

Because Excel data types and relational data such as Oracle database types are different, if you want to import the data in the Excel table into the Oracle database, you need to perform data conversion. Before importing data, Oracle will import the corresponding data content from the Excel table according to its own data type to convert the data, and judge the data that does not meet the requirements, and finally import the converted data conforming to the Oracle data type into the Oracle database. This method can't visually see the result of converting Excel data into Oracle data. You can only view the related imported data by importing and then opening the Oracle database. When the Excel data type conversion Oracle data type has an error that cannot be converted, the above method cannot continue to perform data import after the operation of modifying and deleting the data to be processed in the import process, but can only be performed after modifying the Excel data. Import again.

Summary of the invention

The embodiment of the present invention provides a method, a device, a computer device, and a storage medium for importing an Excel data into a database, which can process data to be processed according to a conversion rule in the process of importing Excel data into a database, and then import and improve data import. effectiveness.

In a first aspect, an embodiment of the present application provides a method for importing Excel data into a database, where the method includes:

Connect the Excel to the relational database through the Excel VBA program; set the correspondence between the fields in the Excel table and the fields in the relational database; analyze the related attributes of the fields in the relational database; according to the corresponding relationship of the fields and related Attribute, determining a conversion rule; using the conversion rule, converting the value of the corresponding column of the field in the Excel table to the value of the column corresponding to the field in the relational database according to the corresponding relationship of the field, and displaying the conversion result in Excel; The conversion result is imported into a relational database.

In a second aspect, an embodiment of the present application provides an apparatus for importing an Excel data into a database, the apparatus comprising means for performing the method of importing the Excel data into the database according to the first aspect.

In a third aspect, an embodiment of the present application provides a computer device, where the computer device includes a memory, and a processor connected to the memory;

The memory is for storing a computer program for executing a computer program stored in the memory to perform the method of importing Excel data into the database according to the first aspect.

In a fourth aspect, an embodiment of the present application provides a computer readable storage medium, where the computer readable storage medium stores a computer program, where the computer program includes program instructions, and when the program instructions are executed by a processor, implementing the foregoing The method of importing Excel data into a database according to the first aspect.

The embodiment of the present application connects Excel to the relational database through the Excel VBA program, determines the conversion rule according to the correspondence relationship and related attributes of the fields in the correspondence table of the relational database, and then converts the value of the corresponding column of the field in the Excel table into the conversion rule according to the conversion rule. The relational database corresponds to the value of the column corresponding to the field in the table and displays the conversion result in Excel, and imports the conversion result into the relational database. In the process of importing Excel data into the database, the embodiment of the present application uses the determined conversion rules to process the data to be processed, and then import, thereby improving the efficiency of data import; before the import, the conversion result is displayed in Excel for convenience. The user view is so convenient for the user to operate and improve the user experience.

DRAWINGS

In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings used in the description of the embodiments will be briefly described below. Obviously, the drawings in the following description are some embodiments of the present application, For the ordinary technicians, other drawings can be obtained based on these drawings without any creative work.

1 is a schematic diagram of a scenario of a method for importing Excel data into a database provided by an embodiment of the present application;

2 is a schematic flowchart of a method for importing Excel data into a database according to an embodiment of the present application;

3 is a schematic diagram of a sub-flow of a method for importing Excel data into a database according to an embodiment of the present application;

4 is a schematic diagram of another sub-flow of a method for importing Excel data into a database according to an embodiment of the present application;

5 is a flow chart showing an example of a method for importing an Excel data into a database according to another embodiment of the present application;

6 is a schematic block diagram of an apparatus for importing an Excel data into a database provided by an embodiment of the present application;

7 is a schematic block diagram of a setting unit provided by an embodiment of the present application;

8 is a schematic block diagram of an import unit provided by an embodiment of the present application;

9 is a schematic block diagram of an apparatus for importing an Excel data into a database according to another embodiment of the present application;

FIG. 10 is a schematic block diagram of a computer device according to an embodiment of the present application.

detailed description

The technical solutions in the embodiments of the present application are clearly and completely described in the following with reference to the drawings in the embodiments of the present application. It is obvious that the described embodiments are a part of the embodiments of the present application, and not all of the embodiments. All other embodiments obtained by a person of ordinary skill in the art based on the embodiments of the present application without departing from the inventive scope are the scope of the present application.

The use of the terms "comprising", "comprising", "","," The presence or addition of a plurality of other features, integers, steps, operations, elements, components, and/or collections thereof. It is also to be understood that the term "and/or" used in the specification and the appended claims are intended to mean any combination and all possible combinations of one or more of the associated listed items.

The embodiments described below can be applied to portable devices such as mobile phones, laptop computers or tablet computers having displays and/or touch pads, as well as non-portable devices having displays and/or touch pads, such as In a desktop computer. It should be noted that Excel related software is installed in these devices.

Please refer to FIG. 1. FIG. 1 is a schematic diagram of a method for importing Excel data into a database according to an embodiment of the present application. When the user receives the requirement to import the data in an Excel table with multiple rows and columns into the relational database, first connect the Excel to the relational database through the Excel VBA program; read the relational database table through the driver such as the ADO driver. All the fields and field attributes are returned to Excel; the correspondence between the fields in the Excel table and the relational database table is set by Excel VBA; the related attributes of the corresponding fields in the relational database are analyzed; according to the corresponding relationship of the fields and related attributes, Determining a conversion rule, and using the conversion rule to convert the value of the column corresponding to the field in the Excel table to the value of the column corresponding to the field in the relational database according to the corresponding relationship of the field and display the conversion result in Excel; when the receiving user clicks When importing the import request generated by the button, the conversion result displayed in Excel is imported into the relational database through the ADO driver. In the process of importing Excel data into the database, the determined conversion rules are used to process the data to be processed, and then imported, thereby improving the efficiency of data import; before the import, the conversion result is displayed in Excel, so that the user can view the conversion. The result is an error, which is convenient for the user to operate and improves the user experience.

FIG. 2 is a schematic flowchart diagram of a method for importing Excel data into a database according to an embodiment of the present application. The method includes the following steps S201-S206.

S201, connecting Excel to a relational database through an Excel VBA program.

Among them, the relational database is a database based on the relational model. Various entities in the real world and various connections between entities are represented by a relational model. Relational databases include Oracle databases, SQL Server databases, My SQL databases, and more. The following example will use the Oracle database as an example. Excel is connected to the relational database through the Excel VBA program. Specifically, through the VBA program of Excel, the Excel is connected to the relational database by using the driver, for example, the Excel is connected to the Oracle database through the ADO driver. Can be understood as, through Excel VBA programming, call the ADO driver, connect Excel to the Oracle database. Among them, ADO (ActiveX Data Objects) is a COM component for accessing data sources. Excel needs to reference the ADO driver library when connecting to the Oracle database via VBA. To reference the ADO driver library, follow these steps: 1. Open the VBA editor, click on "Tools", "References" in the menu; check "Microsoft ActiviteX Data Objects 2.8 Library" and "Microsoft ActiviteX Data ObjectS Recordset 2.8 Library" Option to ensure that the ADO driver library is selected. Among them, the ADO driver library name may be another name. After citing the ADO driver library, through the ADO driver library, such as calling the ADO driver library open method, enter the corresponding user name, password, Oracle database server name, etc. in the open method to open the Oracle database and establish a connection with the Oracle database. And maintain a connection to the Oracle database. If you do not have an ADO driver or do not reference the ADO driver, you can also create an ADO object. Specifically, the create object function is called to create an ADO object, and an object function such as CreateObject is created. After creating the ADO object, call the open method of the ADO object, enter the corresponding user name, password, Oracle database server name, etc. in the open method to open the Oracle database, establish a connection with the Oracle database, and maintain a connection with the Oracle database. In the embodiment of the present application, the specific driver is not limited, and may be an ADO driver or an ODBC (Open Database Connectivity) driver. It is also possible to select the driver that best matches the corresponding database according to the specific database.

By creating an ADO object, the code to connect the excel to the Oracle database can be found as follows:

Dim cnn As Object, rst As Object

Set cnn=CreateObject("ADODB.Connection")

Set rst=CreateObject("ADODB.Recordset")

cnn.Open"Provider=msdaora.1;Data Source=d1580;User Id=emssxjk;Password=emssxjk;"

OraOpen=True'

Among them, Data Source represents the database server name, User Id and Password respectively represent the user name and password to connect to the database. After the above code is successfully executed, the Oracle database is opened.

S202: Set a correspondence between a field in the Excel table and a field in the correspondence table of the relational database.

First, you need to create a table in a relational database and add fields to the corresponding table. Each field has a corresponding attribute, and a field is added in the corresponding table and the attribute corresponding to each field is set. If the attribute includes a field name, a field type, a field length, etc., the attribute may also include a uniqueness constraint, a primary key constraint, and the like. The number of fields in the relational database correspondence table is less than or equal to the number of fields in the Excel table. If the number of fields in the relational database correspondence table is equal to the number of fields in the Excel table, it can be understood that all the columns in the Excel table need to be imported into the relational database. If the number of fields in the relational database correspondence table is smaller than the number of fields in the Excel table, it can be understood that only the data of some columns in the Excel table need to be imported into the relational database. It should be noted that creating a table in the relational database and adding a field in the corresponding table may be performed before step S101, or may be performed after step S101. Creating a table and adding fields in the corresponding table can be done by executing the corresponding SQL language. After there is a corresponding table in the relational database, the correspondence between the fields in the Excel table and the fields in the relational database correspondence table is set. Set the correspondence by VBA programming.

In an embodiment, as shown in FIG. 2, the step S202 includes steps S301-S303.

S301. Acquire a field in a relational database correspondence table by using a VBA program. For example, for the Oracle database, through the VBA program, call the ADO driver, so that Excel can connect to the Oracle database, the data in the Oracle database can be read through the ADO driver, so that the fields in the corresponding table in the Oracle database are obtained, wherein the Oracle database The corresponding table refers to the table that imports Excel data into the Oracle database.

S302: Set a correspondence between a field in the Excel table and a field in the correspondence table of the relational database. The correspondence relationship can be understood as which column of the corresponding table in the Oracle database is imported into the corresponding column of the Excel table.

S303. Save the correspondence. The corresponding relationship is saved so that the data of the corresponding column in the Excel table is imported into the corresponding column in the corresponding Oracle database when the subsequent import is performed, so as to avoid an error in the import.

S203. Analyze the related attribute of the field in the correspondence table of the relational database.

The field in the relational database correspondence table refers to a field in which the correspondence relationship is set in the relational database. Read the related attributes of the fields in the Oracle database correspondence table and the corresponding table, such as the field name, field type, and field length. After reading the related attributes of the fields in the corresponding table of the Oracle database, the related attributes of the read are obtained through the VBA program, and the related attributes of the fields in the correspondence table of the relational database are obtained. Parse the relevant properties obtained. If the field name is date and the field type is date, the date data type in the Oracle database includes century, year, month, day, hour, minute, and second. A total of 7 bytes are occupied.

S204. Determine a conversion rule according to the correspondence between the fields and related attributes.

Obtain the attributes of the fields in the Excel table, and then determine the conversion rules according to the corresponding relationship of the fields and related attributes. It can be understood that the conversion rule is determined according to the field in the excel table and the field type of the field in the relational database correspondence table. For example, the field type of the date field in the Excel table is char, and the field type of the corresponding date field in the Oracle database is date, which is a field of two different data types, then the conversion rule is: the field corresponding to the column data of the Excel table. The type is converted from char to the Oracle database corresponding field type date. In this way, the date data in the Excel table can be imported into the Oracle database. If the type of the field in the Excel table is int, and the type of the corresponding field in the relational database is flout, then the field type corresponding to the column data of the field needs to be converted from int to the field type flout of the corresponding field of the relational database.

S205. Convert, by using the conversion rule, the value of the corresponding column of the field in the Excel table to the value of the column corresponding to the field in the relational database correspondence table according to the correspondence relationship of the field, and display the conversion result in Excel.

In an embodiment, the step S205 includes: obtaining a value of a column corresponding to a field in each row of the Excel table; and converting, by using the conversion rule, a value of a column corresponding to a field in each row of the Excel table according to a correspondence relationship of the field The value corresponding to the column in the field in the Oracle database corresponds to the table; the conversion result is displayed in Excel. It can be understood that in the present embodiment, the conversion is completed line by line. In other embodiments, the value of the column corresponding to the field in the Excel table may also be obtained; using the conversion rule, the value of the corresponding column of the Excel table field is converted into the column corresponding to the field in the corresponding table of the Oracle database according to the corresponding relationship of the field. Value; display the conversion result in Excel. It can be understood that the value of the corresponding column of one field can be converted at one time, and then the value of the corresponding column of another field is converted to complete the conversion of all the fields. If an error occurs during the conversion process, the cause of the conversion error is displayed; if there is no error during the conversion process, after all the data is converted, the conversion result is displayed on the Excel interface. You can see if the conversion is wrong and where it went wrong by displaying the conversion result on the Excel interface. The conversion rules can be modified depending on the cause of the error.

S206, importing the conversion result into the relational database. Specifically, the conversion result is obtained by the VBA program, and after receiving the input import instruction, the conversion result is imported into the Oracle database through the ADO driver. The user clicks the "Import" related button to generate an import command.

In an embodiment, as shown in FIG. 4, the step S206 includes the following steps S401-S402.

S401: Compare the data in the conversion result with the data stored in the relational database correspondence table. After receiving the import instruction, the comparison of the data is performed. When comparing, compare the data of each row in the conversion result with the data stored in the relational database correspondence table, for example, according to the column of the primary key constraint in the Oracle database correspondence table and the value of the corresponding column in the conversion result, To quickly determine whether there is data for the corresponding row. After judging that there is data of the corresponding row, the data of the corresponding row is compared to determine whether the data of the corresponding row in the conversion result is consistent with the data in the relational database. After all the data are compared, S402 is executed.

S402: if there is no corresponding data in the relational database, import the data in the conversion result into the relational database; if there is consistent data in the relational database, prompt; if there is inconsistent data in the relational database , the inconsistent data is updated to the data in the conversion result.

If there is no data corresponding to the data in the conversion result in the relational database, the data in the conversion result is imported into the relational database; if there is data in the relational database that is consistent with the conversion result, a prompt is given, such as In the form of a dialog box, "consistent data already exists in the database, whether to re-import"; if there is data in the relational database that is inconsistent with the conversion result, the inconsistent data is updated to the data in the conversion result in units of rows. If the data of a column in a row in the conversion result is inconsistent with the data of the corresponding row in the relational database, the data of the row in the relational database is updated by the data of the row in the conversion result; if the data of the row in the conversion result is multi-row If it does not exist in the relational database, the data of the multiple rows is inserted into the relational database in units of rows.

In the above embodiment, the Excel data is used in the process of importing the database, and the data to be processed is processed, and then imported, thereby improving the efficiency of data import; before the import, the conversion result is displayed in Excel for the user to view. This facilitates further user operations and improves the user experience.

FIG. 5 is a schematic flowchart of a method for importing Excel data into a database according to another embodiment of the present application. The method includes steps S501-S508. Among them, steps S506-S507 are added. The other steps are the same as those shown in the embodiment of FIG. 2 . For details, please refer to the description in the embodiment of FIG. 2 , and details are not described herein again.

S506, detecting whether the conversion result is an error. It can be determined by the cause of the error in the feedback. It can also be combined with the value of the corresponding column in the specific application and the value of the corresponding column in the relational database correspondence table to determine whether the conversion result is wrong, such as detecting whether the converted data format is correct and/or Whether the converted data meets the requirements of the specific application scenario.

S507, if the conversion result is wrong, the conversion rule is modified according to the error reason. For example, in Excel, the data in a column is 123.5, and in the relational database, the column is int, and the corresponding value after conversion is 124, but in the relational database, the value required for the column in the row is 123, then judge the conversion result error, modify the conversion rule of the corresponding column to: round down. Then step S505 is performed.

S508: If the conversion result is not in error, the conversion result is imported into the relational database.

According to the displayed conversion result, the embodiment further checks whether the conversion result is in error. If an error occurs, the corresponding conversion rule is modified to improve the probability of successful import, and the import efficiency is further improved.

FIG. 6 is a schematic block diagram of an apparatus for importing an Excel data into a database according to an embodiment of the present application. As shown in FIG. 6, the apparatus 60 includes a connection unit 601, a setting unit 602, an analysis unit 603, a rule determination unit 604, a conversion unit 605, and an introduction unit 606.

The connecting unit 601 is configured to connect Excel to the relational database through an Excel VBA program.

The setting unit 602 is configured to set a correspondence between the field in the Excel table and the field in the relational database correspondence table.

In an embodiment, as shown in FIG. 7, the setting unit 602 includes a field obtaining unit 701, a relationship setting unit 702, and a saving unit 703.

The field obtaining unit 701 is configured to obtain a field in the relational database correspondence table by using a VBA program. The relationship setting unit 702 is configured to set a correspondence between the field in the Excel table and the field in the relational database correspondence table.

The saving unit 703 is configured to save the correspondence.

The parsing unit 603 is configured to parse related attributes of the fields in the relational database correspondence table.

The rule determining unit 604 is configured to determine a conversion rule according to the correspondence between the fields and related attributes.

The converting unit 605 is configured to convert, by using the conversion rule, the value of the corresponding column of the field in the Excel table to the value of the column corresponding to the field in the relational database correspondence table according to the correspondence relationship of the field, and display the conversion result in Excel.

In an embodiment, the conversion unit 605 includes a column value acquisition unit, a value conversion unit, and a display unit. The column value obtaining unit is configured to obtain a value of a column corresponding to a field in each row of the Excel table. And a value conversion unit, configured to convert, by using the conversion rule, a value of a field corresponding column in each row of the Excel table to a value corresponding to a field in the Oracle database correspondence table according to the correspondence relationship of the fields. Display unit for displaying the conversion result in Excel. It can be understood that in the present embodiment, the conversion is completed line by line. In other embodiments, the value of the column corresponding to the field in the Excel table may also be obtained; using the conversion rule, the value of the corresponding column of the Excel table field is converted into the column corresponding to the field in the corresponding table of the Oracle database according to the corresponding relationship of the field. Value; display the conversion result in Excel. It can be understood that the value of the corresponding column of one field can be converted at one time, and then the value of the corresponding column of another field is converted to complete the conversion of all the fields. If an error occurs during the conversion process, the cause of the conversion error is displayed; if there is no error during the conversion process, after all the data has been converted, the conversion result is displayed on the Excel interface. You can see if the conversion is wrong and where it went wrong by displaying the conversion result on the Excel interface. The conversion rules can be modified depending on the cause of the error.

The import unit 606 is configured to import the conversion result into the relational database.

In an embodiment, as shown in FIG. 8, the import unit 606 includes a comparison unit 801 and an update import unit 802.

The comparing unit 801 is configured to compare the data in the conversion result with the data stored in the relational database correspondence table.

The update import unit 802 is configured to: if the corresponding data does not exist in the relational database, import the data in the conversion result into the relational database; if there is consistent data in the relational database, prompt; if the relational database If there is inconsistent data, the inconsistent data is updated to the data in the conversion result.

FIG. 9 is a schematic block diagram of an apparatus for importing an Excel data into a database according to another embodiment of the present application. As shown in FIG. 9, the apparatus 90 includes a connection unit 901, a setting unit 902, an analysis unit 903, a rule determination unit 904, a conversion unit 905, a detection unit 906, a modification unit 907, and an introduction unit 908. The difference between this embodiment and the embodiment of FIG. 6 is that the detecting unit 906 and the modifying unit 907 are added. The detecting unit 906 and the modifying unit 907 will be described below. For other units, please refer to the description of the corresponding unit in the embodiment of FIG. 6, and details are not described again.

The detecting unit 906 is configured to detect whether the conversion result is an error.

The modifying unit 907 is configured to modify the conversion rule according to the error reason if the conversion result is wrong. The execution conversion unit 905 is then triggered.

The import unit 908 is configured to import the conversion result into the relational database if the conversion result is not erroneous.

For the specific working process and the beneficial effects of the foregoing device embodiments, refer to the corresponding implementation process and beneficial effects of the foregoing method embodiments, and details are not described herein again.

The above apparatus may be embodied in the form of a computer program that can be run on a computer device as shown in FIG.

FIG. 10 is a schematic block diagram of a computer device according to an embodiment of the present application. The device 100 can also be in the form of a client. The device 100 includes a processor 102, a memory and a network interface 103 connected by a system bus 101, wherein the memory can include a non-volatile storage medium 104 and an internal memory 105.

The non-volatile storage medium 104 can store an operating system 1041 and a computer program 1042. When the computer program 1042 is executed, the processor 102 can be caused to execute a method of importing Excel data into a database. The processor 102 is used to provide computing and control capabilities to support the operation of the entire device 100. The internal memory 105 provides an environment for the operation of a computer program in a non-volatile storage medium that, when executed by the processor 102, causes the processor 102 to perform a method of importing Excel data into a database. The network interface 103 is used for network communication, such as receiving instructions and the like. It will be understood by those skilled in the art that the structure shown in FIG. 10 is only a block diagram of a part of the structure related to the solution of the present application, and does not constitute a limitation of the device 100 to which the solution of the present application is applied. The specific device 100 may be It includes more or fewer components than those shown in the figures, or some components are combined, or have different component arrangements.

The processor 102 is configured to execute a computer program stored in a memory to implement any one of the foregoing methods for importing Excel data into a database.

It should be understood that, in the embodiment of the present application, the processor 102 may be a central processing unit (CPU), and the processor may also be another general-purpose processor, a digital signal processor (DSP). , Application Specific Integrated Circuit (ASIC), Field-Programmable Gate Array (FPGA) or other programmable logic device, discrete gate or transistor logic device, discrete hardware component, etc. The general purpose processor may be a microprocessor or the processor or any conventional processor or the like.

In another embodiment of the present application, a computer readable storage medium is stored, the computer readable storage medium storing a computer program, the computer program comprising program instructions, when executed by a processor, Any embodiment of a method of implementing the aforementioned Excel data import database.

The computer readable storage medium may be an internal storage unit of the terminal described in any of the foregoing embodiments, such as a hard disk or a memory of the terminal. The computer readable storage medium may also be an external storage device of the terminal, such as a plug-in hard disk equipped on the terminal, a smart memory card (SMC), and a Secure Digital (SD) card. Wait. Further, the computer readable storage medium may also include both an internal storage unit of the terminal and an external storage device.

In the several embodiments provided by the present application, it should be understood that the disclosed terminal and method may be implemented in other manners. For example, the terminal embodiment described above is only illustrative. For example, the division of the unit is only a logical function division, and the actual implementation may have another division manner. A person skilled in the art can clearly understand that, for the convenience and brevity of the description, the specific working process of the terminal and the unit described above can be referred to the corresponding process in the foregoing method embodiment, and details are not described herein again. The foregoing is only a specific embodiment of the present application, but the scope of protection of the present application is not limited thereto, and any equivalents can be easily conceived by those skilled in the art within the technical scope disclosed in the present application. Modifications or substitutions are intended to be included within the scope of the present application. Therefore, the scope of protection of this application should be determined by the scope of protection of the claims.

Claims

A method for importing Excel data into a database, the method comprising:

Connect Excel to a relational database through an Excel VBA program;

Setting the correspondence between the fields in the Excel table and the fields in the correspondence table of the relational database;

Parsing the related attributes of the fields in the relational database correspondence table;

Determining a conversion rule according to the correspondence between the fields and related attributes;

Using the conversion rule, converting the value of the column corresponding to the field in the Excel table to the value of the column corresponding to the field in the relational database corresponding table according to the corresponding relationship of the field, and displaying the conversion result in Excel;

Import the conversion result into a relational database.
The method according to claim 1, wherein before the importing the conversion result into a relational database, the method further comprises:

Detecting whether the conversion result is erroneous;

If the conversion result is in error, the conversion rule is modified according to the error reason; and the conversion rule is used to convert the value of the corresponding column of the field in the Excel table into the corresponding column of the field in the relational database corresponding table according to the correspondence relationship of the field The value and the step of displaying the conversion result in Excel;

If the conversion result is not erroneous, perform the step of importing the conversion result into the relational database.
The method according to claim 1, wherein the setting of the correspondence between the fields in the Excel table and the fields in the relational database correspondence table comprises:

Obtain the fields in the relational database correspondence table through the Excel VBA program;

Setting the correspondence between the fields in the Excel table and the fields in the correspondence table of the relational database;

Save the correspondence.
The method according to claim 1, wherein the converting the value of the corresponding column of the field in the Excel table to the value of the corresponding column of the field in the relational database correspondence table according to the correspondence relationship of the field by using the conversion rule And display the conversion results in Excel, including:

Get the value of the corresponding column of the field in each row of the Excel table;

Using the conversion rule, converting the value of the corresponding column of the field in each row of the Excel table to the value of the column corresponding to the field in the relational database corresponding table according to the corresponding relationship of the field;

Display the conversion result in Excel.
The method according to claim 1, wherein said importing said conversion result into a relational database comprises:

Comparing the data in the conversion result with the data stored in the relational database correspondence table;

If the corresponding data does not exist in the relational database, the data in the conversion result is imported into the relational database;

Prompt if there is consistent data in the relational database;

If there is inconsistent data in the relational database, the inconsistent data is updated to the data in the conversion result.
An apparatus for importing Excel data into a database, wherein the apparatus comprises:

a connection unit for connecting Excel to a relational database through an Excel VBA program;

a setting unit, configured to set a correspondence between a field in the Excel table and a field in the correspondence table of the relational database;

a parsing unit, configured to parse related attributes of the fields in the correspondence table of the relational database;

a rule determining unit, configured to determine a conversion rule according to the corresponding relationship of the field and related attributes;

a conversion unit, configured to convert, by using the conversion rule, a value of a column corresponding to a field in an Excel table to a value of a column corresponding to a field in a relational database according to a correspondence relationship of the field, and display a conversion result in Excel;

An import unit for importing the conversion result into a relational database.
The device according to claim 6, wherein the device further comprises:

a detecting unit, configured to detect whether the conversion result is erroneous;

And a modifying unit, configured to: if the conversion result is in error, modify the conversion rule according to the error reason, trigger the conversion unit; if the conversion result is not in error, trigger the import unit.
The device according to claim 6, wherein the setting unit comprises:

a field obtaining unit, configured to obtain a field in a relational database correspondence table by using an Excel VBA program;

a relationship setting unit, configured to set a correspondence between a field in the Excel table and a field in the correspondence table of the relational database;

A saving unit is configured to save the correspondence.
The apparatus according to claim 6, wherein the converting unit comprises:

a column value obtaining unit, configured to obtain a value of a column corresponding to a field in each row of the Excel table;

a value conversion unit, configured to convert, by using the conversion rule, a value of a column corresponding to a field in each row of the Excel table to a value corresponding to a column in a correspondence table of the relational database according to a correspondence relationship of the field;

Display unit for displaying the conversion result in Excel.
The device according to claim 6, wherein the importing unit comprises:

a comparison unit, configured to compare data in the conversion result with data stored in a relational database correspondence table;

Updating the import unit, if the corresponding data does not exist in the relational database, importing the data in the conversion result into the relational database; if there is consistent data in the relational database, prompting; if the relational type If there is inconsistent data in the database, the inconsistent data is updated to the data in the conversion result.
A computer device, comprising: a memory, and a processor coupled to the memory; the memory for storing a computer program; the processor for running a computer program stored in the memory, To perform the following steps:

Connect Excel to a relational database through an Excel VBA program;

Setting the correspondence between the fields in the Excel table and the fields in the correspondence table of the relational database;

Parsing the related attributes of the fields in the relational database correspondence table;

Determining a conversion rule according to the correspondence between the fields and related attributes;

Using the conversion rule, converting the value of the column corresponding to the field in the Excel table to the value of the column corresponding to the field in the relational database corresponding table according to the corresponding relationship of the field, and displaying the conversion result in Excel;

Import the conversion result into a relational database.
The computer apparatus according to claim 11, wherein said processor further performs the following steps before said importing said conversion result into said relational database:

Detecting whether the conversion result is erroneous;

If the conversion result is in error, the conversion rule is modified according to the error reason; and the conversion rule is used to convert the value of the corresponding column of the field in the Excel table into the corresponding column of the field in the relational database corresponding table according to the correspondence relationship of the field The value and the step of displaying the conversion result in Excel; if the conversion result is not in error, perform the step of importing the conversion result into the relational database.
The computer device according to claim 11, wherein the processor performs the following steps when performing the correspondence between the fields in the setting Excel table and the fields in the relational database correspondence table:

Obtain the fields in the relational database correspondence table through the Excel VBA program; set the correspondence between the fields in the Excel table and the fields in the relational database correspondence table; save the corresponding relationship.
The computer device according to claim 11, wherein the processor performs the using the conversion rule to convert a value of a column corresponding to a field in an Excel table to a relational database according to a correspondence relationship of the fields. When the field in the table corresponds to the value of the column and displays the conversion result in Excel, perform the following steps:

Get the value of the corresponding column of the field in each row of the Excel table;

Using the conversion rule, converting the value of the corresponding column of the field in each row of the Excel table to the value of the column corresponding to the field in the relational database corresponding table according to the corresponding relationship of the field;

Display the conversion result in Excel.
The computer device according to claim 11, wherein the processor performs the following steps when performing the importing the conversion result into a relational database:

Comparing the data in the conversion result with the data stored in the relational database correspondence table;

If the corresponding data does not exist in the relational database, the data in the conversion result is imported into the relational database; if there is consistent data in the relational database, the prompt is performed; if there is inconsistent data in the relational database And updating the inconsistent data to the data in the conversion result.
A computer readable storage medium, characterized in that the computer readable storage medium stores a computer program, the computer program comprising program instructions, when the program instructions are executed by the processor, implementing the following steps:

Connect Excel to a relational database through an Excel VBA program;

Setting the correspondence between the fields in the Excel table and the fields in the correspondence table of the relational database;

Parsing the related attributes of the fields in the relational database correspondence table;

Determining a conversion rule according to the correspondence between the fields and related attributes;

Using the conversion rule, converting the value of the column corresponding to the field in the Excel table to the value of the column corresponding to the field in the relational database corresponding table according to the corresponding relationship of the field, and displaying the conversion result in Excel;

Import the conversion result into a relational database.
The computer readable storage medium according to claim 16, wherein said processor further implements the following steps before said importing said conversion result into said relational database:

Detecting whether the conversion result is erroneous;

If the conversion result is in error, the conversion rule is modified according to the error reason; and the conversion rule is used to convert the value of the corresponding column of the field in the Excel table into the corresponding column of the field in the relational database corresponding table according to the correspondence relationship of the field The value and the step of displaying the conversion result in Excel; if the conversion result is not in error, perform the step of importing the conversion result into the relational database.
The computer readable storage medium according to claim 16, wherein the processor specifically implements the following steps when performing the correspondence between the fields in the setting Excel table and the fields in the relational database correspondence table:

Obtain the fields in the relational database correspondence table through the Excel VBA program; set the correspondence between the fields in the Excel table and the fields in the relational database correspondence table; save the corresponding relationship.
The computer readable storage medium according to claim 16, wherein the processor performs the using the conversion rule to convert a value of a column corresponding to a field in an Excel table into a relationship according to a correspondence relationship of the fields. When the type database corresponds to the value of the column corresponding to the field in the table and displays the conversion result in Excel, the following steps are implemented:

Get the value of the corresponding column of the field in each row of the Excel table;

Using the conversion rule, converting the value of the corresponding column of the field in each row of the Excel table to the value of the column corresponding to the field in the relational database corresponding table according to the corresponding relationship of the field;

Display the conversion result in Excel.
The computer readable storage medium according to claim 16, wherein the processor specifically implements the following steps when performing the importing the conversion result into a relational database:

Comparing the data in the conversion result with the data stored in the relational database correspondence table;

If the corresponding data does not exist in the relational database, the data in the conversion result is imported into the relational database; if there is consistent data in the relational database, the prompt is performed; if there is inconsistent data in the relational database And updating the inconsistent data to the data in the conversion result.