Method, system, device and storage medium for importing table data into table relational database

By establishing connections with table relational databases and table data storage documents in the target running environment, obtaining table entry information and creating data tables, the problem of low efficiency in table data import is solved, automatic import is realized, and efficiency is improved.

CN114780544BActive Publication Date: 2024-12-03GENEW TECH
View PDF 1 Cites 0 Cited by

Patent Information

Application Number
CN202210374579.2
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2022-04-11
Publication Date
2024-12-03
Estimated Expiration
2042-04-11

AI Technical Summary

Technical Problem

In the prior art, table data is inefficient when imported into table relational databases, and technical personnel need to operate manually, affecting efficiency.

Method used

By establishing a connection with the table relational database and table data storage documents in the target running environment, obtaining table entry information, and creating data tables in the table relational database, importing table data into the data table, realizing automatic import.

Benefits of technology

It realizes fully automatic transfer of table data, reduces manual demand, reduces manual workload, and improves the efficiency of table data import.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN114780544B_ABST
    Figure CN114780544B_ABST
Patent Text Reader

Abstract

The embodiment of the present invention discloses a method, system, device and storage medium for importing table data into a table relational database, belonging to the technical field of data transfer; the method includes establishing a connection with both the table relational database and the table data storage document in a target operating environment; obtaining table item information in the table data storage document based on the connection established with the table data storage document; creating a data table in the table relational database based on the connection established with the table relational database and the table item information; writing the table data in the table data storage document into the data table. Through the target operating environment, the table data in the table data storage document can be automatically imported into the table relational database without manual import, and the manual workload is small. Compared with the method of manually importing table data by technicians, the present application improves the efficiency of importing table data.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the technical field of data transfer, and in particular to a method, system, device and storage medium for importing table data into a table relational database. Background Art

[0002] With the development of Internet technology, the number of tools and / or software for storing data has increased day by day. Different computer storage tools have been developed according to different data storage needs. For example, spreadsheets are convenient for daily recording and storing data; databases are convenient for long-term and large-scale storage of data, which have become indispensable tools in the field of data storage.

[0003] Tables can be created or drawn in software such as Excel and Word. Databases are dominated by table-relational databases similar to the DAMO database, which occupy most of the database market. However, as the amount of data storage increases, if you want to store table data in a table into a table-relational database, you cannot automatically import it into the table-relational database. Therefore, in the existing technology, it can only be imported manually by technicians, which affects the efficiency of importing table data. Summary of the invention

[0004] In view of this, the present invention provides a method, system, device and storage medium for importing table data into a table relational database, which is used to solve the problem of low efficiency of table data import in the prior art. In order to achieve one or part or all of the above purposes or other purposes, the present invention proposes a method, system, device and storage medium for importing table data into a table relational database. In the first aspect:

[0005] A method for importing table data into a table relational database, comprising:

[0006] In the target operating environment, establish connections with both the table relational database and the table data storage document;

[0007] Based on the connection established with the table data storage document, obtaining table item information in the table data storage document;

[0008] Creating a data table in the table relational database based on the connection established with the table relational database and the table item information;

[0009] The table data in the table data storage document is written into the data table.

[0010] Preferably, the step of creating a data table in the table relational database based on the connection established with the table relational database and the table item information comprises:

[0011] Get the table name of the data table;

[0012] Creating a table creation cursor associated with the table relational database;

[0013] Starting the table creation cursor, and determining a table creation execution instruction based on a combination of the table name and the table item information;

[0014] The table creation execution instruction is traversed based on the table creation cursor to create the data table in the table relational database.

[0015] Preferably, the step of writing the table data in the table data storage document into the data table comprises:

[0016] Read the data in the target table below the row where the column name is located according to the number of rows; read one row at a time;

[0017] Generate at least one piece of data information based on the table name, the column name and the data in the table;

[0018] Creating an insert cursor associated with the table relational database;

[0019] Starting the insert cursor and determining an insert execution instruction obtained based on the data information;

[0020] The insert execution instruction is traversed based on the insert cursor to enter the data in the table into the data table.

[0021] Preferably, the step of obtaining the table item information in the table data storage document includes:

[0022] Get the location information of the target table;

[0023] locking the target table based on the location information;

[0024] Based on a preset search condition, the column name of each column in the target table is obtained; the search condition includes a fixed condition or a calculated condition.

[0025] Preferably, when the search condition is the fixed condition, the step of searching for the column names of each column in the target table based on the preset search condition includes:

[0026] Get the preset number of rows;

[0027] Determine the row in the target table corresponding to the row number information as the first row;

[0028] Read the content of each cell in the first row, and determine the content of each cell in the first row as the column name;

[0029] When the search condition is a calculation condition, the step of searching for the column names of each column in the target table based on the preset search condition includes:

[0030] Traversing the target table along the column direction to obtain the column number information of each row of the target table;

[0031] Obtain the maximum number of columns in the column number information;

[0032] Calculate the difference between the maximum number of columns and a preset number threshold to obtain a target number of columns;

[0033] Searching along the column direction for the first target row in the target table where the number of columns is greater than or equal to the target number of columns;

[0034] The content of each cell in the target row is read, and the content of each cell in the target row is determined as the column name.

[0035] Preferably, after obtaining the table name of the data table, the method further includes:

[0036] Determine whether there is a data table with the same name as the table name in the table relational database;

