Forum Discussion
PaddyB
Dec 25, 2021Brass Contributor
Import Excel and Sort by eMail-Domain
Hi all merry XMAS to you all I use the time to get some "long term wish" fixed but no luck at all - excel is not my "world" 🙂 my problem ist I get every month a CSV from one of our "Saa...
Yea_So
Dec 28, 2021Bronze Contributor
I would go with what Mr Van_Eekelen suggested because you set up power query once and it will repeat the process just by pressing the update all button, so if you setup a query by folder all you have to do is drop the new file into a folder then open the pq workbook and press ctrl+alt+f5 and it will update the workbood with the new data.
bosinander
Dec 29, 2021Steel Contributor
Yea_So IMHO Power Query (PQ) is a nice alternative when connecting to slow changing data since it demands a specific update event in Excel.
PQ also needs a completly different set of skills compared to worksheets but it may pay back by providing a bunch of nice features like unpivot.
To get the transformed data, in this case email addresses sliced and separated by domain names to different sheets, PQ need to have a separate question for each domain. They can all be based on the same connection to the source file, but still need to be splitted into one separate 'question' for XXX.ch, another for XXX.de and a third for XXX.com since the wish is to have the result load to different sheets.
With PQ alone it is needed to prepare a sheet for each domain and manually manage (append a new question and a new sheet) whenever a new domain appears.
I do not see any smooth way around that except using a macro to loop through the months domains and setup a sheet for each one found. And macros seem to not be an alternative for this user.
Loading the csv file via the menu Data and command From Text/CSV and then use a worksheet formula like NowshadAhmeds ought to be a pretty good solution as above Dec 26 2021 10:24.
- Yea_SoDec 29, 2021Bronze Contributor
yes it requires a one time query setup for each domain/sheet but after the initial query setup, the task becomes automatic just by pressing ctrl+alt+F5 to update all queries setup within the workbook so the cost benefit more than justifies the initial setup whereas with VBA the user might not be familiar with it and make maintaining more complicated as you have mentioned:
"To get the transformed data, in this case email addresses sliced and separated by domain names to different sheets, PQ need to have a separate question for each domain. They can all be based on the same connection to the source file, but still need to be splitted into one separate 'question' for XXX.ch, another for XXX.de and a third for XXX.com since the wish is to have the result load to different sheets.
With PQ alone it is needed to prepare a sheet for each domain and manually manage (append a new question and a new sheet) whenever a new domain appears."
if the user is not familiar with VBA how would the user maintain to update the code to add the VBA statements for the new domains?
- NowshadAhmedDec 29, 2021Iron ContributorIn vba, a statement would generally check if there is a sheet by the domain name, and then create one if missing.
Another statement would lump the email addresses of a domain to the respective sheet.
This way, the user won't have to worry about the code for new domains because the codes are general and not specific to domain names.
Then assign a button to initiate the task and do everything automated.
However, for the current task the user needs, vba and power query is over engineering in my opinion. A simple formatted/structured table is more than enough, assuming it is only list of domain names. Here the formula for spliting the domain names will also help him filter effectively.- Yea_SoDec 29, 2021Bronze Contributor
How is creating one power query against a CSV file, then creating a reference query filtering for each domain sheet over engineering?
Come now iterate the procedures of updating each domain sheet every time there is a CSV update.
With power query by folder, after the
1. initial setup of each domain sheet
2. all the op has to do is drop the new CSV file into the queried folder
3. then press Ctrl+alt+F5 and it updates the main query and all the reference queries.
4. and if there are new domain sheets to setup just create new reference queries for the new domains, filter the appropriate domain within the reference query then close and load to a new sheet and save the workbook so the next time there is another CSV update all the op has to do is steps 1 thru 3.
Now iterate the procedure involved using formatted structured table to update each domain sheet below and let the op decide which solution they would prefer to use.