ExcelVBA-based data format conversion method and system

Abstract

The invention discloses a data format conversion method and system based on ExcelVBA, and relates to the technical field of data standardization. The method comprises the following specific steps: performing structural analysis on original analysis test result data to obtain a first data format; performing structural analysis on a sample database of the DGSS system to obtain a second data format; and performing data format conversion by utilizing an ExcelVBA technology, and converting the first data format into the second data format. By means of the method, the complicated sample input process can be simplified, format conversion is conducted on a table of sample analysis results through ExcelVBA programming, the table can be conveniently converted into sample data of a digital geological survey system, the input workload of project workers can be greatly reduced, the labor cost is saved, and the error occurrence probability is reduced.

Description

Technical Field

[0001] The present invention relates to the technical field of data standardization, and in particular to a data format conversion method and system based on Excel VBA. Background Art

[0002] The Digital Geological Survey Software System (DGSS) has achieved innovation in the integrated application of information technology, greatly promoting the application of information technology in the field of geology in my country. At present, it has been widely used in national regional geological surveys and strategic mineral prospecting surveys, mineral resource surveys and evaluations, crisis mine replacement resource surveys, etc. At the same time, it is also widely used in coal, metallurgy, nonferrous metals, chemicals, building materials and other industry departments and university scientific research departments in geological surveys. The application scope covers basic geological surveys, geological and mineral surveys, resource reserve estimation and mine three-dimensional modeling. In the later data collation of the Digital Geological Survey Software System (DGSS), due to software development reasons, the system did not provide the import function of sample analysis results, making the sample entry process too complicated, relying on direct operation of the MDB database, and requiring high data processing personnel. The manual entry makes it impossible to check errors. If the operation is improper, it will cause irreversible serious consequences, destroying the early results of the entire project. Therefore, for those skilled in the art, how to realize the simple entry of geological sample database based on the DGSS system is an urgent problem to be solved. Summary of the invention

[0003] The purpose of the present invention is to provide a data format conversion method and system based on Excel VBA to solve the problems raised in the background technology.

[0004] To achieve the above object, the present invention provides the following solutions: On the one hand, a data format conversion method based on Excel VBA is provided, and the specific steps include the following:

[0005] Performing structural analysis on original analysis and test result data to obtain a first data format;

[0006] Perform structural analysis on the sample database of the DGSS system to obtain the second data format;

[0007] The data format is converted using Excel VBA technology to convert the first data format into the second data format.

[0008] Preferably, the specific steps of using Excel VBA technology to convert data format are:

[0009] In Excel VBA, select the analysis elements in the original analysis test result data to calculate the number of elements;

[0010] In the newly created table, paste n rows of the same number in column A, starting from the mth row, and paste n analysis elements in column B accordingly;

[0011] The Transpose function is used to convert the original analysis test result data into a column format and put it into column C, thereby completing the conversion of the first data format into the second data format.

[0012] Preferably, the data type and structure of the original analysis test result data and the sample database, including column names and data formats, are automatically identified through data recognition technology.

[0013] Preferably, it also includes inserting new data and updating existing data based on the mapping results based on Excel VBA to achieve real-time synchronization of the database.

[0014] On the other hand, a data format conversion system based on Excel VBA is provided, including an original data format acquisition module, a target data format acquisition module, and a format conversion module; wherein,

[0015] The original data format acquisition module is used to perform structural analysis on the original analysis and test result data to obtain a first data format;

[0016] The target data format acquisition module is used to perform structural analysis on the sample database of the DGSS system to acquire the second data format;

[0017] The format conversion module is used to perform data format conversion using Excel VBA technology to convert the first data format into the second data format.

[0018] According to the specific embodiments provided by the present invention, the present invention discloses the following technical effects: simplifying the complicated sample entry process, using Excel VBA programming to transform the format of the table of sample analysis results so that it can be easily converted into the sample data of the digital geological survey system (DGSS), greatly reducing the entry workload of project staff, saving labor costs, and reducing the probability of errors. BRIEF DESCRIPTION OF THE DRAWINGS

[0019] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative labor.

[0020] Figure 1 The present invention is a flow chart of the method. DETAILED DESCRIPTION

[0021] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0022] The purpose of this invention is to provide a data format conversion method based on Excel VBA. Figure 1 As shown, the specific steps include the following:

[0023] S1. Performing structural analysis on original analysis and test result data to obtain a first data format;

[0024] S2, performing structural analysis on the sample database of the DGSS system to obtain a second data format;

[0025] S3. Use Excel VBA technology to convert the data format, and convert the first data format into the second data format.

[0026] The original analysis and test result data structure is shown in Table 1:

