472,982 Members | 2,381 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,982 software developers and data experts.

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 3714
32,548 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,548 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,548 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

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

Similar topics

by: John | last post by:
HI, I'm having trouble importing text files. I had problem with Access '97 that forced me to reinstall. After I did this I couldn't import text files. The Help file recommends reinstall again,...
by: Ruslan Shlain | last post by:
I have a | (pipe) delimited file that i need to import in to a Dataset. With code below i get 7 items in the first column of each row and the rest is empty. PLEASE HELP Below if the code that...
by: gdaniels | last post by:
Hi, I need some help. The routine below runs when a command button is clicked. A prompt appears to enter the directory path to the folder containing delimited text files to be imported into an...
by: MMcCarthy | last post by:
This is a module that scans for files and folders on a specified path and describe them in comma separated values file in a text format. The information is stored in this file consecutively like:...
by: sena0112 | last post by:
Hi, I'm a beginner in visual basic and programming. My programme needs to let the user browse for a text file and when the user press a command button, the programme will list out the text file into...
by: totalnewbie | last post by:
Hi all, Here's the situation. We are sending out a survey and tracking who we send it to. The automated system that sends the survey generates a text file called "surveydd mmm yyyy.txt" every...
by: vvasude2 | last post by:
Hi all, How can i import text files with same specefication into one table I should be able to select multiple text files and import them automatically in the table. The import specefication...
by: Gareth Jones | last post by:
Hi, I am trying to import a number of CSV's from a number of folders including subfolders. The snag is that I need to record the folder name with the file contents from each folder. I can...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.