CN109947832A

CN109947832A - EXCEL tables of data is generated to method, apparatus, terminal and the storage medium of SQL file

Info

Publication number: CN109947832A
Application number: CN201910086141.2A
Authority: CN
Inventors: 林琪琛; 佘西泠
Original assignee: Ping An Technology Shenzhen Co Ltd
Current assignee: Ping An Technology Shenzhen Co Ltd
Priority date: 2019-01-29
Filing date: 2019-01-29
Publication date: 2019-06-28

Abstract

A method of EXCEL tables of data being generated into SQL file, comprising: obtain EXCEL tables of data and its first row data as the first critical field title；Text file is created according to the first critical field title, and the second critical field title being arranged in the first critical field title and text file corresponds；When receiving the generation instruction of user, the first critical field of corresponding first critical field title in EXCEL tables of data is obtained by row；Use one SQL subfile of generation after corresponding second critical field of the second critical field title in the first critical field replacement text file；When having traversed the last line of EXCEL tables of data, multiple SQL subfiles are generated, multiple SQL subfiles are integrated to obtain SQL file.The present invention also provides a kind of device, terminal and storage mediums that EXCEL tables of data is generated to SQL file.EXCEL tables of data can be generated SQL file automatically by the present invention, improve the working efficiency of Database Engineer, avoid the problem of artificially writing the cumbersome of SQL file and easily making a fault.

Description

EXCEL tables of data is generated to method, apparatus, terminal and the storage medium of SQL file

Technical field

The present invention relates to technical field of data processing, and in particular to a kind of side that EXCEL tables of data is generated to SQL file Method, device, terminal and storage medium.

Background technique

In today that development of computer is swift and violent, miscellaneous application system is all in the daily life and work for facilitating us Make.Most application system, is all B/S structure, i.e. client terminal/server structure, and server end meeting storage system owns Information required for running.For example, server can store some Transaction Informations etc. for transaction system.System is in the first of operation Phase, the data that often will do it some batches import work, complete the processes of these work approximately as: business side combs number According to, and be written in EXCEL and EXCEL is given to Database Engineer；Database Engineer is by the data conversion in EXCEL The .SQL file described for sql like language；.SQL file is put into application database by last engineer to be executed, and completes to import.

But for large batch of data, if be written one by one by engineer or in such a way that replacement is pasted in duplication, It will take a substantial amount of time and energy, while can not ensure the integrality and correctness of data.And there is no visualization interface, match It is all inconvenient with maintenance to set.

Therefore, how EXCEL tables of data quickly switched into .SQL file, is a technical problem urgently to be resolved.

Summary of the invention

In view of the foregoing, it is necessary to propose it is a kind of by EXCEL tables of data generate the method, apparatus of SQL file, terminal and EXCEL tables of data can be generated SQL file automatically by storage medium, it is ensured that every data line in EXCEL tables of data and be inserted The data line entered into relevant database corresponds, to improve the working efficiency of Database Engineer, avoids The problem of artificially writing the cumbersome of SQL file and easily making a fault.

The first aspect of the present invention provides a kind of method that EXCEL tables of data is generated SQL file, is applied in terminal, The described method includes:

Obtain the EXCEL tables of data to be processed for needing to generate SQL file；

The first row data in the EXCEL tables of data to be processed are obtained as the first critical field title；

According to the first critical field title create text file, and be arranged the first critical field title with it is described The second critical field title in text file corresponds；

When receiving the generation instruction of user, is obtained in the EXCEL tables of data to be processed by row and correspond to described first First critical field of critical field title；

Corresponding second key of the second critical field title in the text file is replaced using first critical field A SQL subfile is generated after field；And

When having traversed the last line data of the Excel tables of data to be processed, multiple SQL subfiles are generated, it will The multiple SQL subfile obtains SQL file after being integrated.

Preferably, when the generation instruction for receiving user, it is right in the EXCEL tables of data to be processed to obtain by row The first critical field for answering the first critical field title includes:

The target keywords that the first critical field title is corresponded in the EXCEL tables of data to be processed are obtained by row Section；

Judge whether the target critical field is standard key field；

It is crucial using the target critical field as described first when the target critical field is standard key field Field；

When the target critical field is not standard key field, according between preset keyword section and criteria field Mapping relations, the target critical field that will acquire are converted to standard key field as first critical field.

Preferably, the method also includes:

Display interface is provided, have on the display interface store the EXCEL tables of data to be processed path input frame, The table name input frame and extraction columns input frame of EXCEL tables of data to be processed；

When detect have input path in the path input frame when, it is described wait locate that storage is obtained according to the path that is inputted The path of the EXCEL tables of data of reason；

When detect have input the table name of EXCEL tables of data in the table name input frame when, according to the EXCEL number inputted The EXCEL tables of data of the corresponding table name is obtained according to the table name of table；

When detect have input number in the extraction columns input frame when, the number inputted is determined as described wait locate Columns to be extracted in the EXCEL tables of data of reason, it is right in the EXCEL tables of data to be processed to be obtained according to the columns to be extracted The data answered.

Preferably, obtained described according to the path that is inputted store the EXCEL tables of data to be processed path it Afterwards, the method can also include:

Whether the quantity of documents for judging corresponding storage in inputted path is 1；

When the quantity of documents for determining corresponding storage in inputted path is 1, using unique file as described in The EXCEL tables of data of processing；

When the quantity of documents for determining corresponding storage in inputted path is multiple, according to the EXCEL data inputted The table name of table obtains the EXCEL tables of data of the corresponding table name.

Preferably, the first row data obtained in the EXCEL tables of data to be processed are as the first critical field Title includes:

