Analysis of Web site data with Excel parsing XML documents

Intermediary transaction http://www.aliyun.com/zixun/aggregation/6858.html ">seo diagnose Taobao guest cloud host technology Hall

More Web sites choose to use XML as a data exchange document because XML works across platforms, including two aspects of fetching data from XML documents and translating data into XML documents. Excel can do this by creating a map that can either get data from XML and display it in Excel, or you can transform the data in the worksheet into an XML document by mapping. This greatly facilitates the webmaster to the station some data management, statistics and analysis. Next, I'll share with you the details of how Excel processes XML documents in the form of detailed explanations.

Data analysis typically does not view data directly from an XML file, but instead exports it through a Third-party advanced application or platform, and then transforms it into a table to read the XML file data. In fact, in addition to professional software, everyone commonly used and easy Excel can complete the reading process well.

(1) Open XML document

Open an XML document using the OpenXML method of the workbook, which produces a mapping when the workbook opens or imports an XML document. For example, use the VBA code in Excel to open the site registration user information. xml file located under D disk in Excel 2007 and generate a mapping.

The results of the operation are as follows:

Such Excel data table is convenient for the webmaster to sort by time, according to the number of landing times, the number of recently registered users, such as statistical analysis, and can easily use column charts and other images of the contrast. This is advantageous for the stationmaster to analyze the website registration user's information more carefully and carries on the screening analysis.

(2) Append Data

In general, the import data is to delete the original data before writing new data, in order to avoid deleting the original data, you need to change the mapping properties. For example, use the VBA code in Excel below to append new data. First put a button on the worksheet, named "Import new Registered user data",

When the user clicks the button, an open dialog box appears, an XML file is selected, and if the worksheet mapping does not exist, a map of the site registration user information is automatically created and the file content is imported, otherwise the contents of the new file are appended to the worksheet.

The VBA code for Excel is as follows

Click the "Import new registered User Data" button to repeat the import of the "Site registration user information. xml" document, the results of the run are as follows:

To sum up, the use of Excel VBA to import XML document data, with Excel tables for statistical analysis, more can let webmaster handy analysis of web site operation related data, always master the relevant indicators of the operation of the website.

This article by the ancient city official website http://www.nakzen.com original, starts in A5, the reprint time please keep the author link, thanks.