[help] i need to extract data from a text file via batch
Moderator: DosItHelp
Re: [help] i need to extract data from a text file via batch
You upload the source file to your company public webspace and provide a URL.
Or you can use any public web fileserving web site.
Or you can use any public web fileserving web site.
Re: [help] i need to extract data from a text file via batch
i have provided the input and desired output files to dropbox. These are defintely the correct format
input
https://www.dropbox.com/s/mb8npfjkt95bkxy/input2.txt
output
https://www.dropbox.com/s/z20n9ob0b4lt49m/output.xls
Hope this round is clear the doubt.. i really appreciate all the effort u guys have provided..
input
https://www.dropbox.com/s/mb8npfjkt95bkxy/input2.txt
output
https://www.dropbox.com/s/z20n9ob0b4lt49m/output.xls
Hope this round is clear the doubt.. i really appreciate all the effort u guys have provided..

Last edited by cheeseng on 31 May 2012 02:11, edited 1 time in total.
Re: [help] i need to extract data from a text file via batch
Edit the links and test them. ATM they are broken.
Re: [help] i need to extract data from a text file via batch
hi,
changed.. sorry for the mistake..
changed.. sorry for the mistake..
Re: [help] i need to extract data from a text file via batch
For the output in csv format can ignore rc code review1 review2 and remark.
Re: [help] i need to extract data from a text file via batch
"Diane Tee" doesn't appear in the source file so please give us an example of the output lines you are expecting - do a couple manually.
Re: [help] i need to extract data from a text file via batch
hi foxidrive,
thanks for asking..
in fact, the output for column header of rc code, reviewer 1, reviewer 2, remark can be ignore..
i have done few manual output for reference.. thanks
to ensure i didn't screw out the input file maybe u can use the dropbox version.. really appreciate ur help... 
input::
case1
reference user is not operator, hence column group is empty
case2
reference user is multiple, hence group and description will have content but keyword is "GRP"...
case3
where reference user is multiple, has more than 1 group and description...
I have think the column header can be done in this way..
here i attached the new_input.txt which has complete input
http://dl.dropbox.com/u/6621198/new_input.txt
however, for testing purpose u can use the one i offered previously as this input is 20mb kinda of slow down the processing..
thanks for asking..
in fact, the output for column header of rc code, reviewer 1, reviewer 2, remark can be ignore..
i have done few manual output for reference.. thanks


