batch file to extract context from text file to csv format

May 28, 2012 at 02:29:41
Specs: Windows XP
i need to extract some content from following information (source file)
* *
* * * * * * * * * 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 . . . . . . . . . : ABCG * * * * * * * * *
* User text . . . . . . . . . : Barchiesi Giancarlo *
Begin menu . . . . . . . . : *NONE
Group jobs . . . . . . . . : 15
End group jobs . . . . . . : *NO
Command line on menus . . . : *NO
Attention-key program . . . : *ACTIVE
Reference user . . . . . . : OPERATOR
=====================================================================================================================================

and the list go on,,,

my desire output is
user name , User text , Begin menu , Group jobs , End group jobs , Command line on menus , ....
Beatrice .. , *NONE ,
Barchiesi ,
basically will have a column header and follow by the entries...


i read somewhere
for /f "tokens=*" %%a in ('find /i "User name" ^<..\abc.txt') do (
set %%a

(echo User name: !user name!
echo user text: !user text!
echo Begin menu : !Begin menu!
echo Group jobs : !Group jobs!
echo End group jobs : !End group jobs!
echo Command line on menus : !command line on menus!
echo Attention-key program : !Attention-key program!
echo Reference user : !Reference user!
)>>output.txt
)

but this doesn't work on my case.. would appreciate someone reply ...thanks!!!


See More: batch file to extract context from text file to csv format

Report •


#1
May 28, 2012 at 03:35:32
Am having a problem understanding exactly what output you want. The first input record shows the User name as ABAX * * * * * * * * * but the output record shows the User name as Beatrice. Should the User surname not appear in the same field as the first name?

Please clarify exactly what the output record should be.


Please come back & tell us if your problem is resolved.


Report •

#2
May 28, 2012 at 18:14:00
hi Wahine,

Thanks for asking...
Sorry for the typo

in fact the ouput shall be..

User name User text Begin menu ......
ABAX Beatrice Raffaele *NONE
ABCG Barchiesi Giancarlo *NONE


something like this... and continue to the rest of the row
:) thanks.. would appreciate for the help..


Report •

#3
May 29, 2012 at 04:36:05
Please test the following script. It's probably not very efficient, one of the forum gurus might drop in with some improvements.

@echo off>%temp%\input.txt
cls
setlocal enabledelayedexpansion

for /f "tokens=*" %%1 in (input.txt) do (
    set inline=%%1& set inline=!inline:.=!
    if not "!inline:~0,3!" equ "* *" (
    if not "!inline:~0,3!" equ "===" (
       echo !inline!>>%temp%\input.txt
    )
  )
)

@echo off>%temp%\input2.txt
for /f "tokens=*" %%1 in (%temp%\input.txt%) do (
    set input=%%1

    for /f "tokens=2 delims=:" %%A in ("!input!") do (
        set ending=%%A

        if "!ending:~1,1!" equ "*" (
            echo !ending:~2!>>%temp%\input2.txt
            ) else (
            echo !ending!>>%temp%\input2.txt
      )
   )
)

@echo off>%temp%\input.txt
for /f "tokens=1* delims=*" %%1 in (%temp%\input2.txt) do (
    set final=%%1
    if "!final:~0,1!" equ " " (
    set final=!final:~1!
    )
    echo !final!>>%temp%\input.txt
   
)

echo User name,User text,Begin menu,Group jobs,End group jobs,Command ^
line on menus,Attention-key program,Reference user>newfile.csv

for /f "tokens=*" %%1 in (%temp%\input.txt) do (
    set line=!line!%%1,
    set /a ctr +=1
    If !ctr! equ 8 (
       set line=!line:~0,-1!
       echo !line!>>newfile.csv
       set ctr=
       set line=
   )
)
    
:cleanup
del %temp%\input*.txt


Please come back & tell us if your problem is resolved.


Report •

Related Solutions

#4
May 29, 2012 at 18:33:01
Thanks for the solution.. :)

However, in the newfile.csv it only print out the header
(
User name,User text,Begin menu,Group jobs,End group jobs,Command line on menus,Attention-key program,Reference user
)

In fact, there is only one input file, the example i shown you on previous is all come from one input.

for example,
=====================================================================================================================================
* *
* * * * * * * * *
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
====================================================================================================================================
XXXXXXXX |
XXXXXXXXX | --- other information not relevant
XXXXXXXXXX |

=====================================================================================================================================
* *
* * * * * * * * *
User information . . . . . : : User name . . . . . . . . . : ABCG * *
User text . . . . . . . . . : Barchiesi Giancarlo *
Begin menu . . . . . . . . : *NONE
Group jobs . . . . . . . . : 15
End group jobs . . . . . . : *NO
Command line on menus . . . : *NO
Attention-key program . . . : *ACTIVE
Reference user . . . . . . : OPERATOR
====================================================================================================================================

XXXXXXXX |
XXXXXXXXX | --- other information not relevant
XXXXXXXXXX |

