Add filename to access table while importing csv file

Question

I have multiple csv files in multiple folders all in one master folder on drive C:. Some files are updated each day. If a file has an update, I need to load the new daily data into an access table including the file name. The script so far imports all data from all csv files. Then it adds the file name in a new record. I need the file name added to all records.

Any help would be much appreciated.

Script:

Sub Import_multiple_csv_files()

Const strPath As String = "C:\text1\" 'Directory Path
Dim strFile As String 'Filename
Dim strFileList() As String 'File  Array
Dim intFile As Integer 'File Number
Dim rs As DAO.Recordset
 'Loop through the folder & build file list
strFile = Dir(strPath & "*.csv")
While strFile <> ""
     'add files to the list
    intFile = intFile + 1
    ReDim Preserve strFileList(1 To intFile)
    strFileList(intFile) = strFile
    strFile = Dir()
Wend
 'see if any files were found
If intFile = 0 Then
    MsgBox "No files found"
    Exit Sub
End If
 'cycle through the list of files &  import to Access
 'creating a new table called MyTable

For intFile = 1 To UBound(strFileList)

DoCmd.TransferText acImportDelimi, , _
    "Test", strPath & strFileList(intFile)
    ‘add file name to record
    Set rs = CurrentDb.OpenRecordset("Test")
    rs.AddNew
    rs.Fields("Com").Value = Dir(strPath & "*")
    rs.Update
    rs.Close
    Set rs = Nothing


Next

MsgBox UBound(strFileList) & " Files were Imported"

End Sub

Simon Simon · Accepted Answer · 2011-12-07T12:13:58

I'm not 100% sure what the question is, but if you're trying to update the filename in the records you've just imported you can do with a simple update statement:

UPDATE Test SET Com='MyFileName' WHERE Com IS NULL OR Com=''

Here I'm assuming that the field will be null or an empty string but you can replace this with your own criteria if this is incorrect. You can use DoCmd.RunSQL to do this.

If I am misunderstanding the problem then please update your question to make it clearer.

Add filename to access table while importing csv file

1 Answers