[0037] If not, executing the step of creating a table cursor associated with the table relational database;

[0038] If so, the column name is input into the data table corresponding to the table name.

[0039] Preferably, the step of establishing a connection with both the table relational database and the table data storage document in the target operating environment includes:

[0040] Get the document information of the table data storage document;

[0041] Retrieve a document interface, and establish a connection with the table data storage document based on the document interface and the document information;

[0042] Get database information of table relational database;

[0043] A database interface is called, and a connection is established with the table relational database based on the database interface and the database information.

[0044] Second aspect:

[0045] A system for importing table data into a table relational database, applied in a target operating environment, comprising a connection module for establishing connections with both the table relational database and the table data storage document;

[0046] An acquisition module, configured to acquire table item information in the table data storage document based on a connection established with the table data storage document;

[0047] A table creation module, used for creating a data table in the table relational database based on the connection established with the table relational database and the table item information;

[0048] and a writing module, used for writing the table data in the table data storage document into the data table.

[0049] The third aspect:

[0050] A device for importing table data into a table relational database comprises a memory and a processor, wherein the memory stores a method for importing table data into a table relational database, and the processor is used to adopt any of the above methods when executing the method for importing table data into a table relational database.

[0051] Fourth aspect:

[0052] A storage medium stores a computer program that can be loaded by a processor and execute any of the above methods.

[0053] Implementing the embodiments of the present invention will have the following beneficial effects:

[0054] By establishing a connection with the table relational database and the table data storage document, it is possible to obtain table item information from the table data storage document, then create a data table corresponding to the table item information in the table relational database, and finally write the table data in the table data storage document into the data table created in the table relational database, thereby realizing the transfer of table data from the table data storage document to the table relational database. Since the entire execution process is located in the target operating environment, it can be executed automatically, achieving the purpose of fully automatic transfer of table data, without the need for technicians to manually import table data into the table relational database, reducing manual demand, reducing manual workload, and improving the efficiency of table data import. BRIEF DESCRIPTION OF THE DRAWINGS

[0055] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0056] in:

[0057] Figure 1 The figure is an overall flow chart of a method for importing table data into a table relational database in one embodiment.

[0058] Figure 2A flowchart of establishing a connection with a table data storage document and a table relational database in one embodiment.

[0059] Figure 3 The figure is a flowchart of obtaining table entry information in one embodiment.

[0060] Figure 4 This is a flowchart when the search condition is a fixed condition in one embodiment.

[0061] Figure 5 This is a flowchart when the search condition is a calculation condition in one embodiment.

[0062] Figure 6 The flowchart of creating a data table in a table relational database in one embodiment.

[0063] Figure 7 The present invention is a flowchart of importing table data into a table relational database in one embodiment.

[0064] Figure 8 Another flowchart of importing table data into a table relational database in one embodiment.

[0065] Fig. 9 It is a schematic diagram of a table in an Excel document in an embodiment.

[0066] Fig.10 It is a schematic diagram of a data table in the DAMO database in one embodiment.

[0067] Fig.11 The overall framework diagram of a system for importing table data into a table relational database in one embodiment. DETAILED DESCRIPTION

[0068] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0069] In the following description, reference is made to “some embodiments”, which describe a subset of all possible embodiments, but it will be understood that “some embodiments” may be the same subset or different subsets of all possible embodiments and may be combined with each other without conflict.

[0070] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as those commonly understood by those skilled in the art to which the present invention belongs. The terms used herein are only for the purpose of describing the embodiments of the present invention and are not intended to limit the present invention.

[0071] Before further describing the embodiments of the present invention in detail, the nouns and terms involved in the embodiments of the present invention are described. The nouns and terms involved in the embodiments of the present invention are subject to the following interpretations.

[0072] Table-based database: A relational database that has a table structure, that is, a number of data tables can be established under the database, and the data to be stored can be stored in rows and / or columns of the data table. Common table-based databases include MySQL database, DAMO database and Oracle database.

[0073] Table data storage document is an electronic document that can create, record and store tables, and can record data in the table. The recorded data is the table data; and the table data can be stored. Common table data storage documents include word, excel, etc.

[0074] Python: A high-level scripting language that combines interpretability, compilation, interactivity, and object-orientation. It has the characteristics of scalability, extensibility, and embeddability. For example, if you need a key code to run faster, or if you want some algorithms to be kept private, you can write part of the program in C or C++, and then use them in the Python program. Python itself is extensible, so that all features and functions do not need to be integrated into the language core; Python has more APIs and tools to facilitate the use of C, C++, and Cython to write extension modules. Embeddability means that Python can be embedded in C or C++ programs to provide scripting functions to the programs.

[0075] Dmpython package: a data package that provides the necessary connection package for Python to connect to the DAMO database. The dmpython package described in this application refers to a data package that can connect to a table relational database in a Python environment.

[0076] Xlrd package: a data package used to connect to Excel tables in the Python environment.

[0077] The embodiment of the present invention provides a method for importing table data into a table relational database. The method for importing table data into a table relational database provided by the embodiment of the present invention can be implemented by a computer program, which, when executed, completes each step of the method for importing table data into a table relational database provided by the embodiment of the present invention.

[0078] Reference Figure 1 , the methods for importing table data into a table relational database include:

[0079] S100. In the target operating environment, connections are established with both the table relational database and the table data storage document.

