File names from folders into rows and columns

%3CLINGO-SUB%20id%3D%22lingo-sub-1528636%22%20slang%3D%22en-US%22%3EFile%20names%20from%20folders%20into%20rows%20and%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1528636%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20please%2C%20in%20Libreoffice%20Calc%20I%20use%20the%20function%20of%20importing%20a%20list%20of%20files%20into%20the%20rows%20%2F%20columns%20of%20a%20Calc%20table%2C%20via%20Macro%20and%20based%20on%20certain%20criteria%2C%20which%20I%20describe%20below.%20Now%20I%20would%20need%20to%20do%20the%20same%20in%20Excel%20(%3CA%20href%3D%22https%3A%2F%2Fask.libreoffice.org%2Fen%2Fquestion%2F252930%2Ffile-names-from-folders-into-rows-and-columns%2F%3Fanswer%3D253074%23post-id-253074%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E%20is%20Macro%2C%20which%20works%20for%20me%20in%20LO)%3A%3C%2FP%3E%3CUL%3E%3CLI%3EI%20have%20a%20lot%20of%20folders%20that%20contain%201%20or%20more%20images.%3C%2FLI%3E%3CLI%3EIf%20the%20folder%20contains%201%20image%2C%20I%20need%20it%20to%20be%20on%20a%20new%20line%20in%20Excel.%3C%2FLI%3E%3CLI%3E%3CP%3EIf%20the%20folder%20contains%20more%20images%2C%20the%20first%20being%20on%20a%20new%20line%20and%20the%20second%20either%3A%3C%2FP%3E%3CP%3E-%20next%20to%20the%20first%20in%20the%20same%20row%20(in%20one%20column)%2C%20separated%20by%20the%20character%20%7C%20(example%3A%20photo%201.jpeg%20%7C%20photo%202.jpeg)%3C%2FP%3E%3CP%3E-%20or%20to%20have%20their%20names%20in%20the%20columns%20(even%20without%20the%20%7C%20character)%20(the%20first%20option%20would%20be%20better%2C%20but%20the%20second%20is%20enough%20as%20well)%3C%2FP%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3EThese%20are%20the%20product%20images%20whose%20names%20I%20need%20for%20import%20into%20Woocommerce%20-%20via%20a%20csv%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1528636%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1528895%22%20slang%3D%22en-US%22%3ERe%3A%20File%20names%20from%20folders%20into%20rows%20and%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1528895%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F729591%22%20target%3D%22_blank%22%3E%40temporalnaut%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20PowerQuery%20for%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20Data%26gt%3BGet%20%26amp%3B%20Transform%20Data%26gt%3BGet%20Data%26gt%3BFrom%20File%26gt%3BFrom%20Folder%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22OwenPrice_0-1594983760106.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206026i42D4A2F5F6759C6F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22OwenPrice_0-1594983760106.png%22%20alt%3D%22OwenPrice_0-1594983760106.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESelect%20the%20root%20folder%20that%20contains%20all%20your%20other%20folders%20and%20click%20OK%2C%20then%20Transform.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20removed%20all%20the%20columns%20I%20didn't%20need%20so%20I%20was%20just%20left%20with%20the%20Folder%20Path%20and%20the%20Name.%20I%20then%20right-clicked%20on%20Folder%20Path%20and%20used%20Replace%20Values%20to%20remove%20the%20absolute%20path%20leading%20up%20to%20the%20root%20folder%20on%20my%20system%2C%20so%20what%20remained%20was%20just%20the%20root%20folder%20and%20any%20sub-folders%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22OwenPrice_1-1594983902409.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206027i316ABFA764529704%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22OwenPrice_1-1594983902409.png%22%20alt%3D%22OwenPrice_1-1594983902409.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20this%2C%20I%20use%20Home%26gt%3BTransform%26gt%3BGroup%20By%20and%20configure%20it%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22OwenPrice_2-1594984034944.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206028iB398F9CAB8EB4217%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22OwenPrice_2-1594984034944.png%22%20alt%3D%22OwenPrice_2-1594984034944.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnderstandably%2C%20trying%20to%20sum%20a%20text%20column%20will%20give%20an%20error%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22OwenPrice_3-1594984061291.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206029iC260BF1A9ABC4AC9%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22OwenPrice_3-1594984061291.png%22%20alt%3D%22OwenPrice_3-1594984061291.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20Home%26gt%3BAdvanced%20Editor%2C%20I%20locate%20the%20line%20in%20the%20query%20that%20describes%20the%20sum%20transformation%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3E%23%22Grouped%20Rows%22%20%3D%20Table.Group(%23%22Reordered%20Columns%22%2C%20%7B%22Folder%20Path%22%7D%2C%20%7B%7B%22PipeSeparatedFileList%22%2C%20each%20List.Sum(%5BName%5D)%2C%20type%20text%7D%7D)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20I%20change%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3EList.Sum(%5BName%5D)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3EText.Combine(%5BName%5D%2C%22%7C%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20click%20OK%20on%20the%20Advanced%20Editor%20and%20I%20have%20the%20desired%20result%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22OwenPrice_4-1594984281752.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206030i5B8320D9D5397F9B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22OwenPrice_4-1594984281752.png%22%20alt%3D%22OwenPrice_4-1594984281752.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20this%2C%20you%20can%20use%20Home%26gt%3BClose%26amp%3BLoad%20to%20put%20the%20data%20back%20in%20the%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1531028%22%20slang%3D%22en-US%22%3ERe%3A%20File%20names%20from%20folders%20into%20rows%20and%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1531028%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F725726%22%20target%3D%22_blank%22%3E%40OwenPrice%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%20it%20works%2C%20thank%20you.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20Couldn't%20it%20be%20automated%20somehow%3F%20Compared%20to%20the%20Libreoffice%20solution%2C%20this%20is%20complicated.%20In%20LO%2C%20I%20have%20created%20a%20button%20on%20the%20toolbar%20for%20this%20particular%20macro%2C%20and%20after%20pressing%20it%2C%20I%20select%20a%20folder%2C%20and%20the%20list%20of%20images%20is%20immediately%20inserted%20into%20a%20new%20document.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1531047%22%20slang%3D%22en-US%22%3ERe%3A%20File%20names%20from%20folders%20into%20rows%20and%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1531047%22%20slang%3D%22en-US%22%3EIf%20the%20steps%20above%20are%20saved%20into%20a%20workbook%2C%20you%20can%20set%20the%20steps%20to%20automatically%20refresh%20the%20output%20when%20you%20open%20the%20file.%20Go%20to%20Query%20tab%2C%20then%20Properties%20to%20configure%20the%20options.%3C%2FLINGO-BODY%3E
Contributor

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):

  • 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

@temporalnaut 

 

You can use PowerQuery for this.

 

Use Data>Get & Transform Data>Get Data>From File>From Folder

OwenPrice_0-1594983760106.png

 

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:

OwenPrice_1-1594983902409.png

 

After this, I use Home>Transform>Group By and configure it like this:

 

OwenPrice_2-1594984034944.png

 

Understandably, trying to sum a text column will give an error:

 

OwenPrice_3-1594984061291.png

 

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:

 

OwenPrice_4-1594984281752.png

 

After this, you can use Home>Close&Load to put the data back in the workbook.

 

 

@OwenPrice 

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.