input::
Code: Select all
(C) SRC Secure Solutions bv iMenu400 Authorised Options for User 28/05/12 15:39:36 Page 1
=====================================================================================================================================
* *
* * * * * * * * * User information . . . . . : User name . . . . . . . . . : ABAX * * * * * * * * *
* User text . . . . . . . . . : Beatrice Raffaele *
Begin menu . . . . . . . . : *NONE
Group jobs . . . . . . . . : 15
End group jobs . . . . . . : *NO
Command line on menus . . . : *NO
Attention-key program . . . : *ACTIVE
Reference user . . . . . . : OPERATOR
=====================================================================================================================================
* *
* * * * * * * * * User information . . . . . : User name . . . . . . . . . : AGNESP * * * * * * * * *
* User text . . . . . . . . . : AGNES L Q POOI *
Begin menu . . . . . . . . : MAIN
Group jobs . . . . . . . . : 15
End group jobs . . . . . . : *NO
Command line on menus . . . : *NO
Attention-key program . . . : *ACTIVE
Reference user . . . . . . : *MULTIPLE
Reference User
_____________________________________________________________________________________________________________________________________
ALLUSR Main Menu User GRPBOSECA Securities Admin.
GRPBOSECI Securities Admin. GRPBOSECU Securities Admin
* *
* * * * * * * * * User information . . . . . : User name . . . . . . . . . : SGKANT * * * * * * * * *
* User text . . . . . . . . . : SUNNY SEOW PENG KANG *
Begin menu . . . . . . . . : MAIN
Group jobs . . . . . . . . : 15
End group jobs . . . . . . : *NO
Command line on menus . . . : *NO
Attention-key program . . . : *ACTIVE
Reference user . . . . . . : *MULTIPLE
Reference User
_____________________________________________________________________________________________________________________________________
ALLUSR Main Menu User GRPMKTFO Front Office Mkting
=====================================================================================================================================
Code: Select all
desired output
UserName,User Text,Begin Menu,Group Jobs,End Group Jobs,Command Line on Menus,Attention-Key Program,Reference user,Group I,Group I Description,Group II Group II Description,Group III,Group III Description,Group IV,Group IV Description,Group V,Group V Description,Group VI,Group VI Description
ABAX, Beatrice Raffaele,*NONE,15,*NO,*NO,*ACTIVE,OPERATOR
AGNESP, AGNES L Q POOI,MAIN,15,*NO,*NO,ACTIVE,*MULTIPLE,GRPBOSECA,Securities Admin, GRPBOSECI, Securities Admin, GRPBOSECU, Securities Admin
SGKANT,SUNNY SEOW PENG KANG,MAIN,15,*NO,*NO,*ACTIVE,*MULTIPLE,GRPMKTFO, Front office Mkting
case1
reference user is not operator, hence column group is empty
case2
reference user is multiple, hence group and description will have content but keyword is "GRP"...
case3
where reference user is multiple, has more than 1 group and description...
I have think the column header can be done in this way..
Code: Select all
echo User name,User text,Begin menu,Group jobs,End group jobs,Command ^ ine on menus,Attention-key program,Reference user, Group I, Group I Description,Group II, Group II Description,Group III, Group III Description, Group IV, Group IV Description,Group V, Group V Description,Group VI, Group VI Description>outputfile.csv
here i attached the new_input.txt which has complete input
http://dl.dropbox.com/u/6621198/new_input.txt
however, for testing purpose u can use the one i offered previously as this input is 20mb kinda of slow down the processing..
Re: [help] i need to extract data from a text file via batch
Code: Select all
@echo off
setlocal EnableDelayedExpansion
set numFields=8
set header=UserName,User Text,Begin Menu,Group Jobs,End Group Jobs,Command Line on Menus,Attention-Key Program,Reference user
for %%a in (I II III IV V VI) do set header=!header!,Group %%a,Group %%a Description
echo %header%> outputFile.csv
call :processRecord < sourceFile.txt >> outputFile.csv
goto :EOF
:processRecord
set line=
set maxEmptyLines=10
:ignoreEmptyLine
set /A maxEmptyLines-=1
if %maxEmptyLines% equ 0 exit /B
set /P line=
if not defined line goto ignoreEmptyLine
if "!line:~23,16!" neq "User information" goto processRecord
for /F "tokens=3 delims=:" %%a in ("!line!") do (
for /F %%b in ("%%a") do (
set record=%%b
)
)
for /L %%i in (2,1,%numFields%) do (
set /P line=
for /F "tokens=2 delims=:" %%a in ("!line!") do set "token=%%a"
if %%i equ 2 (
set "token=!token: =!"
set "token=!token:~0,-1!"
if "!token:~-1!" equ " " set "token=!token:~0,-1!"
)
set "record=!record!,!token:~1!"
)
if /I "!token!" neq " *MULTIPLE" goto writeRecord
:firstGroup
set line=
set /P line=
if "!line:~0,6!" neq "ALLUSR" goto firstGroup
set "line=!line:~50!"
goto splitGroup
:nextGroup
set line=
set /P line=
if not defined line goto writeRecord
:splitGroup
set group=
set description=
for %%a in (!line!) do (
if not defined group (
set "group=%%a"
if "!group:~0,3!" neq "GRP" goto writeRecord
) else (
set "word=%%a"
if "!word:~0,3!" neq "GRP" (
set "description=!description! %%a"
) else (
set "record=!record!,!group!,!description!"
set "group=%%a"
set description=
)
)
)
if defined group set "record=!record!,!group!,!description!"
goto nextGroup
:writeRecord
echo !record!
goto processRecord
Code: Select all
output
UserName,User Text,Begin Menu,Group Jobs,End Group Jobs,Command Line on Menus,Attention-Key Program,Reference user,Group I,Group I Description,Group II,Group II Description,Group III,Group III Description,Group IV,Group IV Description,Group V,Group V Description,Group VI,Group VI Description
ABAX,Beatrice Raffaele,*NONE, 15,*NO,*NO,*ACTIVE,OPERATOR
AGNESP,AGNES L Q POOI,MAIN, 15,*NO,*NO,*ACTIVE,*MULTIPLE,GRPBOSECA, Securities Admin.,GRPBOSECI, Securities Admin.,GRPBOSECU, Securities Admin
SGKANT,SUNNY SEOW PENG KANG,MAIN, 15,*NO,*NO,*ACTIVE,*MULTIPLE,GRPMKTFO, Front Office Mkting
Re: [help] i need to extract data from a text file via batch
Well done Aacini.
Re: [help] i need to extract data from a text file via batch
Dear Foxidrive and Aacini,
I am so pleased for your help.. the code work 98%
which i was so happy..
@Aacini,
I didn't aware that you will use the datai put in the post, and i thought u will use my dropbox one.. cause there is a missing part that didn't encode there but in the source file i uploaded in dropbox..
in case u wondering what i am talking about..
here is the real scenario and i feel so sorry of my careless ..
I know you gonna angry with me again
(Kindly don't use this, as this is for illustration purpose only. Please forgive my ignorance.[b])[/b]
However, this time would you like to use source file i put in dropbox.
https://www.dropbox.com/s/mb8npfjkt95bkxy/input2.txt
Maybe this time u can hint me something. i will work on it further...
WHICH THE FORMAT CONFIRM IS CORRECT...
I will definitely, recommend my friend come here to seek the support
You guys ROCK!!!
I am so pleased for your help.. the code work 98%

@Aacini,
I didn't aware that you will use the datai put in the post, and i thought u will use my dropbox one.. cause there is a missing part that didn't encode there but in the source file i uploaded in dropbox..
in case u wondering what i am talking about..
here is the real scenario and i feel so sorry of my careless ..
I know you gonna angry with me again

(Kindly don't use this, as this is for illustration purpose only. Please forgive my ignorance.[b])[/b]
Code: Select all
(C) SRC Secure Solutions bv iMenu400 Authorised Options for User 28/05/12 15:39:36 Page 1
=====================================================================================================================================
* *
* * * * * * * * * User information . . . . . : User name . . . . . . . . . : ABAX * * * * * * * * *
* User text . . . . . . . . . : Beatrice Raffaele *
Begin menu . . . . . . . . : *NONE
Group jobs . . . . . . . . : 15
End group jobs . . . . . . : *NO
Command line on menus . . . : *NO
Attention-key program . . . : *ACTIVE
Reference user . . . . . . : OPERATOR
=====================================================================================================================================
Menu information: Menu . . . . . . . . . . . : ITOP
Menu text . . . . . . . . . : ITOP menu for the IBM operators
User authority . . . . . . : *REFUSER
Reference user authority . : *USE
Option Option User Ref.user Group Date Time Number
number text authority authority job Activated used used acesses
_____________________________________________________________________________________________________________________________________
1 Job Schedul. BOD 06am EOD:20pm *REFUSER *USE *NO *YES
9 WRKJOBJS *REFUSER *USE *NO *YES 14/05/12 11:54:37 209
10 Work with Job Scheduler (BOD) *REFUSER *USE *NO *YES 14/05/12 5:04:37 156
11 Work with Job Scheduler (EOD) *REFUSER *USE *NO *YES 11/05/12 23:27:57 248
12 Check JS History *REFUSER *USE *NO *YES
13 WRKJOBSCDE *REFUSER *USE *NO *YES 29/02/12 4:10:25 2
15 Save and Restore *REFUSER *USE *NO *YES
20 Save Objects (SAVOBJ) *REFUSER *USE *NO *YES
21 Restore Object (RSTOBJ) *REFUSER *USE *NO *YES
22 Save Library (SAVLIB) *REFUSER *USE *NO *YES
23 Restore Library (RSTLIB) *REFUSER *USE *NO *YES
24 Save restore obj (SAVRSTOBJ) *REFUSER *USE *NO *YES
25 *REFUSER *USE *NO *YES
27 Work with commands *REFUSER *USE *NO *YES
28 Work with BRMS (WRKMEDIBRM *REFUSER *USE *NO *YES
29 Work with object (WRKOBJ) *REFUSER *USE *NO *YES 29/02/12 23:38:06 1
30 Active Jobs (WRKACTJOB) *REFUSER *USE *NO *YES 28/05/12 12:11:10 675
31 MQSeries (WRKMQM) *REFUSER *USE *NO *YES 25/10/11 21:15:30 5
32 Configurat.status (WRKCFGSTS) *REFUSER *USE *NO *YES
33 Submitted Jobs (WRKSBMJOB) *REFUSER *USE *NO *YES 3/08/11 4:03:06 1
34 Object Locks (WRKOBJLCK) *REFUSER *USE *NO *YES 7/06/11 23:53:36 9
35 Work with Job (WRKJOB) *REFUSER *USE *NO *YES 14/05/12 5:08:32 25
36 Work with Writers (WRKWTR) *REFUSER *USE *NO *YES
37 Output Queues (WRKOUTQ) *REFUSER *USE *NO *YES
38 Spool Files (WRKSPLF) *REFUSER *USE *NO *YES 29/02/12 4:58:54 10
39 *REFUSER *USE *NO *YES
40 Device Description (WRKDEVD) *REFUSER *USE *NO *YES 8/01/12 20:53:13 4
41 Work with Libraries(WRKLIB) *REFUSER *USE *NO *YES 27/03/12 15:28:43 5
42 With Subsystems (WRKSBS) *REFUSER *USE *NO *YES 8/01/12 18:18:51 10
(C) SRC Secure Solutions bv iMenu400 Authorised Options for User 28/05/12 15:39:36 Page 2
=====================================================================================================================================
Option Option User Ref.user Group Date Time Number
number text authority authority job Activated used used acesses
_____________________________________________________________________________________________________________________________________
* *
* * * * * * * * * User information . . . . . : User name . . . . . . . . . : AGNESP * * * * * * * * *
* User text . . . . . . . . . : AGNES L Q POOI *
Begin menu . . . . . . . . : MAIN
Group jobs . . . . . . . . : 15
End group jobs . . . . . . : *NO
Command line on menus . . . : *NO
Attention-key program . . . : *ACTIVE
Reference user . . . . . . : *MULTIPLE
Reference User
_____________________________________________________________________________________________________________________________________
ALLUSR Main Menu User GRPBOSECA Securities Admin.
GRPBOSECI Securities Admin. GRPBOSECU Securities Admin
=====================================================================================================================================
* *
* * * * * * * * * User information . . . . . : User name . . . . . . . . . : SGKANT * * * * * * * * *
* User text . . . . . . . . . : SUNNY SEOW PENG KANG *
Begin menu . . . . . . . . : MAIN
Group jobs . . . . . . . . : 15
End group jobs . . . . . . : *NO
Command line on menus . . . : *NO
Attention-key program . . . : *ACTIVE
Reference user . . . . . . : *MULTIPLE
Reference User
_____________________________________________________________________________________________________________________________________
ALLUSR Main Menu User GRPMKTFO Front Office Mkting
=====================================================================================================================================
Menu information: Menu . . . . . . . . . . . : MAIN
Menu text . . . . . . . . . : ABN Amro Singapore
User authority . . . . . . : *REFUSER
Reference user authority . : *USE
Option Option User Ref.user Group Date Time Number
number text authority authority job Activated used used acesses
_____________________________________________________________________________________________________________________________________
33 Front Office Main Menu *REFUSER *USE *NO *YES 10/04/12 18:15:34 229
90 SIGNOFF *REFUSER *USE *NO *YES
=====================================================================================================================================
Menu information: Menu . . . . . . . . . . . : MK01
Menu text . . . . . . . . . : Front Office Marketing - RM Main Menu
User authority . . . . . . : *REFUSER
Reference user authority . : *USE
Option Option User Ref.user Group Date Time Number
number text authority authority job Activated used used acesses
_____________________________________________________________________________________________________________________________________
1 Clients Management *REFUSER *USE *NO *YES 10/04/12 18:15:35 206
2 Securities Management *REFUSER *USE *NO *YES
3 Tables Management *REFUSER *USE *NO *YES
4 Rates Management *REFUSER *USE *NO *YES 15/11/10 14:33:53 25
5 Treasury Management *REFUSER *USE *NO *YES
6 Statistics and Reports *REFUSER *USE *NO *YES 14/05/10 11:25:32 1
7 Mirage *REFUSER *USE *NO *YES 22/04/10 9:03:43 3
80 Change Passwords *REFUSER *USE *NO *YES
81 View Reports *REFUSER *USE *NO *YES
90 SIGNOFF *REFUSER *USE *NO *YES 21/04/10 10:07:11 1
(C) SRC Secure Solutions bv iMenu400 Authorised Options for User 28/05/12 15:39:36 Page 1728
=====================================================================================================================================
=====================================================================================================================================
However, this time would you like to use source file i put in dropbox.
https://www.dropbox.com/s/mb8npfjkt95bkxy/input2.txt
Maybe this time u can hint me something. i will work on it further...
WHICH THE FORMAT CONFIRM IS CORRECT...

I will definitely, recommend my friend come here to seek the support

You guys ROCK!!!
Last edited by cheeseng on 31 May 2012 20:41, edited 1 time in total.
Re: [help] i need to extract data from a text file via batch
cheeseng wrote:Dear Foxidrive and Aacini,
You guys are ROCK!!!
You just called them a rock as in a physical earthly hard object instead of saying they ROCK as in the musical reference.
Re: [help] i need to extract data from a text file via batch
sorry typo, type too fast..
btw, i am now working on try to filter off the content to reach what @Aacini has offered previously..
i am thinking to work in 2 step.. first with the raw data > filter off > become the input what Aacini has done > then run the code > output.
i try to use findstr /B "content"
but doesn't work..
any guide
?
btw, i am now working on try to filter off the content to reach what @Aacini has offered previously..
i am thinking to work in 2 step.. first with the raw data > filter off > become the input what Aacini has done > then run the code > output.
Code: Select all
(C) SRC Secure Solutions bv iMenu400 Authorised Options for User 28/05/12 15:39:36 Page 1
=====================================================================================================================================
* *
* * * * * * * * * User information . . . . . : User name . . . . . . . . . : ABAX * * * * * * * * *
* User text . . . . . . . . . : Beatrice Raffaele *
Begin menu . . . . . . . . : *NONE
Group jobs . . . . . . . . : 15
End group jobs . . . . . . : *NO
Command line on menus . . . : *NO
Attention-key program . . . : *ACTIVE
Reference user . . . . . . : OPERATOR
=====================================================================================================================================
Menu information: Menu . . . . . . . . . . . : ITOP
Menu text . . . . . . . . . : ITOP menu for the IBM operators
User authority . . . . . . : *REFUSER
Reference user authority . : *USE
Option Option User Ref.user Group Date Time Number
number text authority authority job Activated used used acesses
_____________________________________________________________________________________________________________________________________
1 Job Schedul. BOD 06am EOD:20pm *REFUSER *USE *NO *YES
9 WRKJOBJS *REFUSER *USE *NO *YES 14/05/12 11:54:37 209
10 Work with Job Scheduler (BOD) *REFUSER *USE *NO *YES 14/05/12 5:04:37 156
11 Work with Job Scheduler (EOD) *REFUSER *USE *NO *YES 11/05/12 23:27:57 248
12 Check JS History *REFUSER *USE *NO *YES
13 WRKJOBSCDE *REFUSER *USE *NO *YES 29/02/12 4:10:25 2
15 Save and Restore *REFUSER *USE *NO *YES
20 Save Objects (SAVOBJ) *REFUSER *USE *NO *YES
21 Restore Object (RSTOBJ) *REFUSER *USE *NO *YES
22 Save Library (SAVLIB) *REFUSER *USE *NO *YES
23 Restore Library (RSTLIB) *REFUSER *USE *NO *YES
24 Save restore obj (SAVRSTOBJ) *REFUSER *USE *NO *YES
25 *REFUSER *USE *NO *YES
27 Work with commands *REFUSER *USE *NO *YES
28 Work with BRMS (WRKMEDIBRM *REFUSER *USE *NO *YES
29 Work with object (WRKOBJ) *REFUSER *USE *NO *YES 29/02/12 23:38:06 1
30 Active Jobs (WRKACTJOB) *REFUSER *USE *NO *YES 28/05/12 12:11:10 675
31 MQSeries (WRKMQM) *REFUSER *USE *NO *YES 25/10/11 21:15:30 5
32 Configurat.status (WRKCFGSTS) *REFUSER *USE *NO *YES
33 Submitted Jobs (WRKSBMJOB) *REFUSER *USE *NO *YES 3/08/11 4:03:06 1
34 Object Locks (WRKOBJLCK) *REFUSER *USE *NO *YES 7/06/11 23:53:36 9
35 Work with Job (WRKJOB) *REFUSER *USE *NO *YES 14/05/12 5:08:32 25
36 Work with Writers (WRKWTR) *REFUSER *USE *NO *YES
37 Output Queues (WRKOUTQ) *REFUSER *USE *NO *YES
38 Spool Files (WRKSPLF) *REFUSER *USE *NO *YES 29/02/12 4:58:54 10
39 *REFUSER *USE *NO *YES
40 Device Description (WRKDEVD) *REFUSER *USE *NO *YES 8/01/12 20:53:13 4
41 Work with Libraries(WRKLIB) *REFUSER *USE *NO *YES 27/03/12 15:28:43 5
42 With Subsystems (WRKSBS) *REFUSER *USE *NO *YES 8/01/12 18:18:51 10
(C) SRC Secure Solutions bv iMenu400 Authorised Options for User 28/05/12 15:39:36 Page 2
=====================================================================================================================================
i try to use findstr /B "content"
Code: Select all
type %1 | findstr /B " **** " | findstr /B "Begin menu" | findstr /B "Group jobs" | findstr /B "End group jobs" | findstr /B "Command line on menus" | findstr /B "Reference user"> output.txt
but doesn't work..
any guide

Re: [help] i need to extract data from a text file via batch
/B only matches from the start of the line.
and you can't use pipes for this - you are doing this sort of thing:
findstr /b "a" |findstr /b "b"
and this will output lines starting with a and then piping it through findstr again looking for lines starting with b. You won't find any, there will only be lines starting with a so the output will be blank.
and you can't use pipes for this - you are doing this sort of thing:
findstr /b "a" |findstr /b "b"
and this will output lines starting with a and then piping it through findstr again looking for lines starting with b. You won't find any, there will only be lines starting with a so the output will be blank.
Re: [help] i need to extract data from a text file via batch
how about this, as i was refer to what you guys am helping me on..
i try to think about the pseudocode first
i try to think about the pseudocode first
Code: Select all
for /f "delims=" %%a in ('type "file.txt" ^|find ". . :"') do (
:: print the line
:: but if find "Menu information:" OR "Menu text" OR "User authority " OR Reference user authority >> THEN IGNORE.
then for /f "delims=" %%a in ('type "file.txt" ^|find "ALLUSR"') do (
print the line...
then for /f "delims=" %%a in ('type "file.txt" ^|find "GRP"') do (
print the line...
Re: [help] i need to extract data from a text file via batch
I'm not too sure if this is useful, but maybe Aacini can use this with his code to create what you need.
http://dl.dropbox.com/u/6621198/new_input.txt <---- this is the input file
It needs space to be pressed three times to convert the Unicode to ascii text due to the more.exe bug at 64K lines.
In essence it should extract all the required lines for your task, if I have done it properly, and uses SED.
I couldn't make FINDSTR return three strings in the one pass - I wonder what I did wrong? strings are ". . :" ALLUSR GRP
http://dl.dropbox.com/u/6621198/new_input.txt <---- this is the input file
It needs space to be pressed three times to convert the Unicode to ascii text due to the more.exe bug at 64K lines.
In essence it should extract all the required lines for your task, if I have done it properly, and uses SED.
Code: Select all
@echo off
more <new_input.txt >new_input2.txt
sed -e "s/^ allusr.*/. . \/&/i" -e "s/^ grp.*/. . :&/i" "new_input2.txt"|find ". . :"|find /v /i "Menu . . . . . . . . . . . :"|find /v /i "User authority . . . . . . :"|find /v /i "Menu text . . . . . . . . . :" >file.txt
I couldn't make FINDSTR return three strings in the one pass - I wonder what I did wrong? strings are ". . :" ALLUSR GRP