Jul 17 2020 02:07 AM
Hi, please, in Libreoffice Calc I use the function of importing a list of files into the rows / columns of a Calc table, via Macro and based on certain criteria, which I describe below. Now I would need to do the same in Excel (here is Macro, which works for me in LO):
If the folder contains more images, the first being on a new line and the second either:
- next to the first in the same row (in one column), separated by the character | (example: photo 1.jpeg | photo 2.jpeg)
- or to have their names in the columns (even without the | character) (the first option would be better, but the second is enough as well)
These are the product images whose names I need for import into Woocommerce - via a csv file.
Jul 17 2020 04:12 AM
You can use PowerQuery for this.
Use Data>Get & Transform Data>Get Data>From File>From Folder
Select the root folder that contains all your other folders and click OK, then Transform.
I removed all the columns I didn't need so I was just left with the Folder Path and the Name. I then right-clicked on Folder Path and used Replace Values to remove the absolute path leading up to the root folder on my system, so what remained was just the root folder and any sub-folders:
After this, I use Home>Transform>Group By and configure it like this:
Understandably, trying to sum a text column will give an error:
Use Home>Advanced Editor, I locate the line in the query that describes the sum transformation:
#"Grouped Rows" = Table.Group(#"Reordered Columns", {"Folder Path"}, {{"PipeSeparatedFileList", each List.Sum([Name]), type text}})
Now I change this:
List.Sum([Name])
To this:
Text.Combine([Name],"|")
Then click OK on the Advanced Editor and I have the desired result:
After this, you can use Home>Close&Load to put the data back in the workbook.
Jul 18 2020 08:45 AM - edited Jul 18 2020 08:48 AM
Yes it works, thank you. :) Couldn't it be automated somehow? Compared to the Libreoffice solution, this is complicated. In LO, I have created a button on the toolbar for this particular macro, and after pressing it, I select a folder, and the list of images is immediately inserted into a new document.
Jul 18 2020 09:14 AM