[0027]

[0028] Table 1

[0029] The data structure of the DGSS sample library is studied as follows:

[0030] In the embodiment of the present invention, the most complicated spectral data entry in DGSS is mainly aimed at. The data table storing the spectrum is divided into two parts (PROD_YKCF table and PROD_YKCF_HL table). As shown in Table 2, the PROD_YKCF table is used to store basic information such as analysis number, rock name and analysis method; as shown in Table 3, the PROD_YKCF_HL table is used to store specific data such as sample number, analysis element and analysis result.

[0031]

[0032] Table 2

[0033]

[0034] Table 3

[0035] It is worth noting that the PROD_YKCF_HL table adds the value of each sample's single element analysis as a separate piece of data, which is different from the usual way of adding a lot of data after a number. With the data structure model of the target data, the next thing to do is how to convert the analysis result table into the data structure of the target database.

[0036] Furthermore, data identification technology is used to automatically identify the data type and structure of the original analysis test result data and the sample database, including column names and data formats.

[0037] The next step is to convert the analysis data of each sample in each row into a column one by one, and then add another row of data to complete the data structure conversion. The specific data conversion method is:

[0038] In Excel VBA, first use Selection.Columns.Count to return the number of columns in the selected area, that is, you can select the analysis elements in the analysis result table to calculate the number of elements.

[0039] n = Selection.Columns.Count

[0040] In the newly created table, paste n rows with the same number in column A, starting from row m.

[0041] Selection.Copy

[0042] ActiveCell.Range("A"&m&":A"&m+n).Select

[0043] ActiveSheet.Paste

[0044] Paste n analysis elements in column B accordingly, and use the Transpose function.

[0045] Selection.Copy

[0046] Range("B"&m).Select

[0047] Selection.PasteSpecial Paste:=xlPasteAll,Operation:=xlNone,SkipBlanks:=_

[0048] False,Transpose:=True

[0049] The Transpose function can also be used to convert the analysis results into a column format and put it in column C. This completes the conversion from the original data structure to the target data structure. The EXCEL data can be easily imported into the DGSS ACCESS database.

[0050] Furthermore, it also includes inserting new data and updating existing data based on the mapping results based on Excel VBA to achieve real-time synchronization of the database.

[0051] On the other hand, a data format conversion system based on Excel VBA is provided, including an original data format acquisition module, a target data format acquisition module, and a format conversion module; wherein,

[0052] The original data format acquisition module is used to perform structural analysis on the original analysis and test result data to obtain a first data format;

[0053] A target data format acquisition module is used to perform structural analysis on the sample database of the DGSS system and acquire the second data format;

[0054] The format conversion module is used to convert the data format by using Excel VBA technology, and convert the first data format into the second data format.

[0055] The principles and implementation methods of the present invention are described in this article using specific examples. The description of the above embodiments is only used to help understand the method and core idea of ​​the present invention. At the same time, for those skilled in the art, according to the idea of ​​the present invention, there will be changes in the specific implementation methods and application scope. In summary, the content of this specification should not be understood as limiting the present invention.

Claims

1. A data format conversion method based on Excel VBA, characterized in that: The specific steps include the following: Performing structural analysis on original analysis and test result data to obtain a first data format; Perform structural analysis on the sample database of the DGSS system to obtain the second data format; The data format is converted using Excel VBA technology to convert the first data format into the second data format.

2. A data format conversion method based on Excel VBA according to claim 1, characterized in that: The specific steps for data format conversion using Excel VBA technology are: In Excel VBA, select the analysis elements in the original analysis test result data to calculate the number of elements; In the newly created table, paste n rows of the same number in column A, starting from the mth row, and paste n analysis elements in column B accordingly; The Transpose function is used to convert the original analysis test result data into a column format and put it into column C, thereby completing the conversion of the first data format into the second data format.

3. The data format conversion method based on Excel VBA according to claim 1, characterized in that: The data type and structure of the original analysis test result data and sample database, including column names and data formats, are automatically identified through data recognition technology.

4. The data format conversion method based on Excel VBA according to claim 1, characterized in that: It also includes inserting new data and updating existing data based on the mapping results based on Excel VBA to achieve real-time synchronization of the database.

5. A data format conversion system based on Excel VBA, characterized in that: It includes an original data format acquisition module, a target data format acquisition module, and a format conversion module; wherein, The original data format acquisition module is used to perform structural analysis on the original analysis and test result data to obtain a first data format; The target data format acquisition module is used to perform structural analysis on the sample database of the DGSS system to acquire the second data format; The format conversion module is used to perform data format conversion using Excel VBA technology to convert the first data format into the second data format.

Images