[0080] In one embodiment, the target operating environment is a python environment, that is, under a python server, the python language is used; the table relational database is a DAMO database; and the table data storage document is an excel document.

[0081] In other embodiments, the target operating environment may also be other language environments, the table relational database may be a MySQL database or an Oracle database, and the table data storage document may be a word document, a PPT document, or a CAD document. The purpose is to be able to use the code language in the target operating environment to establish a connection with the table relational database and the table data storage document. Among them, the so-called establishment of a connection refers to the ability to perform basic operation control and data interaction with the table relational database and the table data storage document, such as establishing a data table in the table relational database, performing a search action in the table relational database, performing an add, delete, and modify action in the table relational database, writing data into the table data storage document, performing an add, delete, and modify action in the table data storage document, etc.

[0082] Reference Figure 2 In one application scenario, step S100 specifically includes:

[0083] S110, obtaining document information of table data storage documents.

[0084] The document information includes at least one of a document storage location, a document name, and a document connection port, and the purpose is to determine a unique table data storage document through the document information. In one embodiment, the document information is obtained by automatic acquisition; for example, a first list storing a plurality of document information is pre-set. When the acquisition is required, the document information is retrieved one by one from the first list. In another embodiment, the document information is obtained by outputting an acquisition request, and the corresponding connected display device displays a document information input box based on the output acquisition request; then the display device feeds back the content in the document information input box to obtain the document information.

[0085] S120: Retrieve a document interface, and establish a connection with the table data storage document based on the document interface and document information.

[0086] In one embodiment, the document interface uses the xlrd toolkit in python, and the document information is input into the xlrd toolkit, or the document information is associated with the xlrd toolkit, and the xlrd toolkit can establish a connection with the corresponding table data storage document.

[0087] In other embodiments, the document interface only needs to be able to establish a connection with a table data storage document of a corresponding type. For example, when the type of the table data storage document is a word document, the corresponding interface toolkit in the target operating environment is used to read the document information of the word document and establish a connection with the word document.

[0088] S130: Obtain database information of the table relational database.

[0089] The database information includes at least one of the database name, database IP port, database account, and database password, and the purpose is to determine the unique DAMO database through the database information. It should be noted that for a table-relational database that requires an account and password to enter, the database information must include the corresponding database account and database password. Specifically, in one embodiment, the database information is obtained by retrieving the database information from a preset second list in a preset acquisition order; in another embodiment, the database information and document information are both stored in a third list, and both have a corresponding relationship. After obtaining the document information, the corresponding database information in the third list can be obtained; or after obtaining the database information, the corresponding document information in the third list can be obtained. In another embodiment, the database information is obtained by external input.

[0090] S140: Retrieve a database interface, and establish a connection with a table relational database based on the database interface and database information.

[0091] In one embodiment, the database interface uses the dmpython package in python, and the database information is input into the dmpython package, or the database information is associated with the dmpython package, and the dmpython package can establish a connection with the corresponding table relational database.

[0092] In other embodiments, the database interface only needs to be able to establish a connection with a corresponding type of table relational database. For example, when the type of the table relational database is a MySQL database, the pymysql toolkit is used to establish a connection with the MySQL database.

[0093] It should be noted that steps S110 and S120 have execution order requirements, that is, S110 is executed first, and then S120; correspondingly, steps S130 and S140 also have execution order requirements, that is, S130 is executed first, and then S140. However, there is no execution order requirement between steps S110 and S130, and either S110 can be executed first, or S130 can be executed first, or S110 and S130 can be executed at the same time.

[0094] After obtaining the document information and database information, a connection can be automatically established, which facilitates improving the efficiency of connecting with table relational databases and table data storage documents, thereby improving the efficiency of table data import.

[0095] Reference Figure 1 , S200, based on the connection established with the table data storage document, obtain table item information in the table data storage document.

[0096] Since the program runs in the target running environment, if you want to obtain relevant information from the table data storage document, you need to maintain a connection with the table data storage document. Specifically, the table item information refers to the structural information of the table in the table data storage document, for example, the table item information may include at least one of the table name, the table location, the number of rows and columns in the table, the row and column names of the table, and the font style in the table.

[0097] Reference Figure 3 In one application scenario, step S200 includes:

[0098] S210: Obtain location information of the target table.

[0099] The location information refers to the location of the target table. In one embodiment, the table data storage document is an Excel document. Since multiple tables can exist in an Excel document at the same time, the location information of the target table can be the cell information of the two opposite corners of the target table, for example (A1:C3), which means that the target table has three columns and three rows, A1 is the cell position of the first row and first column of the target table, and C3 is the cell position of the third row and third column of the target table; if there is and not only one sheet page in the Excel document, when determining the location of the target table, the sheet page number information needs to be added, that is, the location information of the target table should include the sheet page number information. For example, the target table includes sheet1, sheet2 and sheet3, but there is and only one table on each sheet page, then the location information of the target table only needs to include the sheet page number information; if the number of tables in each sheet page is uncertain, then the location information of the target table also needs to include the cell information of the two opposite corners of the target table. In addition, the method for obtaining the location information of the target table can be the same or similar to the method for obtaining the document information, which will not be repeated here.

[0100] S220: Lock the target table based on the location information.

[0101] That is, the positions of all cells in the target table are obtained through the position information.

