DosTips.com

Hello,

I have a specific requirement here. Have a txt file which consists of 5 different columns with 500 rows. I need to look into the 3rd column which consists of date(Ex.2012-01-04 10:18:49), and fetch all rows which are only 5 days old(In this case, i only want records which are older than 31-Dec-2012). Means i want to fetch all rows which are more than 5 days old(depending on the 3rd column-date) and put it to other file.This has to be done through batch file.

Any suggestions will be highly appreciated.

Thank You so much.

An input example and desired output would help.

File Name/Job ID Job Name Date Stream Cnt
4e34ghsj4440000b A1010001_SHAWS_P 2012-01-04 10:18:49 3
4e81f10f0000004c A1010542_SHAWS_P 2012-01-04 15:10:18 3
4e81f10f00000056 AB018985_MISC 2012-01-14 15:43:11 3
4e81f10f0bgh0057 AB020000_PDCR 2012-01-06 10:09:55 3
4f075c3b00000001 AGFVLFNG 2012-01-25 09:56:08 3
4f075c3b000bg002 ARCVLHTN 2012-01-09 12:25:19 3
4f075c3b000rf004 BD010000_TDEBASE 2012-01-10 12:19:30 1
4f075c3b00000005 BD023456_SYSDBA 2012-01-24 12:19:39 1
4f075c3b00ddf006 BD015402_SV_ADMIN 2012-01-10 12:47:46 1
4f075c3b00000007 BD016503_EDW_ABS 2012-01-10 12:48:02 1
4f075c3b00000008 BD010909_TDETABLES 2012-01-25 12:48:21 3
4f075c3b00000009 BD010005_MISC 2012-01-24 10:52:41 3
4f0df8a100derf02 BD002340_DBC 2012-01-30 13:04:30 1
4f0df8a1000g0003 BD012300_DBC 2012-01-30 13:28:41 1
4f0df8a100000004 BD000400_DBC 2012-01-30 13:43:16 1

It should only fetch the record which is older than 5 days. In this case taking 31 into consideration, it should only fetch records which are older than 25th.

I want output to be something like this:

File Name/Job ID Job Name Date Stream Cnt
4e34ghsj4440000b A1010001_SHAWS_P 2012-01-04 10:18:49 3
4e81f10f0000004c A1010542_SHAWS_P 2012-01-04 15:10:18 3
4e81f10f00000056 AB018985_MISC 2012-01-14 15:43:11 3
4e81f10f0bgh0057 AB020000_PDCR 2012-01-06 10:09:55 3
4f075c3b00000001 AGFVLFNG 2012-01-25 09:56:08 3
4f075c3b000bg002 ARCVLHTN 2012-01-09 12:25:19 3
4f075c3b000rf004 BD010000_TDEBASE 2012-01-10 12:19:30 1
4f075c3b00000005 BD023456_SYSDBA 2012-01-24 12:19:39 1
4f075c3b00ddf006 BD015402_SV_ADMIN 2012-01-10 12:47:46 1
4f075c3b00000007 BD016503_EDW_ABS 2012-01-10 12:48:02 1
4f075c3b00000008 BD010909_TDETABLES 2012-01-25 12:48:21 3
4f075c3b00000009 BD010005_MISC 2012-01-24 10:52:41 3

you can try this script.cmd:


session protocol:


I used a new test data file and the date format YYYY-MM-DD. The JDN function is by Aacini.

Hi Endoro,

This doesn't seem to be working. I am copying your code with your file.txt but i am getting the same output in file.new as file.txt. Its not filtering any records. And its not working in my file as well. Tell me i am doing something wrong.

probably you have another date format.
I used the format from the file, you provided.

Date format is correct. Endoro i want the records older than 2 days. But your script is giving me records only for 2 days counting from sysdate. what small change i will have to make it work.
Please suggest me on that. Thanks alot

In this line you can replace the variable %date% with another date in the format YYY-MM-DD.
For other date differences put hier the new days in, eg. "2".

I am putting it in Same way.
Here is my subscript:

@echo off &SETLOCAL ENABLEDELAYEDEXPANSION

call:DateToJDN 2013-07-02 JDN5
SET /a JDN5-=2

(FOR /f "tokens=1-5" %%a IN (file.txt) DO (
CALL:DateToJDN %%c jdn
IF !jdn! gtr %JDN5% ECHO(%%a %%b %%c %%d %%e
))>file.new
goto:eof

rem Convert the date to Julian Day Number
:DateToJDN YYYY-MM-DD jdn=
setlocal
set date=%1
pause
set /A yy=%date:~0,4%, mm=1%date:~5,2% %% 100, dd=1%date:~-2% %% 100
set /A a=mm-14, jdn=(1461*(yy+4800+a/12))/4+(367*(mm-2-12*(a/12)))/12-(3*((yy+4900+a/12)/100))/4+dd-32075
endlocal & set %2=%jdn%
exit /B

I have doubt in the colored variable. Even though i am passing my custom date to the line
call:DateToJDN 2013-07-02 JDN5
this script is not fetching anything to me.

Its working fine now Endoro. You really helped me a lot. Hats off to you for this. This was like hectic requirement for me from last 2-3 days. You resolved it within a snap of finger. Thanks a lot.