CN109871405A - A kind of method and device that Excel list data is imported to database - Google Patents
A kind of method and device that Excel list data is imported to database Download PDFInfo
- Publication number
- CN109871405A CN109871405A CN201811523732.3A CN201811523732A CN109871405A CN 109871405 A CN109871405 A CN 109871405A CN 201811523732 A CN201811523732 A CN 201811523732A CN 109871405 A CN109871405 A CN 109871405A
- Authority
- CN
- China
- Prior art keywords
- data
- specified
- type
- tables
- read
- 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.)
- Granted
Links
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention proposes a kind of methods that Excel list data is imported database, comprising the following steps: S100) storage location of specified source data table and target matrix, title, the data type of one or more tables of data column fields and one or more column fields and data read method；S200) check that specified storage location, title, one or more tables of data column fields whether there is, and check whether the data type of specified one or more tables of data column fields and specified data type are consistent, if inspection result is to be, then continue to execute following steps, if at least one inspection result be it is no, prompt mistake；S300 the data under specified one or more data column fields in the tables of data of the specified title in designated position) are read according to the first read method；S400) read data are stored under the specified tables of data column field of the target matrix of designated position.
Description
Technical field
The present invention relates to database field, in particular to a kind of method and dress that Excel list data is imported to database
It sets.
Background technique
Microsoft Excel is Microsoft for using the electricity of Windows and Apple Macintosh
A spreadsheet software that brain is write.Excel is now popular pc data processing software.It realizes at present
The method that imported into system that there are mainly two types of of data in Excel list.
First method: using Excel formula, write Insert sentence, is run by copy Insert sentence to SQL soft
Part executes, thus realize the importing work of Excel data, second method: with the journey of POI or other read-write EXCEL
Sequence writes code and reads Excel list, to realize that data imported into system.
Above-mentioned existing method there are problems that needing the other learning program exploitation code of developer.
Summary of the invention
The present invention needs developer in addition to learn journey for of the existing technology import Excel list in database
The problem of sequence exploitation code, proposes a kind of method and device that Excel list data is imported to database.
Firstly, the present invention proposes a kind of method that Excel list data is imported database, comprising the following steps:
S100) specify the storage location of source data table and target matrix, title, one or more tables of data column fields and
The data type of one or more column fields and the read method of data；
S200) check that specified storage location, title, one or more tables of data column fields whether there is, and check
Whether the data type and specified data type of specified one or more tables of data column fields are consistent, if inspection result
Be to be, then continue to execute following steps, if at least one inspection result be it is no, prompt mistake；
S300 specified one or more numbers in the tables of data of the specified title in designated position) are read according to the first read method
According to the data under column field；
S400) read data are stored under the specified tables of data column field of the target matrix of designated position.
Specifically, in the above method proposed by the present invention, first read method is included at least in following methods
It is a kind of: full table scan, ROWID and index.
Further, in the above method proposed by the present invention, if specified data type is character type:
Step S100 further includes following sub-step: the minimum length of specified data；Meanwhile
Step S300 further includes following sub-step: it is long to judge whether the length of data streams read is greater than specified minimum
Degree, if so, subsequent step is continued to execute, if it is not, then reading next data record.
Further, in the above method proposed by the present invention, if specified data type is character type:
Step S100 further includes following sub-step: the maximum length of specified data；Meanwhile
Step S300 further includes following sub-step: judging whether the length of data streams read is less than and specified most greatly enhances
Degree, if so, subsequent step is continued to execute, if it is not, then reading next data record.
Further, in the above method proposed by the present invention, if specified data type is value type:
Step S100 further includes following sub-step: specified value type specifies data most for integer type value type
Long length, numberical range；For floating type value type, the digit and numberical range that retain after specified decimal point；Meanwhile
Step S300 further includes following sub-step:
Judge whether the value type of read data is consistent with specified value type, if being consistent, for integer type
Numerical value, continue to judge whether the length of the numerical value is less than specified maximum length and whether the numerical value falls into specified number
Be worth range, if judging result simultaneously be it is yes, continue to execute subsequent step；For the numerical value of floating type, continue to judge the numerical value
Whether the digit after decimal point is less than specified reservation digit and whether the numerical value falls into specified numberical range, if judgement
As a result be simultaneously it is yes, then subsequent step is continued to execute, if it is not, then prompting mistake and reading next data to record；
Wherein, the value type is integer type or floating type.
Further, in the above method proposed by the present invention,
Step S100 further includes specifying whether data are required item；Meanwhile
Step S300 further includes judging whether read data are that sky is read next if read data are sky
Data record, if read data non-empty, continues to execute subsequent step.
Further, in the above method proposed by the present invention,
Step S100 further includes the starting line number and/or end line for specifying data in one or more tables of data column field
Number；Meanwhile
Step S300 further includes reading the starting line number of data since specified one or more tables of data column fields
Data, and/or the end line number of data stops reading data in specified one or more tables of data column fields.
Further, in the above method proposed by the present invention,
Step S100 further includes specifying data and another or multiple tables of data column in one or more tables of data column field
The constraint relationship of field data；Meanwhile
Step S400 further includes judging data and another or multiple data in specified one or more tables of data column fields
Whether table column field data meets specified the constraint relationship, if satisfied, subsequent step is then continued to execute, if not satisfied, then prompting
Mistake.
Further, in the above method proposed by the present invention,
Step S100 further includes, and is one-to-one pass between the data of specified source data table and the data of target matrix
System or one-to-many relationship then also need specified one or more to exist simultaneously the number of corresponding relationship if one-to-many relationship
According to column field；
Step S400 further includes referring to the one or more that read data are stored in the target matrix of designated position
Determine under tables of data column field.
Secondly, the present invention proposes a kind of device that Excel list data is imported to database, comprise the following modules:
Specified module, for specifying the storage location, title, one or more tables of data of source data table and target matrix
The read method of the data type and data of column field and one or more column fields；
Module is checked, for checking whether specified storage location, title, one or more tables of data column fields deposit
, and check whether the data type of specified one or more tables of data column fields and specified data type are consistent,
If inspection result is to be, continue to execute following steps, if at least one inspection result be it is no, prompt mistake；
Read module, it is one specified in the tables of data of title for being specified according to the first read method reading designated position
Or the data under multiple data column fields；
Preserving module, the specified tables of data column word of the target matrix for read data to be stored in designated position
Under section.
Finally, the present invention proposes a kind of computer readable storage medium, it is stored thereon with computer instruction, which is located
Manage the step of realizing the above method when device executes.
Detailed description of the invention
Fig. 1 show a kind of the first implementation of method that Excel list data is imported to database that the application is proposed
The data mapping table schematic diagram of example；
Fig. 2 show a kind of the first implementation of method that Excel list data is imported to database that the application is proposed
The dtd--data type definition page schematic diagram of example；
Fig. 3 show a kind of the first implementation of method that Excel list data is imported to database that the application is proposed
The data definition page schematic diagram of example；
Fig. 4 show a kind of the second implementation of method that Excel list data is imported to database that the application is proposed
Example flow chart；
The third that Fig. 5 show a kind of method that Excel list data is imported to database that the application is proposed is implemented
Example flow chart；
Fig. 6 show a kind of the 4th implementation of method that Excel list data is imported to database that the application is proposed
Example flow chart；
Fig. 7 show a kind of the 5th implementation of method that Excel list data is imported to database that the application is proposed
Example flow chart；
Fig. 8 show a kind of the 6th implementation of method that Excel list data is imported to database that the application is proposed
Example flow chart；
Fig. 9 show a kind of the 7th implementation of method that Excel list data is imported to database that the application is proposed
Example flow chart；
Figure 10 show a kind of the 8th implementation of method that Excel list data is imported to database that the application is proposed
Example flow chart；
Figure 11 show a kind of frame diagram for device that Excel list data is imported to database that the application is proposed.
Specific embodiment
It is carried out below with reference to technical effect of the embodiment and attached drawing to design of the invention, specific structure and generation clear
Chu, complete description, to be completely understood by the purpose of the present invention, scheme and effect.It should be noted that the case where not conflicting
Under, the features in the embodiments and the embodiments of the present application can be combined with each other.The identical attached drawing mark used everywhere in attached drawing
Note indicates the same or similar part.
It should be noted that unless otherwise specified, when a certain feature referred to as " fixation ", " connection " are in another feature,
It can directly fix, be connected to another feature, and can also fix, be connected to another feature indirectly.In addition, this
The descriptions such as upper and lower, left and right used in application are only the mutual alignment pass relative to each component part of the application in attached drawing
For system.In the application and the "an" of singular used in the attached claims, " described " and "the" also purport
It is including most forms, unless the context clearly indicates other meaning.
In addition, unless otherwise defined, the technology of all technical and scientific terms used herein and the art
The normally understood meaning of personnel is identical.Term used in the description is intended merely to description specific embodiment herein, without
It is to limit the application.Term as used herein "and/or" includes the arbitrary of one or more relevant listed items
Combination.
It will be appreciated that though various elements may be described in this application using term first, second, third, etc., but
These elements should not necessarily be limited by these terms.These terms are only used to for same type of element being distinguished from each other out.For example, not taking off
In the case where the application range, first element can also be referred to as second element, and similarly, second element can also be referred to as
First element.Depending on context, word as used in this " if " can be construed to " ... when " or " when ...
When ".
It show the first of a kind of method that Excel list data is imported to database that the application is proposed referring to Fig.1
The data mapping table schematic diagram of embodiment, in the present embodiment, Report Engine is mainly made of two Excel tables, point
It Wei not data and data_Define.It is the detailed parsing to two tables below.
Specifically, the relationship of the data in data table main definitions Excel table and database table.Under normal conditions,
The true report that data report, that is, user uses, developer fill in { prefix.column_name } on report to correspond to certain
The title of some field item inside one report.Wherein, prefix is the code name of some database table, prefix and data
Definition relationship in the table of library can embody in data_Define.Column_name is true field defined in database table
, when Programmable detection, can detect whether the field item exists in database table.The motor vehicle of example as shown in figure 1 enters and leaves registration form
In, main be a database table t_car_inspection title code name, each of table gauge outfit define its
Each of field item, that is, table in database gauge outfit is corresponding with the field item in database, the corresponding table of gauge outfit
Lattice content is then corresponding with the value of the field, for example, corresponding in the detection date is { main.check_date }, indicates, detects day
It is the field item of check_date in tables of data that phase is corresponding, detects the value that the value in the date is exactly the field item.In this implementation
In example, further restriction can be made to the type of field value, so that the value of the field meets the definition of the field, example
Such as, the type of the value of check_date can be date type.
Further, as can be seen that the field item of gauge outfit name and tables of data is correspondingly, i.e., not deposit from the table of Fig. 1
The case where the same gauge outfit name corresponds to different data sheet field items, the same data also are corresponded to there is no different gauge outfit names
The case where the case where literary name section item, in the present embodiment, one-to-many situation are allowed, and formulate area if necessary, then need
It further makes and indicating.
Specifically, data_Define table mainly realizes the definition to form data type and read method.The table packet
Bookmark containing two parts is dtd--data type definition bookmark and dtd--data type definition bookmark respectively.
Specifically, dtd--data type definition bookmark: data_type, main function are to define data type, data length, number
According to database table logic corresponding relationship, whether data are that must fill out.The definition of Data_Type is usually N
{datatype.define1.define2…..defineN}.Wherein dataType is data type, define1, define2
It is to define data characteristic, including whether data length, data format, data must fill out to defineN.Following table is that dataType is detailed
Thin definition:
Further, a kind of method that Excel list data is imported into database proposed referring to the application shown in Fig. 2
First embodiment dtd--data type definition page schematic diagram；Wherein, each of table gauge outfit corresponds to the value of its content
Definition illustrates that value corresponding to the gauge outfit is defined as date.M.yyyy-mm- in figure with detection this gauge outfit of date again
Dd, according to the explanation of upper table, value corresponding to the gauge outfit is date format, i.e. date format, and it is that must fill out that M, which represents the gauge outfit,
, yyyy-mm-dd represents the date in the month-two in the year-two that the format on the date is four, for example, 2018-12-
10, represent on December 10th, 2018.For another example in appraising and deciding this gauge outfit of mounted mass, the definition of value is
Decimal.2.M indicates that the number is the format of decimal, i.e. decimal, and can at most retain 2 significant digits, example
If 10.1 and 10.12 the two data are all allowed, equally, M indicates that the gauge outfit is required item.
Specifically, dtd--data type definition bookmark define, bookmark definition are divided into following two major classes:
Implementation procedure is defined, is needed including the storing process and JAVA method that are needed to be implemented before importing and after importing
The storing process and JAVA method of execution.The business logic of itself needs can be written in user after importing and before importing,
Including some commercial verifications, the work such as data preparation
It defines the corresponding relationship inside data template and how to read data template, be that the bookmark illustrates table below
Specifically, a kind of method that Excel list data is imported into database that the application is proposed is shown referring to Fig. 3
First embodiment data definition page schematic diagram；There are three parts to form in figure, by blank line space between each section, referring initially to the
The table of a part is as follows, needs to be implemented before respectively defining the storing process needed to be implemented before importing, importing
The JAVA method needed to be implemented after JAVA method, the storing process needed to be implemented after importing and importing.
| before procedure | sp_before_import_car_insp |
| before java Method | NULL |
| after procedure | sp_after_import_car_insp |
| after java Method | NULL |
It will be seen that the storing process needed to be implemented before importing is defined as sp_before_import_ from table
Car_insp is directed to this table of import_car come what is operated, can be instruction, step, sentence or executable file,
This instruction, step, sentence or executable file is needed to be implemented before importing data.Specifically, it the instruction, step, sentence or can hold
Style of writing part can be for data import and optimize, such as be ranked up to data, repeated data delete or
The method etc. that person defines some storages；Similarly, the storing process needed to be implemented after importing is defined as sp_after_
import_car_insp.It can also be seen that the side JAVA without needing to be implemented before specified import in the present embodiment from upper table
The JAVA method needed to be implemented after method and importing.
Second part in table is as follows, and the second part in table is as one of example in the present embodiment:
| Define Start | |
| Define Type | Header |
| Mapping Table | t_car_inspection |
| prefix | main |
| Start Row | 1 |
| End Row | 18 |
| Define End |
Define Start starts label and Define End end-tag without filling in as can be seen from the table,
The value of Define Type is Header, represents table corresponding with database in data table and is not present there are one-to-one relationship
The case where one gauge outfit correspondence database table multiple field names.The value of Mapping Table and prefix are t_car_ respectively
Inspection and main, shows the entitled t_car_inspection of the database table of target, in corresponding source table
Data be with main for beginning, it is corresponding with the first embodiment in the present invention；The value of Start Row and End Row
It is 1 and 18 respectively, representative is to terminate since the first row of tables of data to the 18th row.
Part III in table is as follows, and the Part III in table is as another example in the present embodiment:
Define Start starts label and Define End end-tag without filling in as can be seen from the table,
The value of Define Type is Detail, and representing table corresponding with database in data table, there are one-to-many relationships, that is, is existed not
With a gauge outfit correspondence database table multiple field names the case where.The value of Mapping Table and prefix are t_ respectively
Car_inspection and detail shows the entitled t_car_inspection of the database table of target, corresponding source
Data in table are with detail for beginning, corresponding with the first embodiment in the present invention；Start Row and End
The value of Row is 19 and NULL respectively, and representative is not specify end position since the 19th row of tables of data；In addition,
The value of Group Row is 1, indicates to define many-to-one relationship, that is, with the definition of above-mentioned Detail is matched.
Specifically, a kind of method that Excel list data is imported into database proposed referring to the application shown in Fig. 4
Flow chart of the second embodiment, show following steps in figure:
S100) specify the storage location of source data table and target matrix, title, one or more tables of data column fields and
The data type of one or more column fields and the read method of data；
S200) check that specified storage location, title, one or more tables of data column fields whether there is, and check
Whether the data type and specified data type of specified one or more tables of data column fields are consistent, if inspection result
Be to be, then continue to execute following steps, if at least one inspection result be it is no, prompt mistake；
S300 specified one or more numbers in the tables of data of the specified title in designated position) are read according to the first read method
According to the data under column field；
S400) read data are stored under the specified tables of data column field of the target matrix of designated position；
Specifically, first read method includes at least one of following methods: full table scan, ROWID and index；
Specifically, the read method of full table scan are as follows: read row all in table, and check whether every a line meets sentence
Restrictive condition, sequentially read and distribute to each data block of table, (the high water at the highest waterline for reading table
Mark, HWM identify the last one data block of table).One muti-piece read operation can enable primary I/O read multi-block data block
(db_block_multiblock_read_count parameter setting), rather than a data block is only read, this is significantly reduced
I/O total degree, improves the handling capacity of system.
Specifically, referred to by ROWID reading, data file, data block and the row where ROWID indicates the row exist
Position in the block, so can quickly be navigated in target data by ROWID.
Specifically, first passing through index and finding the corresponding ROWID value of data (may return to non-only indexes multiple
ROWID value), specific data are directly then obtained from table according to ROWID, this lookup mode is known as indexing scanning or index
It searches.
Further, a kind of method that Excel list data is imported into database that the application referring to Figure 5 is proposed
3rd embodiment flow chart, the case where present embodiment illustrates specified data type being character type data, then step
S100 further includes following sub-step: the minimum length and/or maximum length of specified data；Meanwhile step S300 further includes following
Sub-step: judging whether the length of data streams read is greater than specified minimum length and/or less than specified maximum length,
If so, subsequent step is continued to execute, if it is not, then reading next data record；Specifically, referring to the application shown in Fig. 2
A kind of dtd--data type definition page of the first embodiment of the method that Excel list data is imported to database proposed is illustrated
Figure, such as the trend type of the Vehicle Identification Number in figure is exactly character type, and, it is desirable that the length of the data is at least 17
Position uses method proposed by the present invention, for reading the case where being less than 17 Vehicle Identification Number, then prompt mistake or
This data is skipped, next data record is read, then execute identical judgement, in the present invention, in definition character type data
When, the maximum length of data is defined using digital N, and the minimum length of data is defined using digital NB, for example,
{ varchar.17 } indicates that the maximum length of the character type data is 17, and { varchar.2B } indicates that the data items length is necessary
Greater than 2.
Further, a kind of method that Excel list data is imported into database proposed referring to the application shown in fig. 6
Fourth embodiment flow chart, present embodiment illustrates specified data type be value type the case where, in the case,
Step S100 further includes following sub-step: specified value type, for integer type value type, specify data maximum length and
Numberical range；For floating type value type, the digit and numberical range that retain after specified decimal point；Meanwhile step S300 is also
Including following sub-step: judge whether the value type of read data is consistent with specified value type, if being consistent, for
The numerical value of integer type continues to judge whether the length of the numerical value is less than specified maximum length and whether the numerical value falls into meaning
Fixed numberical range, if judging result simultaneously be it is yes, continue to execute subsequent step；For the numerical value of floating type, continue to judge
Whether the digit after the numerical value decimal point is less than specified reservation digit and whether the numerical value falls into specified numberical range,
If judging result be simultaneously it is yes, subsequent step is continued to execute, if it is not, then prompting mistake and reading next data record；Its
In, the value type is that integer type or floating type when defining numeric type data, use respectively in the present invention
Interger and decimal defines integer type and real-coded GA, and in integer type data, can further limit the number
The maximum length or numberical range of value, for example, interger.-2 >=indicate that the integer has to be larger than or be equal to -2, then example
Such as, { interger.17 } indicates that the maximum length of the data is 17.
Further, a kind of method that Excel list data is imported into database proposed referring to the application shown in Fig. 7
The 5th embodiment flow chart, the case where present embodiment illustrates specified data whether being required item, if specified data is must
Item is filled out, then when reading data, needing read data is non-empty, otherwise prompt mistake or the next data note of reading
Record, in the present invention, indicates that the data are required items using alphabetical M when defining data, for example, { decimal.M }
It is expressed as the data item for the floating type that one must fill out.
Further, a kind of method that Excel list data is imported into database proposed referring to the application shown in Fig. 8
Sixth embodiment flow chart, present embodiment illustrates in specified tables of data originate line number and/or terminate line number the case where, if referring to
Starting line number is determined and/or has terminated line number, then the starting line number of data is opened from specified one or more tables of data column fields
Begin to read data, and/or the end line number of data stops reading data in specified one or more tables of data column fields,
In the present invention, Start Row can be used to define starting line number, end line number is defined using End Row, for example, Start
Row=1 indicates that starting line number is then to be defaulted as a last valid data row in the not specified situation of 1, End Row.
Further, a kind of method that Excel list data is imported into database proposed referring to the application shown in Fig. 9
The 7th embodiment flow chart, present embodiment illustrates data in specified one or more tables of data column fields and another or it is more
The case where the constraint relationship of a tables of data column field data, there may be certain logical relations for the data in tables of data, such as
In the data of date format, month and number of days where month there is certain logical relation, such as 2 months number of days most
Mostly 29 days, the case where 2 months and 30 days are such as existed simultaneously in a data, then it is judged as corrupt data.Therefore in this implementation
Some existing the constraint relationships are specified in example, i.e., the logical constraint relationship between different data can then be further ensured that data
Validity and reasonability find unreasonable or wrong data in time.
Further, a kind of side that Excel list data is imported to database that the application shown in 0 is proposed referring to Fig.1
8th embodiment flow chart of method, present embodiment illustrates be one-to-one relationship or one between source data and target data
The case where to more relationships, then also needs specified one or more to exist simultaneously the data of corresponding relationship if one-to-many relationship
Column field, and when saving data, need for read data to be stored in the one of the target matrix of designated position
Under a or multiple specified tables of data column fields, in the present invention it is possible to define one-to-many correspondence situation using Detail, together
When Group Row variable value be set as 1.
Further, a kind of dress that Excel list data is imported to database that the application shown in 1 is proposed referring to Fig.1
The frame diagram set, comprises the following modules:
Specified module, for specifying the storage location, title, one or more tables of data of source data table and target matrix
The read method of the data type and data of column field and one or more column fields；
Module is checked, for checking whether specified storage location, title, one or more tables of data column fields deposit
, and check whether the data type of specified one or more tables of data column fields and specified data type are consistent,
If inspection result is to be, continue to execute following steps, if at least one inspection result be it is no, prompt mistake；
Read module, it is one specified in the tables of data of title for being specified according to the first read method reading designated position
Or the data under multiple data column fields；
Preserving module, the specified tables of data column word of the target matrix for read data to be stored in designated position
Under section.
Specifically, first read method includes at least one of following methods: full table scan, ROWID and index；
Specifically, the read method of full table scan are as follows: read row all in table, and check whether every a line meets sentence
Restrictive condition, sequentially read and distribute to each data block of table, (the high water at the highest waterline for reading table
Mark, HWM identify the last one data block of table).One muti-piece read operation can enable primary I/O read multi-block data block
(db_block_multiblock_read_count parameter setting), rather than a data block is only read, this is significantly reduced
I/O total degree, improves the handling capacity of system.
Further, if specified data type is character type data, specified module further includes the submodule of following functions
Block: the minimum length and/or maximum length of specified data；Meanwhile read module further includes the submodule of following functions: judging institute
Whether the length for reading data is greater than specified minimum length and/or less than specified maximum length, if so, continuing to hold
Row subsequent step, if it is not, then reading next data record；Specifically, a kind of general proposed referring to the application shown in Fig. 2
Excel list data imports the dtd--data type definition page schematic diagram of the first embodiment of the method for database, such as the vehicle in figure
Identification code name trend type be exactly character type, and, it is desirable that the length of the data is at least 17, i.e., is mentioned using the present invention
Method out then prompts mistake or skips this data, read for reading the case where being less than 17 Vehicle Identification Number
Next data record, then identical judgement is executed, in the present invention, when definition character type data, come using digital N
The maximum length of data is defined, and defines the minimum length of data using digital NB, for example, { varchar.17 }, indicates
The maximum length of the character type data is 17, and { varchar.2B } indicates that the data items length has to be larger than 2.
Further, if specified data type is value type, specified module further includes the submodule of following functions:
Specified value type specifies the maximum length and numberical range of data for integer type value type；For floating type numerical value class
Type specifies the digit and numberical range retained after decimal point；Meanwhile read module further includes the submodule of following functions: judgement
Whether the value type of read data is consistent with specified value type, if being consistent, for the numerical value of integer type, continues to sentence
Whether the length of the numerical value of breaking is less than specified maximum length and whether the numerical value falls into specified numberical range, if judgement
As a result simultaneously be it is yes, then continue to execute subsequent step；For the numerical value of floating type, continue to judge the digit after the numerical value decimal point
Whether be less than specified reservations digit and whether the numerical value fall into specified numberical range, if judging result at the same be it is yes,
Subsequent step is then continued to execute, if it is not, then prompting mistake and reading next data record；Wherein, the value type is whole
Number type or floating type when defining numeric type data, define integer using interger and decimal respectively in the present invention
Type and real-coded GA, and in integer type data, the maximum length or numberical range of the numerical value, example can be further limited
Such as, interger.-2 >=indicate that the integer has to be larger than or is equal to -2, for another example { interger.17 } indicates the data
Maximum length be 17.
Further, whether it is required item that specified data can also be set, if specified data is required item, is reading number
According to when, needing read data is non-empty, otherwise prompt mistake or read next data record, in the present invention,
Indicate that the data are required items using alphabetical M when defining data, for example, { decimal.M }, which is expressed as one, to be filled out
Floating type data item.
Further, can also specify in tables of data originate line number and/or terminate line number, if specify starting line number and/or
Terminate line number, then since specified one or more tables of data column fields the starting line number of data read data, and/or
The end line number of data stops reading data in specified one or more tables of data column fields, in the present invention it is possible to use
Start Row defines starting line number, and end line number is defined using End Row, for example, Start Row=1 indicates initial row
Number is then to be defaulted as a last valid data row in the not specified situation of 1, End Row.
Further, data and another or multiple tables of data column words in one or more tables of data column fields can also be specified
The case where the constraint relationship of segment data, there may be certain logical relations for the data in tables of data, such as in date format
In data, month and number of days where month are there is certain logical relation, such as the number of days in 2 months is up to 29, such as
The case where 2 months and 30 days are existed simultaneously in a data, then be judged as corrupt data.Therefore it specifies in the present embodiment
Existing the constraint relationship, i.e., the logical constraint relationship between different data, then can be further ensured that data validity and rationally
Property, unreasonable or wrong data are found in time.
Further, can also specify between source data and target data is one-to-one relationship or one-to-many pass
System then also needs specified one or more to exist simultaneously the data column field of corresponding relationship, and protecting if one-to-many relationship
When deposit data, the one or more for needing for read data to be stored in the target matrix of designated position specifies data
Under table column field, in the present invention it is possible to define one-to-many correspondence situation, while Group Row variable using Detail
Value be set as 1.
Finally, the present invention proposes a kind of computer readable storage medium, it is stored thereon with computer instruction, which is located
Manage the step of realizing the above method when device executes.
In conclusion two tables of data of data and data_define can be given using method proposed by the invention
Developer, developer need to only understand the corresponding relationship of list and database table, form data type, and list inspection type is
Software production efficiency can be improved without learning the interface methods such as JXL, POI in achievable importing list work, whole process,
The time of software development process is saved, while avoiding the defect generated in cataloged procedure.
It should be appreciated that the embodiment of the present invention can be by computer hardware, the combination of hardware and software or by depositing
The computer instruction in non-transitory computer-readable memory is stored up to be effected or carried out.Standard volume can be used in the method
Journey technology include realized in computer program configured with the non-transitory computer-readable storage media of computer program, wherein
Configured in this way storage medium make computer operated in a manner of specific and is predefined-according in a particular embodiment describing
Method and attached drawing.Each program can be realized with the programming language of level process or object-oriented with logical with computer system
Letter.However, if desired, the program can be realized with compilation or machine language.Under any circumstance, the language can be compiling or
The language of explanation.In addition, the program can be run on the specific integrated circuit of programming for this purpose.
Further, this method can be realized in being operably coupled to suitable any kind of computing platform, including
But it is not limited to PC, mini-computer, main frame, work station, network or distributed computing environment, individual or integrated
It computer platform or is communicated with charged particle tool or other imaging devices etc..Each aspect of the present invention can be with storage
No matter machine readable code on non-transitory storage medium or equipment is moveable or is integrated to calculate and put down to realize
Platform, such as hard disk, optically read and/or write-in storage medium, RAM, ROM, so that it can be read by programmable calculator, when depositing
Storage media or equipment can be used for configuration and operation computer to execute process described herein when being read by computer.In addition,
Machine readable code, or part thereof can be transmitted by wired or wireless network.When such media include in conjunction with microprocessor or
When other data processors realize the instruction or program of steps described above, invention as described herein includes that these and other are different
The non-transitory computer-readable storage media of type.When methods and techniques according to the present invention programming, the present invention is also
Including computer itself.
This document describes embodiment of the disclosure, become known for executing optimal mode of the invention including inventor.It is readding
After having read foregoing description, the variation of these embodiments will be apparent those skilled in the art.Inventor wishes skill
Art personnel optionally use such modification, and inventor be intended to by be different from it is as specifically described herein in a manner of practice this public affairs
The embodiment opened.Therefore, through applicable legal permission, the scope of the present disclosure includes describing in this appended claims
The all modifications and equivalent of theme.In addition, the scope of the present disclosure covers any of the above-mentioned element in its all possible modification
Combination, unless herein in addition instruction or otherwise significantly with contradicted by context.
Although description of the invention is quite detailed and especially several embodiments are described, it is not
Any of these details or embodiment or any specific embodiments are intended to be limited to, but should be considered as is by reference to appended
A possibility that claim provides broad sense in view of the prior art for these claims explanation, to effectively cover the present invention
Preset range.In addition, with the foreseeable embodiment of inventor, present invention is described above, its purpose is to be provided with
Description, and those still unforeseen at present change to unsubstantiality of the invention can still represent equivalent modifications of the invention.
Therefore, should be with descriptive sense rather than restrictive sense understands the specification and drawings.However, by apparent
It is:, can be to the application in the case where not departing from the broader spirit and scope of the application as described in claims
Make various modifications and change.
Other modifications are in spirit herein.Therefore, although disclosed technology may be allowed various modifications and substitution structure
It makes, but has shown that in the accompanying drawings and its some embodiments shown in being described in detail above.It will be appreciated, however, that not
It is intended to for the application to be confined to disclosed one or more concrete forms；On the contrary, its intention covers such as the appended claims
Defined in fall in all modifications, alternative constructions and equivalent in spirit and scope.
Claims (10)
1. a kind of method that Excel list data is imported database, which comprises the following steps:
S100) storage location of specified source data table and target matrix, title, one or more tables of data column fields and one
Or multiple column fields data type and data read method；
S200) check that specified storage location, title, one or more tables of data column fields whether there is, and check meaning
Whether the data type and specified data type of fixed one or more tables of data column fields are consistent, if inspection result is
Be then continue to execute following steps, if at least one inspection result be it is no, prompt mistake；
S300) reading designated position according to the first read method specifies specified one or more data in the tables of data of title to arrange
Data under field；
S400) read data are stored under the specified tables of data column field of the target matrix of designated position.
2. the method according to claim 1, wherein if specified data type be character type:
Step S100 further includes following sub-step: the minimum length of specified data；Meanwhile
Step S300 further includes following sub-step: judge whether the length of data streams read is greater than specified minimum length, if
It is then to continue to execute subsequent step, if it is not, then reading next data record.
3. the method according to claim 1, wherein if specified data type be character type:
Step S100 further includes following sub-step: the maximum length of specified data；Meanwhile
Step S300 further includes following sub-step: judge whether the length of data streams read is less than specified maximum length, if
It is then to continue to execute subsequent step, if it is not, then reading next data record.
4. the method according to claim 1, wherein if specified data type be value type:
Step S100 further includes following sub-step: specified value type, for integer type value type, specified data are most greatly enhanced
Degree and numberical range；For floating type value type, the digit and numberical range that retain after specified decimal point；Meanwhile
Step S300 further includes following sub-step:
Judge whether the value type of read data is consistent with specified value type, if being consistent, for the number of integer type
Value continues to judge whether the length of the numerical value is less than specified maximum length and whether the numerical value falls into specified numerical value model
Enclose, if judging result simultaneously be it is yes, continue to execute subsequent step；For the numerical value of floating type, then continue to judge that the numerical value is small
Whether the digit after several points is less than specified reservation digit and whether the numerical value falls into specified numberical range, if judgement knot
Fruit be simultaneously it is yes, then subsequent step is continued to execute, if it is not, then prompting mistake and reading next data record；
Wherein, the value type is integer type or floating type.
5. the method according to claim 1, wherein
Step S100 further includes specifying whether data are required item；Meanwhile
Step S300 further include judge read data whether be it is empty, if read data are sky, read next number
According to record, if read data non-empty, continues to execute subsequent step.
6. the method according to claim 1, wherein
Step S100 further includes specifying the starting line number of data in one or more tables of data column field and/or terminating line number；Together
When,
Step S300 further includes reading number the starting line number of data since specified one or more tables of data column fields
According to, and/or in specified one or more tables of data column fields data end line number stop read data.
7. the method according to claim 1, wherein
Step S100 further includes specifying data and another or multiple tables of data column fields in one or more tables of data column field
The constraint relationship of data；Meanwhile
Step S400 further includes judging data and another or multiple tables of data column in specified one or more tables of data column fields
Whether field data meets specified the constraint relationship, if satisfied, subsequent step is then continued to execute, if not satisfied, then prompting mistake.
8. the method according to claim 1, wherein
Step S100 further includes, be between the data of specified source data table and the data of target matrix one-to-one relationship also
It is one-to-many relationship, if one-to-many relationship, then specified one or more is also needed to exist simultaneously the data column of corresponding relationship
Field；Meanwhile
Step S400 further includes the specified number of the one or more that read data are stored in the target matrix of designated position
According under table column field.
9. a kind of device that Excel list data is imported to database, which is characterized in that comprise the following modules:
Specified module, for specifying the storage location, title, one or more tables of data column words of source data table and target matrix
The read method of the data type and data of section and one or more column fields；
Check module, for checking that specified storage location, title, one or more tables of data column fields whether there is, and
Check whether data type and the specified data type of specified one or more tables of data column fields are consistent, if checking
Result is to be, then continues to execute following steps, if at least one inspection result be it is no, prompt mistake；
Read module, it is one or more specified in the tables of data of title for being specified according to the first read method reading designated position
Data under a data column field；
Preserving module, the specified tables of data column field of the target matrix for read data to be stored in designated position
Under.
10. a kind of computer readable storage medium, is stored thereon with computer instruction, which is characterized in that the instruction is by processor
It realizes when execution such as the step of method of any of claims 1-8.
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 true CN109871405A (en) | 2019-06-11 |
| CN109871405B 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) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111832268A (en) * | 2020-06-30 | 2020-10-27 | 北京印象笔记科技有限公司 | Information interaction method, readable storage medium and electronic device |
| CN113010527A (en) * | 2021-04-22 | 2021-06-22 | 苏州创腾软件有限公司 | Processing method and device for detection data, computer equipment and storage medium |
Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20120253874A1 (en) * | 2011-04-01 | 2012-10-04 | Caterpillar Inc. | Graphical user interface for product quality planning and management |
| CN102841784A (en) * | 2011-06-24 | 2012-12-26 | 镇江华扬信息科技有限公司 | Method for dynamically importing Excel data into database |
| CN103020089A (en) * | 2011-09-27 | 2013-04-03 | 深圳市金蝶友商电子商务服务有限公司 | Method and device for importing data in EXCEL file to database |
| CN105740443A (en) * | 2016-02-01 | 2016-07-06 | 深圳市盛弘电气股份有限公司 | Android database-based data management method and device |
| CN105760382A (en) * | 2014-12-16 | 2016-07-13 | 深圳云之家网络有限公司 | Method and device for importing excel data into database |
| 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 (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20120253874A1 (en) * | 2011-04-01 | 2012-10-04 | Caterpillar Inc. | Graphical user interface for product quality planning and management |
| CN102841784A (en) * | 2011-06-24 | 2012-12-26 | 镇江华扬信息科技有限公司 | Method for dynamically importing Excel data into database |
| 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 |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111832268A (en) * | 2020-06-30 | 2020-10-27 | 北京印象笔记科技有限公司 | Information interaction method, readable storage medium and electronic device |
| CN113010527A (en) * | 2021-04-22 | 2021-06-22 | 苏州创腾软件有限公司 | Processing method and device for detection data, computer equipment and storage medium |
Also Published As
| Publication number | Publication date |
|---|---|
| CN109871405B (en) | 2023-01-24 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| Jensen et al. | PASCAL user manual and report: ISO PASCAL standard | |
| Hoare | Chapter II: Notes on data structuring | |
| JP4604041B2 (en) | An extension to the SQL language to modify set-valued and scalar-valued columns with a single statement | |
| CN106372044B (en) | A method of based on the typing dimension XBRL report of report generation | |
| US20080109780A1 (en) | Method of and apparatus for optimal placement and validation of i/o blocks within an asic | |
| Baum | An introduction to Stata programming | |
| Tharp | File organization and processing | |
| WO1999060492A1 (en) | Data village system | |
| CN101154239A (en) | System and method for transforming tabular form date into structured document | |
| CN107844425A (en) | A kind of database statement inspection method and device | |
| CN109871405A (en) | A kind of method and device that Excel list data is imported to database | |
| Roelofs | AIMMS 3. 10 Language Reference | |
| Robinson et al. | Report on the textual criticism challenge 1991 | |
| CN103744943A (en) | Data inputting method applied to position evaluation system | |
| Carpenter | Carpenter's Guide to Innovative SAS Techniques | |
| Howe | Data analysis for database design | |
| CN108427675A (en) | Build the method and apparatus of index | |
| Jones | Lecture Notes in Computer Science | |
| US7124382B1 (en) | Method and apparatus for rule file generation | |
| CN102929700B (en) | Method for importing word test library to interactive teaching platform | |
| Fillat et al. | Generalized Organization of Large Data Bases: A Set-theoretic Approach to Relations | |
| CN102902760B (en) | Method for detecting demand conflict relation | |
| Rasdorf et al. | NIAM conceptual data-base design in construction management | |
| Kilov | An approach to the user interface and semantic integrity for a relational DBMS | |
| JP3109331B2 (en) | Form output device |
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 |
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. |
|
| TR01 | Transfer of patent right |