[0102] S230. Based on a preset search condition, obtain the column name of each column in the target table; the search condition includes a fixed condition or a calculated condition.

[0103] Since the target table is locked, the position information of each cell in the target table is known, such as the position of the first row and the first column of the target table. By searching the contents of each cell in the target table according to the search conditions, the column names of each column can be obtained. In one embodiment, all column names of the target table constitute the table item information.

[0104] The column names of the target table are automatically obtained through the preset search conditions. No manual intervention is required for locking the target table or obtaining the column names, which reduces the workload of technicians and improves the efficiency of obtaining table information, thereby improving the efficiency of importing table data.

[0105] Specifically, refer to Figure 4 In an application scenario, when the search condition is a fixed condition, step S230 includes:

[0106] S231. Obtain preset row number information.

[0107] The row number information refers to the number of rows. In one embodiment, the row number information can be a number such as 2, 3, or 4; for example, when the row number information is 2, it refers to the second row of the target table. In other embodiments, the row number information can be letters, symbols, and a combination thereof, and the purpose is to be able to find a specified row in the target table through the row number information. It should be noted that when the table data storage document is an Excel document, the row number information can also refer to the number of rows in a sheet page of the Excel document. For example, when the row number information is 2, it refers to the second row in sheet 1 of the Excel document. This row can be the first row of the target table or the second row of the target table; that is, as long as it can correspond to one of the rows of the target table.

[0108] S232: Determine the row corresponding to the row number information in the target table as the first row.

[0109] The first row is relative to the target table, not the first row in the Excel document. For example, if the location information of the target table is (C6:I20), the first row of the target table is actually the 6th row in the Excel document.

[0110] S233. Read the content of each cell in the first row, and determine the content of each cell in the first row as a column name.

[0111] Since the position of the target table is determined, the number of columns is also determined. Once the first row is determined, the content of each cell in the first row can be obtained; the content of the cell is the column name. It should be noted that this solution is usually suitable for the case where the column names are laid out in the first row of the target table by default.

[0112] To reduce the limitations of reading column names, refer to Figure 5 When the search condition is a calculation condition, step S230 includes:

[0113] S234, traverse the target table along the column direction to obtain the column number information of each row of the target table.

[0114] The column direction includes two situations: from top to bottom and from bottom to top. In this embodiment, the column direction refers to the direction from top to bottom, such as from the first row to the last row in Excel; from the top of the page to the end of the page in Word. In one embodiment, the column number information is equivalent to the row number information, both of which are numbers, such as 1, 2, 3, etc. For ease of understanding, for example, after traversing the target table, there are 5 column number information, namely 1, 10, 10, 10, and 2. This proves that the target table has 5 rows, the first row has only 1 column, the second row of the target table has 10 columns, and so on.

[0115] S235. Obtain the maximum number of columns in the column number information.

[0116] That is, after obtaining all the column number information of the target table, the column number information is compared to obtain the maximum column number. Taking the above as an example, the maximum column number is 10. Of course, in other embodiments, the maximum column number can also be obtained by comparing each newly obtained column number information with the retained column number information to obtain the temporary maximum column number, and then using the temporary maximum column number to update the retained column number information, so as to ensure that the retained column number information is always the largest among all the obtained column number information.

[0117] S236: Calculate the difference between the maximum number of columns and a preset number threshold to obtain a target number of columns.

[0118] The quantity threshold is preset and can be 0, 0.5, 1, 2, 2.4, 3, etc., which is set according to the actual target table style. For example, the table style of the target table is that the first row is the name of the target table, which has only one column; the second row is the creation time, creator and department of the target table, that is, there are 3 columns; the third row is the column name, and the column name includes number, name, gender, age, contact information, and job position, that is, there are 6 columns; after the third row is the specific data in the table, and it is stipulated that a unit can enter a maximum of 2 contact information, and when a unit has 2 corresponding contact information, the corresponding cells are divided into two columns, and the contact information is filled in separately. At this time, the number of columns contained in the rows after the third row, that is, below the third row, may appear to be 7 columns, but will not be less than 6 columns. For this case, the quantity threshold can be set to 1 or 2.

[0119] S237, searching along the column direction for the first target row in the target table whose number of columns is greater than or equal to the target number of columns.

[0120] Taking the above example as an example, as long as the difference between the maximum number of columns 7 and the quantity threshold 2 is greater than or equal to the number of columns 6 in the row where the column name is located, and greater than the number of columns 3 in the second row, it will be sufficient.

[0121] S238. Read the content of each cell in the target row, and determine the content of each cell in the target row as a column name.

[0122] The target row is the row where the column name is located in the target table determined by calculation, and the column name can be obtained by reading the information of each cell in the target row.

[0123] It should be noted that steps S231-S233 are one embodiment, and steps S234-S238 are another embodiment, and the two embodiments cannot exist at the same time. When the search condition is a fixed condition, it is only necessary to write the number of rows into the program, which is convenient and fast, and the program is not prone to errors. When the search condition is a calculation condition, it can reduce the requirements for the table style of the target table when obtaining the column name, which is convenient to reduce the limitations of obtaining the column name, so that more table styles of target tables are applicable, and the flexibility of obtaining the table item information of the target table is improved.

[0124] Reference Figure 1 , S300, creating a data table in the table relational database based on the connection established with the table relational database and the table item information.

