Checking if a date in the file is near the end of month 1

[AnaFlor]

Hello,

I am trying to do the following:
- I receive a file every week. In one line of the file I have something like "Values from 03/05/2004 to 09/05/2004".
I need to check if the last date of that line of the file (in this case 09/05/2004) is less than or equal to the end of the month (31/05/2004). If so, I need to copy the file to c:\tmp. If the condition is false it doesn't do anything.

Can you help me solving this?

Thanks

 

[EBGreen]

Open the file using the FileSystemObject. Read the file using the readline method of the file object. Determine how to identify the line of interest. When you read the line of interest, then retrieve the date from the line and convert it to a date value. Next compare the date to the date of the end of the month (NOTE: the logic that you have expressed above i.e. "is less than or equal to the end of the month" will always be true for any date in this month).

If you need help on the specifics of any of these, post your question back here along with what you have tried so far.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[AnaFlor]

can you explain me a little more please?
I don't know much about vb script. I start learning last week.


the logic is less than or equal to the end of the month, I wanted to say
is less than or equal to 7 days before the end of the month

Can you help me?

Thanks

 

[EBGreen]

Can you post an example of the file you will be reading, or is it too large? The first step is to determine how we know the right line to read the date from.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[AnaFlor]

the date is always in line 1 and it starts at character 34

 

[EBGreen]

Ok let's do it in steps. First we will determine the date that is in the file. Use this code and let me know when it is telling you the correct date:

Option Explicit

Dim oFSO, oFile, strLine, strDate, strFile

strFile = "myFile.txt" 'Change this to be the path and name
                       'of the file that you want to get
                       'the date from.

'Instantiate a filesystemobject for file access
Set oFSO = CreateObject("Scripting.FileSystemObject")
'Open the file and instantiate a file object
Set oFile = oFSO.OpenTextFile(strFile)

'We only care about the first line in the file so read it 
'and close the file
strLine = oFIle.ReadLine
oFile.Close
Set oFile = Nothing

'Now get from char 34 to 44
strDate = Mid(strLine, 34, 10)

'Display the date
WScript.Echo strDate

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[AnaFlor]

The problem is that the date is in the column 4 of that file.

what if I check the date in a temporary table.
in that case, in dts I load that date to a temporary table wich have only 09/05/2004 and then make a code to compare the end of the month with that date?

is it easier?

Thanks

 

[tsuji]

Hello AnaFlor,

You can use TomThumbKP to read the line. One the string is retrieve, maybe you can use this time the regular express to sort thing out. As an quick and dirty illustration:

s="afaducasdfmasdlrpfdlklasdfkle421/5/2004dfasdfpoew;ldf"
set regEx=new regexp
regEx.Pattern = "[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}"

regEx.IgnoreCase = true
regEx.Global = true
set matches = regEx.Execute(s)
wscript.echo matches.count
for each match in matches
	wscript.echo match.value
next

regards - tsuji

 

[AnaFlor]

TomThumbKP, I was trying to do what you said and it is giving me the correct date: 09/05/2004.

So the first step is correct.
Now, I have to check if this date is less or equal than 7 days to the end of the month.

 

[EBGreen]

Ok, there is probably a better way to determine it, but here is how I would do it. Stick this onto the end of what you have so far:

Dim strFileDay, strFileMonth, strFileYear, arrMonthEnd
Dim strMonthEnd
'First get the date parts from the file date
strFileDay = Day(strDate)
strFileMonth = Month(strDate)
strFileYear = Year(strDate)
'WScript.Echo "Day = " & strFileDay
'WScript.Echo "Month = " & strFileMonth
'WScript.Echo "Year = " & strFileYear

'Make a list of the month end days. This part
'could probably be done programatically And
'doing it this way will not account for leap years
'but this way is simple
arrMonthEnd = Array("31", "28", "31", "30", "31", "30", "31", "31", "30", "31", "30", "31")
strMonthEnd = strFileMonth & "/" & arrMonthEnd(CStr(strFileMonth)-1) & "/" & strFileYear
'WScript.Echo strMonthEnd

If DateDiff("d", strDate, strMonthEnd) >= 7 Then
	WScript.Echo "The file is more than seven days from the last day of the month"
Else
	WScript.Echo "The file is less than seven days from the last day of the month"
End If

Get this working and let me know when it is. As an aside, it appears that you may be using European date format. I wrote this using American. You may need to change the order of the parts when you build the strMonthEnd variable to accomodate the difference.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[PHV]

What about this ?
If Day(DateAdd("d", 7, strDate)) <= 7 Then
MsgBox "is less than or equal to 7 days before the end of the month"
End If

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[EBGreen]

Leave it to PHV to find a better solution. Here is the complete changed code:

Option Explicit

Dim oFSO, oFile, strLine, strDate, strFile

strFile = "myFile.txt" 'Change this to be the path and name
                       'of the file that you want to get
                       'the date from.

'Instantiate a filesystemobject for file access
Set oFSO = CreateObject("Scripting.FileSystemObject")
'Open the file and instantiate a file object
Set oFile = oFSO.OpenTextFile(strFile)

'We only care about the first line in the file so read it 
'and close the file
strLine = oFIle.ReadLine
oFile.Close
Set oFile = Nothing

'Now get from char 34 to 44
strDate = Mid(strLine, 34, 10)

If Day(DateAdd("d", 7, strDate)) <= 7 Then
  	WScript.Echo "is less than or equal to 7 days before the end of the month"
Else
	WScript.Echo "is more than 7 days before the end of the month"
End If

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[AnaFlor]

Thanks for all your help.

 

[EBGreen]

A star for PHV seeing the simple solution.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]