Help to import variable file with date in file name

[lookforsmt]

HI all

I am trying to import two text files on daily basis, the files have date in its name,
however the date is not today's date and can be any other dates, for e.g. as below
outwardsort_250620
onussort_250620

Below is the code which is working if the date is current date. How can i amend the code to import the below two files.
outwardsort_ 210520
onussort_210520

    Dim path1 As String, path2 As String, path3 As String, path4 As String

    path1 = "D:\Import\Folder\Outward" & Format(Date, "ddmmyy") & ".txt"
    path2 = "D:\Import\Folder\Onus" & Format(Date, "ddmmyy") & ".txt"
    
    DoCmd.TransferText acImportDelim, "Outward_Specification", "tbl_Outward", path1, False
    DoCmd.TransferText acImportDelim, "Onus_Specification", "tbl_Onus", path2, False

Thank you

 

[theDBguy]

Hi. Will the folders only always contain the file you need to import? If so, maybe you can just ignore the date part of the filename.

 

[pbaldy]

Try

path1 = "D:\Import\Folder\outwardsort_ 210520 .txt"

Or is there some relationship between the desired date and the current date?

 

[Isaac]

Well what is the criteria to find the "other dates", when this task is run on a daily basis? I'm missing some of the logic required here.

Of course for a one time load, you can just amend the following two lines to hardcode a path.
path1 = "D:\Import\Folder\Outward" & Format(Date, "ddmmyy") & ".txt"
path2 = "D:\Import\Folder\Onus" & Format(Date, "ddmmyy") & ".txt"

 

[lookforsmt]

Thanks all
Dear theDBguy, no there will be other files too in that folder. Currently i rename the file name to OutwardSort and OnusSort (without the date) and import the files. However i was looking for a automation solution, where user places the file in the folder and clicks import for the next steps.

Dear pbaldy, i need to import file names with different dates, how can i import the files without doing change in the code each time

Dear pisorsisaac, i tried your suggestion even before i post this thread, and it gives me an error: the MA database engine could not find the object 'OutwardSort_020720.txt'.....

 

[theDBguy]

Dear theDBguy, no there will be other files too in that folder. Currently i rename the file name to OutwardSort and OnusSort (without the date) and import the files. However i was looking for a automation solution, where user places the file in the folder and clicks import for the next steps.