[0125] Since a data table needs to be created in a table relational database, it is necessary to maintain a connection relationship with the table relational database. In one embodiment, the table item information is all column names of the target table, so the table item information is used as the table structure of the data table to create a corresponding data table in the table relational database.

[0126] Specifically, refer to Figure 6 In one application scenario, step S300 includes:

[0127] S310. Obtain the table name of the data table.

[0128] The table name of the data table refers to the table name of the data table in the table relational database, and in one embodiment, refers to the table name of the data table in the DAMO database. The table name can be manually input or automatically obtained from a preset table name queue.

[0129] S320, determine whether there is a data table with the same name as the table name in the table relational database.

[0130] Avoid creating data tables repeatedly, which will cause waste of resources and subsequent data confusion.

[0131] If so, execute step S330; otherwise, execute step S340.

[0132] S330, input the column name into the data table corresponding to the table name.

[0133] That completes the entry of the data table structure.

[0134] S340: Create a table creation cursor associated with the table relational database.

[0135] Specifically, in the Python environment, if you want to execute an action in the DAMO database, you need to create a cursor and traverse the SQL statement through the cursor. In this embodiment, the cursor is a table-building cursor. When creating a table-building cursor, the database information of the table-relational database is assigned to the creation statement cursor() of the table-building cursor, and the table-building cursor can be associated with the table-relational data.

[0136] Reference Figure 6 , after step S340, further comprising:

[0137] S350: Start a table creation cursor, and determine a table creation execution instruction based on a combination of the table name and the table item information.

[0138] In one embodiment, after the table creation cursor is started, the table creation cursor begins to traverse the SQL statements associated with the DAMO database. At the same time, the table name of the obtained data table is combined with the table item information obtained from the target table in the Excel document to obtain a table creation execution instruction. It should be noted that the table creation execution instruction is an SQL statement, that is, it can generate corresponding operations in the DAMO database. Specifically, the structure of the table creation execution instruction is createtable+table name+(column name 1, column name 2, ...).

[0139] S360: Traverse the table creation execution instruction based on the table creation cursor to create a data table in the table relational database.

[0140] On the one hand, the table creation cursor is associated with the table relational database, and the table relational database continues to maintain a connection state; on the other hand, the table creation execution instruction contains the table name and table item information, so that a data table can be created in the table relational database.

[0141] After obtaining the table name, first determine whether a data table with the same table name already exists in the table relational database to avoid data confusion when executing subsequent steps, thereby reducing the failure rate and ensuring the efficiency of creating data tables. When a data table with the same table name does not exist in the table relational database, automatically create a table creation cursor and table creation execution instructions, perform table creation actions in the table relational database, and obtain a data table corresponding to the column name of the target table. Automated execution does not require manual intervention, reduces manual workload, and improves the efficiency of creating data tables.

[0142] Reference Figure 1, S400, writing the table data in the table data storage document into the data table.

[0143] In-table data refers to the data in the target table, located below the row where the column name is located, and the data in the cell content corresponds to the column name. For example, the existing target table A:

[0144] serial number Name gender department 1 Abc male LL 2 Bcd male LL

[0145] Then the column names in the target table A include number, name, gender and department; the table data in the target table A include 1, Abc, male and LL in the second row; and 2, Bcd, male and LL in the third row.

[0146] Reference Figure 7 In one application scenario, step S400 includes:

[0147] S410: Get insert data based on the table name and column name.

[0148] Specifically, the table name and column name have been obtained, and the structure of the inserted data is: table name + (column name 1, column name 2, column name 3, ...).

[0149] S420, reading the data in the target table below the row where the column name is located according to the number of rows; reading one row at a time.

[0150] Taking the target table A in the previous text as an example, in the target table A, the row where the column name is located refers to the first row of the target table A, and the row below the column name is the second and third rows of the target table A. Each time a row is read, the contents of all cells in the row are read out.

[0151] It should be noted that there is no requirement for the execution order of steps S410 and S420, and they can be executed simultaneously, or S420 can be executed first and then S410. In this embodiment, step S410 is executed first and then step S420.

[0152] After step S420, the method further includes:

[0153] S430: Obtain insertion information based on the combination of the insertion data and the data in the table.

[0154] That is, the inserted data is combined with the data in the table. It should be noted that since there is at least one piece of data in the table, in one embodiment, there can be at least two pieces of data in the table. Taking 5 pieces as an example, each piece of data in the table will be combined with the inserted data, so the number of inserted information is the same as the number of data in the table.

[0155] Specifically, the structure of the inserted information is: table name + (column name 1, column name 2, column name 3, ...) + (value 1, value 2, value 3, ...). Each piece of data in the table includes value 1, value 2, value 3, etc.

[0156] S440: Create an insert cursor associated with the table relational database.

[0157] The method of creating an insert cursor is the same as that of creating a table cursor, so it will not be repeated here.

[0158] S450: Start the insert cursor and determine the insert action instruction obtained based on the insert information.

[0159] In one embodiment, after the insert cursor is started, the insert cursor begins to traverse the SQL statements associated with the DAMO database. At the same time, the obtained insert information is combined with the preset sentence information to obtain the insert action instruction. Among them, the sentence information includes insertinto and values. The structure of the insert action instruction is: insertinto+table name+(column name 1, column name 2, column name 3, ...)+values+(value 1, value 2, value 3, ...).