=====================================================================================================================================
* *
* * * * * * * * *
User information . . . . . : : User name . . . . . . . . . : ABEF * *
User text . . . . . . . . . : Fabio Besomi*
Begin menu . . . . . . . . : *NONE
Group jobs . . . . . . . . : 15
End group jobs . . . . . . : *NO
Command line on menus . . . : *NO
Attention-key program . . . : *ACTIVE
Reference user . . . . . . : OPERATOR
====================================================================================================================================

Sorry, if you misunderstood on previous example... hope it can help you to help me :)

Impressed that you guys really response quickly.. i will come here more often to learn new stuff!! great job..


Report •

#5
May 29, 2012 at 20:19:22
i will come here more often to learn new stuff!

The first thing you must learn is that when you post a request for assistance you must ensure that the information you give is accurate and complete. It appears that you now want to change the content of the input file you originally posted. This means that my efforts have been wasted and I can assure you that I am less than pleased by that.

I will wait 24 hours for you to review the information you have provided and come back to confirm or alter it. Please remember the information must be accurate and complete.


Please come back & tell us if your problem is resolved.


Report •

#6
May 29, 2012 at 21:06:55
Hi Wahine,

I am very sorry about that.. Due to my lack of experience on this forum..
I hope you don't mind..
Yup, please give me some time to work around.. i will defintely confirm with you again soon.

In addition, just want to thank you your help. Your effort doesn't wasted, because i did learn from the script you wrote.

Sincerely apologize


Report •

#7
May 29, 2012 at 22:12:08
for example,
=====================================================================================================================================
* *
* * * * * * * * *
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
====================================================================================================================================
XXXXXXXX |
XXXXXXXXX | --- other information not relevant
XXXXXXXXXX |

=====================================================================================================================================
* *
* * * * * * * * *
User information . . . . . : : User name . . . . . . . . . : ABCG * *
User text . . . . . . . . . : Barchiesi Giancarlo *
Begin menu . . . . . . . . : *NONE
Group jobs . . . . . . . . : 15
End group jobs . . . . . . : *NO
Command line on menus . . . : *NO
Attention-key program . . . : *ACTIVE
Reference user . . . . . . : OPERATOR
====================================================================================================================================

XXXXXXXX |
XXXXXXXXX | --- other information not relevant
XXXXXXXXXX |


=====================================================================================================================================
* *
* * * * * * * * *
User information . . . . . : : User name . . . . . . . . . : ABEF * *
User text . . . . . . . . . : Fabio Besomi*
Begin menu . . . . . . . . : *NONE
Group jobs . . . . . . . . : 15
End group jobs . . . . . . : *NO
Command line on menus . . . : *NO
Attention-key program . . . : *ACTIVE
Reference user . . . . . . : OPERATOR
====================================================================================================================================


Wahine,

Hope in this format will give you more accurate input data...
thank you


Report •

#8
May 30, 2012 at 01:33:40
Hi Wahine,

Just for your info, there has 1000 over similar structure..
so basically the bat file need to loop over and over to output in csv format then i will can find way to port over excel file..
thanks


Report •

#9
May 30, 2012 at 15:35:52
XXXXXXXX |
XXXXXXXXX | --- other information not relevant
XXXXXXXXXX |

What you show here might not be relevant with regard to information to be extracted but when writing the script it might be necessary for the three lines to be identified in order that they are eliminated from the output. I need to know the format/content of the lines and whether these lines are identical wherever they appear in the input file.


Please come back & tell us if your problem is resolved.


Report •

#10
May 31, 2012 at 00:48:40

Hi Wahine,
i have provided the input and desired output files to dropbox. These are defintely the correct format

input
https://www.dropbox.com/s/mb8npfjkt...
output
https://www.dropbox.com/s/z20n9ob0b...


The suggestion u offered for column header is great, however i enhanced a bit like the following:
echo User name,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>outputfile.csv

as the maximum group & its description is until 6..

Furthermore,
However, for the record where reference user is "multiple" it will have extra column like i shown on previous post first picture... and the keyword is 'grp'.... , security info

meaning anything that is start from 'GRP'SECINFO put under group && 'anyword behind' put under group description

Hope this time the files i provided are good enough..
i am rookie to scripting, would be appreciate a lot if u can enlighten me :)
I am really sorry for previous mistake.



Report •

#11
May 31, 2012 at 02:24:54
You now seem to want a script to produce 20 cells per line. The output file you posted is, I think, the output from Excel not the output required from a batch script in .csv format which, as you said, would then be ported over Excel file.

Due to the number of changes you seem to want to make and our not being able to agree on formats I regret I cannot assist you any further.

Good luck.


Please come back & tell us if your problem is resolved.


Report •

#12
May 31, 2012 at 02:33:59
Dear Wahine,

I agree previously is my mistake as i am not aware that batch file will affected so much because of the format issue..
and the output column header only require

echo User name,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>outputfile.csv

rc code review1 review2 and remark can be ignore...

output as long as in the format of csv with delimiter,
i can port to excel afterward...
The reason i changed is because my BOSS!! didn't offer me a final requirement which resulted my headache!!

I am really hope that you can continue and i can assure this will be the last change i make.. :( :(

do let me know how i can help you back in other form..


Report •


Ask Question