How to take the file path and insert it into a table?

Question

I need to create a Mysql statement that takes all the file paths of the files in a specific folder and inserts them into a Mysql table. For example, if my folder is called PastaTeste, I need something like this on my chart:

C:\PastaTeste\SubPasta1\Arquivo1.pdf

C:\PastaTeste\SubPasta1\Arquivo2.pdf

C:\PastaTeste\SubPasta2\Arquivo3.pdf

C:\PastaTeste\SubPasta3\Arquivo4.pdf

C:\PastaTeste\SubPasta3\Arquivo5.pdf

Is there any way to do this with Mysql? If not, I can export a list of files from mine PastaTeste for Excel, for example?

Answer 1

With excel it is possible to accomplish what you need with the following VBA code:

 Option Explicit

 Sub ListarArquivos()
     Dim objFSO As Object
     Dim objTopFolder As Object
     Dim strTopFolderName As String
     Range("A1").Value = "Arquivo"
     strTopFolderName = "C:\PastaTeste\\"
     Set objFSO = CreateObject("Scripting.FileSystemObject")
     Set objTopFolder = objFSO.GetFolder(strTopFolderName)

     Call ListarRecursivo(objTopFolder, True)
     Columns.AutoFit
 End Sub

 Sub ListarRecursivo(objFolder As Object, IncludeSubFolders As Boolean)
     Dim objFile As Object
     Dim objSubFolder As Object
     Dim NextRow As Long

     NextRow = Cells(Rows.Count, "A").End(xlUp).row + 1

     For Each objFile In objFolder.Files
         Cells(NextRow, "A").Value = objFile.Path & objFile.Name
         NextRow = NextRow + 1
     Next objFile

     If IncludeSubFolders Then
         For Each objSubFolder In objFolder.SubFolders
             Call RecursiveFolder(objSubFolder, True)
         Next objSubFolder
     End If
 End Sub

Answer 2

I don’t know any alternative to Sql Server xp_dirtree that allows you to directly import filenames. To get the same result you can try as follows. First create a file with your directory listing. For example at the windows command line:

dir /s/b > lista_ficheiros.txt

Then use the LOAD DATA INFILE command to load the read files to a table as follows:

CREATE TABLE ficheiros
  (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   Ficheiro TEXT NOT NULL);
LOAD DATA INFILE 'lista_ficheiros.txt' INTO TABLE ficheiros
  LINES TERMINATED BY '\n%%\n' (Ficheiro);