[0160] S460, traversing the insert execution instruction based on the insert cursor to enter the data in the table into the data table.

[0161] Since there is at least one insert information, there is also at least one insert action instruction, that is, the number of insert action instructions is the same as the number of insert information. When the insert cursor traverses an insert execution instruction, the table data contained in the insert execution instruction is entered into the corresponding data table in the table relational database.

[0162] In addition, in order to reduce the execution steps, in this application scenario, in step S410, insertinto can be directly inserted before the table name, that is, the structure of the inserted data is: insertinto+table name+(column name 1, column name 2, column name 3, ...).

[0163] Then, when executing step S430, values ​​is inserted between the column name and the data in the table, so that the structure of the insert information becomes: insertinto+table name+(column name 1, column name 2, column name 3, ...)+values+(value 1, value 2, value 3, ...). The structure of the generated insert information is the same as the structure of the insert execution instruction, thereby obtaining the insert information with the same function as the insert execution instruction. At this time, the insert cursor can be directly started, and there is no need to determine the insert action instruction obtained based on the insert information. Reducing the execution steps helps to reduce operating pressure, reduce storage space occupancy and improve the efficiency of importing data in the table.

[0164] Reference Figure 8 In another application scenario, step S400 includes:

[0165] S420, reading the data in the target table below the row where the column name is located according to the number of rows; reading one row at a time.

[0166] This is the same as step S420 in the previous application scenario and will not be repeated here.

[0167] S431. Generate at least one piece of data information based on the table name, column name, and data in the table.

[0168] The data information is composed of the table name, column name and data in the table. Specifically, the structure of the data information is: table name + (column name 1, column name 2, column name 3, ...) + (value 1, value 2, value 3, ...). Since there is at least one piece of data in the table, there is at least one piece of data information. That is, the number of data information is the same as the number of data in the table.

[0169] S440: Create an insert cursor associated with the table relational data.

[0170] This is the same as step S440 in the previous application scenario and will not be repeated here.

[0171] S451, start the insert cursor, and determine the insert execution instruction obtained based on the data information.

[0172] The structure of the insert execution instruction is: insertinto+table name+(column name 1, column name 2, column name 3, ...)+values+(value 1, value 2, value 3, ...). That is, add insertinto before the table name and add values ​​between the column name and the data in the table to form an insert execution instruction.

[0173] S461. Traverse the insert execution instruction based on the insert cursor to enter the data in the table into the data table.

[0174] Different from the previous application scenario, in this application scenario, the data in the table is obtained first, and then the table name, column name and data in the table are combined to form data information. Finally, based on the sentence requirements of the SQL statement, necessary fields are added to the data information to form an insert execution instruction. The entire execution process also does not require manual intervention, which improves the efficiency of importing table data.

[0175] The implementation principle of importing table data into a table relational database in one embodiment of the present application is as follows: after establishing a connection with the DAMO database and the Excel document, first determine the location of the target table from the Excel document and read the column names of each column in the target table. Then create a data table in the DAMO database, and write the column names into the data table as the table structure of the data table. Finally, read the table data of the target table from Excel, that is, the data in the table, and input the table data into the corresponding data table in the DAMO database to complete the automatic import of the table data. This improves the efficiency and quality of table data import. For ease of understanding, as shown in the figure, Fig. 9 As shown in the figure, it is a table in an Excel document. The table has 5 rows and 8 columns, and the position is (A1:H5), that is, the cell in the first row and the first column is A1, and the cell in the fifth row and the eighth column is H5; the content in the cells in the first row of the table is the column name, and the content in the cells in the second to fifth rows is the data in the table. Fig.10 As shown in the DAMO database, Fig. 9 The data table generated by the table in .

[0176] The present application also discloses a system for importing table data into a table relational database, which is applied in a target operating environment. Fig.11 , including a connection module 1, used to establish a connection with both a table relational database and a table data storage document. An acquisition module 2, used to acquire table item information in a table data storage document based on the connection established with the table data storage document. A table creation module 3, used to create a data table in the table relational database based on the connection established with the table relational database and the table item information. And a writing module 4, used to write the table data in the table data storage document into the data table.

[0177] In one embodiment, the connection module 1 includes a document acquisition unit, a database acquisition unit and a connection unit. The document acquisition unit is used to acquire document information of a table data storage document; the database acquisition unit is used to acquire database information of a table relational database; the connection unit is used to call a document interface and a database interface, and establish a connection with the table data storage document based on the document interface and the document information, and establish a connection with the table relational database based on the database interface and the database information.

[0178] The acquisition module 2 includes a table acquisition unit, a table locking unit and a retrieval unit. The table acquisition unit is used to acquire the location information of the target table; the table locking unit is used to lock the target table based on the location information; the retrieval unit is used to acquire the column names of each column in the target table based on the preset retrieval conditions. The retrieval conditions include fixed conditions and calculated conditions.

[0179] Specifically, when the retrieval condition is a fixed condition, the retrieval unit includes a calling sub-unit for obtaining preset row number information; a first row sub-unit for determining the row corresponding to the row number information in the target table as the first row; and a reading sub-unit for reading the contents of each cell in the first row and determining the contents of each cell in the first row as the column name.

