Forum Discussion

rachel's avatar
rachel
Steel Contributor
Nov 03, 2023

Merge multiple excel files into different tabs of one workbook using Power Query on Mac

Hello,

 

I have 33 excel files in one folder. all of them have data in "Sheet1". the data are in the same position in each file.

 

I am trying combine those data from different files into one workbook, but in different tabs .

 

Is power query the correct and easiest approach?

If not, what is the most painless way to do this?

 

Sample files in a "Data folder":

 

Expected results is in file "expected_results.xlsx".

 

Thanks a lot in advance!! 

Rachel

 

 

 

excel
Excel on Mac
office 365
  • mathetes's avatar
    mathetes
    Silver Contributor
    Nov 04, 2023

    rachel 

     

    You've had over 60 views and no replies. Not sure exactly why that is, but it may be that not a one of us is sure how exactly to help.

     

    I'd like to ask, if you are willing to answer, that you tell us a bit more what kind of data are in each of these 33 separate sheets. I ask because it's often the case that people think they're making things clearer, easier to understand, by breaking common or recurring data sets--you do note that they all are arranged the same--into separate sheets. In fact, although that may be clearer to the human, it interferes with Excel's remarkable abilities to store and parse data. For example, if each of these is for a different product line, or a different month, or a different store....assembling all that data into a single sheet would enable you to still pull out the data for the single product line, or month, or store (or whatever), AND to do all kinds of high level summaries as well, very easily.

     

    So before trying to solve the problem of importing each sheet into its own tab in a single workbook, let's talk about another possible way to assemble the data.....if you're willing.

     

    If the data are not confidential, maybe you could help us help you even more effectively by posting a copy of several of these sheets on OneDrive or GoogleDrive, with a link here that grants access.

    • rachel's avatar
      rachel
      Steel Contributor
      Nov 05, 2023

      mathetes 

       

      Hi,

       

      Below is a link for sample files.


      https://hkustconnect-my.sharepoint.com/:f:/g/personal/rwuad_connect_ust_hk/ElHCwxsCo2JIlB6b4ioNZXIBcVF-SePrDsC5G1RA80XEpQ?e=hhOszd

       

      Those 3files are the raw data. 

       Those two are the formats that I need to transform my separate 3 files into.

       

      I need the data to be arranged into "expected_results1.xlsx" or "expected_results1.xlsx" because we have a tool (written in csharp) that accepts those two formats and uploads the data into our internal database.

       

      I ended up solving this problem by updating the csharp code in our tool to accommodate multiple files in a one folder.

       

      However, I am still posting the question here because I am sure this sort of issue will come up again in the near future.

      Those files are sent by our clients, and there is no way to ensure that they will send us data in a consistent format.

      Instead of updating csharp script every time they send us files in a different format, I am trying to find out an easier way to transform excel files into a certain format that our csharp tool can process.

       

      The reason I ask about Power Query is that I came across below youtube video showing that I can use "Get Data"->"From Folder" in power query for this sort of situation:

      https://www.youtube.com/watch?v=EpVQyXD5bV4

       

       

       

      However, in my Office365 on mac, I see no such thing as "Get Data from Folder":

       

      • peiyezhu's avatar
        peiyezhu
        Bronze Contributor
        Nov 05, 2023

        rachel 

        Re:there is no way to ensure that they will send us data in a consistent format.

        As to result2.xlsx.

        If I were you,I would like regular expression and sql to do this kind of ETL(extract transform and load).

         

        select regexp2('^.*(?=\.)',f01) f01,f03,f04 from consolidateSheet where regexp('^\d+$',f04)

         

        https://b23.tv/evB7Lp1

  • rachel's avatar
    rachel
    Steel Contributor
    Apr 13, 2024

    rachel 

    Just for Mac users who would like to use Power Query, a legend came across this post and told me that there is indeed a work around to use "Get Files From Folder" on Mac.

     

    Here is the youtube video:

    https://www.youtube.com/watch?v=chBlyDrejHo&t=318s

     

    Turns out we just need to grant Excel access to the folder we want to use as source on Mac. Just one line of VBA code can do the trick.

     

    After granting Excel access to "Documents/dev.nosync/MyTest/DataFolder" using VBA, below code would work:

    Source = Folder.Files("/Users/rachel.wu/Documents/dev.nosync/MyTest/DataFolder"),

     

    I also re-attached the sample files and expected result to the original post, just in case some genius still want to comment on it.