Content in the EXCEL tables of data to be processed is read in into memory；

The content is parsed according to preset parsing code and generates script file；

Using the data in the script file as first critical field.

Preferably, described that text file, the first critical field title are created according to the first critical field title Include: with the second critical field title one-to-one correspondence in the text file

Text file is created according to the first critical field title and default template file, and the critical field is set The second critical field title in title and the text file corresponds, and the default template file is previously according to SQL One template file of file format creation.

Preferably, it when the generation instruction for receiving user, is obtained by row corresponding in the EXCEL data to be processed First critical field of the first critical field title includes:

Since the second row of the EXCEL tables of data to be processed, obtain line by line every in EXCEL tables of data to be processed The data in the column of part being selected in all column or every row in row are as the first critical field, until described to be processed The last line of EXCEL tables of data.

The second aspect of the present invention provides a kind of device that EXCEL tables of data is generated to SQL file, runs in terminal, Described device includes:

First obtains module, needs to generate the EXCEL tables of data to be processed of SQL file for obtaining；

Second obtains module, closes for obtaining the first row data in the EXCEL tables of data to be processed as first Key field title；

Creation module, for creating text file according to the first critical field title, and it is crucial to be arranged described first The second critical field title in field name and the text file corresponds；

Third obtains module, when the generation for receiving user instructs, obtains the EXCEL data to be processed by row The first critical field of the first critical field title is corresponded in table；

Replacement module, for replacing the second critical field title pair in the text file using first critical field A SQL subfile is generated after the second critical field answered；And

Generation module, for generating multiple when having traversed the last line data of the Excel tables of data to be processed SQL subfile obtains SQL file after being integrated the multiple SQL subfile.

The third aspect of the present invention provides a kind of terminal, and the terminal includes processor, and the processor is deposited for executing The method that EXCEL tables of data is generated into SQL file is realized when the computer program stored in reservoir.

The fourth aspect of the present invention provides a kind of computer readable storage medium, is stored thereon with computer program, special Sign is that the computer program realizes the method that EXCEL tables of data is generated SQL file when being executed by processor.

Method, apparatus, the terminating machine storage medium that EXCEL tables of data is generated to SQL file provided by the invention, root Text file is generated according to the EXCEL tables of data to be processed, the first critical field in the EXCEL tables of data to be processed The second critical field title in title and the text file corresponds.It can so ensure the EXCEL to be processed Correspondence between every data line in tables of data and the data line that be inserted into relevant database, it is described to be processed Some of the data of certain cell in every a line in EXCEL tables of data and certain the row data that be inserted into relational database Field corresponds, and has then reached and has been associated with the data in EXCEL tables of data one by one with the data being inserted into database. It realizes and EXCEL tables of data generation SQL file is avoided into people to improve the working efficiency of Database Engineer automatically The problem of to write the cumbersome of SQL file and easily making a fault.

Detailed description of the invention

In order to more clearly explain the embodiment of the invention or the technical proposal in the existing technology, to embodiment or will show below There is attached drawing needed in technical description to be briefly described, it should be apparent that, the accompanying drawings in the following description is only this The embodiment of invention for those of ordinary skill in the art without creative efforts, can also basis The attached drawing of offer obtains other attached drawings.

Fig. 1 is the flow chart for the method that EXCEL tables of data is generated to SQL file that the embodiment of the present invention one provides.

Fig. 2 is the structure chart of the device provided by Embodiment 2 of the present invention that EXCEL tables of data is generated to SQL file.

Fig. 3 is the schematic diagram for the terminal that the embodiment of the present invention three provides.

The present invention that the following detailed description will be further explained with reference to the above drawings.

Specific embodiment

To better understand the objects, features and advantages of the present invention, with reference to the accompanying drawing and specific real Applying example, the present invention will be described in detail.It should be noted that in the absence of conflict, the embodiment of the present invention and embodiment In feature can be combined with each other.

In the following description, numerous specific details are set forth in order to facilitate a full understanding of the present invention, described embodiment is only It is only a part of the embodiment of the present invention, instead of all the embodiments.Based on the embodiments of the present invention, ordinary skill Personnel's every other embodiment obtained without making creative work, shall fall within the protection scope of the present invention.

Unless otherwise defined, all technical and scientific terms used herein and belong to technical field of the invention The normally understood meaning of technical staff is identical.Term as used herein in the specification of the present invention is intended merely to description tool The purpose of the embodiment of body, it is not intended that in the limitation present invention.

Preferably, the method for the invention that EXCEL tables of data is generated into SQL file apply in one or more terminal or In person's server.The terminal is that one kind can be automatic to carry out numerical value calculating and/or letter according to the instruction for being previously set or storing The equipment of processing is ceased, hardware includes but is not limited to microprocessor, specific integrated circuit (Application Specific Integrated Circuit, ASIC), programmable gate array (Field-Programmable Gate Array, FPGA), number Word processing device (Digital Signal Processor, DSP), embedded device etc..

The terminal can be desktop PC, notebook, palm PC and cloud server etc. and calculate equipment.It is described Terminal can carry out human-computer interaction by modes such as keyboard, mouse, remote controler, touch tablet or voice-operated devices with client.

Embodiment one

In the present embodiment, the method that EXCEL tables of data is generated SQL file can be applied to have display function Terminal or fixed terminal in, the terminal is not limited to PC, smart phone, tablet computer, is equipped with camera shooting Desktop computer or all-in-one machine of head etc..

