Import multi csv files and add filename to NULL cell

[TommyW]

Hello everyone
I am new to this site and definitely not a programmer but I have a problem with importing multiple csv files and would appreciate any help with my problem

I have managed to get this code to bring in the files but now I need it to populate a column that is empty with the filename of the imported csv file

I have thousands of these files to import into a single table but I need to label each record with the source filename

This is what I have that works for importing but I have no clue as to how to populate the records with the name.

Any help with this would be much appreciated

Tommy



Sub Import_Counts_csv_files()

Const strPath As String = "H:\My Documents\Gatwick\Database\counts\test" 'Directory Path
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number

'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
For intFile = 1 To UBound(strFileList)
DoCmd.TransferText acImportDelimi, , _
"counts", strPath & strFileList(intFile), HasFieldNames:=True



Next
MsgBox UBound(strFileList) & " Files were Imported"
End Sub

Thanks again in anticipation

 

[TommyW]

After looking through the forum I have made some changes and the code is telling me that I am about to update X records but when I look into the table There is no filenames in the Name1 Column

Below is the updated code

Sub Import_Counts_csv_files()

Const strPath As String = "H:\My Documents\Gatwick\Database\counts\test" 'Directory Path
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number

'Loop through the folder & build file list
strFile = Dir(strPath & "*.csv")
Do While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Loop
'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
For intFile = 1 To UBound(strFileList)
DoCmd.TransferText acImportDelimi, , _
"counts", strPath & strFileList(intFile), HasFieldNames:=True

DoCmd.RunSQL "UPDATE Counts SET Counts.Name1 ='" & strFile & "' WHERE Name1 IS NULL;"

Next
MsgBox UBound(strFileList) & " Files were Imported"
End Sub

 

[Peter Reid]

Looking at

DoCmd.RunSQL "UPDATE Counts SET Counts.Name1 ='" & strFile & "' WHERE Name1 IS NULL;"

Further up the code you have a loop that repeats until strFile is blank so the SQL will never update the field to be a non-blank value

It should read

DoCmd.RunSQL "UPDATE Counts SET Counts.Name1 ='" & strFileList(intFile) & "' WHERE Name1 IS NULL;"

 

[TommyW]

Thanks Peter That works a treat, you don't know how much I have tried to get this to work, you are a lifesaver