A method and system for transferring Excel data source to big data platform

Abstract

A method and system for transferring Excel data source to a big data platform, comprising: obtaining a file version of the Excel data source; processing the obtained Excel data according to the file version to obtain a transfer file; transferring the transfer file to the big data platform according to the obtained distributed file system path. The present invention constructs a method for simply and quickly parsing data from an Excel data source to a big data platform. Compared with traditional ETL tools, it solves the problem that ETL only supports versions 2007 and earlier, overcomes the shortcomings of complex installation and cumbersome configuration, and thus improves work efficiency.

Description

Technical Field

[0001] The present invention relates to the field of power system information technology, and in particular to a method and system for transferring an Excel data source to a big data platform. Background Art

[0002] The era of big data has already arrived. It has become the primary task of each enterprise to store different data sources on the big data platform for unified management, improve data processing efficiency, and better realize business processing. In enterprises, the storage center of core key data is spreadsheets, which are responsible for access based on all front-end pages. However, Excel is widely used as one of the sources for storing data sources, but spreadsheets cannot be directly accessed by big data. Although ETL (Extract Transform Load) can support versions 2007 and earlier, the configuration information is extremely complex, so it cannot be directly called by the big data platform. With the advent of the big data era, all industries have shown the demand for big data. How to quickly transfer data from spreadsheets to the big data platform needs to be solved urgently. Summary of the invention

[0003] In order to solve the above-mentioned deficiencies in the prior art, the present invention provides a method and system for transferring Excel data sources to a big data platform. Based on traditional Excel data source storage and new storage technology, the Excel data source is stored in the big data platform quickly and efficiently.

[0004] The technical solution provided by the present invention is: a method for transferring an Excel data source to a big data platform, comprising:

[0005] Get the file version of the Excel data source;

[0006] The Excel data is obtained and processed according to the file version to obtain a transfer file;

[0007] The transferred file is transferred to the big data platform according to the obtained distributed file system path.

[0008] Preferably, the obtaining of Excel data according to the file version to obtain a transfer file includes:

[0009] Based on the file version and the calling method in the Apache POI component, the data in the Excel data source is transferred to a transfer file in a predetermined format;

[0010] The suffix of the dumped file is dat.

[0011] Preferably, the transferring and saving the data in the Excel data source into a transfer file in a predetermined format based on the file version and the calling method in the Apache POI component includes:

[0012] When the Excel version is a version before Excel 2007, the Excel data source is parsed based on the readXls method in the Apache POI component to obtain the transferred data;

[0013] When the Excel version is Excel 2007 or later, the Excel data source is parsed based on the readXlsx method in the Apache POI component to obtain the transferred data;

[0014] The transferred data is transferred into a transferred file in a predetermined format.

[0015] Preferably, the transferring the transferred data into a transfer file in a predetermined format includes:

[0016] Read the data of each table in the file of the Excel data source;

[0017] The data of each table in the file of the Excel data source are separated according to a preset separator, and the data of each table in the file of the Excel data source and the separator are stored as a transfer file.

[0018] Preferably, after transferring the transferred file to the big data platform according to the obtained distributed file system path, the method further includes:

[0019] The transfer file is packaged in the big data platform and then waits for the application interface to be called.

[0020] Preferably, obtaining the file version of the Excel data source includes:

[0021] Get the Excel data source file name, and extract the suffix character string of the Excel file name;

[0022] When the suffix string is xlsx, the file of the Excel data source is a text file of Excel 2007 or later;

[0023] When the suffix character string is xls, the file of the Excel data source is a text file before Excel 2007.

[0024] Preferably, the distributed file system path of the big data platform is stored in the configuration information of the distributed file system.

[0025] Preferably, before obtaining the distributed file system path, the method further includes:

[0026] Check whether the big data platform is running normally. If it is running normally, obtain the distributed file system path.

[0027] Based on the same inventive concept, the present invention also provides a system for transferring an Excel data source to a big data platform, including:

[0028] The acquisition module is used to obtain the file version of the Excel data source;

[0029] A processing module, used for processing the acquired Excel data according to the file version to obtain a transfer file;

[0030] The saving module is used to transfer the transferred file to the big data platform according to the obtained distributed file system path.

[0031] Preferably, the processing module comprises:

