I have about 600 text files with headers, and I don't really feel like importing them one by one manually into MS Access.
I don't want to append the text files into one Access table. If I have 600 text files, I want the result to be 600 Access tables.
I've searched high and low for this, the closest I've come is some code that would take my text files and append them into one access table. I don't want that.
Consider a For/Loop VBA using the DoCmd.TransferText command that iterates through all text files in a folder directory and imports them with suffix to denote 1-600.
Dim FSO as Object, objFolder As Object, objFile as Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = FSO.GetFolder("C:\Path\To\TextFiles")
i = 1
For each objFile in objFolder.Files
If Right(objFile.Name, 3) = "txt" Then
DoCmd.TransferText acImportDelim, , "File_" & i, objFolder & "\" & objFile.Name, True
End if
i = i + 1
Next objFile
Set objFile = Nothing
Set objFolder = Nothing
Set FSO = Nothing
In the empty argument in TransferText you can use a pre-defined specification object which you create during one manual import of the text file. This allows you to name fields, define data types, etc. of the imported text file.
Finally, if all files are structured the same, consider again importing into one table and use queries by relevant fields to break up in the 600 groupings if needed. To use one table, simply replace the "File_" & i argument above with a table string name: "dataFiles". You save on table namespaces, storage from less database objects, and overall helps in better organization as well as the relational model.