SQL insert / update database from file

millsey · Post by **millsey** » Fri Mar 11, 2005 2:38 am

Is it possible to get Robotask to open a file (.csv or could be text) and for each line in the file do an SQL insert or update command so that we can have a list of items to add to a table?

Obviously we would have to make sure that the data is correct in the file!!

Millsey

Post by **Oleg** » Fri Mar 11, 2005 3:46 am

See task below. In this task text loop is used. If value of user variable ia a comma separated text you can access each value so: {UserVariable(N)}, there N is a number from 0 to <max_value_number>. I've made so that text loop is based on internal text. You can change it to any external text.

Save task text to any file and use menu "Task | Import" to import into RoboTask

;**********************
;* RoboTask Task file *
;* Do not edit! *
;**********************

[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|565106205
ContinueOnError=INTEGER|0
ExternalName=STRING|"Task238"
Hide=INTEGER|0
ID=INTEGER|1221458337
Name=STRING|"Exec SQL from text"
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3

[Actions]
Action1=FOLDER
Action2=FOLDER
Action3=FOLDER
Action4=FOLDER

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

[Actions\Action1\Params]
destvar=STRING|"TEXT_LINE"
line0=STRING|"""1-st value"",1,""1-st comment"""
line1=STRING|"""2-nd value"",2,""2-nd comment"""
line2=STRING|"""3-d value"",3,""3-d comment"""
linecount=STRING|"3"
sourcetext=STRING|"1"

[Actions\Action2]
ActionID=STRING|"A_EXECSQL"
Enabled=INTEGER|-1
Name=STRING|"SQL Query"
Params=FOLDER

[Actions\Action2\Params]
connectionstring=STRING|"Provider=Microsoft.Jet.OLEDB.4.0;Da ta Source=C:\test\test.mdb;Mode=ReadWrite|Share Deny None;Persist Security Info=False"
override=STRING|"0"
password=STRING|"2005621642221612164019088"
sql=STRING|"""insert into table1 (field1,field2,field3) values "",""('{Text_Line(0)}', {Text_Line(1)}, '{Text_Line(2)}')"""

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

[Actions\Action4]
ActionID=STRING|"A_VARIABLES_REMOVE"
Enabled=INTEGER|-1
Name=STRING|"Remove variable ""TEXT_LINE"""
Params=FOLDER

[Actions\Action4\Params]
varname=STRING|"TEXT_LINE"

Post by **Oleg** » Fri Mar 11, 2005 3:53 am

In this example I've created table named Table1 with such structure:
field1 - text(50)
field2 - number
field3 - text(50)

millsey · Post by **millsey** » Fri Mar 11, 2005 5:39 am

I cannot beleive you have replied so quickly! Thanks very much and I will try this shortly.

Regards
Brian

millsey · Post by **millsey** » Fri Mar 11, 2005 6:08 am

Will this work? I have set a variable for the name of the file to parse and also changed the SQL (note I am calling the parts of the variable out of order)
;**********************
;* RoboTask Task file *
;* Do not edit! *
;**********************

[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|0
CatID=INTEGER|0
ContinueOnError=INTEGER|0
ExternalName=STRING|"Task238"
Hide=INTEGER|0
ID=INTEGER|804024195
Name=STRING|"Update PI04 case detail"
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
[Actions]
Action1=FOLDER
Action2=FOLDER
Action3=FOLDER
Action4=FOLDER
Action5=FOLDER
[Actions\Action1]
ActionID=STRING|"A_DIALOG_OPEN"
Enabled=INTEGER|-1
Name=STRING|"Open Dialog"
Params=FOLDER
[Actions\Action1\Params]
caption=STRING|"Select file containing update data"
default=STRING|"None"
filter=STRING|"csv files|*.csv|All files|*.*|"
variable=STRING|"UPDATEFILENAME"
[Actions\Action2]
ActionID=STRING|"A_LOOP_TEXT"
Enabled=INTEGER|-1
Name=STRING|"Text Loop"
Params=FOLDER
[Actions\Action2\Params]
destvar=STRING|"TEXT_LINE"
file=STRING|"{UPDATEFILENAME}"
linecount=STRING|"0"
sourcetext=STRING|"0"
[Actions\Action3]
ActionID=STRING|"A_EXECSQL"
Enabled=INTEGER|-1
Name=STRING|"SQL Query"
Params=FOLDER
[Actions\Action3\Params]
connectionstring=STRING|"Provider=MSDASQL.1;Persist Security Info=False;Data Source=arista32"
override=STRING|"0"
password=STRING|"2159220111216422138819088"
sql=STRING|"""update casdet set case_text = '{Text_Line(1)}', case_date = "",""'{Text_Line(2)}' where case_id = '{Text_Line(0)}' and "",""case_detail_code = 'PI04'"""
[Actions\Action4]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"End Loop"
[Actions\Action5]
ActionID=STRING|"A_VARIABLES_REMOVE"
Enabled=INTEGER|-1
Name=STRING|"Remove variable ""TEXT_LINE"""
Params=FOLDER
[Actions\Action5\Params]
varname=STRING|"TEXT_LINE"
<end of export> Millsey?

millsey · Post by **millsey** » Fri Mar 11, 2005 7:03 am

I have altered it a little more and IT IS WORKING

The problem is that the array is being seperated by ANY non-alpha character rather than just commas, so if I am trying to insert a case_text of "testing testing" the array is split by the space inbetween the words.

Can you help at all??

Post by **Oleg** » Fri Mar 11, 2005 7:20 am

Why not? It looks correctly. But some remarks:
Does all fields have string type? You quoted all parameters (ex. '{Text_Line(1)}'). If parameter has a numeric type you should write without quotation marks (ex. {Text_Line(1)}).
And you should use correct .csv files. Otherwise an error will be raised at SQL execution.

Post by **Oleg** » Fri Mar 11, 2005 7:26 am

The problem is that the array is being seperated by ANY non-alpha character rather than just commas, so if I am trying to insert a case_text of "testing testing" the array is split by the space inbetween the words.

Write comma-separated text as this
"any string or number","anoter string","and so on"
and you will have not problems.

millsey · Post by **millsey** » Fri Mar 11, 2005 7:54 am

Oleg
Doing it this way breaks the SQL statement where we are telling the database
update casdet set case_text = ' " text stuff " ' I have aded spaces to show the problem = we are trying to insert quotes into tthe database which is not allowed. Would you consider addint a delimit selector or some other way to identify the delimiter. We use csv files heavily here and in most cases we do not include quotes around the text fields.

Thanks for all your help

Millsey

Post by **Oleg** » Sat Mar 12, 2005 3:22 am

We have decided to add into new release of RoboTask one more action. This action shall bring to normalized kind any delimited string. User can set one or more delimiter char, quote char and get the normalized comma-separated string
I think it is necessary to eliminate any discrepancies.
New release of RoboTask will be very soon, not more 2 weeks.