Okay, so if there are going to be other files in the folder, are you only trying to import one of them? If so, how will Access know which one? What is the rule to select the correct folder (especially, if we can't rely on using the current date)?

 

[Isaac]

However i was looking for a automation solution, where user places the file in the folder and clicks import for the next steps

What about a solution where they browse for the file and select it? One way of doing it

 

[lookforsmt]

The transferText through import specifcation picks these two files after i rename them.

Yes, browse solution is another option but i dont want user to do this, since the user dumping the file in folder are not the same person and may take the wrong file or the hassle of always going through the files daily.

I was only hoping if there is a solution

 

[theDBguy]

I was only hoping if there is a solution

You'll have to give us the logic to use to determine which files to import (other than you manually renaming the files - I mean, how did you know which files to rename in the first place?). If you can give us a logic, we can translate it into code. For example, can we say you want to import whichever file was put in the folder the latest?

 

[Isaac]

Ok, no problem on the Browse solution - I understand. What I think we're still missing is the logic :

On any given day, if someone comes along and runs this code, how should the code decide which dates' files are to be imported?

 

[lookforsmt]

its random files called OutwardSort_ddmmyy & OnusSort_ddmmyy. the ddmmyy can be any date but it will be same date for both the files. UserA, dumps the files and UserB does the import. and these two files will be either deleted or moved to another location. So at any given point there will be only 1 set of files. I hope i have explained this.

 

[theDBguy]

its random files called OutwardSort_ddmmyy & OnusSort_ddmmyy. the ddmmyy can be any date but it will be same date for both the files. UserA, dumps the files and UserB does the import. and these two files will be either deleted or moved to another location. So at any given point there will be only 1 set of files. I hope i have explained this.

Almost. If UserA dumps a couple of files in a folder containing other files in it, how does UserB determines which files UserA dumped in there and have to be imported? What "logic" or business rule would UserB use to determine which files to import (among many)?

 

[lookforsmt]

The outwardSort and onuSort are files which will be required for sorting by UserB, which he would inform UserA to dump them on his request. So he would always mention the date as the file name. As i mentioned earlier, there will be only two files with these names, the rest of the files are with different names and for different purpose. Through import specification, it picks only these file.

 

[Isaac]

its random files called OutwardSort_ddmmyy & OnusSort_ddmmyy. the ddmmyy can be any date but it will be same date for both the files. UserA, dumps the files and UserB does the import. and these two files will be either deleted or moved to another location. So at any given point there will be only 1 set of files

As i mentioned earlier, there will be only two files with these names, the rest of the files are with different names and for different purpose

Okay ... the following code searches a folder for 2 files, following the exact pattern you have mentioned. It does not specifically validate whether the ddmmyy part is a valid date, it just goes as far as to check if the file name is (for example), OutwardSort_nnnnnn.txt (where nnnnnn could be anything).
Assuming this is good enough for you:

Function ReturnFileNames(strFolder As String) As String
'   filepath1|filepath2
Dim fso As Object, fsoFolder As Object, fsoFile As Object
Dim strPath1 As String, strPath2 As String, strPaths As String
Set fso = CreateObject("scripting.filesystemobject")
If fso.folderexists(strFolder) = False Then
    ReturnFileNames = "Error-no folder"
    Exit Function
End If
Set fsoFolder = fso.getfolder(strFolder)

For Each fsoFile In fsoFolder.Files
    'OutwardSort_ddmmyy + ".txt"
    If Left(fsoFile.Name, 11) = "OutwardSort" And (Len(fsoFile.Name) = 22) Then
        strPath1 = fsoFile.Path
    End If
  
    'OnusSort_ddmmyy + ".txt"
    If Left(fsoFile.Name, 8) = "OnusSort" And (Len(fsoFile.Name) = 19) Then
        strPath2 = fsoFile.Path
    End If
Next fsoFile
ReturnFileNames = strPath1 & "|" & strPath2

End Function

This will return the filepaths like this:
filepath1|filepath2

Your code might then be:

    Dim path1 As String, path2 As String, bothpaths as string
    bothpaths=ReturnFileNames("D:\Import\Folder")
  
    path1 = left(bothpaths,instr(1,bothpaths,"|")-1)
    path2 = replace(bothpaths,path1 & "|"
  
    DoCmd.TransferText acImportDelim, "Outward_Specification", "tbl_Outward", path1, False
    DoCmd.TransferText acImportDelim, "Onus_Specification", "tbl_Onus", path2, False

Now someone will probably follow up my post with a much simpler and better way.

 

[lookforsmt]

thanks pisorsisaac for the code, i will try and give my feedback.

 

[Isaac]

thanks pisorsisaac for the code, i will try and give my feedback.

The more files there are in the folder, the slower the code will run. Hopefully you're not dealing with thousands of files.
Don't forget to establish a totally fail-proof method of archiving/moving the files, else you'll end up with wrong information at some point ...

If you have a very large number of files in the folder, there may be a superior way involving Dir and wildcard, but I am not so good with that.
Just understand the dependencies of this process, I guess is the caveat I am making.

 

[lookforsmt]

there will not be many files, will keep that in mind

i tried your code, i am getting error on below line as Argument not optional

 path2 = replace(bothpaths,path1 & "|"

 

[Isaac]

Looks like it's missing some things on the end. My fault.

 path2 = replace(bothpaths,path1 & "|","")

 

[lookforsmt]

i amended the code,
now i get run-time error "5" Invalid procedure call or argument on below line

path1 = left(bothpaths,instr(1,bothpaths,"|")-1)

 

[Isaac]

Well, my guess then is that something isn't adding up at some point earlier in the process. that error you are getting for the left & instr indicates that the content of bothpaths just isn't sufficient for the left & instr interaction.

On my machine, I tested the code using a folder that had about 10 files, including two: OutwardSort_300620.txt and OnusSort_250620.txt

Are you certain you have the two files in the folder?

When the error occurs, try clicking "Debug". Once the code breaks and is yellow highlighting the line of code, go to View > Immediate Window (if not already shown), and type
?bothpaths [then hit Enter]. What does it show?