Forum Discussion
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
Here is the variant, but made on Windows
17 Replies
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.With the help of TTodorov , I finally figured out the correct buttons to press in power query UI and find a painless way to get this done. (Many thanks again for offering a long-term solution for this nuisance!)
Mainly, first follow below to hack the source folder on mac
https://www.youtube.com/watch?v=chBlyDrejHo&t=318s
Then follow below to select the columns I need based on an external config:
https://www.youtube.com/watch?v=-owZ7G880Jc
Come up with something like the attached.
Still struggling to figure out how to write ForEach loop. (and I suspect my Mac is partially to blame here). so I will just live with pressing buttons for now.
After examining each line of code you sent me more carefully, I finally figured out why it doesn't work on my Mac.
The reason is quite stupid, and it is exactly what you suspected initially: that is, the external table "ColumnsToLoad" doesn't work in a for loop on Mac. (or maybe, on a Mac, loading an external table and using it as a global variable is just different from Windows, I have no ideas...).
I had to hard code a table in Advanced Editor to get your code to work:
ConfigTable = #table(type table [ColumnsToLoad = text, DataType = text], {{"ETF Ticker", "text"}, {"Name", "text"},{"Ticker", "text"}, {"Identifier", "text"}, {"SEDOL", "text"}, {"Weight", "number"}, {"Sector", "text"}, {"Shares Held", "number"}, {"Local Currency", "text"}}),I attached the spreadsheet to demonstrate the problem.
In the Query "Transform Sample File", Table.NestedJoin works just fine:
But if I copy over the code to the custom function "TransfromFile", and then invoke that "TransformFile" in my main query, I get below error:
I had to write the custom function "TransfromFile" like below. that is, I just cannot use the table "ColumnsToLoad" that sits in Excel Sheet1.
Hopefully there is a way around this. because hard-coding excel table in M code is just pathetic!
- I find an online tool that does exactly what I need.
Thanks all!!
https://products.groupdocs.app/merger/excel- Good solution for result1👍
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.
Hi,
Below is a link for sample files.
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":
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)