It is described by EXCEL tables of data generate SQL file method also can be applied to by terminal and by network with it is described In the hardware environment that the server that terminal is attached is constituted.Network includes but is not limited to: wide area network, Metropolitan Area Network (MAN) or local Net.The method that EXCEL tables of data is generated SQL file of the embodiment of the present invention can be executed by server, can also be by end End can also be and be executed jointly by server and terminal to execute.

For example, needs are carried out with the terminal that EXCEL tables of data is generated to SQL file, can directly integrate at the terminal EXCEL tables of data is generated to the function of SQL file, or installation for realizing side of the invention provided by method of the invention The client of method.For another example, method provided by the present invention can be with Software Development Kit (Software Development Kit, SDK) form operate in the equipment such as server.

As shown in Figure 1, the method that EXCEL tables of data is generated SQL file specifically includes following steps, according to difference Demand, the sequence of step can change in the flow chart, and certain steps can be omitted.

S11: the EXCEL tables of data to be processed for needing to generate SQL file is obtained.

In the present embodiment, record has data in advance in the EXCEL tables of data.For example, selling operation personnel use EXCEL Tables of data has recorded order data.Human resources (Human Resource, HR) with EXCEL tables of data have recorded corporate history and Current employee's list data.Data in the EXCEL tables of data by user according to project or the actual needs of work voluntarily It defines, the data in the EXCEL tables of data of different purposes are inevitable different.EXCEL tables of data in the present embodiment is for basis The pending data table that project demand is quickly converted as SQL file.For example, an ordering system newly developed, in the order Data just are started without, need the order data in the EXCEL tables of data in selling operation personnel's hand quickly generating SQL (Structured Query Language, structured query language) file is simultaneously imported into the corresponding database of order.Again Such as, associate's management system newly developed is just started without data in associate's management system, need by Employee's list data in EXCEL tables of data in HR hand quickly generates SQL file and imported into associate's management system pair In the database answered.

In the present embodiment, the table format of the EXCEL tables of data to be processed may include table name, field, field pair The field name answered.In other embodiments, the table format of the EXCEL tables of data to be processed is in addition to including table name, word It can also include field type, index and external key outside section, the corresponding field name of field.The EXCEL tables of data it is each Row, referred to as one record, the data comprising at least one field, each column of tables of data indicate the data of same format, referred to as Field, the corresponding field name of the field of each column are placed on the first row of the EXCEL tables of data.

Preferably, the method can also include:

Display interface is provided, has the path input frame for storing the EXCEL tables of data to be processed on the display interface And the table name input frame of EXCEL tables of data to be processed；

When detect have input the table name of EXCEL tables of data in the table name input frame when, according to the EXCEL number inputted The EXCEL tables of data of the corresponding table name is obtained according to the table name of table.

The path input frame is used to receive the path of the EXCEL tables of data to be processed of user's input.The table Name input frame is used to receive the table name input frame of the EXCEL tables of data to be processed of user's input.

The EXCEL tables of data can be stored under a certain path by user in advance, by the path input frame with The EXCEL tables of data is obtained under path set by family.If uniquely housing EXCEL tables of data to be processed under the path, It can then not have to obtain the EXCEL tables of data to be processed by table name input frame, get and store EXCEL number to be processed Behind the path of table, directly using the file under the path as EXCEL to be processed, without again by clicking or touching institute Table name input frame is stated to obtain the corresponding EXCEL tables of data to be processed of inputted table name, it is convenient and efficient；If the path Under house multiple EXCEL tables of data, then need further to receive the table name of user's input by table name input frame, and according to institute The table name of input obtains the EXCEL tables of data to be processed.

S12: the first row data in the EXCEL tables of data to be processed are obtained as the first critical field title.

In the present embodiment, after getting EXCEL tables of data to be processed, obtain in the EXCEL tables of data to be processed The first row data and the first row data are extracted as the first critical field title.

Illustratively, the EXCEL tables of data to be processed is as follows:

name age sex

15 male of Zhang San

12 male of Li Si

5 22 female of king

Obtain the first row data " name " in the EXCEL tables of data to be processed, " age ", " sex " are closed as first Key field title.

Content in the EXCEL tables of data to be processed is read in into memory；

The content is parsed according to preset parsing code and generates script file.

In the present embodiment, it can be pre-configured with parsing code, by the parsing code to the EXCEL number to be processed It is parsed according to the content in table, order is finally generated according to preconfigured script and generates corresponding script.It is described preparatory The parsing code of configuration and preconfigured script are generated order and are write using JAVA language.

Data in all column of the first row in the EXCEL tables of data to be processed can successively be extracted into work For the first critical field title.At this point, in the quantity of the first critical field title and the EXCEL tables of data to be processed Columns it is identical, i.e., how many column data in the described EXCEL tables of data to be processed then gets how many first accordingly and closes Key field title.Illustratively, record has 100 row, 10 column data in order data table, then successively extracts the order data table In the first row 10 column datas as the first critical field title, the quantity of corresponding first critical field title is 10.

Data in the part of the first row in the EXCEL tables of data to be processed can also being arranged successively extract As the first critical field title.At this point, the quantity of the first critical field title and the EXCEL tables of data to be processed In part columns it is identical, that is, have chosen how many a part column datas in the EXCEL tables of data to be processed, then obtain accordingly Get how many first critical field titles.Illustratively, record has 100 row, 10 column data in financial data table, if need to only mention Preceding 3 column data is taken, then successively extracts preceding 3 column data of the first row in the financial data as the first critical field title, The quantity of corresponding first critical field title is 3.

Preferably, it is inputted there are also columns input frame is extracted when detecting to extract in columns input frame on the display interface When digital, the number inputted is determined as the columns to be extracted in the EXCEL tables of data to be processed, according to it is described to It extracts columns and obtains corresponding data in the EXCEL tables of data to be processed.

