A custom rule data sorting tool based on VBA technology

Abstract

The present invention relates to a custom rule data arrangement tool based on VBA technology, belonging to the field of standardized data office; the tool comprises a new file module, a data arrangement tool module and a formatted data intermittent export module; the new file module is used for inputting original data to form an original data arrangement table, and the original data arrangement table is sent to the data arrangement tool module; the data arrangement tool module is used for converting original data in the original data arrangement table into standardized data, generating a standardized data table, and sending the standardized data table to the formatted data intermittent export module; the formatted data intermittent export module is used for receiving the standardized data table transmitted by the data arrangement tool module, exporting the standardized data table, and realizing data arrangement; the present invention fully integrates various types of data, can assist in establishing a company's standardized database office, greatly reduces the workload and operation error rate of data arrangement, and improves work management efficiency.

Description

Technical Field

[0001] The invention belongs to the field of standardized data office and relates to a user-defined rule data sorting tool based on VBA technology. Background Art

[0002] With the continuous advancement of information management, enterprises are accumulating vast amounts of data in their technological, production, and quality management processes. This data-intensive approach to scientific analysis and decision-making in management has significantly increased. However, this accumulated and historical data is fragmented, dispersed, and unsystematic, with a low degree of standardization. It can only reveal the specific circumstances of individual entities, not the entire picture of the entity under study, its essential characteristics, or the patterns of its development. Therefore, further manual processing and organization of this accumulated data is necessary for effective aggregation and analysis to capture the overall characteristics of the phenomena.

[0003] Currently, there's no mature, standardized data office tool on the market that can transform (discrete data, formatted data) to (formatted data) to (discrete data). Data collation can only be done through manual copying and pasting, which can lead to human errors, is labor-intensive, lacks data flexibility, and inefficiently stores and manages information, placing a huge workload on managers. Summary of the Invention

[0004] The technical problem solved by the present invention is: to overcome the shortcomings of the existing technology and propose a custom rule data sorting tool based on VBA technology, which fully integrates various types of data, can assist in establishing a company's standardized database office, greatly reduce the workload of data sorting and the operation error rate, and improve work management efficiency.

[0005] The solution of the present invention is:

[0006] A custom rule data sorting tool based on VBA technology, including a new file module, a data sorting tool module and a formatted data intermittent export module;

[0007] New file module: input raw data, form a raw data sorting table, and send the raw data sorting table to the data sorting tool module;

[0008] Data sorting tool module: receives the original data sorting table from the new file module, converts the original data in the original data sorting table into standardized data, generates a standardized data table, and sends the standardized data table to the formatted data intermittent export module;

[0009] Formatted data intermittent export module: receives standardized data tables from the data sorting tool module, exports standardized data tables, and realizes data sorting.

[0010] In the above-mentioned custom rule data sorting tool based on VBA technology, the original data includes discrete data and formatted data; the standardized data includes discrete data and formatted data.

[0011] In the above-mentioned custom rule data sorting tool based on VBA technology, the new file module includes a file name module, a file save path selection module and an original data sorting table module;

[0012] When forming the original data sorting table, after inputting the original file, the user can customize the file name through the file name module; select the file save path through the file save path selection module; and form the original data sorting table through the original data sorting table module.

[0013] In the above-mentioned custom rule data sorting tool based on VBA technology, the data sorting tool module includes a source data type module, an output data table type module, a field relationship setting module, a data import and export module, a data intermittent export module and a data batch export module;

[0014] Select the type of original data through the source data type module, including discrete format data or formatted data;

[0015] Select the output standardized data table type through the output data table type module, including discrete data table or formatted data table;

[0016] The field relationship setting module is used to determine the number of columns of required data in the original data sorting table and the target number of columns after the required data is converted into a standardized data table;

[0017] The data import and export module is used to import raw data and form a raw data sorting table;

[0018] The data intermittent export module is used to export standardized data tables in batches;

[0019] The data batch export module is used to export standardized data tables at one time.

[0020] In the above-mentioned custom rule data arrangement tool based on VBA technology, the formatted data intermittent export module includes an import form module, an intermittent output selection module, an export PDF module and an export module;

[0021] Among them, the import table sample module is used to define the table style of the standardized data table;

[0022] The intermittent output selection module is used to determine the starting row and number of consecutive rows of exported data when exporting standardized data tables in batches;

[0023] The Export PDF module is used to export the standardized data table in PDF format;

[0024] The export module is used to perform the export action of standardized data tables.

[0025] In the above-mentioned custom rule data sorting tool based on VBA technology, the data intermittent export module and the data batch export module are two-choose-one working modes. Only when the data intermittent export module is selected to work, the intermittent output selection module will work.

[0026] In the aforementioned VBA-based custom rule data sorting tool, when converting discrete raw data into formatted standardized data, the working process of the custom rule data sorting tool is as follows:

[0027] Select discrete format data through the source data type module; select formatted data table through the output data table type module; determine the number of columns of required data in the original data sorting table and the target number of columns after the required data is converted into a standardized data table through the field relationship setting module; import the original data through the data import and export module and form the original data sorting table; use the data intermittent export module to export standardized data tables in batches; define the table style of the standardized data table through the import table sample module; define the starting row and the number of consecutive rows of the exported data through the intermittent output selection module; use the export PDF module to select to export the standardized data table in PDF format; use the export module to execute the export action of the standardized data table; and realize the conversion form of one column and one record.

[0028] In the aforementioned VBA-based custom rule data sorting tool, when converting discrete raw data into discrete standardized data, the working process of the custom rule data sorting tool is as follows:

[0029] Select discrete format data through the source data type module; select discrete data table through the output data table type module; determine the number of columns of required data in the original data sorting table and the target number of columns after the required data is converted into a standardized data table through the field relationship setting module; import the original data through the data import and export module and form an original data sorting table; use the data intermittent export module to export the standardized data table in batches or use the data batch export module to export the standardized data table at one time to achieve field-to-field conversion.

[0030] In the aforementioned VBA-based custom rule data sorting tool, when converting formatted raw data into discrete standardized data, the working process of the custom rule data sorting tool is as follows:

[0031] Select formatted data through the source data type module; select a discrete data table through the output data table type module; determine the number of columns of required data in the original data sorting table and the target number of columns after the required data is converted into a standardized data table through the field relationship setting module; import the original data through the data import and export module and form an original data sorting table; use the data intermittent export module to export the standardized data table in batches or use the data batch export module to export the standardized data table at one time, thereby realizing the conversion of one column into one table.

[0032] In the aforementioned VBA-based custom rule data sorting tool, when converting formatted raw data into discrete standardized data, the working process of the custom rule data sorting tool is as follows:

[0033] Select the discretized data through the source data type module; select the discretized data table through the output data table type module; determine the number of columns of required data in the original data sorting table and the target number of columns after the required data is converted into the standardized data table through the field relationship setting module; import the original data through the data import and export module and form the original data sorting table; use the data intermittent export module to export the standardized data table in batches; define the table style of the standardized data table through the import table sample module; define the starting row and the number of consecutive rows of the exported data through the intermittent output selection module; use the export PDF module to select to export the standardized data table in PDF format; use the export module to execute the export action of the standardized data table; realize the column conversion form.

[0034] The beneficial effects of the present invention compared with the prior art are:

[0035] (1) The present invention can connect data islands, integrate data scattered across various parts of the enterprise, complete the storage, conversion and organization of massive data, effectively eliminate data islands, achieve real-time data information aggregation, and increase the flexibility of data conversion;

[0036] (2) The present invention can replace manual work, realize the conversion of multiple heterogeneous data sources, support single-table and whole-database data aggregation, assist in data standardization office work, improve enterprise data quality, avoid human errors, and reduce the labor intensity of staff;

[0037] (3) The present invention allows users to perform operations such as adding and deleting data summaries;

[0038] (4) The present invention is applicable to all positions in an enterprise where daily office data storage files (Excel*.xls) are used as the object, meeting the needs of various users for data query, management, control and use, and improving the efficiency of information storage and management;

[0039] (5) From a business perspective, the application of this invention provides valuable insights into previously unknown statistical patterns or trends. Data wrangling technology can provide businesses with a certain degree of foresight into future developments. This involves inputting known values ​​for specific objects and categories and applying these values ​​to another similar set to determine expected values ​​or results. BRIEF DESCRIPTION OF THE DRAWINGS

[0040] Figure 1 This is a schematic diagram of the custom rule data collating tool of the present invention;

[0041] Figure 2 This is a schematic diagram of a new file module of the present invention;

[0042] Figure 3 This is a schematic diagram of the data collating tool module of the present invention;

[0043] Figure 4 This is a schematic diagram of the formatted data intermittent export module of the present invention. DETAILED DESCRIPTION

[0044] The present invention will be further described below in conjunction with the embodiments.

[0045] Currently, enterprises accumulate vast amounts of data in technology, production, quality, and other management processes, significantly increasing the demand for data in scientific analysis and decision-making. The data and historical data accumulated in daily office work are fragmented, dispersed, and unsystematic, with a low degree of standardization. Currently, there are no mature standardized data office tools on the market that can convert discrete data (formatted data) to standardized data (formatted data). Data organization can only be done through manual copying and pasting, which can lead to human errors, is labor-intensive, lacks data flexibility, and inefficient information storage, placing a huge workload on managers.

[0046] This paper provides a custom rule-based data organization tool based on VBA technology. Using VBA technology, and using Excel (*.xls) files for daily office data storage, this tool is designed and developed to implement user-defined rules and automatically convert and organize standardized data. This tool fully integrates various data types and can assist in establishing standardized company databases for office work, significantly reducing the workload and operational errors associated with data organization and improving work management efficiency.

[0047] Custom rule data sorting tools based on VBA technology, such as Figure 1 As shown, it specifically includes a new file module 1, a data sorting tool module 2 and a formatted data intermittent export module 3.

[0048] New file module 1: input original data, form an original data sorting table, and send the original data sorting table to data sorting tool module 2; original data includes discrete data and formatted data; the standardized data includes discrete data and formatted data.

[0049] Data sorting tool module 2: receives the raw data sorting table from the new file module, converts the raw data in the raw data sorting table into standardized data, generates a standardized data table, and sends the standardized data table to the formatted data intermittent export module 3;

[0050] Formatted data intermittent export module 3: receives the standardized data table from the data sorting tool module, exports the standardized data table, and realizes data sorting.

[0051] like Figure 2 As shown, the new file module 1 includes a file name module 101, a file save path selection module 102 and an original data sorting table module 103;

[0052] When forming the original data arrangement table, after inputting the original file, the user customizes the file name through the file name module 101; selects the file save path through the file save path selection module 102; and forms the original data arrangement table through the original data arrangement table module 103.

[0053] like Figure 3 As shown, the data sorting tool module 2 includes a source data type module 201, an output data table type module 202, a field relationship setting module 203, a data import and export module 204, a data intermittent export module 205 and a data batch export module 206;

[0054] The source data type module 201 selects the type of the original data, and the selection content includes discrete format data or formatted data;

[0055] The output data table type module 202 selects the type of the output standardized data table, and the selection content includes a discrete data table or a formatted data table;

[0056] The field relationship setting module 203 is used to determine the number of columns of required data in the original data sorting table and the target number of columns after the required data is converted into a standardized data table;

[0057] The data import and export module 204 is used to import the original data and form an original data sorting table;

[0058] The data intermittent export module 205 is used to export standardized data tables in batches;

[0059] The data batch export module 206 is used to export standardized data tables at one time.

[0060] like Figure 4 As shown, the formatted data intermittent export module 3 includes an import form module 301, an intermittent output selection module 302, an export PDF module 303 and an export module 304;

[0061] The import table template module 301 is used to define the table style of the standardized data table;