[0180] When the retrieval condition is a calculation condition, the retrieval unit includes a column number subunit, which is used to traverse the target table along the column direction to obtain the column number information of each row of the target table; a comparison subunit, which is used to obtain the maximum column number in the column number information; a calculation subunit, which is used to calculate the difference between the maximum column number and a preset number threshold to obtain the target column number; a target row subunit. It is used to find the first target row in the target table whose number of columns is greater than or equal to the target number of columns along the column direction; and a column name subunit, which is used to read the content of each cell in the target row and determine the content of each cell in the target row as the column name.

[0181] The table building module 3 includes a table name unit, a first cursor unit, a second instruction unit and a table building unit. The table name unit is used to obtain the table name of the data table; the cursor unit is used to create a table building cursor associated with the table relational database; the second instruction unit is used to start the table building cursor and determine the table building execution instruction based on the combination of the table name and the table item information; the table building unit is used to traverse the table building execution instruction based on the table building cursor to create a data table in the table relational database.

[0182] The writing module 4 includes a reading unit, a combining unit, a second cursor unit, a second instruction unit and an importing unit. The reading unit is used to read the data in the table below the row where the column name is located in the target table according to the number of rows; one row is read each time; the combining unit is used to generate at least one data information based on the table name, column name and data in the table; the second cursor unit is used to create an insert cursor associated with the table relational database; the second instruction unit is used to start the insert cursor and determine the insert execution instruction obtained based on the data information; the importing unit is used to traverse the insert execution instruction based on the insert cursor to enter the data in the table into the data table.

[0183] It should be noted here that the description of the above system embodiment for importing table data into a table relational database is similar to the above method description and has the same beneficial effects as the method embodiment. For technical details not disclosed in the system embodiment for importing table data into a table relational database of the present invention, those skilled in the art should refer to the description of the method embodiment of the present invention for understanding.

[0184] It should be noted that, in the embodiment of the present invention, if the above method is implemented in the form of a software function module and sold or used as an independent product, it can also be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the embodiment of the present invention can be essentially or partly reflected in the form of a software product that contributes to the prior art. The computer software product is stored in a storage medium, including a number of instructions to enable a computer device (which can be a personal computer, a server, or a network device, etc.) to execute all or part of the methods described in each embodiment of the present invention. The aforementioned storage medium includes: various media that can store program codes, such as a U disk, a mobile hard disk, a read-only memory (ROM, Read Only Memory), a magnetic disk or an optical disk. In this way, the embodiment of the present invention is not limited to any specific combination of hardware and software.

[0185] Correspondingly, an embodiment of the present application also discloses a storage medium storing a computer program that can be loaded by a processor and execute the above method.

[0186] An embodiment of the present application also discloses a device for importing table data into a table relational database, including a memory and a processor, wherein a method for importing table data into a table relational database is stored in the memory; and the processor is used to adopt the above method when executing the method for importing table data into a table relational database.

[0187] The above description of the device and storage medium embodiment for importing table data into a table relational database is similar to the description of the above method embodiment, and has similar beneficial effects as the method embodiment. For technical details not disclosed in the device and storage medium embodiment for importing table data into a table relational database of the present invention, please refer to the description of the method embodiment of the present invention for understanding.

[0188] It should be understood that "one embodiment" or "an embodiment" mentioned throughout the specification means that specific features, structures or characteristics related to the embodiment are included in at least one embodiment of the present invention. Therefore, "in one embodiment" or "in an embodiment" appearing throughout the specification does not necessarily refer to the same embodiment. In addition, these specific features, structures or characteristics can be combined in one or more embodiments in any suitable manner. It should be understood that in various embodiments of the present invention, the size of the serial number of the above-mentioned processes does not mean the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiment of the present invention. The serial numbers of the above-mentioned embodiments of the present invention are only for description and do not represent the advantages and disadvantages of the embodiments.

[0189] It should be noted that, in this article, the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, an element defined by the sentence "comprises a ..." does not exclude the existence of other identical elements in the process, method, article or device including the element.

[0190] In the several embodiments provided by the present invention, it should be understood that the disclosed devices and methods can be implemented in other ways. The device embodiments described above are only schematic. For example, the division of the units is only a logical function division. There may be other division methods in actual implementation, such as: multiple units or components can be combined, or can be integrated into another system, or some features can be ignored or not executed. In addition, the coupling, direct coupling, or communication connection between the components shown or discussed can be through some interfaces, and the indirect coupling or communication connection of the devices or units can be electrical, mechanical or other forms.

[0191] The units described above as separate components may or may not be physically separated, and the components displayed as units may or may not be physical units; they may be located in one place or distributed on multiple network units; some or all of the units may be selected according to actual needs to achieve the purpose of the present embodiment.

[0192] In addition, all functional units in the embodiments of the present invention may be integrated into one processing unit, or each unit may be separately used as a unit, or two or more units may be integrated into one unit; the above-mentioned integrated units may be implemented in the form of hardware or in the form of hardware plus software functional units.

[0193] A person of ordinary skill in the art can understand that: all or part of the steps of implementing the above-mentioned method embodiment can be completed by hardware related to program instructions, and the aforementioned program can be stored in a computer-readable storage medium. When the program is executed, it executes the steps of the above-mentioned method embodiment; and the aforementioned storage medium includes: various media that can store program codes, such as mobile storage devices, ROMs, magnetic disks or optical disks.