All column can be inputted in the extraction columns input frame, can also input number.When input all column, table The bright data by all column of the first row in the EXCEL tables of data to be processed are successively extracted as the first key Field name；When inputting number, show that institute will be corresponded in all column of the first row in the EXCEL tables of data to be processed Data in the column of the number of input are successively extracted as the first critical field title.

S13, according to the first critical field title create text file, and be arranged the first critical field title with The second critical field title in the text file corresponds.

Text file is created according to the first critical field title and default template file, and the critical field is set The second critical field title in title and the text file corresponds.

Illustratively, as follows according to the first critical field title " name ", " age ", the text file of " sex " creation It is shown:

Insert into t_user(name,age,sex)values({name},{age},{sex})；

Wherein, the second critical field title " name " in the text file, " age ", " sex " and first key Field name " name ", " age ", " sex " are corresponded.

Preferably, a template file can be created previously according to SQL file format, the template file corresponds to SQL language Sentence, it is subsequent the template file is edited and is modified according to different EXCEL tables of data to be processed after generate the text This document, so that the EXCEL tables of data to be processed of the second critical field title and acquisition in the text file First critical field title corresponds.

Illustratively, it is assumed that the template file is as follows:

Insert into t_staff(staffname,birth,sex,education background)values ({staffname},{birth},{sex},{education background})；

Then according to the actual content of EXCEL tables of data to be processed, above-mentioned template file is edited and modified, is obtained Text file is as follows:

Insert into t_user(name,age,sex)values({name},{age},{sex})；

By the way that a template file is pre-created, the workload of Database Engineer can be saved, subsequent need to be according to institute The content for stating EXCEL tables of data to be processed carries out adaptability editor and modification, it is ensured that second in text file generated Critical field title and the first critical field title correspond.

The filename of the text file created can voluntarily be configured by user.The store path of the text file created It is identical as the store path of EXCEL tables of data to be processed.

It can also be using the text file that this is created as template file, for subsequent by other EXCEL data to be processed Table makes reference when generating SQL file, and is edited and modified on this basis.

S14 when receiving the generation instruction of user, is obtained described in corresponding in the EXCEL tables of data to be processed by row First critical field of the first critical field title.

In the present embodiment, there are also pre-set virtual keys on the display interface, described by clicking or touching Virtual key triggering generates instruction.When triggering generation instruction, successively obtained in the EXCEL tables of data to be processed by row First critical field of the corresponding first critical field title.

Specifically, being obtained by row corresponding in the EXCEL data to be processed when the generation instruction for receiving user First critical field of the first critical field title includes:

Illustratively, it when receiving the generation instruction of user, successively obtains each in the EXCEL tables of data to be processed Row data, and obtain the first critical field that the first critical field title is corresponded in each row of data.Wherein, described to be processed EXCEL tables of data in the second row correspond to the first critical field of the first critical field title " name ", " age ", " sex " For " Zhang San ", " 15 ", " male ", the third line corresponds to the first critical field title in the EXCEL tables of data to be processed " name ", " age ", " sex " the first critical field be " Li Si ", " 11 ", " male ", " king five ", " 22 ", " female ".

Judge whether the target critical field is standard key field；

Since in a practical application, the data in the EXCEL tables of data to be processed are possible to not on format It is unified, or be not the data of reference format.For example, under male's malapropism name section in EXCEL tables of data to be processed Some are " male " to data, some are " 1 ", some are " female " to the data under women malapropism name section, some are " 0 ".Thus pressing When corresponding to the target critical field of the first critical field title in the row acquisition EXCEL tables of data to be processed, first know Not Chu the target critical field whether be standard key field.When identifying target critical field is standard key field, directly The target critical field that will identify that is connect as the first critical field；It is standard key field when identifying target critical field not When, the corresponding target is matched from presetting database according to the mapping relations between preset keyword section and criteria field closes The standard key field of key field, using the standard key field matched as the first critical field.Pass through preparatory customized pass Mapping relations between key field and criteria field, the data in EXCEL tables of data to be processed are nonstandard or lack of standardization When, the critical field of standard or cannonical format is matched into, it can be convenient for when generating SQL substatement, holding is united on format One, be conducive to the generation of SQL substatement.

S15: the second critical field title corresponding second in the text file is replaced using first critical field A SQL subfile is generated after critical field.

In the present embodiment, every the first critical field obtained in EXCEL tables of data to be processed described in a line then uses institute It states the first critical field and replaces corresponding second critical field of the second critical field title in the text file.It is i.e. every to obtain one When the first critical field in the row EXCEL tables of data to be processed, traverse a text file, until will it is described to First critical field of the last line of the EXCEL tables of data of processing replaces the second critical field name in the text file Claim corresponding second critical field, to generate a SQL subfile.That is, how many row of EXCEL tables of data to be processed Data then traverse text file described in how many times, corresponding to generate how many a SQL subfiles.After each SQL subfile is opened Content be exactly can directly run one obtained according to the EXCEL tables of data to be processed and the text file SQL statement.One SQL statement corresponds to a line content in the EXCEL tables of data to be processed.

The SQL subfile is substantially .txt file.

Content after each SQL subfile is opened is exactly SQL statement.That is, each SQL subfile opens it Content afterwards be exactly obtained according to the text file and the EXCEL tables of data content to be processed that are created can be direct The SQL statement of operation.

