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
Replace multiple occurrences of a character
Re: Replace multiple occurrences of a character
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.
But probably we need to add possibility to change CSV format. I wrote this into our ToDo list
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"
Oleg Yershov
Re: Replace multiple occurrences of a character
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
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
Re: Replace multiple occurrences of a character
Probably it happened because 2 last elements are empty...
I changed the task (see below):
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)
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
Re: Replace multiple occurrences of a character
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
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