470,849 Members | 1,059 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,849 developers. It's quick & easy.

Import Text files from multiple folders

24 16bit
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
May 29 '21 #1
4 3372
32,311 Expert Mod 16PB
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.
May 29 '21 #2
32,311 Expert Mod 16PB
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.
May 29 '21 #3
24 16bit
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:

Expand|Select|Wrap|Line Numbers
  1. Sub MyCombineFiles()
  2.  Dim myFileDir As String
  3.  Dim myFileExt As String
  4.  Dim myFinalFileName As String
  5. '   This process will combine all text files in a directory into one single file (should only be used with text files)
  6.    myFileDir = "text file directory"
  7.    myFileExt = ".txt"
  8.    myFinalFileName = "output file name.txt"
  10.     Dim fname
  11.     Dim TextLine As String
  12.     Dim myCombinedFile As String
  14. '   Create output file
  15.     myCombinedFile = myFileDir & myFinalFileName
  16.     Open myCombinedFile For Output As #1
  18. '   Loop through all files with designated extension in the directory
  19.     fname = Dir(myFileDir & "*" & myFileExt)
  20.     While (fname <> "") And (fname <> myFinalFileName)
  21. '   Open file and write data to output file
  22.         Open myFileDir & fname For Input As #2
  23.         Do While Not EOF(2)
  24.             Line Input #2, TextLine
  25.             Print #1, TextLine
  26.         Loop
  27.         Close #2
  28. '   Delete initial file after it is merged (comment this line out if you do not want to delete initial files)
  30.         'Kill myFileDir & fname
  32. '   Go to the next file
  34.         fname = Dir()
  35.     Wend
  36.     Close #1
  38. End Sub
Code for duplicate deletion as below

Expand|Select|Wrap|Line Numbers
  1. Sub DeleteDuplicateRecords()
  2. Dim StrTableName As String
  4. StrTableName = "dupetest"
  6.   ' Deletes exact duplicates from the specified table.
  7.    ' No user confirmation is required. Use with caution.
  8.     Dim rst As DAO.Recordset
  9.     Dim rst2 As DAO.Recordset
  10.     Dim tdf As DAO.TableDef
  11.     Dim fld As DAO.Field
  12.     Dim strSQL As String
  13.     Dim varBookmark As Variant
  15.     Set tdf = DBEngine(0)(0).TableDefs(StrTableName)
  16.     strSQL = "SELECT * FROM " & StrTableName & " ORDER BY "
  17.    ' Build a sort string to make sure duplicate records are
  18.    ' adjacent. Can't sort on OLE or Memo fields,though.
  19.     For Each fld In tdf.Fields
  20.         If (fld.Type <> dbMemo) And (fld.Type <> dbLongBinary) Then
  21.             strSQL = strSQL & "[" & fld.Name & "]" & ", "
  22.         End If
  23.     Next fld
  24.   ' Remove the extra comma and space from the SQL[/COLOR]
  25.     strSQL = Left(strSQL, Len(strSQL) - 2)
  26.     Set tdf = Nothing
  28.     Set rst = CurrentDb.OpenRecordset(strSQL)
  29.     Set rst2 = rst.Clone
  30.     rst.MoveNext
  31.     Do Until rst.EOF
  32.         varBookmark = rst.Bookmark
  33.         For Each fld In rst.Fields
  34.             If fld.Value <> rst2.Fields(fld.Name).Value Then
  35.                 GoTo NextRecord
  36.             End If
  37.         Next fld
  38.         rst.delete
  39.         GoTo SkipBookmark
  42. NextRecord:
  43.         rst2.Bookmark = varBookmark
  44. SkipBookmark:
  45.         rst.MoveNext
  46.     Loop
  47. End Sub
Jun 1 '21 #4
32,311 Expert Mod 16PB
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?
Jun 1 '21 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by John | last post: by
1 post views Thread by Ruslan Shlain | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.