Replace multiple occurrences of a character

Discuss RoboTask here
Post Reply
sarnusch
Posts: 35
Joined: Thu Apr 28, 2022 9:05 am

Replace multiple occurrences of a character

Post by sarnusch »

Good morning,
I am using a dataset loop to get data from a SQL Database.
After that it is written into a text file.
This is an example extract:

110611,"671,4",4022927075332,89915377,,
110612,"1000,98",4022927075325,89915376,,
110613,1527,9000000067417,89915378,,

My goal is to create a csv file from the data. So I actually need ";" instead of ",".
Is there any way to get the data with a ";" from the database?
Otherwise how can I just change the "," that is not between quotation marks (STR REPLACE?)?
Thanks in advance for some input.

Kind regards,
Sebastian
Oleg
Site Admin
Posts: 3000
Joined: Thu Jan 01, 1970 1:00 am
Contact:

Re: Replace multiple occurrences of a character

Post by Oleg »

Look at my example below.
Save the text of the task to a file and use menu Task->Import to import the task into RoboTask.
Also you can simply copy task text and paste it into the task list.

Code: Select all

;*****************************
;* RoboTask Task file
;* Do not edit in text editor!
;*****************************
 
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|444033236
Comment=STRINGLIST
ContinueOnError=INTEGER|0
DisableOnError=INTEGER|0
DoNotStopWhenShutdown=INTEGER|0
ExternalName=STRING|"Task1410"
Hide=INTEGER|0
ID=INTEGER|-498667185
LogOnAsUser=INTEGER|1
Name=STRING|"Change CSV format"
OnErrorTaskID=INTEGER|-1
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
UnicodeFormat=INTEGER|1
WriteGeneralLog=INTEGER|0

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

[Actions\Action1]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""txt"" with value ""110611,""671,4"",4022927075332,89915377,,{EOL}110612,""1000,98"",4022927075325,89915376,,{EOL}110613,1527,9000000067417,89915378,,"""
Params=FOLDER

[Actions\Action1\Params]
expand=STRING|"0"
varname=STRING|"txt"
varvalue=STRING|"110611,""671,4"",4022927075332,89915377,,{EOL}110612,""1000,98"",4022927075325,89915376,,{EOL}110613,1527,9000000067417,89915378,,"

[Actions\Action10]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""NewLine"" with value ""{NewLine}{item}"""
Params=FOLDER

[Actions\Action10\Params]
expand=STRING|"1"
varname=STRING|"NewLine"
varvalue=STRING|"{NewLine}{item}"

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

[Actions\Action12]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""NewText"" with value ""{NewText}{NewLine}{eol}"""
Params=FOLDER

[Actions\Action12\Params]
expand=STRING|"1"
varname=STRING|"NewText"
varvalue=STRING|"{NewText}{NewLine}{eol}"

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

[Actions\Action14]
ActionID=STRING|"A_DIALOG_SHOWTEXT"
Enabled=INTEGER|-1
Name=STRING|"Show Text"
Params=FOLDER

[Actions\Action14\Params]
autoclose=STRING|"0"
fromfile=STRING|"1"
line00000000=STRING|"Old text:"
line00000001=STRING|"{txt}"
line00000003=STRING|"=================="
line00000004=STRING|"New text"
line00000005=STRING|"{NewText}"
linecount=STRING|"6"
timeout=STRING|"10"

[Actions\Action2]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""NewText"" with value """""
Params=FOLDER

[Actions\Action2\Params]
expand=STRING|"0"
varname=STRING|"NewText"

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

[Actions\Action3\Params]
destvar=STRING|"LINE"
line0=STRING|"{txt}"
linecount=STRING|"1"
sourcetext=STRING|"1"

[Actions\Action4]
ActionID=STRING|"A_TXT_COOMATEXT"
Enabled=INTEGER|-1
Name=STRING|"Comma-separated string -> text"
Params=FOLDER

[Actions\Action4\Params]
kind=STRING|"0"
line00000000=STRING|"{line}"
lines=STRING|"1"
variable=STRING|"line"

[Actions\Action5]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""NewLine"" with value """""
Params=FOLDER

[Actions\Action5\Params]
expand=STRING|"0"
varname=STRING|"NewLine"

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

[Actions\Action6\Params]
destvar=STRING|"ITEM"
line0=STRING|"{line}"
linecount=STRING|"1"
sourcetext=STRING|"1"

[Actions\Action7]
ActionID=STRING|"A_FLOW_IF"
Enabled=INTEGER|-1
Name=STRING|"If Then  //newline is not empty"
Params=FOLDER

[Actions\Action7\Params]
case=STRING|"0"
connection=STRING|"0"
count=STRING|"1"
operator=STRING|"8"
type=STRING|"0"
value1=STRING|"{NewLine}"

[Actions\Action8]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""NewLine"" with value ""{NewLine};"""
Params=FOLDER

[Actions\Action8\Params]
expand=STRING|"1"
varname=STRING|"NewLine"
varvalue=STRING|"{NewLine};"

[Actions\Action9]
ActionID=STRING|"A_FLOW_ENDIF"
Enabled=INTEGER|-1
Name=STRING|"End If"
But probably we need to add possibility to change CSV format. I wrote this into our ToDo list
Oleg Yershov
sarnusch
Posts: 35
Joined: Thu Apr 28, 2022 9:05 am

Re: Replace multiple occurrences of a character

Post by sarnusch »

Hello Oleg,
thanks for your reply and your help.
I tested it and it basically works fine.
But the commas at the end will disappear after processing. This is not good because the file will be imported with another program and the space between every semicolon is a field even if it is a space.

