0

I am attempting to create an access table containing the index information for around 548,000 images. Currently, the index information is contained in around 525 .txt files. I have found that I can accurately import this information into access via external data -> import new data. The .txt files are uniformly formatted with column names in the first row, and the respective attributes delimited by "|". I suppose what I'm asking for, is help creating a macro that will iterate through this folder, and import each of these .txt files into a single table that I can then use to search for the corresponding image. Here's a VBA I used to previously iterate through a folder of .dbf files. How might I use this to iterate through .txt files delimited by "|"

Option Compare Database

Private Sub ImportDBF()
On Error GoTo ErrHandler 'change to On Error GoTo ErrHandler if you want to see errors.

Dim oFSystem As Object
Dim oFolder As Object
Dim oFile As Object
Dim sFolderPath As String
Dim i As Integer

sFolderPath = "C:\Users\Juan Rodriguez\Desktop\Well data headers"

Set oFSystem = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSystem.getfolder(sFolderPath)

For Each oFile In oFolder.Files
    If Right(oFile.Name, 3) = "dbf" Then
        SQL = "INSERT INTO complete_db SELECT * FROM [" & oFile.Name & "]" _
            & " IN '" & sFolderPath & "'[dBASE IV;]"

    CurrentDb.Execute SQL
    End If
Next oFile

Exit Sub
ErrHandler:
MsgBox Err.Description

End Sub

I have attached some images containing the filepath for the .txt files as well as the filepath for the images. The images are in various subdirectories.image1image2

Any insight is greatly appreciated! Thank you in advance!

johnmigurk69
  • 5
  • 2
  • You can use Dir() ot the Scripting filesystemobject to loop over files in a folder. – Tim Williams Aug 04 '20 at 18:19
  • Does this answer your question? [Loop through files in a folder using VBA?](https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba). Also review https://stackoverflow.com/questions/63246079/access-db-save-file-location-full-link-path-in-one-field-and-file-name-in-ot/63252836#63252836 – June7 Aug 04 '20 at 18:42
  • [here](https://imgur.com/a/ltDFYxr) I have attached an image of another VBA I have used previously with success to iterate through a folder of .dbf files. However, the .txt files do not properly import unless I specify it is delimited by "|". How do make this process reflect in my VBA code? – johnmigurk69 Aug 04 '20 at 18:55
  • Edit question to show your attempted code and sample data and it might be reopened for answers. – June7 Aug 04 '20 at 20:42
  • All about querying text files using ADO: https://learn.microsoft.com/en-us/previous-versions/windows/internet-explorer/ie-developer/scripting-articles/ms974559(v=msdn.10) – Tim Williams Aug 04 '20 at 22:40

0 Answers0