Fetching specific column from text file using batch file

[bipul049]

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.

[Endoro]

An input example and desired output would help.

[bipul049]

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

[Endoro]

you can try this script.cmd:

Code: Select all

@echo off &SETLOCAL ENABLEDELAYEDEXPANSION

call:DateToJDN %DATE% JDN5
SET /a JDN5-=5

(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
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

session protocol:

Code: Select all

>type file.txt
4e34ghsj4440000b A1010001_SHAWS_P 2013-07-04 10:18:49 3
4e81f10f0000004c A1010542_SHAWS_P 2013-07-03 15:10:18 3
4e81f10f00000056 AB018985_MISC 2013-07-02 15:43:11 3
4e81f10f0bgh0057 AB020000_PDCR 2013-07-01 10:09:55 3
4f075c3b00000001 AGFVLFNG 2013-06-30 09:56:08 3
4f075c3b000bg002 ARCVLHTN 2013-06-29 12:25:19 3
4f075c3b000rf004 BD010000_TDEBASE 2013-06-28 12:19:30 1
4f075c3b00000005 BD023456_SYSDBA 2013-06-27 12:19:39 1
4f075c3b00ddf006 BD015402_SV_ADMIN 2013-06-26 12:47:46 1
4f075c3b00000007 BD016503_EDW_ABS 2013-06-25 12:48:02 1
4f075c3b00000008 BD010909_TDETABLES 2013-06-24 12:48:21 3
4f075c3b00000009 BD010005_MISC 2013-06-23 10:52:41 3
4f0df8a100derf02 BD002340_DBC 2013-06-22 13:04:30 1
4f0df8a1000g0003 BD012300_DBC 2013-06-21 13:28:41 1
4f0df8a100000004 BD000400_DBC 2013-06-20 13:43:16 1

>script.cmd

>type file.new
4e34ghsj4440000b A1010001_SHAWS_P 2013-07-04 10:18:49 3
4e81f10f0000004c A1010542_SHAWS_P 2013-07-03 15:10:18 3
4e81f10f00000056 AB018985_MISC 2013-07-02 15:43:11 3
4e81f10f0bgh0057 AB020000_PDCR 2013-07-01 10:09:55 3
4f075c3b00000001 AGFVLFNG 2013-06-30 09:56:08 3

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

[bipul049]

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.

[Endoro]

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

[bipul049]

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

[Endoro]

Code: Select all

call:DateToJDN %DATE% JDN5

In this line you can replace the variable %date% with another date in the format YYY-MM-DD.

Code: Select all

SET /a JDN5-=5

For other date differences put hier the new days in, eg. "2".

[bipul049]

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.

[bipul049]

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.