Extract data from txt files

rwwilkins · Post by **rwwilkins** » Tue Jan 10, 2012 6:14 pm

Hello,Each day I receive text files that contain information like the following:011012 00;00;00;00 00;00;30;00 20072781 DMOM1001 011012 00;00;00;00 00;00;30;00 20072782 DMOM1002 011012 00;00;00;00 00;00;30;00 20072783 DMOM1003It appears to be all on one line that is word wrapped.I would like to pull only the # strings that begin with 2007xxxx. In this example, they appear 3 times. I would then like a new text file created that has put each of those numbers on their own lines so that I may continue processing them with another workflow.The things to note:All 5 sections in the above could be different each time the file comes in.The 2007 number could be any 8 character text.The original file may have any number of these entries and is not consistent.I plan to wrap this with an if/then that identifies file size. (if the filesize is 0, then there is no need to continue the process)Any help you could provide would be wonderful!!!

rwwilkins · Post by **rwwilkins** » Wed Jan 11, 2012 12:19 pm

I've done some more research and found that the RegExp Loop is probably what I need, but the programming of it appears to be well above my skillset. Hopefully someone here can help! To expound upon my request, I have another txt file example:011012 00;00;00;00 00;00;30;00 20072782 DMOM1002 011012 00;00;00;00 00;00;30;00 1235AFMW New Car SmellFrom this text file, I would like the RegExp loop to match both the 20072782 and 1235AFMW items. Please keep in mind that this is always an 8 character item, but any of the characters may be different. In addition, this content isnt on seperate lines, rather, it is one long row in the text file that can contain anywhere from zero 5 item segments to many of them.Defining the sections:011012 00;00;00;00 00;00;30;00 20072782 DMOM1002DATE START TIME END TIME SPOT ID SPOT TITLEThe SPOT ID is the data I need pulled.Thanks in advance to anyone who can help!Rich

Post by **Oleg** » Thu Jan 12, 2012 5:49 am

See the example below. The task extracts SPOT ID from each string:

Code: Select all

;*****************************
;* RoboTask Task file
;* Do not edit in text editor!
;*****************************
 
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|1380072790
Comment=STRINGLIST
ContinueOnError=INTEGER|0
ExternalName=STRING|"Task1137"
Hide=INTEGER|0
ID=INTEGER|996006503
LocalVariables=STRING|"cline"
LogOnAsUser=INTEGER|1
Name=STRING|"Extract Spot ID"
OnErrorTaskID=INTEGER|0
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
WriteGeneralLog=INTEGER|0

[Actions]
Action1=FOLDER
Action10=FOLDER
Action11=FOLDER
Action12=FOLDER
Action2=FOLDER
Action3=FOLDER
Action4=FOLDER
Action5=FOLDER
Action6=FOLDER
Action7=FOLDER
Action8=FOLDER
Action9=FOLDER

[Actions\Action1]
ActionID=STRING|"A_LOOP_TEXT"
Enabled=INTEGER|-1
Name=STRING|"Text Loop"
Params=FOLDER

[Actions\Action1\Params]
destvar=STRING|"CLINE"
 line0=STRING|"011012    00;00;00;00&nbsp ;   00;00;30;00    200727 82    DMOM1002"
 
 line1=STRING|"    011012  &nbs p; 00;00;00;00    00;00;30;00     1235AFMW    New  Car Smell"
linecount=STRING|"2"
sourcetext=STRING|"1"

[Actions\Action10]
ActionID=STRING|"A_MISC_COMMENT"
Enabled=INTEGER|-1
Name=STRING|"//exctact 3-d element"
Params=FOLDER

[Actions\Action10\Params]
comment=STRING|"exctact 3-d element"

[Actions\Action11]
ActionID=STRING|"A_DIALOG_MESSAGE"
Enabled=INTEGER|-1
Name=STRING|"Show ""Spot ID: {cline(3)}"""
Params=FOLDER

[Actions\Action11\Params]
icon=STRING|"1"
msg0=STRING|"Spot ID: {cline(3)}"
msgcount=STRING|"1"
playsound=STRING|"0"
showmessage=STRING|"1"

