none
multiple xml files in folder from power query RRS feed

  • Question

  • I'm trying to read in multiple xml files from a folder.  The combine function works and if I view the text output from the combined rows then you can see the files have been appended one after the other. 

    let
        Source = Folder.Files("C:\Users\Tim\Data\Education\Collect\XML files January 2014"),
        CombinedBinaries = Binary.Combine(Source[Content]),
        ImportedText = Table.FromColumns({Lines.FromBinary(CombinedBinaries,null,null,1252)})
    in
        ImportedText

    When I try to create the xml tables (= Xml.Tables(CombinedBinaries)), I only see one row?

    Can anyone help..

    Thanks,

    Tim


    Thank you for you time folks!

    Friday, June 13, 2014 3:41 PM

Answers

All replies

  • When you concatenate multiple XML files, the result isn't a valid XML file any more. You'll need to process each file separately and then combine the results. You can see a blog post which does something similar for Excel files at http://dutchdatadude.com/combining-excel-files-using-power-query-for-excel/

    If you still have trouble implementing this after reading the blog post, feel free to reply to this thread for more help.

    Friday, June 13, 2014 4:37 PM
  • Thanks Curt,

    just for others....  My Function for reading XML:-

    let XmlImportedFile = (FilePath, FileName) =>
    
        let
        	Source = Folder.Files(FilePath),
    	File = Source{[#"Folder Path"=FilePath,Name=FileName]}[Content],
        	ImportedXML = Xml.Tables(File)
        in
        ImportedXML
    in
        XmlImportedFile

    then it's use to combine into a custom Column:-

    let
        Source = Folder.Files("C:\Users\Tim\Data\Education\Collect\XML files January 2014"),
        InsertedCustom = Table.AddColumn(Source, "XmlImportedFiles", each XmlImportedFile([Folder Path],[Name])),
        RemovedColumns = Table.RemoveColumns(InsertedCustom,{"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
        #"Expand XmlImportedFiles" = Table.ExpandTableColumn(RemovedColumns, "XmlImportedFiles", {"Name", "Header", "School", "Pupils"}, {"XmlImportedFiles.Name", "XmlImportedFiles.Header", "XmlImportedFiles.School", "XmlImportedFiles.Pupils"})
    in
        #"Expand XmlImportedFiles"
    thanks again and great blog post :-)


    Thank you for you time folks!

    Monday, June 16, 2014 12:14 PM
  • Thanks for the great info. When I try to do this I get nulls in all my columns after I expand the custom column, how do I debug why that happens?

    Adriaan Davel

    Wednesday, July 15, 2015 11:25 AM
  • Did you replace the column names in the last step by the ones in your table?

    I think it's best to do this last step manually.

    So I'd suggest you delete the last step and click again on the expand arrows: Then your column names will be shown & you can select which ones to take.


    Imke

    Wednesday, July 15, 2015 1:33 PM
    Moderator