Specifically, beginning stepping through the text text when getting the data line in the EXCEL tables of data to be processed Part, it is when reading in the text file second critical field title, the second critical field title is corresponding The second critical field replace with first critical field that the first critical field title is corresponded in the row.For example, institute Stating { key } in the second critical field title A in text file is { contract_num }, the EXCEL number to be processed According to the entitled contract_num of the first critical field for corresponding to the second critical field title A in table, and described first closes Corresponding first critical field of key field title is " HT-20170628001 ", then with " HT-20170628001 " replacement described the { key } in two critical field title A.

S16: when having traversed the last line data of the Excel tables of data to be processed, multiple SQL Ziwens are generated Part obtains SQL file after being integrated the multiple SQL subfile.

In the present embodiment, data how many row in the EXCEL tables of data to be processed is corresponding to generate how many a SQL Subfile.It should be understood that the data in the EXCEL tables of data refer to that removing the first row represents the one of the column data attribute Row data.

Java language itself provides the api routine of one group of reading and writing of files, generated by calling API file to read SQL subfile generates the function of SQL file to reach.

The SQL file that above-described embodiment generates is applicable to known all databases, such as: oracl, greenplum, Postgresql, mysql etc..

Illustratively, when having traversed the last line data of the Excel tables of data to be processed, pass through the text It is as follows to obtain multiple SQL subfiles for file:

Insert into t_user (name, age, sex) values (' Zhang San ', ' 15 ', ' male ')；

Insert into t_user (name, age, sex) values (' Li Si ', ' 12 ', ' male ')；

Insert into t_user (name, age, sex) values (' king five ', ' 22 ', ' female ')；

Wherein, first SQL subfile of generation corresponds to the first row data of the EXCEL tables of data to be processed, raw At second SQL subfile correspond to the second row data of the EXCEL tables of data to be processed, third SQL of generation File corresponds to the third line data of the EXCEL tables of data to be processed.

It should be noted that in other embodiments, when if there is multiple EXCEL tables of data, for example, existing simultaneously one The achievement data table of class's demographic data's table and corresponding class demographic data's table, then can according to actual needs, and use is above-mentioned The thought of technical solution when traversing every data line of multiple EXCEL tables of data, correspondence produces multiple SQL subfiles, More complicated functions such as to realize newly-increased, deletion, update.

In conclusion the method provided in an embodiment of the present invention that EXCEL tables of data is generated SQL file, acquisition need to give birth to At the EXCEL tables of data to be processed of SQL file；Obtain the first row data conduct in the EXCEL tables of data to be processed First critical field title；Text file is created according to the first critical field title, and first critical field is set The second critical field title in title and the text file corresponds；When receiving the generation instruction of user, obtained by row Take the first critical field that the first critical field title is corresponded in the EXCEL tables of data to be processed；Use described One critical field, which is replaced, generates SQL after corresponding second critical field of the second critical field title in the text file File；When having traversed the last line data of the Excel tables of data to be processed, multiple SQL subfiles are generated, it will be described Multiple SQL subfiles obtain SQL file after being integrated.The present invention generates text according to the EXCEL tables of data to be processed File, the second keyword in the first critical field title and the text file in the EXCEL tables of data to be processed Name section corresponds.It can so ensure every data line in the EXCEL tables of data to be processed and to be inserted into pass It is that data line in type database does correspondence, certain cell in every a line in the EXCEL tables of data to be processed The certain field of data and certain the row data that be inserted into relational database corresponds, and has then reached EXCEL number It is associated with one by one according to the data in table with the data being inserted into database.It realizes and EXCEL tables of data is generated into SQL text automatically Part is avoided and artificially writes the cumbersome of SQL file and easily make a fault to improve the working efficiency of Database Engineer Problem.

Secondly, finishing writing program by JAVA, visual display interface is provided, can easily be chosen in display interface The data in the column of part in EXCEL tables of data are convenient and efficient and practical as data to be converted；Finally, generate SQL file is stsndard SQL sentence, can be directly used for executing storage or save.

Above-mentioned Fig. 1 describes the method for the invention that EXCEL tables of data is generated SQL file in detail, below with reference to the 2nd~ 3 figures, respectively to realize it is described by EXCEL tables of data generate SQL file method software service functional module and realization The hardware device framework of the method that EXCEL tables of data is generated to SQL file is introduced.

It should be appreciated that the embodiment is only purposes of discussion, do not limited by this structure in patent claim.

Embodiment two

As shown in fig.2, being the function in the preferred embodiment for the device that EXCEL tables of data is generated SQL file by the present invention It can module map.

In some embodiments, the device 20 that EXCEL tables of data is generated SQL file is run in terminal.It is described It may include multiple functional modules as composed by program code segments by the device 20 that EXCEL tables of data generates SQL file.It is described The program code that EXCEL tables of data is generated to each program segment in the device 20 of SQL file can store the storage in terminal In device, and as performed by least one described processor.

It is described that EXCEL tables of data is generated into function of the device 20 of SQL file according to performed by it in the present embodiment, it can To be divided into multiple functional modules.The functional module may include: the first acquisition module 201, display module 202, judgement Module 203, second obtains module 204, creation module 205, and third obtains module 206, replacement module 207, generation module 208. The so-called module of the present invention, which refers to, a kind of performed by least one processor and can complete a system of fixed function Column count machine program segment, storage is in memory.It in the present embodiment, will be in subsequent embodiment about the function of each module Middle detailed description.

First obtains module 201, needs to generate the EXCEL tables of data to be processed of SQL file for obtaining.

Display module 202 has the storage EXCEL number to be processed for providing display interface on the display interface According to the path input frame of table and the table name input frame of EXCEL tables of data to be processed.The path input frame is for receiving user The path of the EXCEL tables of data to be processed of input.The table name input frame is used to receive the described wait locate of user's input The table name input frame of the EXCEL tables of data of reason.