For example:
110611,"671,4",4022927075332,89915377,, becomes
110611;671,4;4022927075332;89915377
But it has to be:
110611;671,4;4022927075332;89915377;;

Is it possible to realize that?

Kind regards,
Sebastian
Oleg
Site Admin
Posts: 3000
Joined: Thu Jan 01, 1970 1:00 am
Contact:

Re: Replace multiple occurrences of a character

Post by Oleg »

Probably it happened because 2 last elements are empty...
I changed the task (see below):
  • Add some nonempty string to the end. See step #4 I added $$$ to the end
  • after transformation remove this marker (see step #12)
As you can see, the algorithm is not very simple. Therefore, we decided to add a new action to RoboTask that allows you to change the CSV format from standard to custom and conversely in one step. It will be in next release.

Code: Select all

;*****************************
;* RoboTask Task file
;* Do not edit in text editor!
;*****************************
 
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|444033236
Comment=STRINGLIST
ContinueOnError=INTEGER|0
DisableOnError=INTEGER|0
DoNotStopWhenShutdown=INTEGER|0
ExternalName=STRING|"Task1432"
Hide=INTEGER|0
ID=INTEGER|878988155
LogOnAsUser=INTEGER|1
Name=STRING|"Change CSV format (version 2)"
OnErrorTaskID=INTEGER|-1
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
UnicodeFormat=INTEGER|1
WriteGeneralLog=INTEGER|0

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

[Actions\Action1]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""txt"" with value ""110611,""671,4"",4022927075332,89915377,,{EOL}110612,""1000,98"",4022927075325,89915376,,{EOL}110613,1527,9000000067417,89915378,,{EOL}110613,1527,9000000067417,899..."""
Params=FOLDER

[Actions\Action1\Params]
expand=STRING|"0"
varname=STRING|"txt"
varvalue=STRING|"110611,""671,4"",4022927075332,89915377,,{EOL}110612,""1000,98"",4022927075325,89915376,,{EOL}110613,1527,9000000067417,89915378,,{EOL}110613,1527,9000000067417,89915378,,NonEmpyValue"

[Actions\Action10]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""NewLine"" with value ""{NewLine}{item}"""
Params=FOLDER

[Actions\Action10\Params]
expand=STRING|"1"
varname=STRING|"NewLine"
varvalue=STRING|"{NewLine}{item}"

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

[Actions\Action12]
ActionID=STRING|"A_STR_REPLACE"
Enabled=INTEGER|-1
Name=STRING|"STR Replace // remove end marker"
Params=FOLDER

[Actions\Action12\Params]
case=STRING|"0"
mode=STRING|"2"
source=STRING|"{NewLine}"
substring=STRING|"$$$"
variable=STRING|"NewLine"

[Actions\Action13]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""NewText"" with value ""{NewText}{NewLine}{eol}"""
Params=FOLDER

[Actions\Action13\Params]
expand=STRING|"1"
varname=STRING|"NewText"
varvalue=STRING|"{NewText}{NewLine}{eol}"

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

[Actions\Action15]
ActionID=STRING|"A_DIALOG_SHOWTEXT"
Enabled=INTEGER|-1
Name=STRING|"Show Text"
Params=FOLDER

[Actions\Action15\Params]
autoclose=STRING|"0"
fromfile=STRING|"1"
line00000000=STRING|"Old text:"
line00000001=STRING|"{txt}"
line00000003=STRING|"=================="
line00000004=STRING|"New text"
line00000005=STRING|"{NewText}"
linecount=STRING|"6"
timeout=STRING|"10"

[Actions\Action2]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""NewText"" with value """""
Params=FOLDER

[Actions\Action2\Params]
expand=STRING|"0"
varname=STRING|"NewText"

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

[Actions\Action3\Params]
destvar=STRING|"LINE"
line0=STRING|"{txt}"
linecount=STRING|"1"
sourcetext=STRING|"1"

[Actions\Action4]
ActionID=STRING|"A_TXT_COOMATEXT"
Enabled=INTEGER|-1
Name=STRING|"Comma-separated string -> text //add some non-empty string to the end"
Params=FOLDER

[Actions\Action4\Params]
kind=STRING|"0"
line00000000=STRING|"{line}$$$"
lines=STRING|"1"
variable=STRING|"line"

[Actions\Action5]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""NewLine"" with value """""
Params=FOLDER

[Actions\Action5\Params]
expand=STRING|"0"
varname=STRING|"NewLine"

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

[Actions\Action6\Params]
destvar=STRING|"ITEM"
line0=STRING|"{line}"
linecount=STRING|"1"
sourcetext=STRING|"1"

[Actions\Action7]
ActionID=STRING|"A_FLOW_IF"
Enabled=INTEGER|-1
Name=STRING|"If Then  //newline is not empty"
Params=FOLDER

[Actions\Action7\Params]
case=STRING|"0"
connection=STRING|"0"
count=STRING|"1"
operator=STRING|"8"
type=STRING|"0"
value1=STRING|"{NewLine}"

[Actions\Action8]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""NewLine"" with value ""{NewLine};"""
Params=FOLDER

[Actions\Action8\Params]
expand=STRING|"1"
varname=STRING|"NewLine"
varvalue=STRING|"{NewLine};"

[Actions\Action9]
ActionID=STRING|"A_FLOW_ENDIF"
Enabled=INTEGER|-1
Name=STRING|"End If"
Oleg Yershov
sarnusch
Posts: 35
Joined: Thu Apr 28, 2022 9:05 am

Re: Replace multiple occurrences of a character

Post by sarnusch »

Hello Oleg,

thank you, that works fine for me now.
It would be great to just convert to CSV format with one action.
CSV is still needed so many cases.

Kind regards,
Sebastian
Post Reply