[0032] The processing submodule is used to transfer the data in the Excel data source into a transfer file in a predetermined format based on the file version and the calling method in the Apache POI component; the suffix of the transfer file is dat.

[0033] Preferably, the processing submodule includes:

[0034] A first processing unit is used for parsing the Excel data source to obtain the transferred data based on the readXls method in the ApachePOI component when the Excel version is a version before Excel 2007;

[0035] The second processing unit is used for parsing the Excel data source to obtain the transferred data based on the readXlsx method in the Apache POI component when the Excel version is Excel 2007 or later;

[0036] The transfer unit is used to transfer the transfer data into a transfer file in a predetermined format.

[0037] Preferably, the acquisition module includes:

[0038] An extraction unit, used to obtain the Excel data source file name and extract the suffix character string of the Excel file name;

[0039] A first judgment unit is used for determining that when the suffix character string is xlsx, the file of the Excel data source is a text file of Excel 2007 or later;

[0040] The second judgment unit is used to determine that when the suffix character string is xls, the file of the Excel data source is a text before Excel 2007.

[0041] Compared with the closest prior art, the technical solution provided by the present invention has the following beneficial effects:

[0042] The technical solution provided by the present invention obtains the file version of an Excel data source; processes the acquired Excel data according to the file version to obtain a transfer file; and transfers the transfer file to a big data platform according to the acquired distributed file system path. Compared with traditional ETL tools, the technical solution solves the problem that ETL only supports a specific Excel version, realizes the transfer of the Excel data source to the big data platform, overcomes the shortcomings of complex installation and cumbersome configuration, and thus improves work efficiency.

[0043] The technical solution provided by the present invention collects data information to a big data platform, laying the foundation for future data analysis and mining using big data methods. BRIEF DESCRIPTION OF THE DRAWINGS

[0044] Figure 1 This is a flow chart of the method for transferring an Excel data source to a big data platform in the present invention;

[0045] Figure 2 This is a flowchart of processing an Excel data source in an embodiment of the present invention;

[0046] Figure 3 This is a schematic diagram of the format of transferring to the distributed file system of the big data platform in an embodiment of the present invention. DETAILED DESCRIPTION

[0047] In order to better understand the present invention, the content of the present invention is further described below in conjunction with the accompanying drawings and examples.

[0048] Apache POI is a popular API that allows programmers to create, modify and display MS Office files using Java programs. It is an open source library for designing or modifying Microsoft Office files using Java distributed. It contains classes and methods to decode user input data or files into MS Office documents.

[0049] Internet data center: a new generation of architecture and technology designed to more economically extract value from high-frequency, large-volume, different-structured and typed data. All definitions of big data are basically based on the characteristics of big data. The characteristics of big data can be summarized as: volume, variety, velocity and value, etc. How to transfer Excel data sources to the big data platform based on the existing Apache POI and overcome the problem of version incompatibility remains to be solved.

[0050] The present invention specifically provides a method for transferring an Excel data source to a big data platform. Figure 1 , is a flow chart of the method for transferring the Excel data source to the big data platform in the present invention, such as Figure 1 As shown, including:

[0051] Step S101, obtaining the file version of the Excel data source;

[0052] Step S102, processing the acquired Excel data according to the file version to obtain a transfer file;

[0053] Step S103, transferring the transferred file to the big data platform according to the obtained distributed file system path.

[0054] Before obtaining the distributed file system path, you must first check whether the big data platform is running normally. When it is running normally, obtain the distributed file system path.

[0055] Get the file version of the Excel data source, including:

[0056] Step S101, obtaining the file version of the Excel data source, specifically includes:

[0057] Get the Excel data source file name and extract the suffix string of the Excel file name;

[0058] When the suffix string is xlsx, the file of the Excel data source is a text file of Excel 2007 or later;

[0059] When the suffix character string is xls, the file of the Excel data source is a text file before Excel 2007.

[0060] Step S102, processing the acquired Excel data according to the file version to obtain a transfer file, including:

[0061] Based on the file version and the calling method in the Apache POI component, the data in the Excel data source is transferred to a transfer file in a predetermined format; the suffix of the transfer file is dat.

[0062] The method of transferring the data in the Excel data source into a transfer file in a predetermined format based on the file version and the calling method in the Apache POI component includes:

[0063] When the Excel version is earlier than Excel 2007, the Excel data source is parsed based on the readXls method in the Apache POI component to obtain the transferred data;

