473,327 Members | 1,936 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

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

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
9 2071
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
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
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
"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
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
"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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: SB | last post by:
Hello. I have an input file which is laid out in the following manner... Name Day 1 am time 1 am time 2 appointment pm time 1 pm time 2 appointment Day 2
19
by: Lionel B | last post by:
Greetings, I need to read (unformatted text) from stdin up to EOF into a char buffer; of course I cannot allocate my buffer until I know how much text is available, and I do not know how much...
1
by: Magnus | last post by:
allrite folks, got some questions here... 1) LAY-OUT OF REPORTS How is it possible to fundamentaly change the lay-out/form of a report in access? I dont really know it that "difficult", but...
6
by: KevinD | last post by:
assumption: I am new to C and old to COBOL I have been reading a lot (self teaching) but something is not sinking in with respect to reading a simple file - one record at a time. Using C, I am...
3
by: Nick | last post by:
I have found a class that compresses and uncompresses data but need some help with how to use part of it below is the deflate method which compresses the string that I pass in, this works OK. At...
16
by: WATYF | last post by:
Hi there... I have a huge text file that needs to be processed. At the moment, I'm loading it into memory in small chunks (x amount of lines) and processing it that way. I'd like the process to be...
6
by: efrenba | last post by:
Hi, I came from delphi world and now I'm doing my first steps in C++. I'm using C++builder because its ide is like delphi although I'm trying to avoid the vcl. I need to insert new features...
2
by: Derik | last post by:
I've got a XML file I read using a file_get_contents and turn into a simpleXML node every time index.php loads. I suspect this is causing a noticeable lag in my page-execution time. (Or the...
3
by: xyz | last post by:
Hi, I have a text file around 7GB includes 100 million lines... I want to read the data line by line when I approach my module.. ie., when i read for the first time , my program shuld read only...
1
by: ndedhia1 | last post by:
I was reading in a log file like this that had no milliseconds: QuoteBlockTiming exceeded 1 ms: 1 --- Thu Dec 10 02:01:40 CST 2009 170.137.15.155 Class key = 601650761 block size QuoteBlockTiming...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.