Page 1 of 1
Excel to XML global variable import
Posted: Thu Mar 10, 2022 10:58 am
by DK.Ideagen
I am trying to convert an excel spreadsheet into a suitable Variables XML file.
I have exported an example from robotask and used that as a XML map (image attached)
I have added a name field and ID for the folder level but still can't export.
Can someone please provide a template if possible.
ty
Re: Excel to XML global variable import
Posted: Fri Mar 18, 2022 9:04 am
by Oleg
I can assist you to export data into custom XML structure by using RoboTask functions
But I don't know how to do this in EXCEL. EXCEL can save document to XML with very complex structure.
Could you send small XML with several items you want to get? Enough 2-3 elements to understand the structure
Re: Excel to XML global variable import
Posted: Tue Mar 22, 2022 11:08 am
by Oleg
I found your question in
stackoverflow
Look at this example (below). It creates the XML you want.
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|1163085779
Comment=STRINGLIST
ContinueOnError=INTEGER|0
DisableOnError=INTEGER|0
DoNotStopWhenShutdown=INTEGER|0
ExternalName=STRING|"Task180"
Hide=INTEGER|0
ID=INTEGER|1124069604
LogOnAsUser=INTEGER|1
Name=STRING|"Create XML from EXCEL"
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_EXCEL_OPEN"
Enabled=INTEGER|-1
Name=STRING|"Excel Open Document"
Params=FOLDER
[Actions\Action1\Params]
filename=STRING|"D:\Temp\TestBook.xlsx"
hide=STRING|"0"
mode=STRING|"0"
tryconnect=STRING|"0"
[Actions\Action10]
ActionID=STRING|"A_XML_ADDCHILD"
Enabled=INTEGER|-1
Name=STRING|"XML Add Childnode //add value"
Params=FOLDER
[Actions\Action10\Params]
child00000000=STRING|"<value type=""STRING"">{val}</value>"
childcount=STRING|"1"
parent00000000=STRING|"{Item}"
parentcount=STRING|"1"
variable=STRING|"item"
[Actions\Action11]
ActionID=STRING|"A_XML_ADDCHILD"
Enabled=INTEGER|-1
Name=STRING|"XML Add Childnode // add to root"
Params=FOLDER
[Actions\Action11\Params]
child00000000=STRING|"{Item}"
childcount=STRING|"1"
parent00000000=STRING|"{rootXML}"
parentcount=STRING|"1"
variable=STRING|"rootXML"
[Actions\Action12]
ActionID=STRING|"A_DIALOG_SHOWTEXT"
Enabled=INTEGER|0
Name=STRING|"Show Text"
Params=FOLDER
[Actions\Action12\Params]
autoclose=STRING|"0"
fromfile=STRING|"1"
line00000000=STRING|"{rootXML}"
linecount=STRING|"1"
timeout=STRING|"10"
[Actions\Action13]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"End Loop"
[Actions\Action14]
ActionID=STRING|"A_XML_SAVE"
Enabled=INTEGER|-1
Name=STRING|"XML Save to D:\Temp\TestBook.xml"
Params=FOLDER
[Actions\Action14\Params]
filename=STRING|"D:\Temp\TestBook.xml"
ifexists=STRING|"1"
xml00000000=STRING|"{RootXML}"
xmlcount=STRING|"1"
[Actions\Action15]
ActionID=STRING|"A_DIALOG_SHOWTEXT"
Enabled=INTEGER|-1
Name=STRING|"Show Text"
Params=FOLDER
[Actions\Action15\Params]
autoclose=STRING|"0"
filename=STRING|"D:\Temp\TestBook.xml"
fromfile=STRING|"0"
linecount=STRING|"1"
timeout=STRING|"10"
title=STRING|"Result XML"
[Actions\Action2]
ActionID=STRING|"A_XML_CREATENODE"
Enabled=INTEGER|-1
Name=STRING|"XML Create Node ""Root"" and save to ""rootXML"""
Params=FOLDER
[Actions\Action2\Params]
attcount=STRING|"0"
name=STRING|"Root"
txtcount=STRING|"0"
variable=STRING|"rootXML"
[Actions\Action3]
ActionID=STRING|"A_LOOP_SIMPLE"
Enabled=INTEGER|-1
Name=STRING|"Simple Loop"
Params=FOLDER
[Actions\Action3\Params]
begin=STRING|"2"
end=STRING|"1000"
variable=STRING|"ROW"
[Actions\Action4]
ActionID=STRING|"A_EXCEL_GET"
Enabled=INTEGER|-1
Name=STRING|"Excel Get Cells"
Params=FOLDER
[Actions\Action4\Params]
cell00000000=STRING|"R{row}C1"
cell00000001=STRING|"R{row}C2"
cell00000002=STRING|"R{Row}C3"
count=STRING|"3"
var00000000=STRING|"id"
var00000001=STRING|"nm"
var00000002=STRING|"val"
worksheet=STRING|"1"
[Actions\Action5]
ActionID=STRING|"A_FLOW_IF"
Enabled=INTEGER|-1
Name=STRING|"If Then"
Params=FOLDER
[Actions\Action5\Params]
case=STRING|"0"
connection=STRING|"0"
count=STRING|"1"
operator=STRING|"7"
type=STRING|"0"
value1=STRING|"{id}"
[Actions\Action6]
ActionID=STRING|"A_LOOP_BREAK"
Enabled=INTEGER|-1
Name=STRING|"Break"
[Actions\Action7]
ActionID=STRING|"A_FLOW_ENDIF"
Enabled=INTEGER|-1
Name=STRING|"End If"
[Actions\Action8]
ActionID=STRING|"A_XML_CREATENODE"
Enabled=INTEGER|-1
Name=STRING|"XML Create Node ""{id}"" and save to ""item"""
Params=FOLDER
[Actions\Action8\Params]
att00000000=STRING|"type,FOLDER"
attcount=STRING|"1"
name=STRING|"{id}"
txtcount=STRING|"0"
variable=STRING|"item"
[Actions\Action9]
ActionID=STRING|"A_XML_ADDCHILD"
Enabled=INTEGER|-1
Name=STRING|"XML Add Childnode // add name"
Params=FOLDER
[Actions\Action9\Params]
child00000000=STRING|"<name type=""STRING"">{nm}</name>"
childcount=STRING|"1"
parent00000000=STRING|"{Item}"
parentcount=STRING|"1"
variable=STRING|"item"
Re: Excel to XML global variable import
Posted: Tue Mar 22, 2022 12:48 pm
by Oleg
I shortened and simplified the task a little.
see below
Code: Select all
;*****************************
;* RoboTask Task file
;* Do not edit in text editor!
;*****************************
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|1163085779
Comment=STRINGLIST
ContinueOnError=INTEGER|0
DisableOnError=INTEGER|0
DoNotStopWhenShutdown=INTEGER|0
ExternalName=STRING|"Task362"
Hide=INTEGER|0
ID=INTEGER|-1429457164
LogOnAsUser=INTEGER|1
Name=STRING|"Create XML from EXCEL-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
Action2=FOLDER
Action3=FOLDER
Action4=FOLDER
Action5=FOLDER
Action6=FOLDER
Action7=FOLDER
Action8=FOLDER
Action9=FOLDER
[Actions\Action1]
ActionID=STRING|"A_EXCEL_OPEN"
Enabled=INTEGER|-1
Name=STRING|"Excel Open Document"
Params=FOLDER
[Actions\Action1\Params]
filename=STRING|"D:\Temp\TestBook.xlsx"
hide=STRING|"0"
mode=STRING|"0"
tryconnect=STRING|"0"
[Actions\Action10]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"End Loop"
[Actions\Action11]
ActionID=STRING|"A_EXCEL_CLOSE"
Enabled=INTEGER|-1
Name=STRING|"Excel Close"
[Actions\Action12]
ActionID=STRING|"A_XML_SAVE"
Enabled=INTEGER|-1
Name=STRING|"XML Save to D:\Temp\TestBook.xml"
Params=FOLDER
[Actions\Action12\Params]
filename=STRING|"D:\Temp\TestBook.xml"
ifexists=STRING|"1"
xml00000000=STRING|"{RootXML}"
xmlcount=STRING|"1"
[Actions\Action13]
ActionID=STRING|"A_DIALOG_SHOWTEXT"
Enabled=INTEGER|-1
Name=STRING|"Show Text"
Params=FOLDER
[Actions\Action13\Params]
autoclose=STRING|"0"
filename=STRING|"D:\Temp\TestBook.xml"
fromfile=STRING|"0"
linecount=STRING|"1"
timeout=STRING|"10"
title=STRING|"Result XML"
[Actions\Action2]
ActionID=STRING|"A_XML_CREATENODE"
Enabled=INTEGER|-1
Name=STRING|"XML Create Node ""Root"" and save to ""rootXML"""
Params=FOLDER
[Actions\Action2\Params]
attcount=STRING|"0"
name=STRING|"Root"
txtcount=STRING|"0"
variable=STRING|"rootXML"
[Actions\Action3]
ActionID=STRING|"A_LOOP_SIMPLE"
Enabled=INTEGER|-1
Name=STRING|"Simple Loop"
Params=FOLDER
[Actions\Action3\Params]
begin=STRING|"2"
end=STRING|"1000"
variable=STRING|"ROW"
[Actions\Action4]
ActionID=STRING|"A_EXCEL_GET"
Enabled=INTEGER|-1
Name=STRING|"Excel Get Cells"
Params=FOLDER
[Actions\Action4\Params]
cell00000000=STRING|"R{row}C1"
cell00000001=STRING|"R{row}C2"
cell00000002=STRING|"R{Row}C3"
count=STRING|"3"
var00000000=STRING|"id"
var00000001=STRING|"nm"
var00000002=STRING|"val"
worksheet=STRING|"1"
[Actions\Action5]
ActionID=STRING|"A_FLOW_IF"
Enabled=INTEGER|-1
Name=STRING|"If Then"
Params=FOLDER
[Actions\Action5\Params]
case=STRING|"0"
connection=STRING|"0"
count=STRING|"1"
operator=STRING|"7"
type=STRING|"0"
value1=STRING|"{id}"
[Actions\Action6]
ActionID=STRING|"A_LOOP_BREAK"
Enabled=INTEGER|-1
Name=STRING|"Break"
[Actions\Action7]
ActionID=STRING|"A_FLOW_ENDIF"
Enabled=INTEGER|-1
Name=STRING|"End If"
[Actions\Action8]
ActionID=STRING|"A_XML_ADDCHILD"
Enabled=INTEGER|-1
Name=STRING|"XML Add Childnode // add to root"
Params=FOLDER
[Actions\Action8\Params]
child00000000=STRING|"<{id} type=""FOLDER"">"
child00000001=STRING|" <name type=""STRING"">{nm}</name>"
child00000002=STRING|" <value type=""STRING"">{val}</value>"
child00000003=STRING|"</{id}>"
childcount=STRING|"4"
parent00000000=STRING|"{rootXML}"
parentcount=STRING|"1"
variable=STRING|"rootXML"
[Actions\Action9]
ActionID=STRING|"A_DIALOG_SHOWTEXT"
Enabled=INTEGER|0
Name=STRING|"Show Text"
Params=FOLDER
[Actions\Action9\Params]
autoclose=STRING|"0"
fromfile=STRING|"1"
line00000000=STRING|"{rootXML}"
linecount=STRING|"1"
timeout=STRING|"10"
Re: Excel to XML global variable import
Posted: Fri Mar 25, 2022 12:06 pm
by DK.Ideagen
Thank you so much Oleg finally seen this.
I have improved the above task to ask for a file.
It works perfectly.
One potential additional improvement / question.
I attempted to avoid hardcoding the user path e.g. by using
Code: Select all
%UserProfile%\Desktop\XMLtoImport.xml
as a path.
Yet this is evaluated as
Code: Select all
RobotaskDirectory/%UserProfile%/.....
Would it be possible to fix this?
Code: Select all
;*****************************
;* RoboTask Task file
;* Do not edit in text editor!
;*****************************
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|0
Comment=STRINGLIST
ContinueOnError=INTEGER|0
DisableOnError=INTEGER|0
DoNotStopWhenShutdown=INTEGER|0
ExternalName=STRING|"Task362"
Hide=INTEGER|0
ID=INTEGER|-1429457164
LogOnAsUser=INTEGER|1
Name=STRING|"Create XML from EXCEL-2 (imported)"
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
Action2=FOLDER
Action3=FOLDER
Action4=FOLDER
Action5=FOLDER
Action6=FOLDER
Action7=FOLDER
Action8=FOLDER
Action9=FOLDER
[Actions\Action1]
ActionID=STRING|"A_DIALOG_OPEN"
Enabled=INTEGER|-1
Name=STRING|"Open Dialog"
Params=FOLDER
[Actions\Action1\Params]
allowseveral=STRING|"0"
caption=STRING|"Open File"
default=STRING|"None"
filter=STRING|"*"
stopifcancel=STRING|"0"
variable=STRING|"FILE"
[Actions\Action10]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"End Loop"
[Actions\Action11]
ActionID=STRING|"A_EXCEL_CLOSE"
Enabled=INTEGER|-1
Name=STRING|"Excel Close"
[Actions\Action12]
ActionID=STRING|"A_XML_SAVE"
Enabled=INTEGER|-1
Name=STRING|"XML Save to C:\Users\daniel.kendall\OneDrive - Ideagen plc\Desktop\xmltoImport.xml"
Params=FOLDER
[Actions\Action12\Params]
filename=STRING|"C:\Users\daniel.kendall\OneDrive - Ideagen plc\Desktop\xmltoImport.xml"
ifexists=STRING|"1"
xml00000000=STRING|"{RootXML}"
xmlcount=STRING|"1"
[Actions\Action13]
ActionID=STRING|"A_DIALOG_SHOWTEXT"
Enabled=INTEGER|-1
Name=STRING|"Show Text"
Params=FOLDER
[Actions\Action13\Params]
autoclose=STRING|"0"
filename=STRING|"C:\Users\daniel.kendall\OneDrive - Ideagen plc\Desktop\xmltoImport.xml"
fromfile=STRING|"0"
linecount=STRING|"1"
timeout=STRING|"10"
title=STRING|"Result XML"
[Actions\Action2]
ActionID=STRING|"A_EXCEL_OPEN"
Enabled=INTEGER|-1
Name=STRING|"Excel Open Document"
Params=FOLDER
[Actions\Action2\Params]
filename=STRING|"{file}"
hide=STRING|"0"
mode=STRING|"0"
tryconnect=STRING|"0"
[Actions\Action3]
ActionID=STRING|"A_XML_CREATENODE"
Enabled=INTEGER|-1
Name=STRING|"XML Create Node ""Root"" and save to ""rootXML"""
Params=FOLDER
[Actions\Action3\Params]
attcount=STRING|"0"
name=STRING|"Root"
txtcount=STRING|"0"
variable=STRING|"rootXML"
[Actions\Action4]
ActionID=STRING|"A_LOOP_SIMPLE"
Enabled=INTEGER|-1
Name=STRING|"Simple Loop"
Params=FOLDER
[Actions\Action4\Params]
begin=STRING|"2"
end=STRING|"1000"
variable=STRING|"ROW"
[Actions\Action5]
ActionID=STRING|"A_EXCEL_GET"
Enabled=INTEGER|-1
Name=STRING|"Excel Get Cells"
Params=FOLDER
[Actions\Action5\Params]
cell00000000=STRING|"R{row}C1"
cell00000001=STRING|"R{row}C2"
cell00000002=STRING|"R{row}C3"
count=STRING|"3"
var00000000=STRING|"id"
var00000001=STRING|"nm"
var00000002=STRING|"val"
worksheet=STRING|"1"
[Actions\Action6]
ActionID=STRING|"A_FLOW_IF"
Enabled=INTEGER|-1
Name=STRING|"If Then"
Params=FOLDER
[Actions\Action6\Params]
case=STRING|"0"
connection=STRING|"0"
count=STRING|"1"
operator=STRING|"7"
type=STRING|"0"
value1=STRING|"{id}"
[Actions\Action7]
ActionID=STRING|"A_LOOP_BREAK"
Enabled=INTEGER|-1
Name=STRING|"Break"
[Actions\Action8]
ActionID=STRING|"A_FLOW_ENDIF"
Enabled=INTEGER|-1
Name=STRING|"End If"
[Actions\Action9]
ActionID=STRING|"A_XML_ADDCHILD"
Enabled=INTEGER|-1
Name=STRING|"XML Add Childnode // add to root"
Params=FOLDER
[Actions\Action9\Params]
child00000000=STRING|"<{id} type=""FOLDER"">"
child00000001=STRING|" <name type=""STRING"">{nm}</name>"
child00000002=STRING|" <value type=""STRING"">{val}</value>"
child00000003=STRING|"</{id}>"
childcount=STRING|"4"
parent00000000=STRING|"{rootXML}"
parentcount=STRING|"1"
variable=STRING|"rootXML"
Re: Excel to XML global variable import
Posted: Fri Mar 25, 2022 12:35 pm
by Oleg
DK.Ideagen wrote: ↑Fri Mar 25, 2022 12:06 pm
One potential additional improvement / question.
I attempted to avoid hardcoding the user path e.g. by using
Code: Select all
%UserProfile%\Desktop\XMLtoImport.xml
as a path.
Yet this is evaluated as
Code: Select all
RobotaskDirectory/%UserProfile%/.....
Would it be possible to fix this?
There is
EnvironmentVar system variable
Use the expression
Code: Select all
{EnvironmentVar(UserProfile)}\Desktop\XMLtoImport.xml
also look at the example
Code: Select all
;*****************************
;* RoboTask Task file
;* Do not edit in text editor!
;*****************************
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|1163085779
Comment=STRINGLIST
ContinueOnError=INTEGER|0
DisableOnError=INTEGER|0
DoNotStopWhenShutdown=INTEGER|0
ExternalName=STRING|"Task1382"
Hide=INTEGER|0
ID=INTEGER|-811450540
LogOnAsUser=INTEGER|1
Name=STRING|"Environment variable"
OnErrorTaskID=INTEGER|200524284
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
UnicodeFormat=INTEGER|1
WriteGeneralLog=INTEGER|0
[Actions]
Action1=FOLDER
[Actions\Action1]
ActionID=STRING|"A_DIALOG_MESSAGE"
Enabled=INTEGER|-1
Name=STRING|"Show ""{EnvironmentVar(UserProfile)}\Desktop\XMLtoImport.xml"""
Params=FOLDER
[Actions\Action1\Params]
icon=STRING|"1"
msg0=STRING|"{EnvironmentVar(UserProfile)}\Desktop\XMLtoImport.xml"
msgcount=STRING|"1"
playsound=STRING|"0"
showmessage=STRING|"1"
Re: Excel to XML global variable import
Posted: Fri Mar 25, 2022 3:47 pm
by DK.Ideagen
Perfect thank you