Import Text files from multiple folders

[jackjee]

Hi
I am looking for a vba solution to import multiple txt files at the same time to an Access DB.
While importing the filenames need to be stored in another table so next time these files should not be imported.
As an example,
i have multiple folders (can specify the paths in the module) where these text files are available (new files are copied by someone and store to these paths in a daily basis).
When I execute VBA module from an access DB, the code need to check for any new files in all these folders and import only contents from new files to a table in access db. And at the same, store the filename and path to another table so next time the code can cross check this and read only new files
While importing, one of the field in the txt file is data which is in 'yyyy.mm.dd' and that to be converted to a proper date format
When I run the module next time, it should cross check the filenames stored in the other table and import new files only.
Hope i explained well my issue and kindly request a help from the experts to solve this

[NeoPa]

Hi Jack, & welcome to Bytes.com.

There seems to be some sort of misunderstanding though, as to what we do here. You've posted a technical REQUEST. Almost like a specification for work to be done. That's not what we do. We help you to develop & design your systems. We don't simply do it for you. That sort of stuff you would need to find someone for - and pay them. This isn't just a free technical resource for getting your work done.

On the other hand - we do like to help if you give us half a chance.

I can start by giving some very basic tips. My first tip is to confirm that just by going through the process of writing out this request you've clarified in your head what you're trying to do. This is probably more helpful than you realise at this time.

Consider having a table that lists the folders you want to cover too. Consider updating the files already imported by moving them to another folder, or just making the Read Only.

Beyond that, get started and feel free to post as many smaller, more specific, questions as you may feel the need for. There are many of us here so some will be free most of the time.

[NeoPa]

NB. For anyone thinking of simply posting solutions I am very likely to delete any such posts. You may send them via PM if you feel so inclined but they won't be allowed in the public part of the site.

[jackjee]

Hi NeoPA & experts
Ref. my post, I found a solution for my post, which is looking one specific path and merge all files and provide an output with all files merged. This is ok, but I dont know how to read multiple paths with below code at the same time and merge all files and provide single output.
Also, another question related to this, after importing this merged data to an access table I want to delete the duplicate records based on few fields uniqueness not all fields. I have a code which can delete all the duplicate records if all fields are having same value. The second code in this post does a check on all fields and then delete the duplicate record. Can anyone guide me how to modify both codes to get the expected results

Code for merging txt files in a specific path as below:

Sub MyCombineFiles()
 Dim myFileDir As String
 Dim myFileExt As String
 Dim myFinalFileName As String
'   This process will combine all text files in a directory into one single file (should only be used with text files)
   myFileDir = "text file directory"
   myFileExt = ".txt"
   myFinalFileName = "output file name.txt"
 
    Dim fname
    Dim TextLine As String
    Dim myCombinedFile As String
 
'   Create output file
    myCombinedFile = myFileDir & myFinalFileName
    Open myCombinedFile For Output As #1
 
'   Loop through all files with designated extension in the directory
    fname = Dir(myFileDir & "*" & myFileExt)
    While (fname <> "") And (fname <> myFinalFileName)
'   Open file and write data to output file
        Open myFileDir & fname For Input As #2
        Do While Not EOF(2)
            Line Input #2, TextLine
            Print #1, TextLine
        Loop
        Close #2
'   Delete initial file after it is merged (comment this line out if you do not want to delete initial files)
 
        'Kill myFileDir & fname
 
'   Go to the next file
 
        fname = Dir()
    Wend
    Close #1
 
End Sub
 

Code for duplicate deletion as below

Sub DeleteDuplicateRecords()
Dim StrTableName As String
 
StrTableName = "dupetest"
 
  ' Deletes exact duplicates from the specified table.
   ' No user confirmation is required. Use with caution.
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim strSQL As String
    Dim varBookmark As Variant
 
    Set tdf = DBEngine(0)(0).TableDefs(StrTableName)
    strSQL = "SELECT * FROM " & StrTableName & " ORDER BY "
   ' Build a sort string to make sure duplicate records are
   ' adjacent. Can't sort on OLE or Memo fields,though.
    For Each fld In tdf.Fields
        If (fld.Type <> dbMemo) And (fld.Type <> dbLongBinary) Then
            strSQL = strSQL & "[" & fld.Name & "]" & ", "
        End If
    Next fld
  ' Remove the extra comma and space from the SQL[/COLOR]
    strSQL = Left(strSQL, Len(strSQL) - 2)
    Set tdf = Nothing
 
    Set rst = CurrentDb.OpenRecordset(strSQL)
    Set rst2 = rst.Clone
    rst.MoveNext
    Do Until rst.EOF
        varBookmark = rst.Bookmark
        For Each fld In rst.Fields
            If fld.Value <> rst2.Fields(fld.Name).Value Then
                GoTo NextRecord
            End If
        Next fld
        rst.delete
        GoTo SkipBookmark
 
 
NextRecord:
        rst2.Bookmark = varBookmark
SkipBookmark:
        rst.MoveNext
    Loop
End Sub
 
 

[NeoPa]

That's certainly progress Jack.

We still need specific questions though. One per thread as explained in the site rules. There are good reasons for this as you'll see if you care to read them. Fine if not - few actually do - but in that case just trust me. This is what we need to help you.

Let's just start with one simple question. Something specific. "Sort out my code for me.", even with a please, is not a specific question. If the code you've found is too complicated for you then start simpler. Only you will be left at the end of the day with your work - and you really don't want to be left supporting work you don't even understand. We want to help you understand as well as just fix things for you.

So - your call - what's you first straightforward question?