[0064] When the Excel version is Excel2007 or later, the Excel data source is parsed based on the readXlsx method in the Apache POI component to obtain the transferred data;

[0065] The transfer data is transferred to a transfer file in a predetermined format.

[0066] Further, the transferred data is transferred into a transferred file in a predetermined format, including:

[0067] Read the data of each table in the Excel data source file;

[0068] The data of each table in the file of the Excel data source are separated according to a preset separator, and the data of each table in the file of the Excel data source and the separator are stored as a transfer file.

[0069] Step S103, transferring the transferred file to the big data platform according to the obtained distributed file system path;

[0070] Get the distributed file system path of the big data platform according to the configuration information of NameNode in the distributed file system.

[0071] After the transfer file is transferred to the big data platform according to the obtained distributed file system path, the transfer file is packaged in the big data platform and waits for the WebService interface to be called.

[0072] The specific steps in the embodiment of the present invention include:

[0073] Step 1: Complete the configuration of the big data platform information in a configuration manner. Configure a static variable corresponding to the path of the big data distributed file system. The basis for configuring this parameter is that when communicating with the underlying distributed file system of big data (Hadoop Distributed File System, HDFS), the configuration information of the corresponding file master node NameNode must be known. This information can be obtained from the configuration file of the big data platform.

[0074] Step 2: Log in to the big data platform management system and check whether the big data platform is running normally by observing the big data platform interface;

[0075] Step 3: Execute the java-cp Adapter.jar com.thp.adapter.hadoop.ReadExcelOpts arg1arg2 statement on one of the machines on the big data platform or a machine that can be connected to the big data platform. There are two input parameters, which represent the path of the Excel data source and the HDFS path of the big data platform.

[0076] Step 4: Figure 2 FIG. 1 is a flowchart of processing an Excel data source in an embodiment of the present invention, as shown in FIG. Figure 2 As shown, according to the path of the Excel data source passed in, the Excel file name in the path of the Excel data source is first obtained, and then the suffix string of the file name is extracted. The Excel version is determined based on the suffix string. If the suffix string ends with xls, it is considered to be Excel2003 and earlier versions, and the readXls method will be called to parse the Excel file. If the suffix string ends with xlsx, it is considered to be Excel2007 and later versions, and the readXlsx method will be called accordingly to parse the Excel file;

[0077] Step 5: Process the data source and save it to the big data platform in a fixed data format. The default data structure is that each row of Excel data is saved to the big data platform as a row, and the default separator is "," . However, the separator can be dynamically configured in the configuration file to separate the intervals of data in each row.

[0078] In this embodiment, Table 1 is taken as an example. The content in the Excel table shown in Table 1 is transferred to the big data platform through the transfer method proposed in the present invention. Figure 3 shown.

[0079]

[0080]

[0081] Based on the same inventive concept, the present invention also provides a system for transferring an Excel data source to a big data platform, including:

[0082] The acquisition module is used to obtain the file version of the Excel data source;

[0083] A processing module, used for processing the acquired Excel data according to the file version to obtain a transfer file;

[0084] The saving module is used to transfer the transferred file to the big data platform according to the obtained distributed file system path.

[0085] In an embodiment, the processing module comprises:

[0086] The processing submodule is used to transfer the data in the Excel data source into a transfer file in a predetermined format based on the file version and the calling method in the Apache POI component; the suffix of the transfer file is dat.

[0087] In an embodiment, the processing submodule includes:

[0088] A first processing unit is used for parsing the Excel data source to obtain the transferred data based on the readXls method in the ApachePOI component when the Excel version is a version before Excel 2007;

[0089] The second processing unit is used for parsing the Excel data source to obtain the transferred data based on the readXlsx method in the Apache POI component when the Excel version is Excel 2007 or later;

[0090] The transfer unit is used to transfer the transfer data into a transfer file in a predetermined format.

[0091] In an embodiment, the acquisition module includes:

[0092] An extraction unit, used to obtain the Excel data source file name and extract the suffix character string of the Excel file name;

[0093] A first judgment unit is used for determining that when the suffix character string is xlsx, the file of the Excel data source is a text file of Excel 2007 or later;

[0094] The second judgment unit is used to determine that when the suffix character string is xls, the file of the Excel data source is a text before Excel 2007.

