Reading a file with a time-stamp appended to the filename | | |
I currently have a procedure that loops thru a recordset to determine
what files need to be loaded to my database. The naming convention of
the files has always been accounts.txt, namelist.txt, etc. Within the
procedure I also validate that the file loaded to the server was loaded
within the current day. Everything has run smooth for quite some time
using this method.
Now the vendor has told me he has choice, but to the send the file to
me like accountmmddyyhhmmss.txt. How can I load these files within my
procedure now that a time-stamp has been appended to the file name.
Note I am only concerned about loading the latest file and that file
must be from the current day.
Thanks in advance !
| | | | re: Reading a file with a time-stamp appended to the filename
You could strip out the datetime part from the filename
something like this...
Dim myfiledate as string
Dim myfile as string
myfile=accountmmddyyhhnnss.txt
myfiledate=right(myfile,16)
'result=mmddyyhhnnss.txt
myfiledate=left(myfiledate,14)
'result=mmddyyhhnnss
then you can strip out the datepart bits using Mid(,,) then rebuild it as a
date to check if its todays date
"pob" <pobnospam@gmail.comwrote in message
news:1168885504.644993.220240@11g2000cwr.googlegro ups.com... Quote:
>I currently have a procedure that loops thru a recordset to determine
what files need to be loaded to my database. The naming convention of
the files has always been accounts.txt, namelist.txt, etc. Within the
procedure I also validate that the file loaded to the server was loaded
within the current day. Everything has run smooth for quite some time
using this method.
>
Now the vendor has told me he has choice, but to the send the file to
me like accountmmddyyhhmmss.txt. How can I load these files within my
procedure now that a time-stamp has been appended to the file name.
Note I am only concerned about loading the latest file and that file
must be from the current day.
>
Thanks in advance !
>
| | | | re: Reading a file with a time-stamp appended to the filename
If I do not know the time or hour of the file how is it possible for me
to even bring the file into the database ?
On Jan 15, 1:21 pm, "tombsy" <iclmailspam...@tiscali.co.ukwrote: Quote:
You could strip out the datetime part from the filename
>
something like this...
Dim myfiledate as string
Dim myfile as string
>
myfile=accountmmddyyhhnnss.txt
>
myfiledate=right(myfile,16)
'result=mmddyyhhnnss.txt
>
myfiledate=left(myfiledate,14)
'result=mmddyyhhnnss
>
then you can strip out the datepart bits using Mid(,,) then rebuild it as a
date to check if its todays date
>
"pob" <pobnos...@gmail.comwrote in messagenews:1168885504.644993.220240@11g2000cwr.go oglegroups.com...
> Quote:
I currently have a procedure that loops thru a recordset to determine
what files need to be loaded to my database. The naming convention of
the files has always been accounts.txt, namelist.txt, etc. Within the
procedure I also validate that the file loaded to the server was loaded
within the current day. Everything has run smooth for quite some time
using this method.
> Quote:
Now the vendor has told me he has choice, but to the send the file to
me like accountmmddyyhhmmss.txt. How can I load these files within my
procedure now that a time-stamp has been appended to the file name.
Note I am only concerned about loading the latest file and that file
must be from the current day.
> Quote:
Thanks in advance !
| | | | re: Reading a file with a time-stamp appended to the filename
Note the file sits out on the LAN as account011506024800.txt. I now
need to load the file, but my procedure is looking to load account.txt.
How do I change the procedure to indicate that file
account011506024800.txt should be loaded. I will never know the exact
time of the file so perhaps I just need to use a wildcard character ?
On Jan 15, 2:04 pm, "pob" <pobnos...@gmail.comwrote: Quote:
If I do not know the time or hour of the file how is it possible for me
to even bring the file into the database ?
>
On Jan 15, 1:21 pm, "tombsy" <iclmailspam...@tiscali.co.ukwrote:
> Quote:
You could strip out the datetime part from the filename
> Quote:
something like this...
Dim myfiledate as string
Dim myfile as string
> Quote:
myfile=accountmmddyyhhnnss.txt
> Quote:
myfiledate=right(myfile,16)
'result=mmddyyhhnnss.txt
> Quote:
myfiledate=left(myfiledate,14)
'result=mmddyyhhnnss
> Quote:
then you can strip out the datepart bits using Mid(,,) then rebuild it as a
date to check if its todays date
> Quote:
"pob" <pobnos...@gmail.comwrote in messagenews:1168885504.644993.220240@11g2000cwr.go oglegroups.com...
> Quote: Quote:
>I currently have a procedure that loops thru a recordset to determine
what files need to be loaded to my database. The naming convention of
the files has always been accounts.txt, namelist.txt, etc. Within the
procedure I also validate that the file loaded to the server was loaded
within the current day. Everything has run smooth for quite some time
using this method.
> Quote: Quote:
Now the vendor has told me he has choice, but to the send the file to
me like accountmmddyyhhmmss.txt. How can I load these files within my
procedure now that a time-stamp has been appended to the file name.
Note I am only concerned about loading the latest file and that file
must be from the current day.
> Quote: Quote:
Thanks in advance !
| | | | re: Reading a file with a time-stamp appended to the filename
"pob" <pobnospam@gmail.comwrote Quote:
If I do not know the time or hour of the file
how is it possible for me to even bring the
file into the database?
Perhaps, if you would clarify just what you _will_ know about these files,
someone could make a suggestion.
And, lest you refer me to your later post, files do not "sit out on the
LAN" -- files exist on a storage device, likely a disk of some type. If you
have enough information about the files, their names, the location where
they will reside, etc., it is possible that you can write code to determine
the files that need to be imported or linked, or to display choices to the
user of the database to select.
If the answer is the provider told you "Guess at it," then the possibility
of your getting help here approaches the infinitesimal. If you have enough
information that you could manually find and identify them, the possibility
of your getting help here increases significantly.
Larry Linson
Microsoft Access MVP | | | | re: Reading a file with a time-stamp appended to the filename
I know that everday I will receive an account file 3 times a day. The
file will be FTP'd to my LAN at 9am, noon, and 3pm.
At any given time my users will need to load the latest account file
available found on my LAN. In todays world this is easy since I know
the file will always be account.txt. In todays world the previous
account.txt is always over-written with the latest account.txt file.
Starting next week the file will be FTP'd to my LAN as follows:
account-012506-090000.txt
account-012506-120000.txt
account-012506-150000.txt
Thus, I need to figure out how to load the latest file into my access
database. Currently I have a procedure that only is only prepared to
look for and load a file called account.txt.
Thanks in advance and thanks for the suggestion on how to better
support/phrase the question. Hope the above helps.
On Jan 15, 4:05 pm, "Larry Linson" <boun...@localhost.notwrote: Quote:
"pob" <pobnos...@gmail.comwrote
> Quote:
If I do not know the time or hour of the file
how is it possible for me to even bring the
file into the database?
>
Perhaps, if you would clarify just what you _will_ know about these files,
someone could make a suggestion.
>
And, lest you refer me to your later post, files do not "sit out on the
LAN" -- files exist on a storage device, likely a disk of some type. If you
have enough information about the files, their names, the location where
they will reside, etc., it is possible that you can write code to determine
the files that need to be imported or linked, or to display choices to the
user of the database to select.
>
If the answer is the provider told you "Guess at it," then the possibility
of your getting help here approaches the infinitesimal. If you have enough
information that you could manually find and identify them, the possibility
of your getting help here increases significantly.
>
Larry Linson
Microsoft Access MVP
| | | | re: Reading a file with a time-stamp appended to the filename
"pob" <pobnospam@gmail.comwrote Quote:
>I know that everday I will receive an account file 3 times a day. The
file will be FTP'd to my LAN at 9am, noon, and 3pm.
At any given time my users will need to load the latest account file
available found on my LAN. In todays world this is easy since I know
the file will always be account.txt. In todays world the previous
account.txt is always over-written with the latest account.txt file.
>
Starting next week the file will be FTP'd to my LAN as follows:
account-012506-090000.txt
account-012506-120000.txt
account-012506-150000.txt
Examples are helpful, but definitions with examples are better. In an
earlier post you mentioned "namelist" as well as "account".
Would the defintion be something like this?
"One of several known-in-advance text strings, followed by a hyphen,
followed by a date in mmddyy format, followed by a hyphen followed by time
in hhmmss format."
I'll say again, those files may be in your e-mail, or they may be a disk
file, but "on my LAN" isn't an accurate description, and, if you are going
to create code to process information, then you must accurately define the
information.
If the files, as I suspect, are in a folder on a disk on a server attached
to the same LAN as your computer, you can use the "Dir" statement to
retrieve the file name. You can write VBA code to interpret the file name to
determine the file type, date and time, and pick the most recent one. You
can use the TransferText or TransferDatabase statement to import it into
your Access database.
It's not "rocket science," but you will need to be conversant in VBA coding,
or to find someone who is. Quote:
Thus, I need to figure out how to load the latest file into my access
database. Currently I have a procedure that only is only prepared to
look for and load a file called account.txt.
This is likely to be a good starting point.
How comfortable are you with VBA code? People here can be very helpful
getting you past the stumbling blocks, but it isn't a place to get large
chunks of code written for you (but even that happens sometimes, if what
you're doing is particularly interesting to someone here).
Once you've parsed out the date and time from the filename, into String
variables strD and strT, this will give you a Date variable datDT with the
date and time so you can compare it against others similarly converted:
datDT= DateSerial(CInt(Right(strD, 2)) + 2000, Left(strD, 2), Mid(strD, 3,
2)) + _
TimeSerial(CInt(Left(strT, 2)), CInt(Mid(strT, 3, 2)),
CInt(Right(strT, 2)))
and if you make datDT an array, you can compare to see which is the largest
(most recent).
Larry Linson
Microsoft Access MVP | | | | re: Reading a file with a time-stamp appended to the filename
Your assumption is correct that the files are in a folder on a disk on
a server attached to the same LAN as my computer. I have average
skills in VB as was able to create the current procedure that opens the
recordset (a table), retrieves all the file names (such as asset.txt)
that need to be imported begins the process of the import and other
steps.
I followed all of your e-mail with the exception of making a "datDT an
array". Any further help on the array part would be great.
Thanks again for your time and support.
On Jan 15, 6:03 pm, "Larry Linson" <boun...@localhost.notwrote: Quote:
"pob" <pobnos...@gmail.comwrote
> Quote:
>I know that everday I will receive an account file 3 times a day. The
file will be FTP'd to my LAN at 9am, noon, and 3pm.
At any given time my users will need to load the latest account file
available found on my LAN. In todays world this is easy since I know
the file will always be account.txt. In todays world the previous
account.txt is always over-written with the latest account.txt file.
>
Starting next week the file will be FTP'd to my LAN as follows:
account-012506-090000.txt
account-012506-120000.txt
account-012506-150000.txt
>
Examples are helpful, but definitions with examples are better. In an
earlier post you mentioned "namelist" as well as "account".
>
Would the defintion be something like this?
>
"One of several known-in-advance text strings, followed by a hyphen,
followed by a date in mmddyy format, followed by a hyphen followed by time
in hhmmss format."
>
I'll say again, those files may be in your e-mail, or they may be a disk
file, but "on my LAN" isn't an accurate description, and, if you are going
to create code to process information, then you must accurately define the
information.
>
If the files, as I suspect, are in a folder on a disk on a server attached
to the same LAN as your computer, you can use the "Dir" statement to
retrieve the file name. You can write VBA code to interpret the file name to
determine the file type, date and time, and pick the most recent one. You
can use the TransferText or TransferDatabase statement to import it into
your Access database.
>
It's not "rocket science," but you will need to be conversant in VBA coding,
or to find someone who is.
> Quote:
Thus, I need to figure out how to load the latest file into my access
database. Currently I have a procedure that only is only prepared to
look for and load a file called account.txt.
>
This is likely to be a good starting point.
>
How comfortable are you with VBA code? People here can be very helpful
getting you past the stumbling blocks, but it isn't a place to get large
chunks of code written for you (but even that happens sometimes, if what
you're doing is particularly interesting to someone here).
>
Once you've parsed out the date and time from the filename, into String
variables strD and strT, this will give you a Date variable datDT with the
date and time so you can compare it against others similarly converted:
>
datDT= DateSerial(CInt(Right(strD, 2)) + 2000, Left(strD, 2), Mid(strD, 3,
2)) + _
TimeSerial(CInt(Left(strT, 2)), CInt(Mid(strT, 3, 2)),
CInt(Right(strT, 2)))
>
and if you make datDT an array, you can compare to see which is the largest
(most recent).
>
Larry Linson
Microsoft Access MVP
| | | | re: Reading a file with a time-stamp appended to the filename
What version of Access are you using? In Access 2003, open a module window
(the "Visual Basic Environment" or VBE), click on Visual Basic Help, the
search on "Declare an Array"... two items in the list wil be helpful...
Declaring an Array, and Using Arrays.
Access 2002 VB Help has similar topics, and that's all the working Access
versions I have on this machine to check.
VB Help is also where you'll find information on the Dir function to return
the names of files in a folder.
If you run into a stumbling block, post back here to ask about it...
Larry Linson
Microsoft Access MVP
"pob" <pobnospam@gmail.comwrote in message
news:1168913626.413543.258380@11g2000cwr.googlegro ups.com... Quote:
Your assumption is correct that the files are in a folder on a disk on
a server attached to the same LAN as my computer. I have average
skills in VB as was able to create the current procedure that opens the
recordset (a table), retrieves all the file names (such as asset.txt)
that need to be imported begins the process of the import and other
steps.
>
I followed all of your e-mail with the exception of making a "datDT an
array". Any further help on the array part would be great.
>
Thanks again for your time and support.
>
>
On Jan 15, 6:03 pm, "Larry Linson" <boun...@localhost.notwrote: Quote:
>"pob" <pobnos...@gmail.comwrote
>> Quote:
> >I know that everday I will receive an account file 3 times a day. The
> file will be FTP'd to my LAN at 9am, noon, and 3pm.
> At any given time my users will need to load the latest account file
> available found on my LAN. In todays world this is easy since I know
> the file will always be account.txt. In todays world the previous
> account.txt is always over-written with the latest account.txt file.
> >
> Starting next week the file will be FTP'd to my LAN as follows:
> account-012506-090000.txt
> account-012506-120000.txt
> account-012506-150000.txt
>>
>Examples are helpful, but definitions with examples are better. In an
>earlier post you mentioned "namelist" as well as "account".
>>
>Would the defintion be something like this?
>>
>"One of several known-in-advance text strings, followed by a hyphen,
>followed by a date in mmddyy format, followed by a hyphen followed by
>time
>in hhmmss format."
>>
>I'll say again, those files may be in your e-mail, or they may be a disk
>file, but "on my LAN" isn't an accurate description, and, if you are
>going
>to create code to process information, then you must accurately define
>the
>information.
>>
>If the files, as I suspect, are in a folder on a disk on a server
>attached
>to the same LAN as your computer, you can use the "Dir" statement to
>retrieve the file name. You can write VBA code to interpret the file name
>to
>determine the file type, date and time, and pick the most recent one. You
>can use the TransferText or TransferDatabase statement to import it into
>your Access database.
>>
>It's not "rocket science," but you will need to be conversant in VBA
>coding,
>or to find someone who is.
>> Quote:
> Thus, I need to figure out how to load the latest file into my access
> database. Currently I have a procedure that only is only prepared to
> look for and load a file called account.txt.
>>
>This is likely to be a good starting point.
>>
>How comfortable are you with VBA code? People here can be very helpful
>getting you past the stumbling blocks, but it isn't a place to get large
>chunks of code written for you (but even that happens sometimes, if what
>you're doing is particularly interesting to someone here).
>>
>Once you've parsed out the date and time from the filename, into String
>variables strD and strT, this will give you a Date variable datDT with
>the
>date and time so you can compare it against others similarly converted:
>>
> datDT= DateSerial(CInt(Right(strD, 2)) + 2000, Left(strD, 2), Mid(strD,
>3,
>2)) + _
> TimeSerial(CInt(Left(strT, 2)), CInt(Mid(strT, 3, 2)),
>CInt(Right(strT, 2)))
>>
>and if you make datDT an array, you can compare to see which is the
>largest
>(most recent).
>>
> Larry Linson
> Microsoft Access MVP
>
| | | | re: Reading a file with a time-stamp appended to the filename
Ok, I looked at your post, the help docs and a few other posts and
this is what I came up with, any suggestions for improvement ?
Function fileageck() As Boolean()
Dim myfilename As String
Dim myfilepath As String
Dim strD As String
Dim strT As String
Dim datDT As Date
Dim i As Integer
Dim sngarray() As Date 'array to store filenames
myfilepath = "F:\test\"
myfilename = Dir(myfilepath & "test*.txt", vbHidden)
Do Until myfilename = ""
ReDim Preserve sngarray(i)
strD = Mid(myfilename, 5, 8)
strT = Mid(myfilename, 13, 4)
datDT = DateSerial(CInt(Mid(strD, 3, 2)) + 2000, Mid(strD, 5, 2),
Right(strD, 2)) + TimeSerial(CInt(Left(strT, 2)), CInt(Mid(strT, 3,
2)), CInt(Right(strT, 2)))
sngarray(i) = datDT
myfilename = Dir
i = i + 1
Loop
For i = 0 To UBound(sngarray)
myfilename = sngarray(i)
If i 0 Then
If myfilename sngarray(i - 1) Then
'File being tested is the newest so this file is the winner until
the next loop
Else
myfilename = sngarray(i - 1)
End If
Else
End If
Next i
End Function
po
On Jan 15, 11:34 pm, "Larry Linson" <boun...@localhost.notwrote: Quote:
What version of Access are you using? In Access 2003, open a module window
(the "Visual Basic Environment" or VBE), click on Visual Basic Help, the
search on "Declare an Array"... two items in the list wil be helpful...
Declaring an Array, and Using Arrays.
>
Access 2002 VB Help has similar topics, and that's all the working Access
versions I have on this machine to check.
>
VB Help is also where you'll find information on the Dir function to return
the names of files in a folder.
>
If you run into a stumbling block, post back here to ask about it...
>
Larry Linson
Microsoft Access MVP
>
"pob" <pobnos...@gmail.comwrote in messagenews:1168913626.413543.258380@11g2000cwr.go oglegroups.com...
> Quote:
Your assumption is correct that the files are in a folder on a disk on
a server attached to the same LAN as my computer. I have average
skills in VB as was able to create the current procedure that opens the
recordset (a table), retrieves all thefilenames (such as asset.txt)
that need to be imported begins the process of the import and other
steps.
> Quote:
I followed all of your e-mail with the exception of making a "datDT an
array". Any further help on the array part would be great.
> Quote:
Thanks again for your time and support.
> Quote:
On Jan 15, 6:03 pm, "Larry Linson" <boun...@localhost.notwrote: Quote:
"pob" <pobnos...@gmail.comwrote
> Quote: Quote:
>I know that everday I will receive an accountfile3 times a day. The
>filewill be FTP'd to my LAN at 9am, noon, and 3pm.
At any given time my users will need to load the latest accountfile
available found on my LAN. In todays world this is easy since I know
thefilewill always be account.txt. In todays world the previous
account.txt is always over-written with the latest account.txtfile.
> Quote: Quote:
Starting next week thefilewill be FTP'd to my LAN as follows:
account-012506-090000.txt
account-012506-120000.txt
account-012506-150000.txt
> Quote: Quote:
Examples are helpful, but definitions with examples are better. In an
earlier post you mentioned "namelist" as well as "account".
> Quote: Quote:
Would the defintion be something like this?
> Quote: Quote:
"One of several known-in-advance text strings, followed by a hyphen,
followed by a date in mmddyy format, followed by a hyphen followed by
time
in hhmmss format."
> Quote: Quote:
I'll say again, those files may be in your e-mail, or they may be a disk
>file, but "on my LAN" isn't an accurate description, and, if you are
going
to create code to process information, then you must accurately define
the
information.
> Quote: Quote:
If the files, as I suspect, are in a folder on a disk on a server
attached
to the same LAN as your computer, you can use the "Dir" statement to
retrieve thefilename. You can write VBA code to interpret thefilename
to
determine thefiletype, date and time, and pick the most recent one. You
can use the TransferText or TransferDatabase statement to import it into
your Access database.
> Quote: Quote:
It's not "rocket science," but you will need to be conversant in VBA
coding,
or to find someone who is.
> Quote: Quote:
Thus, I need to figure out how to load the latestfileinto my access
database. Currently I have a procedure that only is only prepared to
look for and load afilecalled account.txt.
> Quote: Quote:
This is likely to be a good starting point.
> Quote: Quote:
How comfortable are you with VBA code? People here can be very helpful
getting you past the stumbling blocks, but it isn't a place to get large
chunks of code written for you (but even that happens sometimes, if what
you're doing is particularly interesting to someone here).
> Quote: Quote:
Once you've parsed out the date and time from thefilename, into String
variables strD and strT, this will give you a Date variable datDT with
the
date and time so you can compare it against others similarly converted:
> Quote: Quote:
datDT= DateSerial(CInt(Right(strD, 2)) + 2000, Left(strD, 2), Mid(strD,
3,
2)) + _
TimeSerial(CInt(Left(strT, 2)), CInt(Mid(strT, 3, 2)),
CInt(Right(strT, 2)))
> Quote: Quote:
and if you make datDT an array, you can compare to see which is the
largest
(most recent).
> Quote: Quote:
Larry Linson
Microsoft Access MVP
|  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,376 network members.
|
