Import multiple txt files and filename

[Stoiff]

Hi all,

I just started using VBA in access and I hope someone can give me a hand with the following:

I have multiple text files in a folder that I need to import in a table, but I want to include the filename (excluding the txt extension) in the first field of my table. I have code that allows me to batch import the file contents, but I can't figure out how I can include the filenames in the first field of my table.
The code is below:
----------------------------------
Function Load_Attendance_Data()
Dim NextFile, ImportFile, FileCriteria, ctr As Variant
Dim DB_Path As Variant

' get the DB path
DB_Path = "C:\IWC\BirdSTAT\F1Files\"
FileCriteria = "C:\IWC\BirdSTAT\F1Files\" & "*.txt"

' create field with path and filename to import'
NextFile = Dir(FileCriteria)

' Check we have something to import
If NextFile = "" Then
MsgBox "No files to import", , "Error"
Exit Function
End If
ctr = 0

' Import each file that meets the criteria 'File for*.txt'
While NextFile <> ""

' count files imported
ctr = ctr + 1

' add the path to the returned filename
ImportFile = DB_Path & NextFile

' Import file into table
DoCmd.TransferText acImportDelim, "3711_0 Import Specification", "Attendance Import", ImportFile, False

' get another file if it exists
NextFile = Dir()

Wend

MsgBox ctr & " files imported", , "Attendance Import"
End Function
-----------------------------
Thanks!

 

[JamesMcS]

Can you put an update query in the loop?

 

[Stoiff]

How would I do that?

 

[JamesMcS]

Something like

docmd.runsql "UPDATE tablename SET tablename.fieldname='" & Dir(FileCriteria) & "' WHERE fieldname IS NULL;"

 

[Stoiff]

I'll give it a try.
Thanks!

 

[JamesMcS]

just edited the code - you need to put an apostrophe in either side of the & Dir(FileCriteria) &

 

[Stoiff]

The code works great, I just don't know how to integrate it in the loop. Currently it loops indefinitely and only appends the first filename in the folder.
Any ideas?
Cheers!
---------------------------------
Function Load_Attendance_Data()
Dim NextFile, ImportFile, FileCriteria, ctr As Variant
Dim DB_Path As Variant

' get the DB path
DB_Path = "C:\IWC\BirdSTAT\F1Files\"
FileCriteria = "C:\IWC\BirdSTAT\F1Files\" & "*.txt"

' create field with path and filename to import MAKE THIS READ ALL files that start with 'FILE FOR'
NextFile = Dir(FileCriteria)

' Check we have something to import
If NextFile = "" Then
MsgBox "No files to import", , "Error"
Exit Function
End If
ctr = 0

' Import each file that meets the criteria 'File for*.xls'
While NextFile <> ""

' count files imported
ctr = ctr + 1

' add the path to the returned filename
ImportFile = DB_Path & NextFile

' Import file into table
DoCmd.TransferText acImportDelim, "Specification", "F1", ImportFile, False

DoCmd.RunSQL "UPDATE F1 SET F1.field6='" & Dir(FileCriteria) & "' WHERE field6 IS NULL;"

' get another file if it exists
NextFile = Dir()

Wend

MsgBox ctr & " files imported", , "Attendance Import"
End Function

 

[DCrake]

BTW I would strongly recommend you read up on naming conventions for tables and fields. Naming tables F1 and fields Field6 is totally against the norm. How is an onlooker or predecessor going to understand the logic?

 

[JamesMcS]

Good point DC, it's going to make your life a lot easier if you n ame things more momorably in the future Stoiff!

Sorry, replace the dir(filecriteria) with NextFile. Also you want "Do While" at the start of your loop, and "Loop" instead of "Wend" (I think)

 

[Stoiff]

Excellent! It worked. Thanks a lot and I'll make sure I'll stick with the norm.

 

[jamie-53]

Hi All

I'm trying to adapt the above code to work with a similar issue I've got trying to import multiple text files whilst keeping the file name as a separate field.

The script brings in the data ok, however instead of bringing in the filename I get a box asking for a parameter for "Field6" (using the example).

Am I missing something? I'm not sure what to enter here without the script failing. If I enter nothing I get an error back as follows:

"Run-time error '3073':

Operation must use an updateable query."

Any suggestions would be greatly appreciated!


Jamie

 

[EricDANG]

because you don't have field6 in your table. You need to add it in import specification where Field Name column and add field6 on last record. Good luck!