[0062] The intermittent output selection module 302 is used to determine the starting row and the number of consecutive rows of exported data when exporting the standardized data table in batches;

[0063] The export PDF module 303 is used to select to export the standardized data table in PDF format;

[0064] The export module 304 is used to perform the export action of the standardized data table.

[0065] The data intermittent export module 205 and the data batch export module 206 are in a two-or-one working mode. Only when the data intermittent export module 205 is selected to work, the intermittent output selection module 302 will work.

[0066] When converting discrete raw data into formatted and standardized data, the custom rule data cleaning tool works as follows:

[0067] Select discrete format data through the source data type module 201; select the formatted data table through the output data table type module 202; determine the number of columns of required data in the original data sorting table and the target number of columns after the required data is converted into a standardized data table through the field relationship setting module 203; import the original data through the data import and export module 204 and form an original data sorting table; use the data intermittent export module 205 to export the standardized data table in batches; define the table style of the standardized data table through the import table sample module 301; define the starting row and the number of consecutive rows of the exported data through the intermittent output selection module 302; use the export PDF module 303 to select to export the standardized data table in PDF format; use the export module 304 to execute the export action of the standardized data table; and realize the conversion form of one column and one record.

[0068] When converting discrete raw data into discrete standardized data, the custom rule data cleansing tool works as follows:

[0069] Discrete format data is selected through the source data type module 201; a discrete data table is selected through the output data table type module 202; the number of columns of required data in the original data sorting table and the target number of columns after the required data is converted into a standardized data table are determined through the field relationship setting module 203; the original data is imported through the data import and export module 204, and an original data sorting table is formed; the standardized data table is exported in batches through the data intermittent export module 205 or the standardized data table is exported at one time through the data batch export module 206, thereby realizing the field-to-field conversion.

[0070] When converting formatted raw data into discrete standardized data, the custom rule data wrangling tool works as follows:

[0071] Select formatted data through the source data type module 201; select a discrete data table through the output data table type module 202; determine the number of columns of required data in the original data sorting table and the target number of columns after the required data is converted into a standardized data table through the field relationship setting module 203; import the original data through the data import and export module 204 and form an original data sorting table; use the data intermittent export module 205 to export the standardized data table in batches or use the data batch export module 206 to export the standardized data table at one time, thereby realizing the conversion form of converting one column into one table.

[0072] When converting formatted raw data into discrete standardized data, the custom rule data wrangling tool works as follows:

[0073] Select the formatted data through the source data type module 201; select the discretized data table through the output data table type module 202; determine the number of columns of required data in the original data sorting table and the target number of columns after the required data is converted into the standardized data table through the field relationship setting module 203; import the original data through the data import and export module 204 and form the original data sorting table; use the data intermittent export module 205 to export the standardized data table in batches; define the table style of the standardized data table through the import table sample module 301; define the starting row and the number of consecutive rows of the exported data through the intermittent output selection module 302; use the export PDF module 303 to select to export the standardized data table in PDF format; use the export module 304 to execute the export action of the standardized data table; and realize the column-to-column conversion form.

[0074] The custom rule data sorting tool based on VBA technology is implemented in the following ways:

[0075] Function 1: Original data is formatted data, and standardized data is formatted data

[0076] Conversion criteria:

[0077] ①Set the field range of the original data to be sorted and convert the columns into columns;

[0078] ② One column at a time (one type of information);

[0079] Function 2: The original data is discrete data, and the standardized data is discrete data

[0080] Conversion criteria:

[0081] ① Set the field range of the original data that needs to be sorted, and change the field;

[0082] ②One field, one record;

[0083] Function 3: Original data is formatted data, and standardized data is discrete data

[0084] Conversion criteria:

[0085] ① Set the field range of the original data that needs to be sorted;

[0086] ②One row, one table;

[0087] Function 4: Original data is discrete data, and standardized data is formatted data

[0088] Conversion criteria:

[0089] ① Set the field range of the original data that needs to be sorted;

[0090] ②One record per line;

