CN109783779B - Tool for importing, exporting and converting data EXCEL - Google Patents
Tool for importing, exporting and converting data EXCEL Download PDFInfo
- Publication number
- CN109783779B CN109783779B CN201811612204.5A CN201811612204A CN109783779B CN 109783779 B CN109783779 B CN 109783779B CN 201811612204 A CN201811612204 A CN 201811612204A CN 109783779 B CN109783779 B CN 109783779B
- Authority
- CN
- China
- Prior art keywords
- configuration
- excel
- data
- old
- new
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
- 230000008676 import Effects 0.000 claims abstract description 14
- 238000000034 method Methods 0.000 claims abstract description 12
- 230000008569 process Effects 0.000 claims abstract description 8
- 238000006243 chemical reaction Methods 0.000 claims description 33
- 238000012546 transfer Methods 0.000 abstract description 3
- 230000005012 migration Effects 0.000 description 6
- 238000013508 migration Methods 0.000 description 6
- 230000008859 change Effects 0.000 description 4
- 230000008901 benefit Effects 0.000 description 3
- 238000012986 modification Methods 0.000 description 2
- 230000004048 modification Effects 0.000 description 2
- 206010033799 Paralysis Diseases 0.000 description 1
- 230000004075 alteration Effects 0.000 description 1
- 238000010586 diagram Methods 0.000 description 1
- 230000000694 effects Effects 0.000 description 1
- 238000012423 maintenance Methods 0.000 description 1
Abstract
The invention provides a tool for importing, exporting and converting data EXCEL, which comprises the following steps: an import module and an export module; the import module is used for carrying out input library configuration, table configuration and data dictionary configuration on an Excel template which needs to be imported by the new system according to the Excel template exported by the old system; the export module is used for carrying out export library configuration according to names and sequences of Excel specific sheets to be imported by the new system, carrying out Sheet item configuration on the Excel template to be imported by the new system in the Excel template of the old system, and running a program. The tool for importing, exporting and converting the data EXCEL provided by the invention can convert the EXCEL data by only configuring the EXCEL template in the EXCEL data transfer process, does not need code writing, has no software technical background requirement on operators, is simple for changing in a system, and greatly simplifies the operation of a user.
Description
Technical Field
The invention relates to the technical field of computers, in particular to a tool for importing, exporting and converting data EXCEL.
Background
More and more enterprises face the problem of replacement or expansion of the ERP system, and maintenance of effective data in the existing ERP system has important influence on switching and expansion of the ERP system and normal operation of a new system. The data migration is slightly careless, so that the new system cannot normally operate, and the new ERP system can be slow to operate and even paralyzed due to the fact that too much junk data is migrated. For the historical data in the ERP replacement process, not all data can be smoothly transferred into a new ERP system, and especially for the replacement of ERP systems of different manufacturers, the utilization rate of the original data is very low. The traditional method is manual input, so that the working efficiency is low, the personnel cost is high, the data migration and data arrangement of a plurality of tables is very difficult, the related data volume is large, and the effect is not ideal.
Disclosure of Invention
In view of this, the present invention provides a tool for solving the replacement of new and old ERP systems, excel import and export.
The invention provides a tool for importing, exporting and converting data EXCEL, which comprises the following steps: an import module and an export module; wherein,
the import module is used for carrying out input library configuration, table configuration and data dictionary configuration on an Excel template which needs to be imported by the new system according to the Excel template exported by the old system;
the export module is used for carrying out export library configuration according to names and sequences of Excel specific sheets to be imported by the new system, carrying out Sheet item configuration on the Excel template to be imported by the new system in the Excel template of the old system, and running a program.
Further, the data EXCEL is imported into a tool for export conversion, and the import module includes: an input library configuration unit, an input table configuration unit and a data dictionary configuration unit; wherein,
the input library configuration unit is used for carrying out input library configuration of an output table according to an Excel template derived by the old system;
the input table configuration unit is used for carrying out input table configuration on each Sheet table to be imported into the new system in the old system according to the input library configuration;
the data dictionary configuration unit is used for configuring the data dictionary in the old data system according to the input table configuration.
Further, the data EXCEL is imported into a tool for export conversion, and the export module includes: an output library configuration unit, a Sheet item configuration unit, and an operation unit; wherein,
the output library configuration unit is used for carrying out output library configuration according to the names and the sequences of specific sheets in Excel to be imported by the new system;
the Sheet item configuration unit is used for configuring Sheet items to be imported into a new system Excel template in the old system Excel template according to the new and old Excel templates, and directly inputting Sheet numbers and column names corresponding to the old system in the new system Excel template;
the running unit is used for directly running a program after the Sheet item configuration is completed so as to complete importing of the old system Excel data into the new system Excel template.
Further, the data EXCEL is imported into a tool for export conversion, and the configuration content of the input library configuration unit includes: report coding, sheet number, number of read sheets, number of start lines, number of header lines, and number of header lines.
Further, the data EXCEL is imported into a tool for export conversion, and the configuration content of the input table configuration unit includes: keywords, column names, column types, and dictionary names.
Further, the data EXCEL is imported into a tool for export conversion, and the configuration content of the data dictionary configuration unit includes: old system data dictionary configuration, new system data dictionary configuration, and conversion of new and old system data dictionaries.
Further, the data EXCEL is imported into a tool for export conversion, and the configuration content of the output library configuration unit includes: report encoding, export file type, export file name, sheet name, whether template export, export template address, and export Sheet number.
Further, the data EXCEL is imported into a tool for export conversion, and the conversion of the new and old system data dictionary adopts one or more modes of direct conversion, program conversion, code comparison, constant conversion and cross-table conversion.
Further, the data EXCEL is imported into a tool for export conversion, and during the conversion process of the new and old system data dictionary, no conversion is performed on the fields which are in the old table but are not in the new table.
Compared with the prior art, the method has the advantages that the tool for importing, exporting and converting the data EXCEL provided by the invention can convert the EXCEL data by only configuring the EXCEL template in the EXCEL data transfer process, code writing is not needed, no software technical background requirement is required for operators, and the method is simple for changing in a system, so that the operation of a user is greatly simplified.
In particular, the data EXCEL import export conversion tool provided by the invention is used for referencing the original system by configuring the new and old system EXCEL templates based on the new system, and simultaneously, the data dictionary of the original system is corresponding to the corresponding system data dictionary, and in the comparison process, the data is subjected to the principle of data moving, and the data is not processed as much as possible, so that the integrity of data conversion between the new and old systems is effectively ensured, and the purpose of data migration is achieved.
Drawings
Various other advantages and benefits will become apparent to those of ordinary skill in the art upon reading the following detailed description of the preferred embodiments. The drawings are only for purposes of illustrating the preferred embodiments and are not to be construed as limiting the invention. Also, like reference numerals are used to designate like parts throughout the figures. In the drawings:
fig. 1 is a block diagram of a tool for importing, exporting and converting data EXCEL according to an embodiment of the present invention.
Detailed Description
Preferred embodiments of the present invention are described below with reference to the accompanying drawings. It should be understood by those skilled in the art that these embodiments are merely for explaining the technical principles of the present invention, and are not intended to limit the scope of the present invention.
Referring to fig. 1, a tool for importing and exporting conversion of data EXCEL according to an embodiment of the present invention includes: an import module and an export module; the import module is used for carrying out input library configuration, table configuration and data dictionary configuration on an Excel template which needs to be imported by the new system according to the Excel template exported by the old system; the export module is used for carrying out export library configuration according to names and sequences of Excel specific sheets to be imported by the new system, carrying out Sheet item configuration on the Excel template to be imported by the new system in the Excel template of the old system, and running a program.
Specifically, the import module includes: an input library configuration unit, an input table configuration unit and a data dictionary configuration unit; the input library configuration unit is used for carrying out input library configuration of an output table according to an Excel template derived by the old system; the input table configuration unit is used for carrying out input table configuration on each Sheet table to be imported into the new system in the old system according to the input library configuration; the data dictionary configuration unit is used for configuring the data dictionary in the old data system according to the input table configuration.
In this embodiment, the importing module includes: an input library configuration unit, an input table configuration unit and a data dictionary configuration unit; the input library configuration unit configures a Sheet name table to be imported, namely input library configuration, wherein the input library configuration comprises report codes, sheet numbers, reading Sheet numbers, starting line numbers, title line numbers and header line numbers according to an Excel template exported by an old system and an Excel template to be imported by a new system. The input table configuration unit configures each Sheet to be imported into the existing system by the original system, namely, the input table configuration is carried out, the name of the input table is consistent with the report code of the input library configuration, and the specific configuration of the input table comprises: keywords, column names, column types, dictionary names. The data dictionary configuration unit configures column data of data dictionary items in the old system into a data dictionary, names of the data dictionary are consistent with dictionary names in the table configuration, and the data dictionary configuration comprises: old system data, new system data and configuring this column data in the new system in the data dictionary, wherein the conversion of the old and new system data is performed by one or more of direct conversion, program conversion, code comparison, constant conversion and cross-table conversion, and no conversion is performed on the fields which are in the old table but are not in the new table.
Specifically, the deriving module includes: an output library configuration unit, a Sheet item configuration unit, and an operation unit; the output library configuration unit is used for carrying out output library configuration according to the names and the sequences of specific sheets in Excel to be imported by the new system; the Sheet item configuration unit is used for configuring Sheet items to be imported into a new system Excel template in the old system Excel template according to the new and old Excel templates, and directly inputting Sheet numbers and column names corresponding to the old system in the new system Excel template; the running unit is used for directly running a program after the Sheet item configuration is completed so as to complete importing of the old system Excel data into the new system Excel template.
In this embodiment, the export module includes an output library configuration unit, a Sheet item configuration unit, and an operation unit; the export library configuration unit sequentially configures an export Sheet name table, namely export library configuration, according to names of specific sheets in Excel to be imported by the new system, wherein the export library configuration mainly comprises report coding, export file types, export file names, sheet names, whether template export is performed, output template addresses and export Sheet numbers. The Sheet item configuration unit compares the old and new Excel templates, configures Sheet items to be imported into the new Excel template in the old Excel template, and directly inputs Sheet numbers and column names corresponding to the old system in the new Excel template. And the running unit directly runs the program after the Sheet item configuration unit is configured, so that the importing of the old system Excel data into the new system Excel template is completed.
The specific use method of the tool for importing, exporting and converting the data EXCEL comprises the following steps:
step 1, comparing an Excel template exported by an old system with a new system Excel template, importing the old table into a Sheet in the new table for sorting, sorting all Sheet names, sheet sequence numbers, report codes, exported file types, exported file names, whether template exports or not and outputting template addresses into a csv file;
and 2, configuring the Sheet to be imported into the new table, wherein the table configuration is called, the name of the table is consistent with the report code in the step 1, and key words, column names, column types and dictionary names are mainly configured in the table. Each sheet forms a csv file;
step 3, configuring a data dictionary, wherein the names of the data dictionary are consistent with those of the data dictionary in the step 2, and configuring column data of which the old table is a data dictionary item into the data dictionary, wherein the data dictionary configuration comprises the following steps: old system data and new system data, and configuring the column data in the new system in the data dictionary, wherein one data dictionary forms a csv file;
step 4, according to the new table template, the Sheet to be subjected to the migration data in the new table is configured, namely an output library configuration is configured, wherein the output library configuration mainly comprises report coding, export file type, export file name, sheet name (name in the new table), whether template export is performed, template address is output, sheet number is exported (sequence number in the new table) to form a csv file;
step 5, configuring a new table template, and binding the corresponding relation with the old table on the new table template;
and 6, after the configuration is finished, directly running a program to finish the importing of the Excel data of the old system into the Excel template of the new system.
In the embodiment, the data migration is performed by configuring the new Excel template and the old Excel template, so that the workload of personnel is greatly reduced, the change and the change in the system are simple, only the Excel configuration is needed, the data can be cut into the new system at one time, and the service can be smoothly transited on the switching of the new system and the old system.
Obviously, the data EXCEL import and export conversion tool can be used for conversion only by configuring an EXCEL template in the EXCEL data transfer process, code writing is not needed, no software technical background requirement is required for operators, change and change in a system are simple, and the operation of a user is greatly simplified.
In particular, the data EXCEL import export conversion tool provided by the invention is used for referencing the original system by configuring the new and old system EXCEL templates based on the new system, and simultaneously, the data dictionary of the original system is corresponding to the corresponding system data dictionary, and in the comparison process, the data is subjected to the principle of data moving, and the data is not processed as much as possible, so that the integrity of data conversion between the new and old systems is effectively ensured, and the purpose of data migration is achieved.
It will be apparent to those skilled in the art that various modifications and variations can be made to the present invention without departing from the spirit or scope of the invention. Thus, it is intended that the present invention also include such modifications and alterations insofar as they come within the scope of the appended claims or the equivalents thereof.
Claims (1)
1. A tool for importing and exporting data EXCEL, comprising: an import module and an export module; wherein,
the import module is used for carrying out input library configuration, table configuration and data dictionary configuration on an Excel template which needs to be imported by the new system according to the Excel template exported by the old system;
the export module is used for carrying out export library configuration according to names and sequences of Excel specific sheets to be imported by the new system, carrying out Sheet item configuration on the Excel template to be imported by the new system in the Excel template of the old system, and running a program;
the importing module includes: an input library configuration unit, an input table configuration unit and a data dictionary configuration unit; wherein,
the input library configuration unit is used for carrying out input library configuration of an output table according to an Excel template derived by the old system;
the input table configuration unit is used for carrying out input table configuration on each Sheet table to be imported into the new system in the old system according to the input library configuration;
the data dictionary configuration unit is used for configuring the data dictionary in the old system according to the input table configuration;
the export module comprises: an output library configuration unit, a Sheet item configuration unit, and an operation unit; wherein,
the output library configuration unit is used for carrying out output library configuration according to the names and the sequences of specific sheets in Excel to be imported by the new system;
the Sheet item configuration unit is used for configuring Sheet items to be imported into a new system Excel template in the old system Excel template according to the new and old Excel templates, and directly inputting Sheet numbers and column names corresponding to the old system in the new system Excel template;
the running unit is used for directly running a program after the Sheet item configuration is completed so as to complete the importing of the old system Excel data into the new system Excel template;
the configuration content of the input library configuration unit comprises: report coding, sheet number, number of read sheets, number of start lines, number of header lines and number of header lines;
the configuration content of the input table configuration unit comprises: keywords, column names, column types, and dictionary names;
the configuration content of the data dictionary configuration unit comprises: old system data dictionary configuration, new system data dictionary configuration and conversion of new and old system data dictionaries;
the configuration content of the output library configuration unit comprises: report coding, export file type, export file name, sheet name, whether template export, output template address and export Sheet number;
the conversion of the new and old system data dictionary adopts one or more modes of direct conversion, program conversion, code comparison, constant conversion and cross-table conversion;
in the conversion process of the old and new system data dictionary, fields which are available in the old table but are not available in the new table are not converted.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811612204.5A CN109783779B (en) | 2018-12-27 | 2018-12-27 | Tool for importing, exporting and converting data EXCEL |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811612204.5A CN109783779B (en) | 2018-12-27 | 2018-12-27 | Tool for importing, exporting and converting data EXCEL |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN109783779A CN109783779A (en) | 2019-05-21 |
| CN109783779B true CN109783779B (en) | 2023-12-22 |
Family
ID=66498631
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201811612204.5A Active CN109783779B (en) | 2018-12-27 | 2018-12-27 | Tool for importing, exporting and converting data EXCEL |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN109783779B (en) |
Families Citing this family (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110188334A (en) * | 2019-05-23 | 2019-08-30 | 福建威盾科技集团有限公司 | A kind of method that operation system imports EXCEL data |
| CN112988790A (en) * | 2021-02-07 | 2021-06-18 | 潍坊北大青鸟华光照排有限公司 | EXECL-based data intercommunication and data batch retrieval query method |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103914438A (en) * | 2012-12-30 | 2014-07-09 | 航天信息股份有限公司 | Template and tag implementation based excel display and export method and system |
| CN105426419A (en) * | 2015-11-03 | 2016-03-23 | 用友网络科技股份有限公司 | System and method for data promotion among heterogeneous systems |
| CN105446948A (en) * | 2015-11-13 | 2016-03-30 | 武汉鸿图节能技术有限公司 | Report automatic generation method and system |
| CN107315771A (en) * | 2017-05-23 | 2017-11-03 | 中国科学院计算机网络信息中心 | A kind of data collection system customization method based on expanding library table data dictionary |
| CN107844605A (en) * | 2017-11-28 | 2018-03-27 | 清远市云商科技有限公司 | A kind of system of big data quick backup |
Family Cites Families (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US10606573B2 (en) * | 2017-06-07 | 2020-03-31 | Syntel, Inc. | System and method for computer language migration using a re-architecture tool for decomposing a legacy system and recomposing a modernized system |
-
2018
- 2018-12-27 CN CN201811612204.5A patent/CN109783779B/en active Active
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103914438A (en) * | 2012-12-30 | 2014-07-09 | 航天信息股份有限公司 | Template and tag implementation based excel display and export method and system |
| CN105426419A (en) * | 2015-11-03 | 2016-03-23 | 用友网络科技股份有限公司 | System and method for data promotion among heterogeneous systems |
| CN105446948A (en) * | 2015-11-13 | 2016-03-30 | 武汉鸿图节能技术有限公司 | Report automatic generation method and system |
| CN107315771A (en) * | 2017-05-23 | 2017-11-03 | 中国科学院计算机网络信息中心 | A kind of data collection system customization method based on expanding library table data dictionary |
| CN107844605A (en) * | 2017-11-28 | 2018-03-27 | 清远市云商科技有限公司 | A kind of system of big data quick backup |
Also Published As
| Publication number | Publication date |
|---|---|
| CN109783779A (en) | 2019-05-21 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US11055287B2 (en) | Eigenvalue-based data query | |
| CN101719149B (en) | Data synchronization method and device | |
| CN102831052A (en) | Automatic generating device and method for test case | |
| CN109783779B (en) | Tool for importing, exporting and converting data EXCEL | |
| CN104598376A (en) | Data driving layered automation test system and method | |
| CN101553779B (en) | Migration apparatus which convert application program of mainframe system into application program of open system and method for thereof | |
| CN105354036A (en) | Database operation code generating method and apparatus | |
| CN103927423A (en) | Part design change method and system in PDM system | |
| CN103971036A (en) | Page field access control system and method | |
| CN107391611A (en) | A kind of process model generation method of the General ETL Tool based on workflow | |
| CN101499065A (en) | Table item compression method and device based on FA, table item matching method and device | |
| CN102495591A (en) | Method for monitoring numerical control machine | |
| CN101211360A (en) | System and method for processing a plurality of data form of report forms data source | |
| CN106095991A (en) | A kind of automatically generate from relevant database to the method for the code of MongoDB database data migration | |
| CN101710322A (en) | Method and system for associating information | |
| CN103744952A (en) | Method and system for implementing multi-table property of NoSQL database | |
| CN104123376B (en) | A kind of intelligent text collecting method and system based on row template | |
| CN101645073A (en) | Method for guiding prior database file into embedded type database | |
| CN115221143A (en) | Cross-type migration operator-based multi-source big data processing method | |
| CN102346765A (en) | Method and device for querying memory data | |
| CN104376113A (en) | Multi-language string processing method and system | |
| CN111221967A (en) | Language data classification storage system based on block chain architecture | |
| CN108090034B (en) | Cluster-based uniform document code coding generation method and system | |
| CN103198127B (en) | Large files sort method and system | |
| CN103092607A (en) | Encoding and decoding method of telecommunications call ticket |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |