Excel to XML global variable import

DK.Ideagen · Post by **DK.Ideagen** » Thu Mar 10, 2022 10:58 am

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

Post by **Oleg** » Fri Mar 18, 2022 9:04 am

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

Post by **Oleg** » Tue Mar 22, 2022 11:08 am

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"

Post by **Oleg** » Tue Mar 22, 2022 12:48 pm

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"

DK.Ideagen · Post by **DK.Ideagen** » Fri Mar 25, 2022 12:06 pm

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"

Post by **Oleg** » Fri Mar 25, 2022 12:35 pm

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"

DK.Ideagen · Post by **DK.Ideagen** » Fri Mar 25, 2022 3:47 pm

Perfect thank you

RoboTask forum

Excel to XML global variable import

Excel to XML global variable import

Re: Excel to XML global variable import

Re: Excel to XML global variable import

Re: Excel to XML global variable import

Re: Excel to XML global variable import

Re: Excel to XML global variable import

Re: Excel to XML global variable import