CN115934720A - Method and system for uploading header data of variable length tables to mysql same table - Google Patents
Method and system for uploading header data of variable length tables to mysql same table Download PDFInfo
- Publication number
- CN115934720A CN115934720A CN202211596284.6A CN202211596284A CN115934720A CN 115934720 A CN115934720 A CN 115934720A CN 202211596284 A CN202211596284 A CN 202211596284A CN 115934720 A CN115934720 A CN 115934720A
- Authority
- CN
- China
- Prior art keywords
- data
- excel
- row
- column
- storing
- 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
Images
Classifications
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a method and a system for uploading data of a fixed-length table header to mysql and the same table, wherein the method comprises the following steps: s1, designing a database table for storing excel table data and a header; s2, uploading excel table data to an application, and reading a sheet object of the excel; s3, reading the row number, column number and header of all data in the sheet object, storing all headers in a mysql database table, and assigning the same table number to each record; s4, reading the data and the metadata of the unit cell with the data in each cell in the sheet object by using a java' S poi tool according to the row number and the column number acquired in the step S2; and S5, converting the data into sub-queries through sql column-to-row conversion according to the recorded header information, the table number, the column number and the row number, and then querying the sub-queries by using the common sql. The data of the excel table with the header of the indefinite length can be saved in the same mysql database table, and the saved data can be inquired through sql like a common table.
Description
Technical Field
The invention relates to the technical field of computers and big data processing, in particular to a method and a system for uploading excel data with an indefinite length header to mysql in the same table based on java.
Background
When uploading excel data to the mysql database, a table corresponding to the excel header needs to be established in the mysql database, and when establishing the table, metadata of the table to be uploaded needs to be known in advance to determine the data type and length of each field in the table. And reading a header and data of the data in the excel through the corresponding java code, then storing the data in the table corresponding to the database, and finally inquiring and using the stored data through the sql.
However, such approaches have drawbacks: on the first hand, a mysql database table is established according to the header of the excel, a corresponding database table can be established in the mysql database only by knowing the header and the data type of the excel in advance, and unknown excel data cannot be uploaded and stored; in the second aspect, a corresponding database table needs to be newly established for each excel table header, corresponding new java codes are developed to read data, and finally the data are stored in the corresponding database table.
Disclosure of Invention
Aiming at the problems in the prior art, the invention aims to provide a method and a system, which can save data of an excel table with an indefinite length header into the same mysql database table and can query the saved data through sql as a common table is looked up.
In order to achieve the above object, the present invention provides a method for uploading header data of an indefinite length to a mysql and the same table, wherein the method comprises the following steps:
step S1, designing a database table for storing excel table data and a header;
s2, uploading excel table data to an application, and reading a sheet object of excel by using a poi tool class of java;
s3, reading the row number, the column number and the header of all data in the sheet object by using a java' S poi tool, storing all headers in a mysql database table, and assigning the same table number to each record;
s4, reading data and metadata of a unit cell with data in each cell in the sheet object by using a java' S poi tool according to the row number and the column number acquired in the step S2, storing the data, the column number, the row number and the metadata of each unit cell into a unified mysql database table after reading, and endowing each record of the same excel with the table number in the step S2;
and S5, converting the data into sub-queries through sql column-to-row conversion according to the recorded header information, the table number, the column number and the row number, and then querying the sub-queries by using the common sql.
Further, in step S1, the database table is designed to:
the method comprises the steps of storing a database table of excel table data, wherein one copy of excel cell with data corresponds to one line of data;
and storing a database table of the header, wherein one excel corresponds to one line of data.
Further, the fields of the database table storing the excel table data are as follows:
id, a primary key of the current data table, which is used for quickly searching table data;
the dataset _ id is used for storing the table number of the uploaded data of the excel, and the uploaded data of the same excel have the same table number;
field for storing column number of excel cell;
row _ number, which is used for storing the line number of the excel cell;
value for storing the value of the excel cell.
Further, the fields of the database table that store the headers are as follows:
id, the primary key of the current data table, which is used for quickly searching table data;
the dataset _ id is used for storing the table number of the uploaded data of the excel, and the uploaded data of the same excel have the same table number;
field for storing column number of excel cell;
the field _ name is used for storing an original header of the excel column;
column _ type for storing data type of excel column.
Further, in step S2, the uploaded content of excel is read by using a poi tool class of java, and a sheet object is obtained.
Further, in step S3, according to the sheet object obtained in step 2, the total number of lines of the sheet object having data is obtained by using a sheet
getRow (finalFisrtRowNumber), getHypersicalNumberOfCells () is used to obtain the total column number of the sheet object and getRow (finalFirstRowNumber), getFirstCellNum () is used to obtain the first column number firstColNumber of all cells with data in the sheet object. And traversing the first row of all the cells with data according to the first row finalFisrtRowNumber and the total column number finalTotalColumns to acquire all the header information, endowing the header information of each list with a uniform table number, and finally storing the header information into a mysql database dataset _ table _ field table.
Further, in step S4, according to the row number, the total row number, and the total column number of the first row acquired in step S3, all data are read from the +1 row of the first row in a traversal manner, and each piece of data is assigned with a table number and then stored in the database; the stored information includes table number, column number, row number and data.
Further, in step S5, all header information acquired records acquire all corresponding column numbers and table numbers dataseitid, and data columns with the table numbers dataseitid recorded in the database table upload _ file _ data are diverted using mysql database functions max and case where and then grouped into one subquery according to the row number row _ number.
Further, the specific conversion method is as follows:
the header information acquires all corresponding column numbers;
acquiring a table number;
and splicing the columns and the rows to form the sql.
On the other hand, the system is used for uploading data of the variable length header to the mysql same table and realizing the method.
The key point of the invention is that the excel data of any header is stored in the same database table, and then the data is converted into a sub-query in a column-to-row mode to query the data through the common sql. Therefore, the header of the excel is not required to be known in advance, the data of the excel table with the header of the indefinite length can be stored in the same mysql database table without newly building a corresponding database table for each kind of excel header, and the stored data can be inquired through sql like a common table.
Drawings
FIG. 1 is a flow chart illustrating uploading excel data with variable length header to mysql in accordance with an embodiment of the present invention;
FIG. 2 illustrates a table style used in accordance with an embodiment of the present invention;
FIG. 3 illustrates a conversion flow in accordance with an embodiment of the present invention;
FIG. 4 shows how the data of a column generates a schematic according to an embodiment of the invention.
Detailed Description
The technical solutions of the present invention will be described clearly and completely with reference to the accompanying drawings, and it is to be understood that the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.
In the description of the present invention, it should be noted that the terms "center", "upper", "lower", "left", "right", "vertical", "horizontal", "inner", "outer", etc., indicate orientations or positional relationships based on the orientations or positional relationships shown in the drawings, and are only for convenience of description and simplicity of description, but do not indicate or imply that the device or element being referred to must have a particular orientation, be constructed and operated in a particular orientation, and thus, should not be construed as limiting the present invention. Furthermore, the terms "first," "second," and "third" are used for descriptive purposes only and are not to be construed as indicating or implying relative importance.
In the description of the present invention, it should be noted that, unless otherwise explicitly specified or limited, the terms "mounted," "connected," and "connected" are to be construed broadly and may be, for example, fixedly connected, detachably connected, or integrally connected; can be mechanically or electrically connected; they may be connected directly or indirectly through intervening media, or they may be interconnected between two elements. The specific meanings of the above terms in the present invention can be understood according to specific situations by those of ordinary skill in the art.
The following describes in detail a specific embodiment of the present invention with reference to fig. 1 to 4. It should be understood that the detailed description and specific examples, while indicating the preferred embodiment of the invention, are given by way of illustration and explanation only, not limitation.
The invention concept of the method and the system for uploading excel data with a non-fixed length header to the mysql same table based on java in the embodiment of the invention is as follows: when the excel data are uploaded, the metadata (line number, column data format and header data) of the excel are read through java codes, then the table data are read from the second line of the excel table, the line number, the column number and the data of each grid data are stored, and the data of one table is endowed with the same table number. When in query, the table numbers are transmitted, and the data with all the table numbers as the transmitted table numbers are queried in a column-to-row mode to be used as a sub-query. And finally, making sql query on the basis of the sub-query.
As shown in fig. 1, the basic flow of the method for uploading header data of an indefinite length table to mysql in the same table is as follows:
step S1, designing a database table for storing excel table data and a header;
s2, uploading excel table data to an application, and reading a sheet object of excel by using a poi tool class of java;
step S3, reading the row number, the column number and the header of all data in the sheet object by utilizing a java poi tool, storing all headers in a mysql database table (hereinafter collectively referred to as dataset _ table _ field), and assigning the same table number (hereinafter collectively referred to as dataset Id) to each record;
s4, reading data and metadata of a cell with data in each cell in the sheet object by using a java' S poi tool according to the row number and the column number acquired in the step 2, storing the data, the column number, the row number and the metadata of each cell into a unified mysql database table (hereinafter, collectively referred to as upload _ file _ data), and endowing each record of the same excel with the dataseitId in the step S2;
and S5, converting the data into sub-queries through sql column-to-row conversion according to the recorded header information, the table number, the column number and the row number, and then querying the sub-queries by using the common sql.
Specifically, in step S1, the following table is taken as an example, and the database table is designed as follows:
excel data:
| A | | C | D | ||
| 1 | name (I) | Age(s) | Date of | Sex | |
| 2 | |
18 | 1996/12/3 | For |
|
| 3 | |
21 | 1998/5/3 | |
|
| 4 | |
23 | 1993/2/3 | For male |
Data after insertion into the database:
| id | dataset_id | | row_number | value | |
| 1 | 1 | A | 1 | |
|
| 2 | 1 | A | 2 | |
|
| 3 | 1 | A | 3 | |
|
| 4 | 1 | |
1 | 18 | |
| 5 | 1 | |
2 | 21 | |
| 6 | 1 | |
3 | 23 | |
| 7 | 1 | |
1 | 1996/12/3 | |
| 8 | 1 | |
2 | 1998/5/3 | |
| 9 | 1 | |
3 | 1993/2/3 | |
| 10 | 1 | |
1 | for |
|
| 11 | 1 | |
2 | |
|
| 12 | 1 | |
3 | For male |
A database table (upload _ file _ data) for storing excel table data, wherein one copy of excel cell with data corresponds to one row of data, and the fields are as follows:
id, the primary key of the current data table, for fast searching the table data;
the dataset _idis used for storing the table number of the uploaded data of the excel, and the uploaded data of the same excel has the same table number;
field for storing the column number of excel cell (a, B, C, etc.);
row _numberfor storing the row number of excel cell (1, 2,3, etc.);
value for storing values of excel cells.
Table (dataset table field) storing header, one excel corresponds to one row of data, and the fields are as follows:
id, the primary key of the current data table, for fast searching the table data;
the database \\ id is used for storing the table number of the uploaded data of the excel, and the data uploaded by the same excel has the same table number;
field for storing the column number of the excel cell (a, B, C, etc.);
field name, for storing the original header (name, age, etc.) of the excel column;
column _typefor storing data type (numeric, text, time, etc.) of excel column.
Specifically, in step S2, the uploaded excel content is read by using a poi tool class of java, and a sheet object is obtained. Wherein, poi: apache POI is an open source function library of the Apache software foundation, and provides API for Java programs to read and write Microsoft Office format archives. In step 2, the pseudo code is as follows:
specifically, in step S3, the total number of lines finalTotalRows of the sheet object having data is obtained by the gethysalcanberofrows () method according to the sheet object obtained in step S2, the line number finaltotarrows of the first line of all the cells having data in the sheet object is obtained by the sheet
sheet, getRow (finalFisrtRowNumberOfCells). To obtain the total column number finaltotalColumns of the sheet object and
get row, getFirstCellNum () to get the first column number firstColNumber of all cells with data in the sheet object. And traversing the first row of all the cells with data according to the first row finalFisrtRowNumber and the total column number finalTotalColumns to acquire all the header information, endowing the header information of each list with a uniform table number, and finally storing the header information into the mysql database table dataset _ table _ field.
The pseudo code is as follows:
as shown in fig. 2, specifically, in step S4, all data are read from the finalfisrtrow number +1 row in a traversal manner according to the finalfisrtrow number, finalfisrtcolumn number, finalTotalRows, finalTotalColumns acquired in step S3, and each piece of data is assigned a table number and then stored in the database. The stored information is the table number dataseitid, column number, row number and data.
The pseudo code is as follows:
specifically, in step S5, all header information and all corresponding column numbers recorded and table number dataseitid are acquired, mysql database functions max and case where are used to shift rows of data columns with the table number dataseitid recorded in the upload _ file _ data, and then grouped into a sub-query according to the row number row _ number. The annotations are as follows:
mysql function max: for returning the maximum value of the set of values of the expression.
case where: for computing a conditional list and returning one of a plurality of possible resulting expressions.
And a group by key used for grouping the data and counting the grouped information.
where the criteria used to specify the selection.
Specific conversion methods are exemplified by:
| id | dataset_id | | row_number | value | |
| 1 | 1 | A | 1 | |
|
| 2 | 1 | A | 2 | |
|
| 3 | 1 | A | 3 | |
|
| 4 | 1 | |
1 | 18 | |
| 5 | 1 | |
2 | 21 | |
| 6 | 1 | |
3 | 23 | |
| 7 | 1 | |
1 | 1996/12/3 | |
| 8 | 1 | |
2 | 1998/5/3 | |
| 9 | 1 | |
3 | 1993/2/3 | |
| 10 | 1 | |
1 | for |
|
| 11 | 1 | |
2 | |
|
| 12 | 1 | |
3 | For male |
In the database, stored data are shown in the figure, and data with dataset _ id =1 needs to be converted into data
This form can query data as a normal table with sql queries.
Assuming that the header information acquires all corresponding column numbers A, B, C and D;
the table number dataseitid is 1;
the specific flow of splicing the columns and the rows to obtain the sql is as follows:
t1. Data after screening using where dataset _ id = 1;
t2. Group by row _ number is used to group the data.
As shown in fig. 3 and 4, taking column id and a as examples, how data of row _ number (id is only an alias) column, column a, is generated will be described.
The role of case field 'a' the value is to return the value of the value field of a line as the final result when the value of the field of this line is 'a'.
Since max is used to screen out the line r where the value of the data with the same row _ number is the largest according to the row _ number grouping, when the row _ number is equal to 1, the data line with the field value ' a ' has only one line, and the data line with id =1, so that the row _ number returns 1 and the value returns three '. The analogy obtains the value when row _ number is 2,3. And analogically obtain the values of columns B, C and D.
The specific codes are as follows:
AS is used to return that the column name is displayed AS A when the column display name, AS A, is displayed in the data.
The key point of the invention is that the excel data of any header is stored in the same database table, and then the data is converted into a sub-query in a column-to-row mode to query the data through the common sql.
In addition, the invention also provides a system for uploading header data of an indefinite length table to the mysql and the same table, and the system is used for realizing the method.
The invention has the advantages that: the header of the excel is not required to be known in advance, and a corresponding database table is not required to be newly established for each excel header.
Any process or method descriptions in flow charts of the present invention or otherwise described herein may be understood as representing modules, segments, or portions of code which include one or more executable instructions for implementing specific logical functions or steps of a process, may be embodied in any computer-readable medium for use by an instruction execution system, apparatus, or device, such computer-readable medium may be any medium that can contain, store, communicate, propagate, or transport the program for use by the execution system, apparatus, or device. Including read-only memory, magnetic or optical disks, and the like.
In the description herein, references to the description of the terms "embodiment," "example," etc., mean that a particular feature, structure, material, or characteristic described in connection with the embodiment or example is included in at least one embodiment or example of the invention. In this specification, the schematic representations of the terms used above are not necessarily intended to refer to the same embodiment or example. Furthermore, various embodiments or examples described in this specification and features thereof may be combined or combined by those skilled in the art without contradiction.
Although embodiments of the present invention have been shown and described, it is understood that the above embodiments are illustrative and not to be construed as limiting the present invention, and that modifications, alterations, substitutions, and alterations may be made to the above embodiments by those of ordinary skill in the art without departing from the scope of the present invention.
Claims (10)
1. The method for uploading the header data of the variable length table to the mysql same table is characterized by comprising the following steps of:
step S1, designing a database table for storing excel table data and a header;
s2, uploading excel table data to an application, and reading a sheet object of excel by using a poi tool class of java;
s3, reading the row number, the column number and the header of all data in the sheet object by using a java' S poi tool, storing all headers in a mysql database table, and assigning the same table number to each record;
s4, reading the data and the metadata of the cell with data in each cell in the sheet object by using a java' S poi tool according to the row number and the column number acquired in the step S2, storing the data, the column number, the row number and the metadata of each cell into a unified mysql database table after reading, and endowing each record of the same excel with the table number in the step S2;
and S5, converting the data into sub-queries through sql column-to-row conversion according to the recorded header information, the table number, the column number and the row number, and then querying the sub-queries by using the sql.
2. The method of uploading variable length header data to mysql same table as in claim 1, wherein in step S1, the database table is designed to:
storing a database table of excel table data, wherein one copy of excel cell corresponds to one line of data;
and storing a database table of the header, wherein one excel corresponds to one line of data.
3. The method for uploading data with indefinite length header to mysql and the same table as in claim 2, wherein the fields of the database table storing the excel table data are as follows:
id, the primary key of the current data table, which is used for quickly searching table data;
the dataset _ id is used for storing the table number of the uploaded data of the excel, and the uploaded data of the same excel have the same table number;
field for storing column number of excel cell;
row _ number, which is used for storing the line number of the excel cell;
value for storing the value of excel cell.
4. The method for uploading data of indefinite length headers to mysql same table according to claim 2 or 3, wherein the fields of the database table storing the headers are as follows:
id, the primary key of the current data table, which is used for quickly searching table data;
the dataset _ id is used for storing the table number of the uploaded data of the excel, and the uploaded data of the same excel have the same table number;
field for storing column number of excel cell;
the field _ name is used for storing an original header of the excel column;
column _ type for storing data type of excel column.
5. The method for uploading data with an indefinite length header to the mysql and the same table as the claim 4, wherein in the step S2, the content of the uploaded excel is read by utilizing the poi tool class of java to obtain the sheet object.
6. The method of claim 5, wherein in step S3, based on the sheet object obtained in step S2, the method of sheet
getRow (finalFisrtRowNumber), getHypersicalNumberOfCells () to obtain the total column number of the sheet object and getRow (finalFirstRowNumber), getFirstCellNum () to obtain the first column number firstColNumber of all cells with data in the sheet object; and traversing the first row of all the cells with data according to the first row finalFisrtRowNumber and the total column number finalTotalColumns to acquire all the header information, endowing the header information of each list with a uniform table number, and finally storing the header information into a mysql database dataset _ table _ field table.
7. The method for uploading data of variable length headers to mysql in the same table as in claim 6, wherein in step S4, all data are read from the first row +1 in a traversal manner according to the row number, the total row number and the total column number of the first row acquired in step S3, and each piece of data is assigned with a table number and then stored in the database; the stored information includes table number, column number, row number and data.
8. The method of claim 7, wherein in step S5, all header information of the obtained records obtain all corresponding column numbers and table numbers dataseld, and the mysql database function max and casewhere are used to forward the data column with the table number dataseld recorded in the database table upload _ file _ data to row, and then the data column is grouped into a sub-query according to the row number row _ number.
9. The method of claim 8, wherein the specific transformation method is as follows:
the header information acquires all corresponding column numbers;
acquiring a table number;
and splicing the sql from the columns to the rows.
10. A system for uploading header data of variable length to mysql and the same table, the system being adapted to implement the method according to any of claims 1-9.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211596284.6A CN115934720A (en) | 2022-12-13 | 2022-12-13 | Method and system for uploading header data of variable length tables to mysql same table |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211596284.6A CN115934720A (en) | 2022-12-13 | 2022-12-13 | Method and system for uploading header data of variable length tables to mysql same table |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN115934720A true CN115934720A (en) | 2023-04-07 |
Family
ID=86698781
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202211596284.6A Pending CN115934720A (en) | 2022-12-13 | 2022-12-13 | Method and system for uploading header data of variable length tables to mysql same table |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN115934720A (en) |
-
2022
- 2022-12-13 CN CN202211596284.6A patent/CN115934720A/en active Pending
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US9805079B2 (en) | Executing constant time relational queries against structured and semi-structured data | |
| CN110413611B (en) | Data storage and query method and device | |
| US9047330B2 (en) | Index compression in databases | |
| US20150026189A1 (en) | Index structure for a relational database table | |
| CN101036141B (en) | A database management system with persistent, user- accessible bitmap values | |
| CN108369587B (en) | Creating tables for exchange | |
| US9411840B2 (en) | Scalable data structures | |
| WO2017074174A1 (en) | A system and method for processing big data using electronic document and electronic file-based system that operates on rdbms | |
| TW202032386A (en) | Data storage apparatus, translation apparatus, and database access method | |
| US8880463B2 (en) | Standardized framework for reporting archived legacy system data | |
| CN1152365A (en) | Method for storing and retrieving data and memory arrangement | |
| CN111400323B (en) | Data retrieval method, system, equipment and storage medium | |
| US20120096054A1 (en) | Reading rows from memory prior to reading rows from secondary storage | |
| US11468031B1 (en) | Methods and apparatus for efficiently scaling real-time indexing | |
| CN109830285B (en) | Medical image file processing method and device | |
| JP5563529B2 (en) | Information recording apparatus, information recording method, and program | |
| CN110990406A (en) | Fuzzy query method, device, equipment and medium | |
| CN111984745B (en) | Database field dynamic expansion method, device, equipment and storage medium | |
| KR100818742B1 (en) | Search methode using word position data | |
| CN109885570A (en) | A kind of multi-Dimensional Range querying method of secondary index combination hash table | |
| CN110389953B (en) | Data storage method, storage medium, storage device and server based on compression map | |
| CN111984649A (en) | Data index searching method and device and related equipment | |
| CN115934720A (en) | Method and system for uploading header data of variable length tables to mysql same table | |
| CN111190895A (en) | Method and device for organizing columnar storage data and storage medium | |
| CN110825747B (en) | Information access method, device and medium |
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 |