none
New Query - From Folder, XML fails as multiple roots RRS feed

  • Question

  • Hi

    I am trying to use the from folder feature in power query in 2016.

    The goal is to be able to add multiple XML  documents into the folder and have them processed as they arrive.

    Opening one file works, however when I add a second file to the folder or try to start with 2 .xml files in the folder I receive an erro that there are multiple roots.

    DataFormat.Error: Xml processing failed. Either the input is invalid or it isn't supported. (Internal error: There are multiple root elements. Line 157, position 2.)
    Details:
        Binary


    There are multiple roots each XML file has 1.

    But how do I get this to work. this is the query it attempts to run.

    = Xml.Tables(#"Combined Binaries")

    Also tried to convert the binary to content but this also fails.

    = Table.AddColumn(#"Removed Other Columns", "Custom", each Xml.Document([[Content]]))



    This ends up being the script

    let
        Source = Folder.Files("C:\Users\sayth\Repos\test"),
        #"Expanded Attributes" = Table.ExpandRecordColumn(Source, "Attributes", {"Archive", "Compressed", "Content Type", "Device", "Directory", "Encrypted", "Hidden", "Kind", "Normal", "NotContentIndexed", "Offline", "ReadOnly", "ReparsePoint", "Size", "SparseFile", "System", "Temporary"}, {"Attributes.Archive", "Attributes.Compressed", "Attributes.Content Type", "Attributes.Device", "Attributes.Directory", "Attributes.Encrypted", "Attributes.Hidden", "Attributes.Kind", "Attributes.Normal", "Attributes.NotContentIndexed", "Attributes.Offline", "Attributes.ReadOnly", "Attributes.ReparsePoint", "Attributes.Size", "Attributes.SparseFile", "Attributes.System", "Attributes.Temporary"}),
        #"Combined Binaries" = Binary.Combine(#"Expanded Attributes"[Content]),
        #"Imported XML" = Xml.Tables(#"Combined Binaries")
    in
        #"Imported XML"


    Update 22/10

    Close but still receive error about xml combination. Looking in the msdn docs too  cannot see an append option or similar option to replace combine  with MSDN Power Query Documentation

    have created an import function GetFiles.

    let GetMyFiles=(FilePath, FileName) =>
    
    let
        Source = Xml.Tables(File.Contents(FilePath&FileName)),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute:id", Int64.Type}, {"Attribute:barriertrial", Int64.Type}, {"Attribute:venue", type text}, {"Attribute:date", type datetime}, {"Attribute:gearchanges", Int64.Type}, {"Attribute:stewardsreport", Int64.Type}, {"Attribute:gearlist", Int64.Type}, {"Attribute:racebook", Int64.Type}, {"Attribute:postracestewards", Int64.Type}, {"Attribute:meetingtype", type text}, {"Attribute:rail", type text}, {"Attribute:weather", type text}, {"Attribute:trackcondition", type text}, {"Attribute:nomsdeadline", type datetime}, {"Attribute:weightsdeadline", type datetime}, {"Attribute:acceptdeadline", type datetime}, {"Attribute:jockeydeadline", type datetime}}),
        #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"club", "race", "Attribute:id", "Attribute:venue", "Attribute:date"}),
        #"Expanded club" = Table.ExpandTableColumn(#"Removed Other Columns", "club", {"Attribute:abbrevname"}, {"club.Attribute:abbrevname"}),
        #"Expanded race" = Table.ExpandTableColumn(#"Expanded club", "race", {"nomination", "Attribute:id", "Attribute:name"}, {"race.nomination", "race.Attribute:id", "race.Attribute:name"}),
        #"Expanded race.nomination" = Table.ExpandTableColumn(#"Expanded race", "race.nomination", {"Attribute:age", "Attribute:career", "Attribute:horse"}, {"race.nomination.Attribute:age", "race.nomination.Attribute:career", "race.nomination.Attribute:horse"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded race.nomination",{"Attribute:id", "Attribute:venue", "Attribute:date", "race.Attribute:name", "club.Attribute:abbrevname"})
    in
        #"Removed Columns"
    in
        GetMyFiles

    and a query but it still crashes attempting to import xml. 

    let
        Source = Folder.Files("C:\Users\sayth\Repos\test"),
        #"Added Custom" = Table.AddColumn(Source, "Custom", each FunGetFiles([Folder Path],[Name])),
        #"Combined Binaries" = Binary.Combine(#"Added Custom"[Content])
    in
        #"Combined Binaries"





    • Edited by SaythJ Thursday, October 22, 2015 12:39 AM
    Wednesday, October 21, 2015 5:53 AM

Answers

  • Instead of combining the files and then processing, try doing Xml.Tables on each file individually, and then appending the resulting tables.

    For example:

    let
        Source = Folder.Files("C:\Users\sayth\Repos\test"),
        #"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".xml"),
        #"Added Custom" = Table.AddColumn(#"Filtered Rows", "XmlTables", each Xml.Tables([Content])),
        Append = Table.Combine(#"Added Custom"[XmlTables])
    in
        Append

    If you want to do a further appending/combining of the inner tables, then add the following step:

    = Table.Combine(Append[Table])

    Ehren



    Wednesday, October 21, 2015 5:48 PM
    Owner

All replies

  • Instead of combining the files and then processing, try doing Xml.Tables on each file individually, and then appending the resulting tables.

    For example:

    let
        Source = Folder.Files("C:\Users\sayth\Repos\test"),
        #"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".xml"),
        #"Added Custom" = Table.AddColumn(#"Filtered Rows", "XmlTables", each Xml.Tables([Content])),
        Append = Table.Combine(#"Added Custom"[XmlTables])
    in
        Append

    If you want to do a further appending/combining of the inner tables, then add the following step:

    = Table.Combine(Append[Table])

    Ehren



    Wednesday, October 21, 2015 5:48 PM
    Owner
  • So I would need to create a basic function first for file and folder.

    let GetMyFiles=(FilePath, FileName) =>
    
    ...
    
    in GetMyFiles
    From http://datapigtechnologies.com/blog/index.php/using-power-query-to-combine-data-from-multiple-excel-files-into-one-table/

    then put an append operation which is just "combine".





    • Edited by SaythJ Wednesday, October 21, 2015 8:28 PM
    Wednesday, October 21, 2015 8:23 PM
  • I've updated my response above to include an example. Hope that helps.

    Ehren

    Thursday, October 22, 2015 5:47 PM
    Owner