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

Improve this question
4
  • 1
    Write a small XML parser program to convert the .XML files into a concatenated .CVS file.
    – Ramhound
    Mar 17, 2016 at 12:37
  • Can you please help me out ? @Ramhound
    – learner1
    Mar 17, 2016 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, 2016 at 17:51
  • I've written a parser code that worked pretty fine @Ramhound. Thanks for the suggestion.
    – learner1
    Mar 29, 2016 at 5:30

2 Answers 2

Reset to default
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.

Improve this answer
6
  • 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, 2016 at 5:38
  • Possibly, but I'll need more info than "is not working".
    – Mike Honey
    Mar 29, 2016 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, 2016 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, 2016 at 23:19
  • What is the Transform / Text Columns / Parse / XML exactly means ? @Mike
    – learner1
    Mar 31, 2016 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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .