Is it possible to build a macro to import similar, but different xml/text files?

[alparsons672]

Hello. I am trying to build a macro to import data from an external file that is generated with another program. I have two options for my source file; an .xml file and a .txt file. Both contain essentially the same data that I want to manipulate into my end product. My plan is to be able to copy a workbook with this macro into the directory of an individual project and then have it pull the data pertaining only to that project. Each project will have its own workbook and source files; I am not trying to make a single workbook to collect all of the data from each individual project.

The problem I am running into is that each source file (pertaining to each individual project) is slightly different. Attached is my attempt at showing the problem I am working with. The columns represent the raw data if imported from the .txt file. The table is what I want my end product to be.

There are some rules certain data follows that I figured I could exploit. In the blue dataset each "Group" has an entry for "AnimalsMice" even though only one group has a value for the amount of mice in that group. For data pertaining to passengers ("People" and "Animals") this will always be the case. I believe I am fortunate because this is the data that I want.

There is some data present that I know I do not need and would like to simply delete. The "Small Bus", "Plane", and "Candy" entries are part of a much more finite set of data that I think I could hard code removal into.
For instance, I know the transportation, if present, will only ever be "Small Bus", "Plane", or "Large Bus". The snacks will only ever be "Candy" or "Chips". Hopefully this makes sense.

Each project will have very similar data categories, but are not guaranteed to be exactly the same. For instance, the yellow dataset also has an additional "Group". With my real data, I could have anywhere from a few dozen groups to a few hundred.
My largest problem has been the variation in categories of data. In the yellow dataset, each "Group" has an entry for the amount of "AnimalsBirds" while the blue dataset does not. In my real data, there might be about 30-50 total categories/columns that could be present. This is what has caused problems with importing the data in the .xml format. If I record a macro and import my xml file the end code looks something like this:

Sub Import_XML()
' Import_XML Macro
    ActiveWorkbook.Queries.Add Name:="SECTION", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Xml.Tables(File.Contents(""file location here""))," & Chr(13) & "" & Chr(10) & "    Table0 = Source{0}[Table]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Table0,{{""Group"", type text}, {""Id"", Int64.Type}, {""Snack"", type text}, {""PeopleBoys"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    'I removed most of the categories here in order to make it simpler to read, I assume these are generated based on what is read from the source file during the query
    'The actual amount of column types gets quite high
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=SECTION;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [SECTION]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "SECTION"
        .Refresh BackgroundQuery:=False
    End With
End Sub

I hope this makes sense. I am having a difficult time because I am not super experienced with making macros or working in VBA. I know what my data looks like and what I want it to look like in the end, but I am having a hard time figuring out the middle part. Most of the time I have been able to get by using the macro recorder and then editing the parts I understand to accomplish my goals. This is a little bit more of a beast than I am used to.