If you have hundreds of CSV files all in the same format, presumably
the data in them all refers to the same class of entity. In that case
you almost certainly should not import them into hundreds of tables in
the database but into a single table. Maybe you'll need to add a field
to identify which file each record came from (e.g. if you're a bank
and each file comes from a different branch).
With lots of identically structured tables you are effectively storing
data in the table names. This is a no-no in terms of database theory,
and can be extremely inconvenient in practice.
To import to a single table and include the filename in each record,
start by creating a table with the necessary fields, and then use code
something like this (which works in my test database but hasn't been
thoroughly tested):
Public Sub ImportAllFilesInFolder(FolderName As String)
'procedure to import all csv files in a folder
Dim FileName As String
Dim CountImported As Long
Dim SQL As String
Dim D As DAO.Database
'Constant part of SQL statement for append query
'substitute actual field names
Const SQL_1 = "INSERT INTO MyTable " & vbCrLf _
& " (ID, Person, Address, Amt, Bal, Source) " & vbCrLf _
& "SELECT F1, F2, F3, F4, F5, "
Set D = CurrentDb()
'Get name of first file
If Right(FolderName, 1) <> "\" Then
FolderName = FolderName & "\"
End If
FileName = Dir(FolderName & "*.CSV")
Do While Len(FileName) > 0
'Build SQL statement
SQL = SQL_1 & "'" & FileName & "' AS Source " & vbCrLf _
& "FROM " _
& BuildJetTextSource(FolderName & FileName, False) _
& ";"
'If text file has header row with field names, change
'False to true in the BuildJetTextSource call
'print it out for debugging
Debug.Print SQL
'execute it to import the file
D.Execute SQL, dbFailOnError
'delete the file now it's been imported
'or maybe rename it or move it to another folder
Kill FolderName & FileName
'get next filename
FileName = Dir
Loop
End Sub
Function BuildJetTextSource(ByVal FileSpec As String, _
ByVal HDR As Boolean) As String
'Takes a filespec and returns a string that can be used
'in the FROM clause of a Jet SQL query.
' E.g.
' C:\My Folder\MyFile.txt
' returns
' [Text;HDR=No;Database=C:\My Folder\;].[MyFile#txt]
' The HDR argument controls the HDR parameter in string returned.
'
' By John Nurick 2005
' Revised 2007 to remove call to Dir()
Dim fso As Object 'FileSystemObject
Dim strFolder As String
Dim strFileName As String
Dim strFileExt As String
Dim strTemp As String
'Parse FileSpec
Set fso = CreateObject("Scripting.FileSystemObject")
With fso
FileSpec = .GetAbsolutePathName(FileSpec)
strFolder = .GetParentFolderName(FileSpec)
strFileName = .GetBaseName(FileSpec)
strFileExt = .GetExtensionName(FileSpec)
End With
Set fso = Nothing
'Build string
strTemp = "[Text;HDR=" _
& IIf(HDR, "Yes", "No") _
& ";Database=" _
& strFolder & "\;].[" _
& strFileName & "#" _
& strFileExt & "]"
BuildJetTextSource = strTemp
End Function
I have multiple (100s) of CSV files in a single directory (these are the only
files in the directory), and they are all the same format. I need to load
them, one by one, into tables in an Access 2003 database. The file names
themselves could be anything - what is important is that I can
programatically (within Access hopefully) and systematically open each file
in turn, read the data and populate the appropriate tables. I dont know how
to get to these files from within Access. Can anyone help?
Thanks!
