Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    New Lounger
    Join Date
    Oct 2013
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Importing multiple XML files into Excel

    Hello,

    I want to import all the XML files into Excel for reports. I was doing this one XML file at a time but this is of course silly.
    Anyway a Excel VB Macro code for importing multiple XML files to a Excel sheet tab (XMLDATA) and copying the imported data in sequence into the Excel columns. All XML Files format are same. Is there a simple code for this? Browse to select the folder and import all XML files.

    Thanks in advance.

  2. #2
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,200
    Thanks
    46
    Thanked 228 Times in 211 Posts
    zmagic,

    This workbook will prompt you to navigate to a folder with XML files. It will load only XML files from the folder and sub folders to a sheet named "XMLData". It will create a header with the file name and copy the file contents to each column.

    HTH
    Maud
    Attached Files Attached Files

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    RogerG (2014-05-29)

  4. #3
    New Lounger
    Join Date
    Oct 2013
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Maudible,

    The data from the XML files are imported out of sequence into the Excel columns. Is there a simple fix to the code for this. Secondly it only imports the first file and other xml files are not imported.

    When i import the file manually it order is perfect but whereas with this code its in single line order, un-readable to create a report.

  5. #4
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,200
    Thanks
    46
    Thanked 228 Times in 211 Posts
    It reads and writes sequentially from the file to Excel. I don't see how it is possible that the lines are out of order. As far as the order of the files in the columns, they are read alphabetically from the main folder and then from each subfolder. Here is a screenshot of the results produced'

    xmlFile.png

    whereas with this code its in single line order, un-readable to create a report.
    Not sure what you mean here. Each line from the XML file is copied to the next line in the Excel file. This is the format I generate in my work and create reports from them with no issues.

  6. #5
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,200
    Thanks
    46
    Thanked 228 Times in 211 Posts
    zmagic,

    Are you looking for the file not to have a linefeed placed after the line is read? Output would look like this:

    XML Format.png

  7. #6
    New Lounger
    Join Date
    Oct 2013
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    zmagic,

    Are you looking for the file not to have a linefeed placed after the line is read? Output would look like this:

    XML Format.png
    Hi Maudibe,
    I open the file with option of 'As an XML Table'. This option set xml file in order of column and rows automatically.

  8. #7
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,200
    Thanks
    46
    Thanked 228 Times in 211 Posts
    zmagic,

    Sorry, I misunderstood. I thought you wanted to read the xml file line by line onto a spreadsheet which the initial code does. You want the file imported not to view the XML coding but to display the file in human readable form. I now understand your intent.

    By not having a sample file, this is a bit difficult to determine the schema. However, I took a stab at it and this works well with XML files I have. Give it a go.
    Attached Files Attached Files

  9. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    RogerG (2014-05-29),screwdriverga (2013-11-16)

  10. #8
    New Lounger
    Join Date
    Oct 2013
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    zmagic,
    By not having a sample file, this is a bit difficult to determine the schema. However, I took a stab at it and this works well with XML files I have. Give it a go.
    Thanks Maud, You have done it, Maud 1 help more while importing multiple xml it imports the Header Row for each file, Please delete that header rows. Not required

  11. #9
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,200
    Thanks
    46
    Thanked 228 Times in 211 Posts
    Please delete that header rows. Not required
    zmagic,

    For me to do that, I will need several samples of your XML files to determine how the header section is coded. Is that possible?

    For my work, I read XML files into Excel in the fashion of the first macro I wrote for you, line-by-line coding. From that point, I modify them to suit our needs to create customized reports. I prefer doing the modifications in Excel as opposed to Notepad or an XML editor because of the VBA routines I can write to automate the process. I was guessing you were doing something along the same lines. Sorry for the confusion.

    Maud
    Last edited by Maudibe; 2013-10-26 at 09:35.

  12. The Following User Says Thank You to Maudibe For This Useful Post:

    swatigrover (2013-11-21)

  13. #10
    New Lounger
    Join Date
    Jan 2014
    Location
    France
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    That's a great job you did. It's almost what I need.
    I would have 2 more functionalities to ask
    - use a XML DTD tu put data to the right row (because some XML files don't use all the tags)
    - Have only one header row.
    - when a XML file is read, copy it to a subfolder called for example "Archives"

    Could I send you files (.xsd and several samples) ?

    Thanks

  14. #11
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,200
    Thanks
    46
    Thanked 228 Times in 211 Posts
    Sure! Let's take a look.

    Maud

  15. #12
    New Lounger
    Join Date
    Jan 2014
    Location
    France
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Great

    The differents samples with differents formats are in the TEST folder.

    As you can see in the screenshot, datas are not always in the good row. You'll notice that when there is only 1 data in a file (ie 2.xml and 5.xml), the header is not shown.

    If you see row O, you'll also notice that when ns1:Sts=PDNG, there is not ns1:Cd. I tried to find a XML file who have all the rows (so that I can import at first) but

    Microsoft Excel - XML Files_Revised v1.1 (test DTD).xlsm.jpg

    Here is what it woull looks like

    Goal.png
    Last edited by kalimero; 2014-01-22 at 13:07.

  16. #13
    New Lounger
    Join Date
    Mar 2014
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    HI Mate,

    My scenario is , i want to import 100+ xml file in my excel 2010 via developer/import. To see all the data from individual xml sheet in 1 single lines.

    any help could be great.

    Thanks

    James

  17. #14
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,200
    Thanks
    46
    Thanked 228 Times in 211 Posts
    Guys,

    Need Sample xml files to help you. An excel spreadsheet with how you would like it to be displayed would also be vey helpful.

    Maud

  18. #15
    New Lounger
    Join Date
    May 2014
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Scope creap!

    Hi,

    The above script is awesome, very impressive(i am not a techie)! I used it and it worked nearly perfectly.

    The problem i encountered was that one single xml tag contains nearly 500,000 characters and it maybe even bigger than that. The tag was exported to excel but only the first 32759 entries were imported. To export that single tag would have taken 14 excel cells to export. Is there a way which the tag can be split down into columns each containing the first 32 k of the characters, then the next column containing the next 32k and so on to the full output has been completed.?!?

    i have an example file if needs be.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •