How To Open Xml File In Excel 2010
Import XML Files into Excel
XML stands for eXtensible Markup Language and is a mutual information storage and manual format. Information technology'due south widely used beyond the web and in many apps and software.
Information technology's a great information structure for computers to read, but it'southward a bit less accessible for a human being to read.
In this mail we'll accept a look at how to import an XML file into Excel and turn it into a more friendly table format. Excel has support for XML files and information technology'south really really piece of cake to import and covert them to tables using power query.
You can download the case files used in this postal service here.
Video Tutorial
XML File Examples
Yous don't need to look very far to find an instance, Excel files are really merely a drove of XML files in a zip binder.
Endeavor information technology for yourself by taking any Excel file and changing the extension from .xlsx to .cypher then exploring the files in the zip folder. Yous'll meet a ton of XML files. Y'all can open up whatever of the XML files in your text editor of choice to view the inner workings of an Excel file.
Another common example can exist constitute beyond the spider web. Most websites accept a sitemap.xml file which is an XML file of the website'southward link structure. This is literally a map of the website for bots like Google to crawl and index.
Check out the XML sitemap on i of my favourite Excel sites, https://www.vertex42.com/sitemaps/pages.xml. It lists all the pages on the site and includes information about when the page was last updated, how often the folio is updated and how much weight search engines should give that folio.
Create an XML Query
In this example we will use a uncomplicated XML file containing mock contact information with names, emails, cities and countries.
Nosotros tin apace create a new query that imports the XML file into Excel.
- Become to the Information tab in the ribbon.
- Select Get Data found in the Get & Transform Data section.
- Choose From File.
- Choose From XML.
This will open up a file pick window and nosotros can navigate to the location of the XML file and printing the Import push.
Expand Fields in the Query Editor
The Navigator window will at present open and nosotros can run into a preview of the data from the XML file. Notice that it'due south in a nice table format? That's automatically done past ability query.
Since the Proper name and Email fields in the XML data comprise sub-fields we see Table listed underneath the column headings in the preview. For example, the Name field contains a First and Last field for the starting time and last names of the person. If our data had a simple structure without any sub-fields, we could skip the query editor and press the Load button to direct load the data into the workbook.
To expand these tables out in the power query editor, press the Transform Data button.
In the query editor, click on the expand column toggle for the Name column which contains the Table elements. Choose the Aggrandize option and press the OK button.
Repeat the process for the E-mail column as well. Depending on the information structure, nosotros may need to do this several times.
Load the Data into a Table in the Workbook
At present our data is fix to be loaded into a table in the workbook.
Get to the Home tab in the query editor and printing the the Close & Load button to load the information into a table in the workbook.
Conclusions
XML data is a very common format. Yous're likely to come up across it sooner or later if you work with data.
It can exist hard to interpret what'due south going on in an XML file, but luckily Excel can handle this type of data with ease.
In a few clicks, we tin utilise power query to go our XML data into a squeamish tabular array format.
Nearly the Author
John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Ability BI, Ability Automate, Ability Apps and SharePoint. You can observe other interesting articles from John on his web log or YouTube channel.
Source: https://www.howtoexcel.org/import-xml-files/
Posted by: bookercantences88.blogspot.com
0 Response to "How To Open Xml File In Excel 2010"
Post a Comment