It is described first obtain module 201, be also used to when detect have input path in the path input frame when, according to institute The path of input obtains the path for storing the EXCEL tables of data to be processed.

Described first obtains module 201, is also used to that EXCEL tables of data ought be had input when detecting in the table name input frame Table name when, the EXCEL tables of data of the corresponding table name is obtained according to the table name of the EXCEL tables of data inputted.

Judgment module 203, for being obtained described in storage according to the path inputted wait locate in the first acquisition module 201 After the path of the EXCEL tables of data of reason, judge whether the quantity of documents of corresponding storage in inputted path is 1；

Described first obtains module 201, is also used to determine corresponding storage in inputted path when the judgment module 203 Quantity of documents be 1 when, using unique file as the EXCEL tables of data to be processed；

Described first obtains module 201, is also used to determine corresponding storage in inputted path when the judgment module 203 Quantity of documents when being multiple, the EXCEL data of the corresponding table name are obtained according to the table name of the EXCEL tables of data inputted Table.

Second obtains module 204, for obtaining the first row data in the EXCEL tables of data to be processed as first Critical field title.

Illustratively, the EXCEL tables of data to be processed is as follows:

name age sex

15 male of Zhang San

12 male of Li Si

5 22 female of king

Preferably, the second acquisition module 204 obtains the work of the first row data in the EXCEL tables of data to be processed Include: for the first critical field title

Content in the EXCEL tables of data to be processed is read in into memory；

Creation module 205 for creating text file according to the first critical field title, and is arranged described first and closes The second critical field title in key field title and the text file corresponds.

Preferably, the creation module 205 creates text file according to the first critical field title, and described first closes Key field title is corresponded with the second critical field title in the text file

Insert into t_user(name,age,sex)values({name},{age},{sex})；

Illustratively, it is assumed that the template file is as follows:

Insert into t_user(name,age,sex)values({name},{age},{sex})；By creating in advance A template file is built, the workload of Database Engineer can be saved, subsequent need to be according to the EXCEL data to be processed The content of table carries out adaptability editor and modification, it is ensured that the second critical field title in text file generated is closed with first Key field title corresponds.

Third obtains module 206, when the generation for receiving user instructs, obtains the EXCEL to be processed by row The first critical field of the first critical field title is corresponded in tables of data.

Specifically, being obtained by row described to be processed when the third acquisition module 206 receives the generation instruction of user The first critical field that the first critical field title is corresponded in EXCEL data includes:

Preferably, it when the third acquisition module 206 receives the generation instruction of user, is obtained by row described to be processed The first critical field that the first critical field title is corresponded in EXCEL tables of data includes:

Judge whether the target critical field is standard key field；

Replacement module 207, for replacing the second critical field name in the text file using first critical field A SQL subfile is generated after claiming corresponding second critical field.

The SQL subfile is substantially .txt file.

Generation module 208, for generating when having traversed the last line data of the Excel tables of data to be processed Multiple SQL subfiles obtain SQL file after being integrated the multiple SQL subfile.

Insert into t_user (name, age, sex) values (' Zhang San ', ' 15 ', ' male ')；

Insert into t_user (name, age, sex) values (' Li Si ', ' 12 ', ' male ')；

In conclusion the device provided in an embodiment of the present invention that EXCEL tables of data is generated to SQL file, acquisition need to give birth to At the EXCEL tables of data to be processed of SQL file；Obtain the first row data conduct in the EXCEL tables of data to be processed First critical field title；Text file is created according to the first critical field title, and first critical field is set The second critical field title in title and the text file corresponds；When receiving the generation instruction of user, obtained by row Take the first critical field that the first critical field title is corresponded in the EXCEL tables of data to be processed；Use described One critical field, which is replaced, generates SQL after corresponding second critical field of the second critical field title in the text file File；When having traversed the last line data of the Excel tables of data to be processed, multiple SQL subfiles are generated, it will be described Multiple SQL subfiles obtain SQL file after being integrated.The present invention generates text according to the EXCEL tables of data to be processed File, the second keyword in the first critical field title and the text file in the EXCEL tables of data to be processed Name section corresponds.It can so ensure every data line in the EXCEL tables of data to be processed and to be inserted into pass It is that data line in type database does correspondence, certain cell in every a line in the EXCEL tables of data to be processed The certain field of data and certain the row data that be inserted into relational database corresponds, and has then reached EXCEL number It is associated with one by one according to the data in table with the data being inserted into database.It realizes and EXCEL tables of data is generated into SQL text automatically Part is avoided and artificially writes the cumbersome of SQL file and easily make a fault to improve the working efficiency of Database Engineer Problem.

Embodiment three

As shown in fig.3, being the structural schematic diagram of terminal provided in an embodiment of the present invention.In present pre-ferred embodiments In, the terminal 3 includes memory 31, at least one processor 32, at least one communication bus 33 and display screen 34.

It will be understood by a person skilled in the art that the structure of the terminal shown in Fig. 3 does not constitute the restriction of the embodiment of the present invention, Either bus topology, be also possible to star structure, the terminal 3 can also include than illustrate it is more or fewer other Hardware perhaps software or different component layouts.

In some embodiments, the terminal 3 includes that one kind can be automatic to carry out according to the instruction for being previously set or storing Numerical value calculates and/or the terminal of information processing, and hardware includes but is not limited to microprocessor, specific integrated circuit, programmable gate Array, digital processing unit, embedded device etc..The terminal 3 may also include customer equipment, and the customer equipment includes but unlimited The electricity of human-computer interaction can be carried out by modes such as keyboard, mouse, remote controler, touch tablet or voice-operated devices with client in any one Sub- product, for example, personal computer, tablet computer, smart phone, digital camera etc..

