How do I get the file path and insert it into a table?

Question

How do I get the file path and insert it into a table?

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

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 to have something in my table like this:

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 it does not, can I export a list of files from my PastaTeste to Excel, for example?

mysql excel

asked by anonymous 05.05.2015 / 15:30

2 answers

Compare URL with PHP Maven Project in SVN

score 1 · Answer 1

With excel you can 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

score 0 · Answer 2

I do not know an alternative to the xp_dirtree of Sql Server that allows you to directly import the names of the files. To get the same result you can try as follows. First you create a file with the listing of your directory. For example on the windows command line:

dir /s/b > lista_ficheiros.txt

Then you use the LOAD DATA INFILE command to load the read files into 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);