XML format, along with CSV are some of the most commonly used formats when sharing, importing and exporting data between services. Both of these formats are readable by many software and services, and allow for easy further manipulation. However, if we want to perform some kind of statistical analysis on either format we need to import them into software for data analysis.
As you might have guessed, one of our favorite software for data cleaning, manipulation and analysis is Microsoft Excel. Unlike PDF, which has to be converted into Excel to be able to use the data (you can convert it with Cometdocs), XML and CSV can be imported into Excel fairly easy.
We have already shown you how to import CSV into Excel, and vice versa. Now, we are going to show you how to import XML format into Microsoft Excel.
All you need to do is go to the Data section. Choose From Other Sources – From XML Data Import.
After that what Microsoft Excel will do, is to use the internal structure of the XML file to accurately import it into rows and columns in the spreadsheet.
You will actually be surprised to see how accurate it is.
Another useful tool that we discovered is a tool which uses Excel to convert XML to CSV format. We found it to be great in working with large XML files, you should check it out as well. The tool is: http://sourceforge.net/projects/convertxmltocsv/
