1

I have multiple XML files which are available as records in database. I can export the records as CSV,XML,text file. I need to convert all the XML files into single Excel file.

OS : Windows 7

Any help is greatly appreciated.

EDIT : I've installed power query add-in for EXCEL which might possibly help me to do my task (as @mike suggested). The input would be something like this(XML file containing XML data as rows) :

enter image description here

  • 1
    Write a small XML parser program to convert the .XML files into a concatenated .CVS file. – Ramhound Mar 17 '16 at 12:37
  • Can you please help me out ? @Ramhound – learner1 Mar 17 '16 at 15:39
  • help you write a program, I have the capability to write such a program, I just don't have the time to do so. – Ramhound Mar 17 '16 at 17:51
  • I've written a parser code that worked pretty fine @Ramhound. Thanks for the suggestion. – learner1 Mar 29 '16 at 5:30
2

I would use the Power Query Add-In for this. Power Query is a free Add-In from Microsoft for Excel 2010 and 2013, and is built into Excel 2016 on the Data ribbon, under Get & Transform.

The easiest way would be to export the CSV files to a folder. Then click New Query / From File / From Folder. Power Query will automatically detect column headings, data types etc and append the data from all the files in the folder into a single Excel Table.

No code is needed.

  • I've understood what you said and I got power query add-in to my Excel. But the thing is that I can export all XML(datatype) records from database to a single CSV/text/XML. And exporting all the XML records to single file and importing it into EXCEL is not working with power query add-in. Can you please help me out ? @Mike – learner1 Mar 29 '16 at 5:38
  • Possibly, but I'll need more info than "is not working". – Mike Honey Mar 29 '16 at 23:17
  • Please refer the edit in the question. And please let me know what should be the next step if I have input like that ? – learner1 Mar 30 '16 at 5:14
  • That file doesnt look like standard XML to me. It looks like an initial line of text, then XML documents on each line? Power Query can still handle that with a bit of fiddling. I would Import it as a text file, then edit the query. I would remove the first row, then use Transform / Text Columns / Parse / XML. This results in a table on each row. You can expand the Tables manually or expand all using a PQ function e.g. gist.github.com/Mike-Honey/f5f3f708310eb1de7f4a – Mike Honey Mar 30 '16 at 23:19
  • What is the Transform / Text Columns / Parse / XML exactly means ? @Mike – learner1 Mar 31 '16 at 6:28
0

Open the folder with the xml files, create new column merging Folder Path & Name ==> Path, remove the other columns, add new column with the code Xml.Tables(File.Contents([Path])), keep expanding. That's it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.