[0091] When the tool is running, first create a new data organization table module through the data organization tool, name the file, select the file save path, select New Data Organization Table, and complete the creation of the new data organization table. Then, through the Customize Data Organization Rules module, set the original data type (input data): discrete data, formatted data; set the standardized data type (output data): discrete data, formatted data; the customization process includes setting the field relationship between the original data and the standardized data (that is, the area where the output data is located), the field name, and the corresponding input and output field positions. Then, import the original data (*.xls) through the data import module. That is, check the Batch Import button and click the local data that needs to be converted to complete the import of the original data. Then, through the Customize Standardized Data Export module (discrete data export, formatted data intermittent export), customize the standardized data output. For discrete data, you need to click "Batch Export of Data (*.xls)" to complete the standardized data organization; for formatted data, you need to click "Intermittent Export of Data (*.xls)" and "Import Standardized Data Table Sample" in sequence, where you need to set "Intermittent Output Start Row" and "Single Table Continuous Output Rows", and check "Export PDF" to export standardized data in consecutive pages as required to complete the data organization.

[0092] The present invention can connect data islands, integrate data scattered across various parts of the enterprise, complete the storage, conversion and organization process of massive data, effectively eliminate data islands, realize real-time data information aggregation, and increase the flexibility of data conversion.

[0093] The present invention can replace manual work, realize the conversion of multiple heterogeneous data sources, support single-table and whole-database data aggregation, assist in data standardization office work, improve enterprise data quality, avoid human errors, and reduce the labor intensity of staff.

[0094] The present invention allows users to implement operations such as adding and deleting data summaries.

[0095] The present invention is applicable to all positions in an enterprise that use Excel (*.xls) files for daily office data storage, and satisfies various users' needs for data query, management, control and use, thereby improving information storage efficiency.

[0096] From a business perspective, the application of this invention provides valuable insights into previously unknown statistical patterns or trends. Data wrangling technology can provide businesses with a degree of foresight into future developments. This involves inputting known values ​​for specific objects and categories and applying these values ​​to another similar set to determine expected values ​​or outcomes.

[0097] Although the present invention has been disclosed above in terms of preferred embodiments, it is not intended to limit the present invention. Any person skilled in the art may make possible changes and modifications to the technical solutions of the present invention by using the methods and technical contents disclosed above without departing from the spirit and scope of the present invention. Therefore, any simple modifications, equivalent changes and modifications made to the above embodiments based on the technical essence of the present invention without departing from the content of the technical solutions of the present invention shall fall within the scope of protection of the technical solutions of the present invention.

Claims

1. A custom rule data sorting tool based on VBA technology, characterized by: It includes a new file module (1), a data sorting tool module (2) and a formatted data intermittent export module (3); New file module (1): inputs original data, forms an original data sorting table, and sends the original data sorting table to the data sorting tool module (2); Data sorting tool module (2): receiving the original data sorting table transmitted by the new file module, converting the original data in the original data sorting table into standardized data, generating a standardized data table, and sending the standardized data table to the formatted data intermittent export module (3); Formatted data intermittent export module (3): receives the standardized data table from the data sorting tool module, exports the standardized data table, and realizes data sorting; The data arrangement tool module (2) includes a source data type module (201), an output data table type module (202), a field relationship setting module (203), a data import and export module (204), a data intermittent export module (205) and a data batch export module (206); Selecting the type of original data through a source data type module (201), wherein the selection content includes discrete format data or formatted data; Selecting the type of the output standardized data table through the output data table type module (202), wherein the selection content includes a discrete data table or a formatted data table; The field relationship setting module (203) is used to determine the number of columns of required data in the original data sorting table and the target number of columns after the required data is converted into a standardized data table; The data import and export module (204) is used to import the original data and form an original data sorting table; The data intermittent export module (205) is used to export standardized data tables in batches; The data batch export module (206) is used to export the standardized data table at one time; When converting discrete raw data into formatted and standardized data, the custom rule data cleaning tool works as follows: Discrete format data is selected through a source data type module (201); a formatted data table is selected through an output data table type module (202); the number of columns of required data in the original data sorting table and the target number of columns after the required data is converted into a standardized data table are determined through a field relationship setting module (203); the original data is imported through a data import and export module (204) to form an original data sorting table; and the standardized data table is exported in batches through a data intermittent export module (205); The table style of the standardized data table is defined by the table sample import module (301); the starting row and the number of consecutive rows of the exported data are defined by the intermittent output selection module (302); the standardized data table is selected to be exported in PDF format by the PDF export module (303); and the standardized data table is exported by the export module (304); thereby realizing a one-column-one-record conversion form.

