none
Appending two access databases in one step RRS feed

  • General discussion

  • Hi there,

    I have the following situation: on monthly base I receive two access databases with the same layout, one for each user, and use this data to do some checks.

    So I was importing this two databases individually into a query and appending them.

    But I faced some issues doing this:

    1) not every month I received two files, sometimes it is just a file for all users

    2) the name of the files change every month, so rename and change folder every time wouldn't be efficient

    I created a table with the folder path and did some coding to get the files directly, without rename/change folder.

    My intention was to get only the access databases in this folder and then appending both with #"Combined Binaries", but it didn't work.

    With that I get just one database, not both..

    • Any insights on how to do the steps to get the database whether it is one or two files, without have to do two versions (for 1 and 2 DB)?

    Thanks!

    let

        Source = Excel.CurrentWorkbook(){[Name="Folder"]}[Content],

        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Folder", type text}}),

        Folder = #"Changed Type"{0}[Folder],

        Custom1 = Folder.Files(Folder),

        #"Filtered Rows" = Table.SelectRows(Custom1, each ([Extension] = ".accdb")),

        #"Combined Binaries" = Binary.Combine(#"Filtered Rows"[Content]),

        #"Imported Access" = Access.Database(#"Combined Binaries"),

        _Reconciliacao = #"Imported Access"{[Schema="",Item="Reconciliacao"]}[Data],

    Thursday, April 19, 2018 1:11 AM

All replies

  • Hey,

    I modified your existing code and I believe this should work for your specific case:

    let
        Source = Excel.CurrentWorkbook(){[Name="Folder"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Folder", type text}}),
        Folder = #"Changed Type"{0}[Folder],
        Custom1 = Folder.Files(Folder),
        #"Filtered Rows" = Table.SelectRows(Custom1, each ([Extension] = ".accdb")),
        Miguel1 = Table.AddColumn( #"Filtered Rows", "Data", each Access.Database([Content]){[Item="Reconciliacao"]}[Data]) 
        in
            Miguel1
    The new step is the Miguel1 

    Thursday, April 19, 2018 3:36 PM