[0194] Alternatively, if the above-mentioned integrated unit of the present invention is implemented in the form of a software function module and sold or used as an independent product, it can also be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the embodiment of the present invention, or the part that contributes to the prior art, can be embodied in the form of a software product, which is stored in a storage medium and includes several instructions for a device to execute all or part of the methods described in each embodiment of the present invention. The aforementioned storage medium includes: various media that can store program codes, such as mobile storage devices, ROMs, magnetic disks or optical disks.

[0195] The above disclosure is only the preferred embodiment of the present invention, which certainly cannot be used to limit the scope of the present invention. Therefore, equivalent changes made according to the claims of the present invention are still within the scope of the present invention.

Claims

1. A method for importing table data into a table relational database, characterized in that: include: In the target operating environment, establish connections with both the table relational database and the table data storage document; Based on the connection established with the table data storage document, obtaining table item information in the table data storage document; Creating a data table in the table relational database based on the connection established with the table relational database and the table item information; Writing the table data in the table data storage document into the data table; The step of obtaining the table item information in the table data storage document comprises: Get the location information of the target table; locking the target table based on the location information; Based on a preset search condition, obtaining the column name of each column in the target table; the search condition includes a fixed condition or a calculated condition; When the search condition is the fixed condition, the step of searching for the column names of each column in the target table based on the preset search condition includes: Get the preset number of rows; Determine the row in the target table corresponding to the row number information as the first row; Read the content of each cell in the first row, and determine the content of each cell in the first row as the column name; When the search condition is a calculation condition, the step of searching for the column names of each column in the target table based on the preset search condition includes: Traversing the target table along the column direction to obtain the column number information of each row of the target table; Obtain the maximum number of columns in the column number information; Calculate the difference between the maximum number of columns and a preset number threshold to obtain a target number of columns; Searching along the column direction for the first target row in the target table where the number of columns is greater than or equal to the target number of columns; The content of each cell in the target row is read, and the content of each cell in the target row is determined as the column name.

2. A method for importing table data into a table relational database as claimed in claim 1, characterized in that: The step of creating a data table in the table relational database based on the connection established with the table relational database and the table item information comprises: Get the table name of the data table; Creating a table creation cursor associated with the table relational database; Starting the table creation cursor, and determining a table creation execution instruction based on a combination of the table name and the table item information; The table creation execution instruction is traversed based on the table creation cursor to create the data table in the table relational database.

3. A method for importing table data into a table relational database as claimed in claim 2, characterized in that: The step of writing the table data in the table data storage document into the data table comprises: Read the data in the target table below the row where the column name is located according to the number of rows; read one row at a time; Generate at least one piece of data information based on the table name, the column name and the data in the table; Creating an insert cursor associated with the table relational database; Starting the insert cursor and determining an insert execution instruction obtained based on the data information; The insert execution instruction is traversed based on the insert cursor to enter the data in the table into the data table.

4. A method for importing table data into a table relational database as claimed in claim 3, characterized in that: After obtaining the table name of the data table, the method further includes: Determine whether there is a data table with the same name as the table name in the table relational database; If not, executing the step of creating a table cursor associated with the table relational database; If so, the column name is input into the data table corresponding to the table name.

5. The method for importing table data into a table relational database according to claim 1, characterized in that: The step of establishing a connection with both the table relational database and the table data storage document in the target operating environment includes: Get the document information of the table data storage document; Retrieve a document interface, and establish a connection with the table data storage document based on the document interface and the document information; Get database information of table relational database; A database interface is called, and a connection is established with the table relational database based on the database interface and the database information.

6. A system for importing table data into a table relational database, applied in a target operating environment, characterized in that: It includes a connection module (1) for establishing a connection with both a table relational database and a table data storage document; An acquisition module (2), configured to acquire table item information in the table data storage document based on a connection established with the table data storage document; A table creation module (3), used for creating a data table in the table relational database based on the connection established with the table relational database and the table item information; and a writing module (4), used for writing the table data in the table data storage document into the data table; The step of obtaining the table item information in the table data storage document comprises: Get the location information of the target table; locking the target table based on the location information; Based on a preset search condition, obtaining the column name of each column in the target table; the search condition includes a fixed condition or a calculated condition; When the search condition is the fixed condition, the step of searching for the column names of each column in the target table based on the preset search condition includes: Get the preset number of rows; Determine the row in the target table corresponding to the row number information as the first row; Read the content of each cell in the first row, and determine the content of each cell in the first row as the column name; When the search condition is a calculation condition, the step of searching for the column names of each column in the target table based on the preset search condition includes: Traversing the target table along the column direction to obtain the column number information of each row of the target table; Obtain the maximum number of columns in the column number information; Calculate the difference between the maximum number of columns and a preset number threshold to obtain a target number of columns; Searching along the column direction for the first target row in the target table where the number of columns is greater than or equal to the target number of columns; The content of each cell in the target row is read, and the content of each cell in the target row is determined as the column name.

7. A device for importing table data into a table relational database, comprising a memory and a processor, characterized in that: The memory stores a method for importing table data into a table relational database, and the processor is used to adopt any one of the methods of claims 1 to 5 when executing the method for importing table data into a table relational database.

8. A storage medium, characterized in that: A computer program is stored which can be loaded by a processor and executes the method according to any one of claims 1 to 5.

Citation Information

Patent Citations

  • File storage method and device, computer equipment and computer readable storage medium

    CN112835901A