How to loop through a folder and add the names of all the files into cells in a column in Libreoffice? Using .csv and Anki

Question

I have several folders, each containing several thousand .mp3 files of example sentences spoken by native speakers. I want to loop through the folder in order and add all the names of the files to the first column of a LibreOffice spreadsheet, so in each cell in the column it looks like [sound:name_of_file.mp3]. The names of the file are in order, but they are split up by section and chapter, so it would be easier if there was a way to grab the name of the file and use that, as opposed to coming up with a function that changes the text based on a for loop or something like that.

I want to create a .csv file including all the [sound:name_of_file.mp3] text, that I will upload into Anki as a deck, and then just copy all the .mp3 files from each of the original folders into Anki's media folder so the card references the audio and plays it.

Is there a way to do this, and if so, how would it be done?

Thank you!

Answer 1

If you don't want to use the system command to read the folder contents, as described in the comment to the question, then the following script will help you do the job:

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, sFile As String
    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, False, False, False, , "mp3")
    nRow = UBound(aList)
    If nRow < 0 Then Exit Sub
    ReDim aRes(nRow)
    For i = LBound(aList) To UBound(aList)
        sFullName = ConvertFromURL(aList(i,0))
        sFile = FileNameOutOfPath(sFullName, GetPathSeparator())
        aRes(i) = "[sound:" & sFile & "]"
    Next i
    sFile = sStartFolder & GetPathSeparator() & "filelist.csv"
    sFile = InputBox("Specify the path and name for the output file:","Found " & (nRow + 1) & " files.", sFile)
    If sFile = "" Then Exit Sub
    SaveDataToFile(ConvertToURL(sFile),aRes)
End Sub