It should be noted that the terminal 3 is only for example, other electronic products that are existing or being likely to occur from now on such as may be used It is adapted to the present invention, should also be included within protection scope of the present invention, and is incorporated herein by reference.

In some embodiments, the memory 31 is used to store program code and various data, and in the operation of terminal 3 The access realized high speed in the process, be automatically completed program or data.The memory 31 includes read-only memory (Read- Only Memory, ROM), random access memory (Random Access Memory, RAM), programmable read only memory (Programmable Read-Only Memory, PROM), Erasable Programmable Read Only Memory EPROM (Erasable Programmable Read-Only Memory, EPROM), disposable programmable read-only memory (One-time Programmable Read-Only Memory, OTPROM), electronics erasing type can make carbon copies read-only memory (Electrically-Erasable Programmable Read-Only Memory, EEPROM), CD-ROM (Compact Disc Read-Only Memory, CD-ROM) or other disc memories, magnetic disk storage, magnetic tape storage or can For carrying or any other computer-readable medium of storing data.

In some embodiments, at least one described processor 32 can be made of integrated circuit, such as can be by single The integrated circuit of encapsulation is formed, and is also possible to be made of the integrated circuit that multiple identical functions or different function encapsulate, be wrapped Include one or more central processing unit (Central Processing unit, CPU), microprocessor, digital processing chip, The combination etc. of graphics processor and various control chips.At least one described processor 32 is the control core of the terminal 3 (Control Unit) is stored in using all parts of various interfaces and the entire terminal 3 of connection by running or executing Program or module in the memory 31, and the data being stored in the memory 31 are called, to execute terminal 3 Various functions and processing data, such as execute the function that EXCEL tables of data is generated to SQL file.

In some embodiments, at least one communication bus 33 be arranged to realize the memory 31, it is described extremely Connection communication between a few processor 32 and the display screen 34 etc..

In some embodiments, the display screen 34 can be used for showing the information by viewer's input or be supplied to viewing The information of person and the various figure viewer interfaces of terminal 3, these figures viewer interface can by figure, text, icon, Video and any combination thereof are constituted.The display screen 34 may include display panel, optionally, can use liquid crystal display Curtain (Liquid Crystal Display, LCD), Organic Light Emitting Diode (Organic Light-Emitting Diode, ) etc. OLED forms configure display panel.

The display screen 34 can also include touch panel.If the display screen 34 includes touch panel, described Display screen 34 may be implemented as touch screen, to receive the input signal from viewer.Touch panel includes one or more A touch sensor is to sense the gesture on touch, slide, and touch panel.Above-mentioned touch sensor can not only sense touch Or the boundary of sliding action, but also detect duration and pressure relevant to above-mentioned touch or slide.The display Panel and the touch panel can be used as two independent components to realize input and input function, but in some embodiments In, the display panel and the touch panel can be integrated and realize and output and input function.

Although being not shown, the terminal 3 can also include the power supply (such as battery) powered to all parts, it is preferred that Power supply can be logically contiguous by electric power controller and at least one described processor 32, to pass through electric power controller reality The functions such as now management charging, electric discharge and power managed.Power supply can also include one or more direct current or alternating current The random components such as source, recharging device, power failure detection circuit, power adapter or inverter, power supply status indicator. The terminal 3 can also include multiple sensors, bluetooth module, Wi-Fi module etc., and details are not described herein.

The above-mentioned integrated unit realized in the form of software function module, can store and computer-readable deposit at one In storage media.Above-mentioned software function module is stored in a storage medium, including some instructions are used so that a computer Equipment (can be personal computer, terminal or the network equipment etc.) or processor (processor) execute each reality of the present invention Apply the part of the method.

In a further embodiment, in conjunction with Fig. 1, the operation of the terminal 3 is can be performed at least one described processor 32 Device and the types of applications program of installation (EXCEL tables of data is generated to the device 20 of SQL file as mentioned), program code Deng for example, above-mentioned modules.

Program code is stored in the memory 31, and at least one described processor 32 can call the memory 31 The program code of middle storage is to execute relevant function.For example, modules described in Fig. 2 are stored in the memory 31 In program code, and as performed by least one described processor 32, to realize the function of the modules to reach EXCEL tables of data is generated to the purpose of SQL file.

In one embodiment of the invention, the memory 31 stores multiple instruction, the multiple instruction by it is described extremely Few performed in-out-storehouse management to realize cargo of a processor 32.

Specifically, at least one described processor 32 can refer to the corresponding implementation of Fig. 1 to the concrete methods of realizing of above-metioned instruction The description of correlation step in example, this will not be repeated here.

In several embodiments provided by the present invention, it should be understood that disclosed device, device and method can be with It realizes by another way.For example, the apparatus embodiments described above are merely exemplary, for example, the module It divides, only a kind of logical function partition, there may be another division manner in actual implementation.

The module as illustrated by the separation member may or may not be physically separated, aobvious as module The component shown may or may not be physical unit, it can and it is in one place, or may be distributed over multiple In network unit.Some or all of the modules therein can be selected to realize the mesh of this embodiment scheme according to the actual needs 's.

It, can also be in addition, each functional module in each embodiment of the present invention can integrate in one processing unit It is that each unit physically exists alone, can also be integrated in one unit with two or more units.Above-mentioned integrated list Member both can take the form of hardware realization, can also realize in the form of hardware adds software function module.

