Import Directory Files Names to Table

  • Thread starter Thread starter Stephen Lynch
  • Start date Start date
S

Stephen Lynch

I saw the code in this forum and modified it, however, I searched and cannot
figure out 2 things, 1) how to limit the import of file names to only *.txt
and *.csv and how to import from multiple directories.

so I want to import all *.txt and *.csv from the following folders
C:SLWIN\1, C:SLWIN\2, C:SLWIN\3, and WIN\4.

Thanks in advance I will keep searching.


Function ImportFileNamestoTable()

Dim strSQL As String
Dim strFileName As String
Const conFolder As String = "C:\SLWIN\"

strFile = Dir(conFolder & "*.*")

Do While Len(strFile) > 0

strSQL = "INSERT INTO tblImportedFileNames (FileName) " & _
"SELECT """ & conFolder & strFile & """;"

CurrentDb.Execute strSQL, dbFailOnError
strFile = Dir$()
Loop

End Function
 
Function ImportFileNamestoTable()

Dim lngLoop As Long
Dim strSQL As String
Dim strFileName As String
Const conFolder As String = "C:\SLWIN\"

For lngLoop = 1 To 4

strFile = Dir$(conFolder & lngLoop & "\*.txt")

Do While Len(strFile) > 0

strSQL = "INSERT INTO tblImportedFileNames (FileName) " & _
"SELECT """ & conFolder & strFile & """;"

CurrentDb.Execute strSQL, dbFailOnError
strFile = Dir$()
Loop


strFile = Dir$(conFolder & lngLoop & "\*.csv")

Do While Len(strFile) > 0

strSQL = "INSERT INTO tblImportedFileNames (FileName) " & _
"SELECT """ & conFolder & strFile & """;"

CurrentDb.Execute strSQL, dbFailOnError
strFile = Dir$()
Loop

Next lngLoop

End Function
 
I saw the code in this forum and modified it, however, I searched and cannot
figure out 2 things, 1) how to limit the import of file names to only *.txt
and *.csv and how to import from multiple directories.

so I want to import all *.txt and *.csv from the following folders
C:SLWIN\1, C:SLWIN\2, C:SLWIN\3, and WIN\4.

Thanks in advance I will keep searching.

Function ImportFileNamestoTable()

Dim strSQL As String
Dim strFileName As String
Const conFolder As String = "C:\SLWIN\"

    strFile = Dir(conFolder & "*.*")

    Do While Len(strFile) > 0

        strSQL = "INSERT INTO tblImportedFileNames (FileName) "& _
            "SELECT """ & conFolder & strFile & """;"

        CurrentDb.Execute strSQL, dbFailOnError
        strFile = Dir$()
    Loop

End Function
Click to expand...

To get a recursive directory search, use the code from Allen Browne's
website, which is here:
http://www.allenbrowne.com/ser-59.html

To import the text files and CSV files only... One way is to run a
slightly modified version of the function twice...

ImportFileNamestoTable("TXT")
ImportFileNamestoTable("CSV")

Here's the modified function:

Function ImportFileNamestoTable(byval strExtension as string)

Dim strSQL As String
Dim strFileName As String
Const conFolder As String = "C:\SLWIN\"

strFile = Dir(conFolder & "*." & strExtension)

Do While Len(strFile)>0

strSQL = "INSERT INTO tblImportedFileNames (FileName) " & _
"SELECT """ & conFolder & strFile & """;"

CurrentDb.Execute strSQL, dbFailOnError
strFile = Dir$()
Loop

End Function
 
Thanks Doug:

The only problem is that the subdirectorys are not 1,2,3,4 they are more
like the follwoing:

C:\SLWIN\02124512
C:\SLWIN\02124512
C:\SLWIN\02124512
 
Doug:

Thanks, Is their a way to loop through all the subfolders as the folders are
not 1,2,3,4.

But more like names like 0121, 6543, 2376, and 4544.

Thanks
 
Sorry reply before I edited.
Stephen Lynch said:
Thanks Doug:

The only problem is that the subdirectorys are not 1,2,3,4 they are more
like the follwoing:

C:\SLWIN\02124512
C:\SLWIN\02124512
C:\SLWIN\02124512
Click to expand...
 
Back
Top