Importing XML Data Into Excel

Web pages sometimes provide data as XML files rather than in a convenient format for Excel spreadsheets.
This video shows you how to save XML files, how to reveal their structure (the schema), how to map XML data fields to Excel column headings and how to import the mapped data into Excel.
I hope it's helpful.

[@peterstaadecker]

I had a couple of offline questions about this: 1. Why not just do a cut and paste from the screen data to Excel? A: This some times works, but not always. It depends on the source page design and the amount of data. In the case of my sample web page, it's painful: Excel treats the date column as different data types in different parts of the cut and paste and you have a ton of clean-up work to do. 2. Aren't the mapping and schema separate items? A: Correct. I should have been more precise in my video commentary. The schema describes how an XML file structures its data, the mapping tells you which part of the XML structure belongs in which column of Excel.

[@ramehharmouch5300]

Hello, I want to ask you, if i have an excel file mapped to an xml map. When i save the file as xml, the empty cells of the excel are not created in the xml file. So, how can i edit the xml to create empty xml tags for the empty elements in the excel file? Thank you in advenced.

[@peterstaadecker]

Hi Rameh - it sounds like you're going in the opposite direction: Excel to XML. Is that right? Can you do a very scaled-down concrete test case to show me and other readers the details of what you're doing and what is not working? Maybe other readers or I will have some insight then. Thanks.

[@ramehharmouch5300]

@@peterstaadecker if you create new file excel, customize the ribbon and add developer option. After that create an xml file for example (fname, minit, lastname) inside two block of xml files. From excel, navigate to developer on the xml part click import to import the created xml file that include 2 blocks of xml tags. Drag the created tree of xml into the excel lines, it appear like a table on the excel lines, and file the table with data, click export in the xmlpart on the developer tab. If you open the file, you will see you're data filled, appeared in the xml file. But when you fill the data in excel if you leave a cell of the mapped xml empty, and you export as xml. The new exported xml file not include empty tag for the field that is not filled. It is not appear totaly, which make an error if you need to import the map into another excel file. So is there any solution for that?

[@peterstaadecker]

Hi Rameh, I won't have time to play with that scenario for a while. In the meantime, perhaps one of the other readers may have an answer - throwing it open to anyone who has an answer.