It is obvious to a person skilled in the art that invention is not limited to the details of the above exemplary embodiments, Er Qie In the case where without departing substantially from spirit or essential attributes of the invention, the present invention can be realized in other specific forms.Therefore, no matter From the point of view of which point, the present embodiments are to be considered as illustrative and not restrictive, and the scope of the present invention is by appended power Benefit requires rather than above description limits, it is intended that all by what is fallen within the meaning and scope of the equivalent elements of the claims Variation is included in the present invention.Any reference signs in the claims should not be construed as limiting the involved claims.This Outside, it is clear that one word of " comprising " is not excluded for other units or, odd number is not excluded for plural number.The multiple units stated in device claim Or device can also be implemented through software or hardware by a unit or device.The first, the second equal words are used to indicate name Claim, and does not indicate any particular order.

Finally it should be noted that the above examples are only used to illustrate the technical scheme of the present invention and are not limiting, although reference Preferred embodiment describes the invention in detail, those skilled in the art should understand that, it can be to of the invention Technical solution is modified or equivalent replacement, without departing from the spirit and scope of the technical solution of the present invention.

Claims

1. a kind of method that EXCEL tables of data is generated SQL file, is applied in terminal, which is characterized in that the method packet It includes:

Text file is created according to the first critical field title, and the first critical field title and the text are set The second critical field title in file corresponds；

When receiving the generation instruction of user, is obtained by row and correspond to first key in the EXCEL tables of data to be processed First critical field of field name；

Corresponding second critical field of the second critical field title in the text file is replaced using first critical field A SQL subfile is generated afterwards；And

When having traversed the last line data of the Excel tables of data to be processed, multiple SQL subfiles are generated, it will be described Multiple SQL subfiles obtain SQL file after being integrated.

2. the method as described in claim 1, which is characterized in that when the generation instruction for receiving user, obtain institute by row It states and corresponds to the first critical field of the first critical field title in EXCEL tables of data to be processed and include:

The target critical field that the first critical field title is corresponded in the EXCEL tables of data to be processed is obtained by row；

Judge whether the target critical field is standard key field；

When the target critical field is standard key field, using the target critical field as first keyword Section；

When the target critical field is not standard key field, according to the mapping between preset keyword section and criteria field Relationship, the target critical field that will acquire are converted to standard key field as first critical field.

3. the method as described in claim 1, which is characterized in that the method also includes:

Display interface is provided, there is the path input frame, described for storing the EXCEL tables of data to be processed on the display interface The table name input frame and extraction columns input frame of EXCEL tables of data to be processed；

When detect have input path in the path input frame when, it is described to be processed that storage is obtained according to the path that is inputted The path of EXCEL tables of data；

When detect have input the table name of EXCEL tables of data in the table name input frame when, according to the EXCEL tables of data inputted Table name obtain the EXCEL tables of data of the corresponding table name；

When detect have input number in the extraction columns input frame when, the number inputted is determined as described to be processed Columns to be extracted in EXCEL tables of data obtains corresponding in the EXCEL tables of data to be processed according to the columns to be extracted Data.

4. method as claimed in claim 3, which is characterized in that obtained described in storage according to the path inputted wait locate described After the path of the EXCEL tables of data of reason, the method can also include:

When the quantity of documents for determining corresponding storage in inputted path is 1, using unique file as described to be processed EXCEL tables of data；

When the quantity of documents for determining corresponding storage in inputted path is multiple, according to the EXCEL tables of data inputted Table name obtains the EXCEL tables of data of the corresponding table name.

5. the method as described in claim 1, which is characterized in that the obtained in the EXCEL tables of data to be processed Data line includes: as the first critical field title

Content in the EXCEL tables of data to be processed is read in into memory；

Using the data in the script file as first critical field.

6. the method as described in claim 1, which is characterized in that described to create text text according to the first critical field title Part, the first critical field title include: with the second critical field title one-to-one correspondence in the text file

Text file is created according to the first critical field title and default template file, and the critical field title is set It is corresponded with the second critical field title in the text file, the default template file is previously according to SQL file One template file of format creation.

7. the method as described in any one of claim 1 to 6, which is characterized in that the generation instruction for receiving user When, include: by the first critical field for corresponding to the first critical field title in the row acquisition EXCEL data to be processed

Since the second row of the EXCEL tables of data to be processed, obtained in EXCEL tables of data to be processed line by line in every row All column or every row in data in the part column that are selected as the first critical field, until described to be processed The last line of EXCEL tables of data.

8. a kind of device that EXCEL tables of data is generated to SQL file, runs in terminal, which is characterized in that described device packet It includes:

Second obtains module, for obtaining the first row data in the EXCEL tables of data to be processed as the first keyword Name section；

For creating text file according to the first critical field title, and first critical field is arranged in creation module The second critical field title in title and the text file corresponds；

Third obtains module, when the generation for receiving user instructs, obtains in the EXCEL tables of data to be processed by row First critical field of the corresponding first critical field title；

Replacement module, it is corresponding for replacing the second critical field title in the text file using first critical field A SQL subfile is generated after second critical field；And

Generation module, for generating multiple SQL when having traversed the last line data of the Excel tables of data to be processed Subfile obtains SQL file after being integrated the multiple SQL subfile.

9. a kind of terminal, which is characterized in that the terminal includes processor, and the processor is used to execute to store in memory The method that EXCEL tables of data is generated into SQL file as described in any one of claim 1 to 7 is realized when computer program.

10. a kind of computer readable storage medium, is stored thereon with computer program, which is characterized in that the computer program The method that EXCEL tables of data is generated into SQL file as described in any one of claim 1 to 7 is realized when being executed by processor.