Forum Discussion
File names from folders into rows and columns
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 (https://ask.libreoffice.org/en/question/252930/file-names-from-folders-into-rows-and-columns/?answer=253074#post-id-253074 is Macro, which works for me in LO):
- I have a lot of folders that contain 1 or more images.
- If the folder contains 1 image, I need it to be on a new line in Excel.
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.
3 Replies
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.
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.
- If the steps above are saved into a workbook, you can set the steps to automatically refresh the output when you open the file. Go to Query tab, then Properties to configure the options.