[0095] Those skilled in the art will appreciate that the embodiments of the present application may be provided as methods, systems, or computer program products. Therefore, the present application may adopt the form of a complete hardware embodiment, a complete software embodiment, or an embodiment in combination with software and hardware. Moreover, the present application may adopt the form of a computer program product implemented in one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) that include computer-usable program code.

[0096] The present application is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the embodiments of the present application. It should be understood that each process and / or box in the flowchart and / or block diagram, as well as the combination of the processes and / or boxes in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to generate a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0097] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0098] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0099] The above are merely embodiments of the present invention and are not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention are included in the scope of the claims of the present invention to be approved.

Claims

1. A method for transferring an Excel data source to a big data platform, characterized in that: include: Get the file version of the Excel data source; The Excel data is obtained and processed according to the file version to obtain a transfer file; Transferring the transferred file to the big data platform according to the obtained distributed file system path; The step of processing the Excel data according to the file version to obtain a transfer file includes: Based on the file version and the calling method in the Apache POI component, the data in the Excel data source is transferred to a transfer file in a predetermined format; The suffix of the transfer file is dat; the method of transferring the data in the Excel data source into the transfer file in a predetermined format based on the file version and the calling method in the Apache POI component includes: When the Excel version is a version before Excel 2007, the Excel data source is parsed based on the readXls method in the Apache POI component to obtain the transferred data; When the Excel version is Excel 2007 or later, the Excel data source is parsed based on the readXlsx method in the Apache POI component to obtain the transferred data; Transferring the transferred data into a transfer file in a predetermined format; The step of transferring the transferred data into a transferred file in a predetermined format includes: Read the data of each table in the file of the Excel data source; The data of each table in the file of the Excel data source are separated according to a preset separator, and the data of each table in the file of the Excel data source and the separator are stored as a transfer file.

2. The method for transferring an Excel data source to a big data platform according to claim 1, characterized in that: After transferring the transferred file to the big data platform according to the obtained distributed file system path, the method further includes: The transfer file is packaged in the big data platform and then waits for the application interface to be called.

3. The method for transferring an Excel data source to a big data platform according to claim 1, characterized in that: The step of obtaining the file version of the Excel data source includes: Get the Excel data source file name, and extract the suffix character string of the Excel file name; When the suffix string is xlsx, the file of the Excel data source is a text file of Excel 2007 or later; When the suffix character string is xls, the file of the Excel data source is a text file before Excel 2007.

4. The method for transferring an Excel data source to a big data platform according to claim 1, characterized in that: The distributed file system path of the big data platform is stored in the configuration information of the distributed file system.

5. The method for transferring an Excel data source to a big data platform according to claim 1, characterized in that: Also include before getting the distributed file system path: Check whether the big data platform is running normally. If it is running normally, obtain the distributed file system path.

6. A system for transferring Excel data sources to a big data platform, characterized in that: include: The acquisition module is used to obtain the file version of the Excel data source; A processing module, used for processing the acquired Excel data according to the file version to obtain a transfer file; A saving module, used for transferring the transferred file to the big data platform according to the obtained distributed file system path; The processing module comprises: A processing submodule, used for transferring the data in the Excel data source into a transfer file in a predetermined format based on the file version and the calling method in the Apache POI component; the suffix of the transfer file is dat; The processing submodule comprises: A first processing unit is used for parsing the Excel data source to obtain the transferred data based on the readXls method in the Apache POI component when the Excel version is a version before Excel 2007; The second processing unit is used for parsing the Excel data source to obtain the transferred data based on the readXlsx method in the ApachePOI component when the Excel version is Excel 2007 or later; A transfer unit, used for transferring the transfer data into a transfer file in a predetermined format; The transfer unit comprises: Read the data of each table in the file of the Excel data source; The data of each table in the file of the Excel data source are separated according to a preset separator, and the data of each table in the file of the Excel data source and the separator are stored as a transfer file.

7. The system for transferring an Excel data source to a big data platform according to claim 6, characterized in that: The acquisition module comprises: An extraction unit, used to obtain the Excel data source file name and extract the suffix character string of the Excel file name; A first judgment unit is used for determining that when the suffix character string is xlsx, the file of the Excel data source is a text file of Excel 2007 or later; The second judgment unit is used to determine that when the suffix character string is xls, the file of the Excel data source is a text before Excel 2007.

Images