2. The VBA-based custom rule data organization tool according to claim 1, characterized in that: The original data includes discrete data and formatted data; the standardized data includes discrete data and formatted data.

3. The VBA-based custom rule data organization tool according to claim 1, characterized in that: The new file creation module (1) includes a file name module (101), a file save path selection module (102) and an original data arrangement table module (103); When forming the original data arrangement table, after inputting the original file, the user customizes the file name through the file name module (101); selects the file saving path through the file saving path selection module (102); and forms the original data arrangement table through the original data arrangement table module (103).

4. The VBA-based custom rule data collation tool according to claim 2, characterized in that: The formatted data intermittent export module (3) comprises a table sample import module (301), an intermittent output selection module (302), a PDF export module (303) and an export module (304); The import table sample module (301) is used to define the table style of the standardized data table; The intermittent output selection module (302) is used to determine the starting row and the number of consecutive rows of exported data when exporting the standardized data table in batches; The export PDF module (303) is used to select to export the standardized data table in PDF format; The export module (304) is used to execute the export action of the standardized data table.

5. The VBA-based custom rule data sorting tool according to claim 4, characterized in that: The data intermittent export module (205) and the data batch export module (206) are in a two-choose-one working mode. Only when the data intermittent export module (205) is selected to work, the intermittent output selection module (302) will work.

6. The VBA-based custom rule data sorting tool according to claim 5, characterized in that: When converting discrete raw data into discrete standardized data, the custom rule data cleansing tool works as follows: Discrete format data is selected through a source data type module (201); a discrete data table is selected through an output data table type module (202); the number of columns of required data in an original data sorting table and the target number of columns after the required data is converted into a standardized data table are determined through a field relationship setting module (203); the original data is imported through a data import and export module (204) to form an original data sorting table; the standardized data table is exported in batches through a data intermittent export module (205) or the standardized data table is exported at one time through a data batch export module (206), thereby realizing a field-for-field conversion form.

7. The VBA-based custom rule data sorting tool according to claim 6, characterized in that: When converting formatted raw data into discrete standardized data, the custom rule data wrangling tool works as follows: Formatted data is selected through a source data type module (201); a discrete data table is selected through an output data table type module (202); the number of columns of required data in an original data sorting table and the target number of columns after the required data is converted into a standardized data table are determined through a field relationship setting module (203); original data is imported through a data import and export module (204) to form an original data sorting table; the standardized data table is exported in batches through a data intermittent export module (205) or the standardized data table is exported at one time through a data batch export module (206), thereby realizing a conversion form of converting one column into one table.

8. The VBA-based custom rule data sorting tool according to claim 7, characterized in that: When converting formatted raw data into discrete standardized data, the custom rule data wrangling tool works as follows: Selecting discrete data through the source data type module (201); selecting a discretized data table through the output data table type module (202); determining the number of columns of required data in the original data sorting table and the target number of columns after the required data is converted into a standardized data table through the field relationship setting module (203); importing the original data through the data import and export module (204) and forming an original data sorting table; A data intermittent export module (205) is used to export standardized data tables in batches; The table style of the standardized data table is defined by the table sample import module (301); the starting row and the number of consecutive rows of the exported data are defined by the intermittent output selection module (302); the standardized data table is selected to be exported in PDF format by the PDF export module (303); the standardized data table is exported by the export module (304); and the column-to-column conversion form is realized.

Images