[Actions\Action12]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"End Loop"

[Actions\Action2]
ActionID=STRING|"A_MISC_COMMENT"
Enabled=INTEGER|-1
Name=STRING|"//trim the string"
Params=FOLDER

[Actions\Action2\Params]
comment=STRING|"trim the string"

[Actions\Action3]
ActionID=STRING|"A_STR_TRIM"
Enabled=INTEGER|-1
Name=STRING|"STR Trim"
Params=FOLDER

[Actions\Action3\Params]
custom=STRING|"0"
eol=STRING|"0"
lead=STRING|"1"
source=STRING|"{cline}"
spaces=STRING|"1"
tabs=STRING|"1"
trailing=STRING|"1"
variable=STRING|"cline"

[Actions\Action4]
ActionID=STRING|"A_MISC_COMMENT"
Enabled=INTEGER|-1
Name=STRING|"//Replace 4 spaces to |"
Params=FOLDER

[Actions\Action4\Params]
comment=STRING|"Replace 4 spaces to |"

[Actions\Action5]
ActionID=STRING|"A_STR_REPLACE"
Enabled=INTEGER|-1
Name=STRING|"STR Replace"
Params=FOLDER

[Actions\Action5\Params]
case=STRING|"0"
mode=STRING|"0"
replacement=STRING|"|"
source=STRING|"{cline}"
substring=STRING|"{chr(32)}{chr(32)}{chr(32)}{chr(32)}"
variable=STRING|"cline"

[Actions\Action6]
ActionID=STRING|"A_DIALOG_MESSAGE"
Enabled=INTEGER|0
Name=STRING|"Show ""{cline}"""
Params=FOLDER

[Actions\Action6\Params]
icon=STRING|"1"
msg0=STRING|"{cline}"
msgcount=STRING|"1"
playsound=STRING|"0"
showmessage=STRING|"1"

[Actions\Action7]
ActionID=STRING|"A_MISC_COMMENT"
Enabled=INTEGER|-1
Name=STRING|"//Make standard comma-separated string"
Params=FOLDER

[Actions\Action7\Params]
comment=STRING|"Make standard comma-separated string"

[Actions\Action8]
ActionID=STRING|"A_VARIABLES_DELIMITED"
Enabled=INTEGER|-1
Name=STRING|"Delimit Variable"
Params=FOLDER

[Actions\Action8\Params]
colon=STRING|"0"
comma=STRING|"0"
other=STRING|"1"
otherchar=STRING|"|"
quote=STRING|"0"
semocolon=STRING|"0"
space=STRING|"0"
spaceasone=STRING|"0"
string=STRING|"{cline}"
tab=STRING|"0"
variable=STRING|"cline"

[Actions\Action9]
ActionID=STRING|"A_DIALOG_MESSAGE"
Enabled=INTEGER|0
Name=STRING|"Show ""{cline}"""
Params=FOLDER

[Actions\Action9\Params]
icon=STRING|"1"
msg0=STRING|"{cline}"
msgcount=STRING|"1"
playsound=STRING|"0"
showmessage=STRING|"1"

rwwilkins · Post by **rwwilkins** » Thu Jan 12, 2012 10:12 am

Thank you Oleg. I just tested it with the data you had copied into the workflow and the 2nd result doesnt show the spot id, it shows the spot title. How do I fix?Thank you very much for your help!

rwwilkins · Post by **rwwilkins** » Thu Jan 12, 2012 10:15 am

I also tried it on an actualy text file and the result was blank.

Post by **Oleg** » Thu Jan 12, 2012 10:33 am

In seems forum engine broke the format of source text. Get the exact task here

rwwilkins · Post by **rwwilkins** » Thu Jan 12, 2012 10:39 am

Thanks Oleg. It seems to work perfectly now with the source text, however, if I place that same text into a text file and run it on that file, the results are blank?

rwwilkins · Post by **rwwilkins** » Thu Jan 12, 2012 10:49 am

Here is a link to an actual file:encodelist.txt

Post by **Oleg** » Thu Jan 12, 2012 11:54 am

