By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,277 Members | 1,237 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,277 IT Pros & Developers. It's quick & easy.

Reading a file with a time-stamp appended to the filename

P: n/a
pob
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 !

Jan 15 '07 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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" <po*******@gmail.comwrote in message
news:11**********************@11g2000cwr.googlegro ups.com...
>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 !

Jan 15 '07 #2

P: n/a
pob
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:
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:11**********************@11g2000cwr.go oglegroups.com...
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 !
Jan 15 '07 #3

P: n/a
pob
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:
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:
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:11**********************@11g2000cwr.go oglegroups.com...
>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 !
Jan 15 '07 #4

P: n/a
"pob" <po*******@gmail.comwrote
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

Jan 15 '07 #5

P: n/a
pob
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:
"pob" <pobnos...@gmail.comwrote
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
Jan 15 '07 #6

P: n/a
"pob" <po*******@gmail.comwrote
>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.
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
Jan 16 '07 #7

P: n/a
pob
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:
"pob" <pobnos...@gmail.comwrote
>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.
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
Jan 16 '07 #8

P: n/a
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" <po*******@gmail.comwrote in message
news:11**********************@11g2000cwr.googlegro ups.com...
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:
>"pob" <pobnos...@gmail.comwrote
> >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.
> 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

Jan 16 '07 #9

P: n/a
pob
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:
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:11**********************@11g2000cwr.go oglegroups.com...
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.
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:
"pob" <pobnos...@gmail.comwrote
>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.
Starting next week thefilewill 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 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.
It's not "rocket science," but you will need to be conversant in VBA
coding,
or to find someone who is.
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.
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 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:
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
Jan 28 '07 #10

This discussion thread is closed

Replies have been disabled for this discussion.