File names from folders into rows and columns

[temporalnaut]

Hi, I have a question if it is possible to import file names with extensions from folders into Calc as follows:

• I have a lot of folders that contain 1 or 2 images.

• If the folder contains 1 image, I need it to be on a new line in Calc (classic way).

• If the folder contains 2 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.
well thank you

[keme]

MS Windows: Start a command line session: cmd.exe

cd <the base folder for your product folders>
dir /w /s > filelist.txt

Linux: open a terminal window

cd <the base folder for your product folders>
ls -R > filelist.txt

Insert content of filelist.txt into Calc.

You may need to use Data - Text to columns to reorganize content

Filter out the rows you don’t need.

[temporalnaut]

How to use “Text to columns” to move “picture 02.jpeg” to the next column and the same row as “picture 01.jpeg”?

[JohnSUN]

Well, try this:

Option Explicit 

Sub createFileList
Dim oFolderDialog As Object, oUcb As Object 
Dim sStartFolder As String 
Dim aList As Variant, aRes As Variant 
Dim nRow As Long, i As Long 
Dim sFullName As String, sExt As String, sFile As String, sPath As String, sFolder As String
Dim oDoc As Variant, oSheet As Variant
	Globalscope.BasicLibraries.LoadLibrary("Tools")
	sStartFolder = ""
	oFolderDialog = CreateUnoService("com.sun.star.ui.dialogs.FolderPicker")
	oUcb = createUnoService("com.sun.star.ucb.SimpleFileAccess")
	If oUcb.Exists(GetPathSettings("Work")) Then oFolderDialog.SetDisplayDirectory(GetPathSettings("Work"))
	If oFolderDialog.Execute() = 1 Then
		sStartFolder = oFolderDialog.GetDirectory()
		If oUcb.Exists(sStartFolder) Then sStartFolder = ConvertFromUrl(sStartFolder)
	End If
	If sStartFolder = "" Then Exit Sub
	aList = ReadDirectories(sStartFolder, True, True, False)
	ReDim aRes(UBound(aList))
	nRow = -1
	sFolder = ""
	For i = LBound(aList) To UBound(aList)
		sFullName = ConvertFromURL(aList(i,0))
		sExt = LCase(GetFileNameExtension(sFullName))
		If sExt = "jpeg" Or sExt = "jpg" Or sExt = "png" Then 
			sFile = FileNameOutOfPath(sFullName, GetPathSeparator())
			sPath = DirectoryNameOutOfPath(sFullName, GetPathSeparator())
			If sPath = sFolder Then 
				aRes(nRow)(1) = aRes(nRow)(1) + "|" + sFile
			Else
				 sFolder = sPath
				 nRow = nRow + 1
				 aRes(nRow) = Array(sFolder, sFile) 
			EndIf 
		EndIf 
	Next i
	If nRow < 0 Then Exit Sub
	ReDim Preserve aRes(nRow)
	oDoc =  CreateNewDocument("scalc")
	oSheet = oDoc.getSheets().getByIndex(0)
	oSheet.getCellRangeByPosition(0,0,1,nRow).setDataArray(aRes)
End Sub

[temporalnaut]

THANK YOU!!!

[temporalnaut]

Hi, can you please modify that code to load only the first file from each folder into the list? well thank you

[JohnSUN]

Sorry, no. But this is not necessary. Divide Column B with Text to Columns Separated by Other - | (pipe) and remove columns from C to the end of the data.