Hm, original text have TAB symbol as column delimiter.
See corrected task below:

Code: Select all

;*****************************
;* RoboTask Task file
;* Do not edit in text editor!
;*****************************
 
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|0
CatID=INTEGER|1380072790
Comment=STRINGLIST
ContinueOnError=INTEGER|0
ExternalName=STRING|"Task1138"
Hide=INTEGER|0
ID=INTEGER|1781971786
LocalVariables=STRING|"cline"
LogOnAsUser=INTEGER|1
Name=STRING|"Extract Spot ID (3-d element)-2"
OnErrorTaskID=INTEGER|0
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
WriteGeneralLog=INTEGER|0

[Actions]
Action1=FOLDER
Action2=FOLDER
Action3=FOLDER
Action4=FOLDER
Action5=FOLDER
Action6=FOLDER
Action7=FOLDER
Action8=FOLDER
Action9=FOLDER

[Actions\Action1]
ActionID=STRING|"A_LOOP_TEXT"
Enabled=INTEGER|-1
Name=STRING|"Text Loop"
Params=FOLDER

[Actions\Action1\Params]
destvar=STRING|"CLINE"
file=STRING|"C:\Temp\encodelist.txt"
linecount=STRING|"0"
sourcetext=STRING|"0"

[Actions\Action2]
ActionID=STRING|"A_MISC_COMMENT"
Enabled=INTEGER|-1
Name=STRING|"//trim the string"
Params=FOLDER

[Actions\Action2\Params]
comment=STRING|"trim the string"

[Actions\Action3]
ActionID=STRING|"A_STR_TRIM"
Enabled=INTEGER|-1
Name=STRING|"STR Trim"
Params=FOLDER

[Actions\Action3\Params]
custom=STRING|"0"
eol=STRING|"0"
lead=STRING|"1"
source=STRING|"{cline}"
spaces=STRING|"1"
tabs=STRING|"1"
trailing=STRING|"1"
variable=STRING|"cline"

[Actions\Action4]
ActionID=STRING|"A_MISC_COMMENT"
Enabled=INTEGER|-1
Name=STRING|"//Make standard comma-separated string"
Params=FOLDER

[Actions\Action4\Params]
comment=STRING|"Make standard comma-separated string"

[Actions\Action5]
ActionID=STRING|"A_VARIABLES_DELIMITED"
Enabled=INTEGER|-1
Name=STRING|"Delimit Variable"
Params=FOLDER

[Actions\Action5\Params]
colon=STRING|"0"
comma=STRING|"0"
other=STRING|"0"
otherchar=STRING|"|"
quote=STRING|"0"
semocolon=STRING|"0"
space=STRING|"0"
spaceasone=STRING|"0"
string=STRING|"{cline}"
tab=STRING|"1"
variable=STRING|"cline"

[Actions\Action6]
ActionID=STRING|"A_DIALOG_MESSAGE"
Enabled=INTEGER|0
Name=STRING|"Show ""{cline}"""
Params=FOLDER

[Actions\Action6\Params]
icon=STRING|"1"
msg0=STRING|"{cline}"
msgcount=STRING|"1"
playsound=STRING|"0"
showmessage=STRING|"1"

[Actions\Action7]
ActionID=STRING|"A_MISC_COMMENT"
Enabled=INTEGER|-1
Name=STRING|"//exctact 3-d element"
Params=FOLDER

[Actions\Action7\Params]
comment=STRING|"exctact 3-d element"

[Actions\Action8]
ActionID=STRING|"A_DIALOG_MESSAGE"
Enabled=INTEGER|-1
Name=STRING|"Show ""Spot ID: {cline(3)}"""
Params=FOLDER

[Actions\Action8\Params]
icon=STRING|"1"
msg0=STRING|"Spot ID: {cline(3)}"
msgcount=STRING|"1"
playsound=STRING|"0"
showmessage=STRING|"1"

[Actions\Action9]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"End Loop"

rwwilkins · Post by **rwwilkins** » Thu Jan 12, 2012 12:00 pm

You are the best Oleg. This seems to work flawlessly. I will work to integrate it into my workflow! Thanks again,Rich