CN109871405B - Method and device for importing Excel table data into database - Google Patents
Method and device for importing Excel table data into database Download PDFInfo
- Publication number
- CN109871405B CN109871405B CN201811523732.3A CN201811523732A CN109871405B CN 109871405 B CN109871405 B CN 109871405B CN 201811523732 A CN201811523732 A CN 201811523732A CN 109871405 B CN109871405 B CN 109871405B
- Authority
- CN
- China
- Prior art keywords
- data
- specified
- data table
- type
- column fields
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Images
Abstract
The invention provides a method for importing Excel table data into a database, which comprises the following steps: s100) specifying storage positions and names of a source data table and a target data table, data types of one or more data table column fields and one or more column fields, and a data reading method; s200) checking whether the specified storage position, name and one or more data table column fields exist or not, checking whether the data type of the specified one or more data table column fields is consistent with the specified data type or not, if the checking results are yes, continuing to execute the following steps, and if at least one checking result is no, prompting an error; s300) reading data under one or more specified data column fields in a data table of specified position specified names according to a first reading method; s400) saves the read data under the specified data table column field of the target data table at the specified position.
Description
Technical Field
The invention relates to the field of databases, in particular to a method and a device for importing Excel table data into a database.
Background
Microsoft Excel is a piece of spreadsheet software written by Microsoft for computers using Windows and Apple Macintosh operating systems. Excel is the personal computer data processing software which is relatively popular at present. At present, the following two methods are mainly used for importing data in an Excel form into a system.
The first method comprises the following steps: writing an Insert statement by using an Excel formula, and copying the Insert statement to SQL running software for execution, thereby realizing the import work of Excel data, wherein the second method comprises the following steps: and writing codes to read the EXCEL form by using POI or other readable and writable EXCEL programs, thereby realizing the import of data into the system.
The above-described conventional method has a problem that a developer needs to additionally learn program development codes.
Disclosure of Invention
The invention provides a method and a device for importing Excel form data into a database, aiming at the problem that developers need to additionally learn program development codes in the process of importing the Excel form into the database in the prior art.
Firstly, the invention provides a method for importing Excel table data into a database, which comprises the following steps:
s100) specifying storage positions, names, data types of one or more data table column fields and one or more column fields of a source data table and a target data table and a data reading method;
s200) checking whether the appointed storage position, name and one or more data table column fields exist or not, checking whether the data type of the appointed one or more data table column fields is consistent with the appointed data type or not, if the checking results are yes, continuing to execute the following steps, and if at least one checking result is no, prompting an error;
s300) reading data under one or more specified data column fields in a data table of the specified position specified name according to a first reading method;
s400) saving the read data under a specified data table column field of the target data table at the specified position.
Specifically, in the method provided by the present invention, the first reading method includes at least one of the following methods: full table scan, ROWID, and index.
Further, in the method of the present invention, if the specified data type is a character type, the method further includes:
step S100 further comprises the following substeps: specifying a minimum length of data; at the same time, the user can select the desired position,
step S300 further includes the following substeps: and judging whether the length of the read data is greater than the specified minimum length, if so, continuing to execute the subsequent steps, and if not, reading the next data record.
Further, in the method of the present invention, if the specified data type is a character type, the method further includes:
step S100 further comprises the following substeps: specifying a maximum length of the data; at the same time, the user can select the required time,
step S300 further includes the following substeps: and judging whether the length of the read data is smaller than the specified maximum length, if so, continuing to execute the subsequent steps, and if not, reading the next data record.
Further, in the above method provided by the present invention, if the specified data type is a numerical type, then:
step S100 further comprises the following substeps: specifying a numerical type, and specifying the maximum length and the numerical range of data for the integer numerical type; for a floating-point type numerical value type, appointing a reserved digit and a numerical range after a decimal point; at the same time, the user can select the required time,
step S300 further includes the following substeps:
judging whether the numerical type of the read data is consistent with the specified numerical type, if so, continuously judging whether the length of the numerical value is smaller than the specified maximum length and whether the numerical value falls into the specified numerical range for the numerical value of the integer type, and if so, continuously executing the subsequent steps; for a floating-point type numerical value, continuously judging whether the number of digits after the decimal point of the numerical value is smaller than a specified reserved number of digits and whether the numerical value falls into a specified numerical range, if so, continuously executing subsequent steps, and if not, prompting an error and reading the next data record;
wherein the numerical type is integer type or floating point type.
Further, in the above method according to the present invention,
step S100 also comprises the steps of determining whether the specified data is a mandatory item; at the same time, the user can select the desired position,
step S300 further includes determining whether the read data is empty, if the read data is empty, reading the next data record, and if the read data is not empty, continuing to execute the subsequent steps.
Further, in the above method proposed by the present invention,
step S100 further includes specifying a starting line number and/or an ending line number of data in one or more data table column fields; at the same time, the user can select the desired position,
step S300 further includes starting reading data from a starting number of rows of data in the specified one or more data table column fields and/or stopping reading data from an ending number of rows of data in the specified one or more data table column fields.
Further, in the above method according to the present invention,
step S100 further comprises specifying a constraint relationship between data in one or more data table column fields and data in another one or more data table column fields; at the same time, the user can select the desired position,
step S400 further includes determining whether the data in the specified one or more data table column fields and the data in the other one or more data table column fields satisfy a specified constraint relationship, if so, continuing to perform the subsequent steps, and if not, prompting an error.
Further, in the above method proposed by the present invention,
step S100 also comprises the steps of appointing one-to-one relation or one-to-many relation between the data of the source data table and the data of the target data table, and if the data of the source data table and the data of the target data table are in the one-to-many relation, appointing one or more data column fields with corresponding relation at the same time;
step S400 further includes saving the read data under one or more designated data table column fields of the target data table at the designated location.
Secondly, the invention provides a device for importing Excel table data into a database, which comprises the following modules:
the system comprises a specifying module, a reading module and a reading module, wherein the specifying module is used for specifying the storage positions and names of a source data table and a target data table, the data types of one or more data table column fields and one or more column fields and the data reading method;
the checking module is used for checking whether the specified storage position, the specified name and one or more data table column fields exist or not, checking whether the data types of the specified one or more data table column fields are consistent with the specified data types or not, if the checking results are yes, continuing to execute the following steps, and if at least one checking result is no, prompting an error;
the reading module is used for reading data under one or more specified data column fields in a data table of a specified position specified name according to a first reading method;
and the storage module is used for storing the read data in a designated data table column field of a target data table at a designated position.
Finally, the invention proposes a computer-readable storage medium having stored thereon computer instructions which, when executed by a processor, implement the steps of the above-mentioned method.
Drawings
Fig. 1 is a data correspondence representation intention of a first embodiment of a method for importing Excel table data into a database according to the present application;
fig. 2 is a schematic diagram illustrating a data type definition page of a first embodiment of a method for importing Excel table data into a database according to the present application;
fig. 3 is a schematic diagram illustrating a data definition page of a first embodiment of a method for importing Excel form data into a database according to the present application;
FIG. 4 is a flowchart illustrating a second embodiment of a method for importing Excel table data into a database according to the present application;
FIG. 5 is a flowchart illustrating a third embodiment of a method for importing Excel table data into a database according to the present application;
FIG. 6 is a flowchart illustrating a fourth embodiment of a method for importing Excel table data into a database according to the present application;
FIG. 7 is a flowchart illustrating a fifth embodiment of a method for importing Excel table data into a database according to the present application;
fig. 8 is a flowchart illustrating a sixth embodiment of a method for importing Excel form data into a database according to the present application;
fig. 9 is a flowchart illustrating a seventh embodiment of a method for importing Excel form data into a database according to the present application;
FIG. 10 is a flowchart illustrating an eighth embodiment of a method for importing Excel table data into a database according to the present application;
fig. 11 is a block diagram illustrating an apparatus for importing Excel table data into a database according to the present application.
Detailed Description
The conception, the specific structure and the technical effects of the present invention will be clearly and completely described in conjunction with the embodiments and the accompanying drawings to fully understand the objects, the schemes and the effects of the present invention. It should be noted that the embodiments and features of the embodiments in the present application may be combined with each other without conflict. The same reference numbers will be used throughout the drawings to refer to the same or like parts.
It should be noted that, unless otherwise specified, when a feature is referred to as being "fixed" or "connected" to another feature, it can be directly fixed or connected to the other feature or indirectly fixed or connected to the other feature. Furthermore, the descriptions of upper, lower, left, right, etc. used in this application are only relative to the positional relationship of the various elements of the application with respect to one another in the drawings. As used in this application and the appended claims, the singular forms "a", "an", and "the" are intended to include the plural forms as well, unless the context clearly indicates otherwise.
Furthermore, unless defined otherwise, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this invention belongs. The terminology used in the description herein is for the purpose of describing particular embodiments only and is not intended to be limiting of the application. As used herein, the term "and/or" includes any combination of one or more of the associated listed items.
It will be understood that, although the terms first, second, third, etc. may be used herein to describe various elements, these elements should not be limited by these terms. These terms are only used to distinguish one element of the same type from another. For example, a first element could be termed a second element, and, similarly, a second element could be termed a first element, without departing from the scope of the present application. The word "if" as used herein may be interpreted as "at \8230; \8230when" or "when 8230; \8230when", depending on the context.
Referring to fig. 1, a data correspondence representation intention of a first embodiment of a method for importing Excel table data into a database according to the present application is shown, in this embodiment, a report engine mainly includes two Excel tables, which are data and data _ Define respectively. The following is a detailed analysis of the two tables.
In particular, the data table mainly defines the relationship between data in the Excel table and the database table. In general, a data report is a real report used by a user, and a developer fills { prefix. The prefix is the code number of a certain database table, and the defined relation between the prefix and the database table is embodied in data _ Define. column _ name is a real field entry defined in a database table, and the program will detect whether the field entry exists in the database table. For example, in the vehicle access registration table in fig. 1, main is a code number of a name of a database table t _ car _ instance, each header in the table defines a field entry in the database, that is, each header in the table corresponds to a field entry in the database, and the table content corresponding to the header corresponds to the value of the field, for example, { check _ date } corresponds to the detection date, which means that the detection date corresponds to the field entry of check _ date in the database, and the value in the detection date is the value of the field entry. In this embodiment, the type of the field value may be further defined such that the value of the field conforms to the definition of the field, for example, the type of the value of check _ date may be a date type.
Further, as can be seen from the table in fig. 1, the header names are in one-to-one correspondence with the field items of the data table, that is, there is no situation that the same header name corresponds to different field items of the data table, nor is there a situation that different header names correspond to the same field item of the data table.
In particular, the data _ Define table mainly implements definition of form data type and reading method. The table contains two part tabs, a data type definition tab and a data type definition tab.
Specifically, the data type defines a tab: the data _ type is mainly used for defining the data type, the data length, the logical correspondence between the data and the database table and whether the data is necessary to be filled. The definition of Data _ Type is usually N { datatype. Define1.Define2 \8230;. DefineN }. The dataType is data type, and define1, define2 to define N are data characteristics including data length, data format, data filling requirement and the like. The following table defines dataType details:
further, refer to a schematic diagram of a data type definition page of a first embodiment of a method for importing Excel table data into a database according to the present application shown in fig. 2; each header in the table corresponds to a definition of a value of its content, which is again described by using a header of a detection date, a value corresponding to the header in the figure is defined as date, m.yyyy-mm-dd, according to the description in the table above, a value corresponding to the header is in a date format, i.e., a date format, M represents that the header is a mandatory item, yyyy-mm-dd represents a date in a four-digit year-two-digit month-two-digit date, e.g., 2018-12-10, which represents 12 months and 10 days in 2018. For another example, in the header of the verification of the payload quality, the value is defined as decimall.2. M, which indicates that the number is in the form of decimal, i.e., decimall, and at most two bits after the decimal point can be reserved, for example, both data of 10.1 and 10.12 are allowed, and likewise, M indicates that the header is a mandatory item.
Specifically, the data type defines a tab definition, which is classified into the following two broad categories:
Specifically, referring to fig. 3, a schematic diagram of a data definition page of a first embodiment of a method for importing Excel form data into a database according to the present application is shown; the table of the first part defines a storage process required to be executed before importing, a JAVA method required to be executed before importing, a storage process required to be executed after importing and a JAVA method required to be executed after importing respectively.
| before procedure | sp_before_import_car_insp |
| before java Method | NULL |
| after procedure | sp_after_import_car_insp |
| after java Method | NULL |
As can be seen from the table, the storage process that needs to be executed before importing is defined as sp _ before _ import _ car _ insp, which operates on the table of import _ car and may be an instruction, a step, a statement, or an executable file, and the instruction, the step, the statement, or the executable file needs to be executed before importing data. Specifically, the instruction, step, statement or executable file may be optimized for importing data, such as sorting data, deleting duplicate data or defining some stored method; similarly, the storage procedure that needs to be executed after the import is defined as sp _ after _ import _ car _ insp. It can also be seen from the above table that the JAVA methods that need to be executed before the import and the JAVA methods that need to be executed after the import are not specified in this embodiment.
The second part of the table is as follows, which serves as one of the examples in this embodiment:
| Define Start | |
| Define Type | Header |
| Mapping Table | t_car_inspection |
| prefix | |
| Start Row | |
| 1 | |
| |
18 |
| Define End |
it can be seen from the table that the Define Start tag and the Define End tag are not required to be filled in, the value of the Define Type is Header, which represents that there is a one-to-one relationship between the tables in the data table corresponding to the database, that is, there is no case that one Header corresponds to multiple field names of the database table. Values of Mapping Table and prefix are t _ car _ instruction and main respectively, which indicate that the name of the target database Table is t _ car _ instruction, and the data in the corresponding source Table is started by main and corresponds to the first embodiment of the invention; the values of Start Row and End Row are 1 and 18, respectively, which represents the beginning of the first Row and the End of the 18 th Row of the data table.
The third part of the table is as follows, as another example in this embodiment:
as can be seen from the table, the Define Start tag and the Define End tag are not required to be filled in, the value of Define Type is Detail, and the data table represents the case that the table corresponding to the database has a one-to-many relationship, i.e. different headers correspond to a plurality of field names of the database table. Values of Mapping Table and prefix are t _ car _ instruction and detail respectively, which indicate that the name of the target database Table is t _ car _ instruction, and the data in the corresponding source Table is beginning with detail and corresponds to the first embodiment of the invention; the values of Start Row and End Row are 19 and NULL respectively, which represents that the data table starts from the 19 th Row of the data table and does not specify the End position; in addition, the value of Group Row is 1, which means that many-to-one relationship is defined, that is, the definition of Detail is matched.
Specifically, referring to a flowchart of a second embodiment of a method for importing Excel table data into a database proposed by the present application shown in fig. 4, the following steps are shown in the flowchart:
s100) specifying storage positions and names of a source data table and a target data table, data types of one or more data table column fields and one or more column fields, and a data reading method;
s200) checking whether the specified storage position, name and one or more data table column fields exist or not, checking whether the data type of the specified one or more data table column fields is consistent with the specified data type or not, if the checking results are yes, continuing to execute the following steps, and if at least one checking result is no, prompting an error;
s300) reading data under one or more specified data column fields in a data table of specified position specified names according to a first reading method;
s400) storing the read data under the column field of the appointed data table of the target data table at the appointed position;
specifically, the first reading method includes at least one of the following methods: full table scan, ROWID, and index;
specifically, the reading method of the full-table scan includes: all rows in the table are read and each row is checked for the constraint of the statement, each data block allocated to the table being read sequentially until the Highest Water Mark (HWM) of the table is read. Instead of reading only one data block, one multi-block read operation can read multiple blocks of data (db _ block _ multi-block _ read _ count) at one time, which greatly reduces the total number of I/O times and improves the throughput of the system.
Specifically, reading by the ROWID means that the ROWID indicates the data file, data block and the position of the row in the block where the row is located, so that the ROWID can quickly locate the target data.
Specifically, the method searches the value of the ROWID corresponding to the data through the index (for a non-unique index, multiple values of the ROWID may be returned), and then obtains the specific data from the table directly according to the ROWID, and this searching manner is called index scanning or index searching.
Further, referring to a flowchart of a third embodiment of a method for importing Excel table data into a database proposed by the present application shown in fig. 5, this embodiment shows a case where the specified data type is character type data, and then step S100 further includes the following sub-steps: specifying a minimum length and/or a maximum length of data; meanwhile, step S300 further includes the following sub-steps: judging whether the length of the read data is larger than the specified minimum length and/or smaller than the specified maximum length, if so, continuing to execute the subsequent steps, and if not, reading the next data record; specifically, referring to the schematic diagram of the data type definition page of the first embodiment of the method for importing Excel form data into the database proposed by the present application shown in fig. 2, for example, the trend type of the vehicle identification code in the figure is character type, and the length of the data is required to be at least 17 bits, even if the method proposed by the present invention is used to prompt an error or skip the data in case of reading a vehicle identification code less than 17 bits, read the next data record, and perform the same judgment, in the present invention, when defining character type data, the number N is used to define the maximum length of the data, and the number NB is used to define the minimum length of the data, for example, { varchar.17}, which indicates that the maximum length of the character type data is 17, { varchar.2b } indicates that the length of the data item must be greater than 2.
Further, referring to a flowchart of a fourth embodiment of a method for importing Excel table data into a database proposed by the present application shown in fig. 6, this embodiment shows a case where the specified data type is a numerical type, in this case, step S100 further includes the following sub-steps: designating a numerical type, and designating the maximum length and the numerical range of data for the integer numerical type; for the floating-point type numerical value type, the reserved digit and numerical value range after the decimal point are appointed; meanwhile, step S300 further includes the following sub-steps: judging whether the numerical type of the read data is consistent with the specified numerical type, if so, continuously judging whether the length of the numerical value is smaller than the specified maximum length and whether the numerical value falls into the specified numerical range for the numerical value of the integer type, and if so, continuously executing the subsequent steps; for a floating-point type numerical value, continuously judging whether the number of digits after the decimal point of the numerical value is smaller than a specified reserved number of digits and whether the numerical value falls into a specified numerical range, if so, continuously executing subsequent steps, and if not, prompting an error and reading the next data record; in the present invention, when defining numeric data, integer and floating-point data are defined using interger and decimal, respectively, and in the integer data, the maximum length or value range of the numeric value may be further defined, for example, { interger. -2> = } indicates that the integer must be greater than or equal to-2, and further for example, { interger.17} indicates that the maximum length of the data is 17 bits.
Further, referring to a flowchart of a fifth embodiment of a method for importing Excel table data into a database, which is shown in fig. 7, this embodiment shows a case that whether specified data is a mandatory item is shown, if the specified data is a mandatory item, the read data is required to be non-empty when the data is read, otherwise an error is prompted or a next data record is read.
Further, referring to a flowchart of a sixth embodiment of a method for importing Excel table data into a database, shown in fig. 8, this embodiment shows a case of specifying a starting line number and/or an ending line number in a data table, if the starting line number and/or the ending line number are specified, data is read from a starting line number of data in a column field of the specified data table or data tables, and/or data reading is stopped at an ending line number of data in the column field of the specified data table or data tables.
Further, referring to a flowchart of a seventh embodiment of a method for importing Excel table data into a database, which is shown in fig. 9 and is proposed in the present application, this embodiment shows a case where a constraint relationship between data in one or more data table column fields and data in another or more data table column fields is specified, and data in a data table may have a certain logical relationship, for example, in data in a date format, a certain logical relationship exists between a month and a day where the month is located, for example, the number of days of 2 months is at most 29 days, and if there are 2 months and 30 days in a piece of data at the same time, it is determined that the data is erroneous. Therefore, some existing constraint relations, namely logic constraint relations among different data are specified in the embodiment, so that the validity and the reasonability of the data can be further ensured, and unreasonable or wrong data can be found in time.
Further, referring to a flowchart of an eighth embodiment of the method for importing Excel table data into a database provided by the present application shown in fig. 10, this embodiment shows a case of one-to-one relationship or one-to-many relationship between source data and target data, if the source data and the target data are in one-to-many relationship, one or more data column fields having corresponding relationship at the same time need to be specified, and when data is stored, the read data needs to be stored in one or more specified data table column fields of the target data table at a specified position.
Further, referring to a frame diagram of an apparatus for importing Excel table data into a database proposed by the present application shown in fig. 11, the apparatus includes the following modules:
the system comprises a specifying module, a reading module and a reading module, wherein the specifying module is used for specifying the storage positions and names of a source data table and a target data table, the data types of one or more data table column fields and one or more column fields and the data reading method;
the checking module is used for checking whether the specified storage position, the specified name and one or more data table column fields exist or not, checking whether the data types of the specified one or more data table column fields are consistent with the specified data types or not, if the checking results are yes, continuing to execute the following steps, and if at least one checking result is no, prompting an error;
the reading module is used for reading data under one or more specified data column fields in a data table of a specified position specified name according to a first reading method;
and the storage module is used for storing the read data in a designated data table column field of a target data table at a designated position.
Specifically, the first reading method includes at least one of the following methods: full table scan, ROWID, and index;
specifically, the reading method of the full-table scan includes: all rows in the table are read and each row is checked for the constraint of the statement, each data block allocated to the table being read sequentially until the Highest Water Mark (HWM) of the table is read. One multi-block read operation can enable one I/O to read a plurality of data blocks (db _ block _ multiblock _ read _ count parameter setting), but not only one data block, so that the total I/O times are greatly reduced, and the throughput of the system is improved.
Further, if the specified data type is character type data, the specifying module further comprises sub-modules of the following functions: specifying a minimum length and/or a maximum length of data; meanwhile, the reading module also comprises sub-modules with the following functions: judging whether the length of the read data is larger than the specified minimum length and/or smaller than the specified maximum length, if so, continuing to execute the subsequent steps, and if not, reading the next data record; specifically, referring to the schematic diagram of the data type definition page of the first embodiment of the method for importing Excel table data into the database proposed by the present application shown in fig. 2, for example, the trend type of the vehicle identification code in the figure is a character type, and the length of the data is required to be at least 17 bits, even if the method proposed by the present invention is used, in the case of reading a vehicle identification code less than 17 bits, an error is prompted or the data is skipped, the next data record is read, and the same judgment is performed, in the present invention, when defining character type data, the maximum length of the data is defined by using a number N, and the minimum length of the data is defined by using a number NB, for example, { varchar.17}, which represents that the maximum length of the character type data is 17, { varchar.2b } indicates that the length of the data item must be greater than 2.
Further, if the specified data type is a numerical type, the specifying module further includes sub-modules of the following functions: designating a numerical type, and designating the maximum length and the numerical range of data for the integer numerical type; for the floating-point numerical type, appointing the reserved digit and numerical range after the decimal point; meanwhile, the reading module also comprises sub-modules with the following functions: judging whether the numerical type of the read data is consistent with the specified numerical type, if so, continuously judging whether the length of the numerical value is smaller than the specified maximum length and whether the numerical value falls into the specified numerical range for the numerical value of the integer type, and if so, continuously executing the subsequent steps; for a floating-point type numerical value, continuously judging whether the number of digits after the decimal point of the numerical value is smaller than a specified reserved number of digits and whether the numerical value falls into a specified numerical range, if so, continuously executing subsequent steps, and if not, prompting an error and reading the next data record; in the present invention, when defining numeric data, an integer and a floating point are defined by using interger and decimal, respectively, and in the integer data, a maximum length or a value range of the numeric value may be further defined, for example, { interger. -2> = } indicates that the integer must be greater than or equal to-2, and further for example, { interger.17} indicates that the maximum length of the data is 17 bits.
Further, the specified data may also be set as a mandatory item, if the specified data is a mandatory item, the read data is required to be not null when the data is read, otherwise an error is prompted or a next data record is read.
Further, a Start line number and/or an End line number in the data table may be specified, and if the Start line number and/or the End line number are specified, reading data is started from the Start line number of the data in the specified one or more data table column fields, and/or reading data is stopped at the End line number of the data in the specified one or more data table column fields.
Further, a constraint relationship between data in one or more data table column fields and data in another or more data table column fields may be specified, and data in a data table may have a certain logical relationship, for example, in data in a date format, a certain logical relationship exists between a month and the number of days of a month, for example, the number of days of 2 months is at most 29 days, and if 2 months and 30 days exist in one piece of data at the same time, it is determined that the data is faulty. Therefore, some existing constraint relations, that is, logical constraint relations between different data are specified in the embodiment, so that validity and reasonability of the data can be further ensured, and unreasonable or wrong data can be timely found.
Further, it is also possible to specify whether the source data and the target data are in a one-to-one relationship or a one-to-many relationship, and if the source data and the target data are in a one-to-many relationship, it is also necessary to specify one or more data column fields having a corresponding relationship at the same time, and when the data is stored, it is necessary to store the read data in one or more specified data table column fields of the target data table at a specified position.
Finally, the invention proposes a computer-readable storage medium having stored thereon computer instructions which, when executed by a processor, implement the steps of the above-mentioned method.
In summary, by using the method provided by the invention, two data tables of data and data _ define can be handed to developers, the developers can complete the work of importing the form only by knowing the corresponding relation between the form and the database table, the data type of the form and the check type of the form, and the whole process does not need to learn interface methods such as JXL, POI and the like, thereby improving the software production efficiency, saving the time of the software development process and avoiding the defects generated in the encoding process.
It should be recognized that embodiments of the present invention can be realized and implemented in computer hardware, a combination of hardware and software, or by computer instructions stored in a non-transitory computer readable memory. The methods may be implemented in a computer program using standard programming techniques, including a non-transitory computer-readable storage medium configured with the computer program, where the storage medium so configured causes a computer to operate in a specific and predefined manner, according to the methods and figures described in the detailed description. Each program may be implemented in a high level procedural or object oriented programming language to communicate with a computer system. However, the program(s) can be implemented in assembly or machine language, if desired. In any case, the language may be a compiled or interpreted language. Furthermore, the program can be run on a programmed application specific integrated circuit for this purpose.
Further, the method may be implemented in any type of computing platform operatively connected to a suitable connection, including but not limited to a personal computer, mini computer, mainframe, workstation, networked or distributed computing environment, separate or integrated computer platform, or in communication with a charged particle tool or other imaging device, or the like. Aspects of the invention may be implemented in machine-readable code stored on a non-transitory storage medium or device, whether removable or integrated onto a computing platform, such as a hard disk, optically read and/or write storage media, RAM, ROM, etc., so that it is readable by a programmable computer, which when read by the computer can be used to configure and operate the computer to perform the procedures described herein. Further, the machine-readable code, or portions thereof, may be transmitted over a wired or wireless network. The invention described herein includes these and other different types of non-transitory computer-readable storage media when such media include instructions or programs that implement the steps described above in conjunction with a microprocessor or other data processor. The invention also includes the computer itself when programmed according to the methods and techniques described herein.
Embodiments of this disclosure are described herein, including the best mode known to the inventors for carrying out the invention. Variations of those described embodiments may become apparent to those of ordinary skill in the art upon reading the foregoing description. The inventors expect skilled artisans to employ such variations as appropriate, and the inventors intend for the embodiments of the disclosure to be practiced otherwise than as specifically described herein. Accordingly, the scope of the present disclosure includes all modifications and equivalents of the subject matter recited in the claims appended hereto as permitted by applicable law. Moreover, the scope of the present disclosure encompasses any combination of the above-described elements in all possible variations thereof unless otherwise indicated herein or otherwise clearly contradicted by context.
While the present invention has been described in considerable detail and with particular reference to a few illustrative embodiments thereof, it is not intended to be limited to any such details or embodiments or any particular embodiments, but it is to be construed as effectively covering the intended scope of the invention by providing a broad, potential interpretation of such claims in view of the prior art with reference to the appended claims. Furthermore, the foregoing describes the invention in terms of embodiments foreseen by the inventor for which an enabling description was available, notwithstanding that insubstantial modifications of the invention, not presently foreseen, may nonetheless represent equivalents thereto.
The specification and drawings are, accordingly, to be regarded in an illustrative sense rather than a restrictive sense. However, it will be apparent that: various modifications and changes may be made thereto without departing from the broader spirit and scope of the application as set forth in the claims.
Other variations are within the spirit of the present application. Accordingly, while the disclosed technology is susceptible to various modifications and alternative constructions, certain embodiments thereof have been shown in the drawings and have been described above in detail. It should be understood, however, that there is no intention to limit the application to the specific form or forms disclosed; on the contrary, the intention is to cover all modifications, alternative constructions, and equivalents falling within the spirit and scope of the application, as defined in the appended claims.
Claims (9)
1. A method for importing Excel table data into a database is characterized by comprising the following steps:
s100) specifying storage positions, names, one or more data table column fields, data types of one or more column fields, ROWIDs of one or more column fields and data reading methods of the source data table and the target data table;
s200) checking whether the specified storage position, name and one or more data table column fields exist or not, checking whether the data type of the specified one or more data table column fields is consistent with the specified data type or not, if the checking results are yes, continuing to execute the following steps, and if at least one checking result is no, prompting an error;
s300) reading data under one or more specified data column fields in a data table of a specified position specified name according to a first reading method, wherein the first reading method is operated in a row unit, and the first reading method at least comprises one of the following methods: the method comprises the steps of scanning a full table line by line, ROWIDs and indexes, wherein the ROWIDs corresponding to data can be found based on the indexes;
s400) storing the read data under a specified data table column field of a target data table at a specified position; and
step S100 further includes specifying a starting line number and/or an ending line number of data in one or more data table column fields; at the same time, the user can select the desired position,
step S300 further includes starting reading data from a starting number of rows of data in the specified one or more data table column fields and/or stopping reading data from an ending number of rows of data in the specified one or more data table column fields; and
step S100 also comprises a step of specifying a constraint relation between data in one or more data table column fields and data in another one or more data table column fields in the same data table; at the same time, the user can select the desired position,
step S400 further includes determining whether the data in the specified one or more data table column fields and the data in the other one or more data table column fields satisfy a specified constraint relationship, if so, continuing to perform the subsequent steps, and if not, prompting an error.
2. The method of claim 1, wherein if the specified data type is a character type, then:
step S100 further comprises the following substeps: specifying a minimum length of data; at the same time, the user can select the desired position,
step S300 further includes the following substeps: and judging whether the length of the read data is greater than the specified minimum length, if so, continuing to execute the subsequent steps, and if not, reading the next data record.
3. The method of claim 1, wherein if the specified data type is a character type, then:
step S100 further comprises the following substeps: specifying a maximum length of data; at the same time, the user can select the desired position,
step S300 further includes the following substeps: and judging whether the length of the read data is smaller than the specified maximum length, if so, continuing to execute the subsequent steps, and if not, reading the next data record.
4. The method of claim 1, wherein if the specified data type is a numeric type:
step S100 further comprises the following substeps: designating a numerical type, and designating the maximum length and the numerical range of data for the integer numerical type; for the floating-point numerical type, appointing the reserved digit and numerical range after the decimal point; at the same time, the user can select the desired position,
step S300 further includes the following substeps:
judging whether the numerical type of the read data is consistent with the specified numerical type, if so, continuously judging whether the length of the numerical value is smaller than the specified maximum length and whether the numerical value falls into the specified numerical range for the numerical value of the integer type, and if so, continuously executing the subsequent steps; for the floating-point type numerical value, continuously judging whether the number of bits after the decimal point of the numerical value is less than the specified reserved number of bits and whether the numerical value falls into the specified numerical range, if so, continuously executing the subsequent steps, and if not, prompting an error and reading the next data record;
wherein the numerical type is integer type or floating point type.
5. The method of claim 1,
step S100 also comprises the steps of determining whether the specified data is a mandatory item; at the same time, the user can select the desired position,
step S300 further includes determining whether the read data is empty, if the read data is empty, reading the next data record, and if the read data is not empty, continuing to execute the subsequent steps.
6. The method of claim 1,
step S100 also comprises that whether the data of the source data table and the data of the target data table are in one-to-one relation or one-to-many relation is appointed, if the data are in one-to-many relation, one or more data column fields with corresponding relation exist at the same time are also appointed; at the same time, the user can select the desired position,
step S400 further includes saving the read data under one or more designated data table column fields of the target data table at the designated location.
7. The method of claim 1,
step S100 further includes specifying execution processes including a storing process and a JAVA method that need to be executed before step S400 is executed, and a storing process and a JAVA method that need to be executed after step S400 is executed.
8. An apparatus for importing Excel table data into a database, comprising the following modules:
the system comprises a specifying module, a reading module and a reading module, wherein the specifying module is used for specifying a storage position, a name, one or more data table column fields, a data type of the one or more column fields, a ROWID of the one or more column fields and a data reading method of the data of a source data table and a target data table;
the checking module is used for checking whether the appointed storage position, name and one or more data table column fields exist or not, checking whether the data type of the appointed one or more data table column fields is consistent with the appointed data type or not, if the checking result is yes, continuously executing the following steps, and if at least one checking result is not, prompting an error;
the reading module is used for reading data under one or more specified data column fields in a data table of a specified position specified name according to a first reading method, wherein the first reading method is operated in a row unit, and the first reading method at least comprises one of the following methods: the method comprises the steps of scanning a full table line by line, ROWIDs and indexes, wherein the ROWIDs corresponding to data can be found based on the indexes;
the storage module is used for storing the read data in a specified data table column field of a target data table at a specified position; and
the specifying module is further used for specifying a starting line number and/or an ending line number of data in one or more data table column fields; at the same time, the user can select the desired position,
the reading module is further used for starting to read data from the starting row number of the data in the specified one or more data table column fields and/or stopping reading data from the ending row number of the data in the specified one or more data table column fields; and
the specifying module is further used for specifying a constraint relation between data in one or more data table column fields and data in another one or more data table column fields in the same data table; at the same time, the user can select the desired position,
the storage module is further used for judging whether the data in the specified one or more data table column fields and the data in the other one or more data table column fields meet the specified constraint relationship, if so, continuing to work, and if not, prompting an error.
9. A computer-readable storage medium having stored thereon computer instructions, which, when executed by a processor, carry out the steps of the method according to any one of claims 1-7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811523732.3A CN109871405B (en) | 2018-12-13 | 2018-12-13 | Method and device for importing Excel table data into database |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811523732.3A CN109871405B (en) | 2018-12-13 | 2018-12-13 | Method and device for importing Excel table data into database |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN109871405A CN109871405A (en) | 2019-06-11 |
| CN109871405B true CN109871405B (en) | 2023-01-24 |
Family
ID=66917089
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201811523732.3A Active CN109871405B (en) | 2018-12-13 | 2018-12-13 | Method and device for importing Excel table data into database |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN109871405B (en) |
Families Citing this family (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113010527A (en) * | 2021-04-22 | 2021-06-22 | 苏州创腾软件有限公司 | Processing method and device for detection data, computer equipment and storage medium |
Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102841784A (en) * | 2011-06-24 | 2012-12-26 | 镇江华扬信息科技有限公司 | Method for dynamically importing Excel data into database |
Family Cites Families (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US8606624B2 (en) * | 2011-04-01 | 2013-12-10 | Caterpillar Inc. | Risk reports for product quality planning and management |
| CN103020089A (en) * | 2011-09-27 | 2013-04-03 | 深圳市金蝶友商电子商务服务有限公司 | Method and device for importing data in EXCEL file to database |
| CN105760382A (en) * | 2014-12-16 | 2016-07-13 | 深圳云之家网络有限公司 | Method and device for importing excel data into database |
| CN105740443A (en) * | 2016-02-01 | 2016-07-06 | 深圳市盛弘电气股份有限公司 | Android database-based data management method and device |
| CN108491510A (en) * | 2018-03-22 | 2018-09-04 | 平安科技（深圳）有限公司 | Excel data import method, apparatus, computer equipment and the storage medium of database |
-
2018
- 2018-12-13 CN CN201811523732.3A patent/CN109871405B/en active Active
Patent Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102841784A (en) * | 2011-06-24 | 2012-12-26 | 镇江华扬信息科技有限公司 | Method for dynamically importing Excel data into database |
Also Published As
| Publication number | Publication date |
|---|---|
| CN109871405A (en) | 2019-06-11 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US6678693B1 (en) | Database design system, database design method and record medium | |
| US9158834B2 (en) | Methods and systems for mapping repair orders within a database | |
| CN110955661B (en) | Data fusion method and device, readable storage medium and electronic equipment | |
| CN102880780A (en) | Systems and methods for creating intuitive context for analysis data | |
| CN110209650A (en) | The regular moving method of data, device, computer equipment and storage medium | |
| SG172426A1 (en) | Method and system for file type identifying and analyzing | |
| US11347719B2 (en) | Multi-table data validation tool | |
| CN111523854B (en) | BIM and database-based automatic price-covering system related to automatic pre-settlement | |
| EP1171832A2 (en) | Interface for an enterprise resource planning program | |
| US7167878B2 (en) | System and method for identifying and maintaining base table data blocks requiring deferred incremental integrity maintenance | |
| WO2005026993A1 (en) | A system and method for managing item interchange and identification in an extended enterprise | |
| CN109871405B (en) | Method and device for importing Excel table data into database | |
| US5305450A (en) | Method of software standardization by unifying and standardizing the names and attributes of data items in developed software | |
| CN106991050B (en) | False positive identification method for reference defect of static test null pointer | |
| CN108427572B (en) | Land survey database updating method and updating increment package generating method thereof | |
| CN108427675A (en) | Build the method and apparatus of index | |
| US7987203B2 (en) | Method of processing data for a system model | |
| CN114942745A (en) | Automatic configuration method for UDS protocol stack of motor controller | |
| US8175937B2 (en) | Systems, methods and computer programs for financial data evaluation | |
| US20230325847A1 (en) | Automated document auditing method and system | |
| US7028021B2 (en) | Aggregating device collection data | |
| CN110516204B (en) | Computer implementation method for batch association and import of branch structure data | |
| US20210133887A1 (en) | Efficiently aging and modifying policy records in a test system | |
| CN117787221A (en) | Data management method and device | |
| CN112287479A (en) | Valve product part-level BOM pre-decomposition method |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant | ||
| TR01 | Transfer of patent right | ||
| TR01 | Transfer of patent right |
Effective date of registration: 20230608 Address after: Room 302, Building 9, No. 31 Yanhe East Road, Xiangzhou District, Zhuhai City, Guangdong Province, 519000 Patentee after: Zhuhai Huicheyi Network Technology Co.,Ltd. Address before: 519000 Unit 218, 2nd Floor, Main Building, No. 10, Keji 1st Road, Gangwan Avenue, Tangjiawan Town, High-tech Zone, Zhuhai City, Guangdong Province Patentee before: ZHUHAI YINGYING TECHNOLOGY Co.,Ltd. |