CN107291674A - A kind of method that Excel list datas are converted to database format - Google Patents
A kind of method that Excel list datas are converted to database format Download PDFInfo
- Publication number
- CN107291674A CN107291674A CN201710436616.7A CN201710436616A CN107291674A CN 107291674 A CN107291674 A CN 107291674A CN 201710436616 A CN201710436616 A CN 201710436616A CN 107291674 A CN107291674 A CN 107291674A
- Authority
- CN
- China
- Prior art keywords
- data
- worksheet
- form attributes
- excel
- attribute
- 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.)
- Pending
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F40/00—Handling natural language data
- G06F40/10—Text processing
- G06F40/12—Use of codes for handling textual entities
- G06F40/151—Transformation
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Health & Medical Sciences (AREA)
- Artificial Intelligence (AREA)
- Audiology, Speech & Language Pathology (AREA)
- Computational Linguistics (AREA)
- General Health & Medical Sciences (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a kind of method that Excel list datas are converted to database format, this method includes new destination file, loaded targets file, create the steps such as tables of data, attribute configuration table is created in Excel, and create form attributes in attribute configuration table, the content of form attributes includes worksheet starting symbol, worksheet termination number, project row number, begin to read line number and terminate line number etc., the function of database management language reads tables of data according to the content of form attributes, and it is converted into the data type files for supporting database management language, for the Microsoft Excel of different-format, only need to change corresponding content in form attributes, function need not be modified, ordinary person can just be competent at by simple training, with simple to operate, the advantages of highly versatile.
Description
Technical field
The present invention relates to a kind of conversion method of electronic data format, belong to computer software fields.
Background technology
There are EXCEL simple operation and other advantages to be widely used in initial data typing, although EXCEL itself has simple
Data processing function, but to carry out complicated, huge data processing, it is necessary to using specialty database management language such as
Visual foxpro etc..Current database management language such as visual foxpro can read data from Microsoft Excel,
But its Microsoft Excel that can be read is fixed form, if the content and form of Microsoft Excel change, it is necessary to repair
Change the function of Read EXCEL form in database management language, and Modification growth function needs programmer's operation of specialty, operation difficulty
Greatly, because the programming custom of distinct program person is different with logical thinking, the difficulty of other programmer's Modification growth functions is also increased.
The content of the invention
In order to overcome the deficiencies in the prior art, the present invention provides a kind of highly versatile, simple to operate by Excel form numbers
According to the method for being converted to database format.
The technical solution adopted for the present invention to solve the technical problems is：
A kind of method that Excel list datas are converted to database format, this method is as follows：
Step 1：New destination file
Newly-built EXCEL file, New anyAttribute allocation list and worksheet in EXCEL paste the data in other Microsoft Excels
Into worksheet, the newly-built form attributes in the attribute configuration table, form attributes one worksheet of correspondence.
Step 2：Loaded targets file
Open in database management language, call function, the loaded targets file in function.
Step 3：Create tables of data
Function sequentially reads the form information and the corresponding worksheet of the form attributes defined in form attributes from file destination
Content, information creating tables of data that database management language is read according to function is simultaneously stored.
The content of the form attributes includes：Dbf Table Properties, xls spans and worksheet names；The dbf tables category
Property include project name and item attribute；The project name refers to the title of each item data in worksheet；The item attribute
Refer to the data attribute of each item data, the data attribute includes the field type and byte length of the item data；The xls takes
Being worth scope includes worksheet starting symbol, worksheet termination number, project row number, the reading line number that begins and terminates line number；The worksheet rises
Beginning number and worksheet termination number refer to that function reads worksheet sequence number involved during a data, and the project row number refers to letter
Number reads the starting column location of data, and the beginning reads line number and refers to that function reads the initial row position of data, terminates line number and refer to
Function reads the termination row position of data；The worksheet names are to work according to the data type that worksheet is deposited
The title of table name.
In the file destination, the worksheet of serial number 1 is attribute configuration table.
In the attribute configuration table of the step (1), n-th form attributes original position behavior " 20*N-19 ", wherein, N is
Natural number.
The data handling procedure of the step (3) is as follows：
(1), N values are 1, according to algorithm " 20*N-19 ", determine the 1st form attributes position in attribute configuration table.
(2) project name and item attribute in the 1st form attributes, are read, according to project name, in data base administration
Table structure is created in software, is the worksheet names in the 1st form attributes by the name nominating of table structure, is then filled with the 1st
Project name in individual form attributes, then the item attribute in the 1st form attributes define each data in table structure
Field type and byte length, generate tables of data framework.
(3), line number is read according to the worksheet starting symbol in the 1st form attributes, worksheet termination number, project row number, beginning
Data path is specified with line number is terminated, data is read from corresponding worksheet, the data of reading is inserted in tables of data framework
Correspondence position generation tables of data.
(4), N values Jia 1, repeat step (1) to (3), the corresponding worksheet of all form attributes in reading attributes allocation list,
Complete to be converted into Excel list datas into the data type files for supporting database management language.
The beneficial effects of the invention are as follows：The present invention creates attribute configuration table in Excel, and is created in attribute configuration table
Built form attributes, the contents of form attributes include worksheet starting symbol, worksheet terminate number, project row number, begin to read line number and
Line number etc. is terminated, the function of database management language reads tables of data according to the content of form attributes, and is converted into support data
The data type files of library management software, for the Microsoft Excel of different-format, it is only necessary to change corresponding in form attributes
Content, it is not necessary to modified to function, ordinary person can just be competent at by simple training, with simple to operate,
The advantages of highly versatile.
Embodiment
A kind of method that Excel list datas are converted to database format, this method is based on existing Windows systems
The database management language such as system, Microsoft Excel office softwares, visual foxpro realizes that this method is included as next
Three steps：
Step 1：New destination file
Newly-built EXCEL file, New anyAttribute allocation list and worksheet (newly-built attribute configuration table and work entry in EXCEL
Record is as shown in table 3), the data in other Microsoft Excels are pasted in worksheet (worksheet is as shown in table 2)；Match somebody with somebody in attribute
Newly-built form attributes (attribute configuration table and form attributes are as shown in table 1) in table are put, a form attributes correspond to a worksheet,
A form attributes fix the space for taking the row cell row of attribute configuration table 14, form attributes and 6, form attributes interval blank
Table unit row；When the data volume of conversion is excessive, the project being related to is various, and the newly-built quantity of worksheet will increase, by using
Some a data of worksheet storage.
Table 1
Table 2
Step 2：Loaded targets file
Open in database management language, call function, the loaded targets file in function.
Step 3：Create tables of data
Function sequentially reads the form information and the corresponding worksheet of the form attributes defined in form attributes from file destination
Content, information creating tables of data that database management language is read according to function is simultaneously stored.
The content of the form attributes includes：Dbf Table Properties, xls spans and worksheet names；The dbf tables category
Property include project name and item attribute；The project name refers to the title of each item data in worksheet；The item attribute
Refer to the data attribute of each item data, the data attribute includes the field type and byte length of the item data；The xls takes
Being worth scope includes worksheet starting symbol, worksheet termination number, project row number, the reading line number that begins and terminates line number；The worksheet rises
Beginning number and worksheet termination number refer to that function reads worksheet sequence number involved during a data, and the project row number refers to letter
Number reads the starting column location of data, and the beginning reads line number and refers to that function reads the initial row position of data, terminates line number and refer to
Function reads the termination row position of data；The line number that terminates is initially set 200000, when the item data read to
200000 cell rows then terminate reading, be transferred to and the project data of the next item down is read out, and the termination line number can be according to required
The data cases to be inputted and change termination line number；The worksheet names are right according to the data type that worksheet is deposited
The title of worksheet name.
In the file destination, (attribute configuration table and each worksheet position are such as attribute configuration table for the worksheet of serial number 1
Shown in table 3).
Table 3
In the attribute configuration table of the step (1), n-th form attributes original position behavior " 20*N-19 ", wherein, N is nature
Number.
The data handling procedure of the step (3) is as follows：
(1), N values are 1, according to algorithm " 20*N-19 ", determine the 1st form attributes position in attribute configuration table.
(2) project name and item attribute in the 1st form attributes, are read, according to project name, in data base administration
Table structure is created in software, is the worksheet names in the 1st form attributes by the name nominating of table structure, is then filled with the 1st
Project name in individual form attributes, then the item attribute in the 1st form attributes define each data in table structure
Field type and byte length, generate tables of data framework.
(3), line number is read according to the worksheet starting symbol in the 1st form attributes, worksheet termination number, project row number, beginning
Data path is specified with line number is terminated, data is read from corresponding worksheet, the data of reading is inserted in tables of data framework
Correspondence position generation tables of data.
(4), N values Jia 1, repeat step (1) to (3), the corresponding work of all form attributes in reading attributes allocation list
Table, completes to be converted into Excel list datas into data type files (the tables of data such as institute of table 4 for supporting database management language
Show).
Table 4
The function source code of the present embodiment is as follows：
The present invention creates in attribute configuration table form attributes, list category by creating attribute configuration table in Excel
Property content include worksheet starting symbol, worksheet and terminate number, project row number, begin to read line number and terminate line number etc., data depositary management
The function for managing software reads tables of data according to the content of form attributes, and is converted into the data type for supporting database management language
File, for the Microsoft Excel of different-format, it is only necessary to corresponding content is changed in form attributes, it is not necessary to letter
Number is modified, and ordinary person can just be competent at by simple training, with simple to operate, the advantages of highly versatile.
Embodiment above can not limit the protection domain of the invention, and the personnel of professional skill field are not departing from
In the case of the invention general idea, impartial modification and the change done still fall within the scope that the invention is covered
Within.
Claims (5)
1. a kind of method that Excel list datas are converted to database format, it is characterised in that this method is as follows：
（1）：New destination file
Newly-built EXCEL file, New anyAttribute allocation list and worksheet in the EXCEL file, by other Microsoft Excels
Data are pasted in worksheet, the newly-built form attributes in the attribute configuration table, form attributes one worksheet of correspondence；
（2）, loaded targets file
Open in database management language, call function, the loaded targets file in function；
（3）, create tables of data
Function sequentially reads the form information and the corresponding worksheet of the form attributes defined in form attributes from file destination
Content, information creating tables of data that database management language is read according to function is simultaneously stored.
2. the method according to claim 1 that Excel list datas are converted to database format, it is characterised in that described
The content of form attributes includes：Dbf Table Properties, xls spans and worksheet names；The dbf Table Properties include entry name
Claim and item attribute；The project name refers to the title of each item data in worksheet；The item attribute refers to each item data
Data attribute, the data attribute includes the field type and byte length of the item data；The xls spans include work
Make table starting symbol, worksheet termination number, project row number, the reading line number that begins and terminate line number；The worksheet starting symbol and worksheet
Termination number refers to that function reads worksheet sequence number involved during a data, and the project row number refers to that function reads data
Starting column location, the beginning reads line number and refers to that function reads the initial row position of data, terminates line number and refers to that function reads data
Termination row position；The worksheet names are the titles named according to the data type that worksheet is deposited worksheet.
3. the method according to claim 1 or 2 that Excel list datas are converted to database format, it is characterised in that
In the file destination, the worksheet of serial number 1 is attribute configuration table.
4. the method according to claim 1 or 2 that Excel list datas are converted to database format, it is characterised in that
The step（1）Attribute configuration table in, n-th form attributes original position behavior " 20*N-19 ", wherein, N is natural number.
5. according to the method that Excel list datas are converted to database format described in claim 4, it is characterised in that the step
Suddenly（3）Data handling procedure it is as follows：
（1）, N values be 1, according to algorithm " 20*N-19 ", determine the 1st form attributes position in attribute configuration table；
（2）, read the 1st form attributes in project name and item attribute, according to project name, in database management language
Middle establishment table structure, is the worksheet names in the 1st form attributes by the name nominating of table structure, is then filled with the 1st table
Project name in single attribute, then the item attribute in the 1st form attributes define the field of each data in table structure
Type and byte length, generate tables of data framework；
（3）, terminate number according to the worksheet starting symbol in the 1st form attributes, worksheet, project row number, begin to read line number and end
Only line number specifies data path, reads data from corresponding worksheet, pair that the data of reading are inserted in tables of data framework
Position is answered to generate tables of data；
（4）, N values Jia 1, repeat step（1）Extremely（3）, the corresponding worksheet of all form attributes, is completed in reading attributes allocation list
Excel list datas are converted into the data type files for supporting database management language.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201710436616.7A CN107291674A (en) | 2017-06-12 | 2017-06-12 | A kind of method that Excel list datas are converted to database format |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201710436616.7A CN107291674A (en) | 2017-06-12 | 2017-06-12 | A kind of method that Excel list datas are converted to database format |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN107291674A true CN107291674A (en) | 2017-10-24 |
Family
ID=60096382
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201710436616.7A Pending CN107291674A (en) | 2017-06-12 | 2017-06-12 | A kind of method that Excel list datas are converted to database format |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN107291674A (en) |
Cited By (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN107832477A (en) * | 2017-12-01 | 2018-03-23 | 深圳市朗仁科技有限公司 | A kind of method and system that database is created based on Excel forms |
| CN108133003A (en) * | 2017-12-21 | 2018-06-08 | 沈阳鼓风机集团自动控制系统工程有限公司 | Industrial control software data library generating method |
| CN108133002A (en) * | 2017-12-21 | 2018-06-08 | 沈阳鼓风机集团自动控制系统工程有限公司 | Industrial control software database creating system |
| CN108491510A (en) * | 2018-03-22 | 2018-09-04 | 平安科技（深圳）有限公司 | Excel data import method, apparatus, computer equipment and the storage medium of database |
| CN109033338A (en) * | 2018-07-23 | 2018-12-18 | 北京新能源汽车股份有限公司 | A kind of generation method and system of the database file of automotive communication protocol |
| CN109284254A (en) * | 2018-10-16 | 2019-01-29 | 浪潮天元通信信息系统有限公司 | A kind of general excel document handling method |
| CN109543154A (en) * | 2018-10-11 | 2019-03-29 | 天津字节跳动科技有限公司 | Method for converting types, device, storage medium and the electronic equipment of list data |
| CN116450578A (en) * | 2023-06-15 | 2023-07-18 | 中国航发四川燃气涡轮研究院 | Aircraft engine material data maintenance management method |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN1741020A (en) * | 2005-09-29 | 2006-03-01 | 北京勤哲软件技术有限责任公司 | Method for storing electronic table unit lattice content with relational data base |
| 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 |
| CN103744982A (en) * | 2014-01-15 | 2014-04-23 | 北京神州普惠科技股份有限公司 | Method for importing Excel data into database |
-
2017
- 2017-06-12 CN CN201710436616.7A patent/CN107291674A/en active Pending
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN1741020A (en) * | 2005-09-29 | 2006-03-01 | 北京勤哲软件技术有限责任公司 | Method for storing electronic table unit lattice content with relational data base |
| 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 |
| CN103744982A (en) * | 2014-01-15 | 2014-04-23 | 北京神州普惠科技股份有限公司 | Method for importing Excel data into database |
Cited By (12)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN107832477A (en) * | 2017-12-01 | 2018-03-23 | 深圳市朗仁科技有限公司 | A kind of method and system that database is created based on Excel forms |
| CN107832477B (en) * | 2017-12-01 | 2021-10-15 | 深圳市云伽智能技术有限公司 | Method and system for creating database based on Excel table |
| CN108133003A (en) * | 2017-12-21 | 2018-06-08 | 沈阳鼓风机集团自动控制系统工程有限公司 | Industrial control software data library generating method |
| CN108133002A (en) * | 2017-12-21 | 2018-06-08 | 沈阳鼓风机集团自动控制系统工程有限公司 | Industrial control software database creating system |
| CN108491510A (en) * | 2018-03-22 | 2018-09-04 | 平安科技（深圳）有限公司 | Excel data import method, apparatus, computer equipment and the storage medium of database |
| CN109033338A (en) * | 2018-07-23 | 2018-12-18 | 北京新能源汽车股份有限公司 | A kind of generation method and system of the database file of automotive communication protocol |
| CN109033338B (en) * | 2018-07-23 | 2022-02-01 | 北京新能源汽车股份有限公司 | Method and system for generating database file of automobile communication protocol |
| CN109543154A (en) * | 2018-10-11 | 2019-03-29 | 天津字节跳动科技有限公司 | Method for converting types, device, storage medium and the electronic equipment of list data |
| CN109543154B (en) * | 2018-10-11 | 2021-07-23 | 天津字节跳动科技有限公司 | Type conversion method and device of table data, storage medium and electronic equipment |
| CN109284254A (en) * | 2018-10-16 | 2019-01-29 | 浪潮天元通信信息系统有限公司 | A kind of general excel document handling method |
| CN116450578A (en) * | 2023-06-15 | 2023-07-18 | 中国航发四川燃气涡轮研究院 | Aircraft engine material data maintenance management method |
| CN116450578B (en) * | 2023-06-15 | 2023-09-15 | 中国航发四川燃气涡轮研究院 | Aircraft engine material data maintenance management method |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN107291674A (en) | A kind of method that Excel list datas are converted to database format | |
| US5175810A (en) | Tabular data format | |
| CN110209728B (en) | Distributed heterogeneous database synchronization method, electronic equipment and storage medium | |
| US9378190B2 (en) | Word processor data organization | |
| US9069818B2 (en) | Textual search for numerical properties | |
| CN103714492A (en) | Automatic report generating method capable of meeting power distribution network management specific data standards | |
| CN106021394A (en) | Website construction method and apparatus | |
| CN104035993B (en) | Memory search method, e-book management system, the reading system of e-book | |
| US7370060B2 (en) | System and method for user edit merging with preservation of unrepresented data | |
| CN107704202A (en) | A kind of method and apparatus of data fast reading and writing | |
| EP3617910A1 (en) | Method and apparatus for displaying textual information | |
| CN105975446A (en) | Method and system for displaying word document content by modules in mobile phone terminal | |
| CN108776702A (en) | A kind of data make a report on page user-defined visual configuration method | |
| CN104408128B (en) | A kind of reading optimization method indexed based on B+ trees asynchronous refresh | |
| CN102467496B (en) | Method and device for converting stream mode typeset content into block mode typeset document | |
| CN114860727A (en) | Zipper watch updating method and device | |
| CN109271479A (en) | A kind of resume structuring processing method | |
| US20120109911A1 (en) | Compression Of XML Data | |
| CA2936485A1 (en) | Optimized data condenser and method | |
| CN110308907A (en) | Data transfer device, device, storage medium and electronic equipment | |
| CN113179294B (en) | Schema unified access method based on intelligent contract | |
| CN107862023A (en) | A kind of method of data in parsing EDB database source files | |
| CN112785249A (en) | Schedule management method and system based on VBA | |
| US8849866B2 (en) | Method and computer program product for creating ordered data structure | |
| CN112131432A (en) | Method and device for acquiring command line multi-way tree of data network equipment |
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 | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20171024 |
|
| RJ01 | Rejection of invention patent application after publication |