MySQL Import Query

[tpaolillo]

Hi Im struggling with an option to import a CSV file into my MSQL database from within Robotask. Im using the latest 7.3.0.0964.
Attached is the CSV file im trying to import. Seems I cant use "INSERT INTO" with robotask because MySQL doesent support ADO or something.?.
Ive created a PHP file successfully with the following SQL code:
"$sql = "INSERT INTO simmons_pricechange (Location, SiteName, PanelID, PriceChangeTime, Grade, Name, OldPriceCash, NewPriceCash, CashPriceDifference, OldPriceCredit, NewPriceCredit, CreditPriceDifference, PriceChanged)
VALUES('" . implode("','",$row) . "')";"

However this is what I have in Robotask which fails because it doesent support INSERT INLINE:
"LOAD DATA INFILE 'C:\Support\SimmonsPrices\negw_dpcr_5006__20180722_204073114.csv'
INTO TABLE simmons_pricechange
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS"

Can someone help me write a way to read from the CSV file and import it into my MySQL database? Can it be done?

Thanks in advance.

Tony

[Oleg]

RoboTask uses ADO/ODBC interface to connect to any database.
So, this have some restrictions of SQL extensions of some databases. But it supports ANSI SQL completely.

You have to process CSV file line-by-line in the loop and add records sequentially
Look at my example:

Code: Select all

;*****************************
;* RoboTask Task file
;* Do not edit in text editor!
;*****************************
 
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|179181099
Comment=STRINGLIST
ContinueOnError=INTEGER|0
DoNotStopWhenShutdown=INTEGER|0
ExternalName=STRING|"Task106"
Hide=INTEGER|0
ID=INTEGER|464676392
LogOnAsUser=INTEGER|1
Name=STRING|"CSV to database"
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
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 ""CSVFILE"" with value ""D:\systemp\Downloads\negw_dpcr_5006__20180722_204073114.csv"""
Params=FOLDER

[Actions\Action1\Params]
expand=STRING|"0"
varname=STRING|"CSVFILE"
varvalue=STRING|"D:\systemp\Downloads\negw_dpcr_5006__20180722_204073114.csv"

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

[Actions\Action10\Params]
expand=STRING|"0"
varname=STRING|"FIRST"
varvalue=STRING|"false"

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

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

[Actions\Action2\Params]
expand=STRING|"0"
varname=STRING|"FIRST"
varvalue=STRING|"true"

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

[Actions\Action3\Params]
destvar=STRING|"LINE"
file=STRING|"{CSVFile}"
linecount=STRING|"0"
sourcetext=STRING|"0"

[Actions\Action4]
ActionID=STRING|"A_MISC_COMMENT"
Enabled=INTEGER|-1
Name=STRING|"//skip first line"
Params=FOLDER

[Actions\Action4\Params]
comment=STRING|"skip first line"

[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|"1"
type=STRING|"3"
value1=STRING|"{first}"

[Actions\Action6]
ActionID=STRING|"A_VARIABLES_DELIMITED"
Enabled=INTEGER|-1
Name=STRING|"Delimit Variable"
Params=FOLDER

[Actions\Action6\Params]
colon=STRING|"0"
comma=STRING|"1"
other=STRING|"0"
quote=STRING|"0"
semocolon=STRING|"0"
space=STRING|"0"
spaceasone=STRING|"0"
string=STRING|"{Line}"
tab=STRING|"0"
variable=STRING|"Line"

[Actions\Action7]
ActionID=STRING|"A_DIALOG_MESSAGE"
Enabled=INTEGER|0
Name=STRING|"Show ""{Line}"""
Params=FOLDER

[Actions\Action7\Params]
icon=STRING|"1"
msg0=STRING|"{Line}"
msg1=STRING|"{Line(0)}"
msg10=STRING|"{Line(9)}"
msg11=STRING|"{Line(10)}"
msg12=STRING|"{Line(11)}"
msg13=STRING|"{Line(12)}"
msg2=STRING|"{Line(1)}"
msg3=STRING|"{Line(2)}"
msg4=STRING|"{Line(3)}"
msg5=STRING|"{Line(4)}"
msg6=STRING|"{Line(5)}"
msg7=STRING|"{Line(6)}"
msg8=STRING|"{Line(7)}"
msg9=STRING|"{Line(8)}"
msgcount=STRING|"14"
playsound=STRING|"0"
showmessage=STRING|"1"

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

[Actions\Action8\Params]
assignvar=STRING|"0"
connectionstring=STRING|"Provider=MSDASQL.1;Persist Security Info=False;Data Source=mytest"
override=STRING|"0"
paramcount=STRING|"13"
password=STRING|"2133621135211341985520625"
pname0=STRING|"par0"
pname1=STRING|"par1"
pname10=STRING|"par10"
pname11=STRING|"par11"
pname12=STRING|"par12"
pname2=STRING|"par2"
pname3=STRING|"par3"
pname4=STRING|"par4"
pname5=STRING|"par5"
pname6=STRING|"par6"
pname7=STRING|"par7"
pname8=STRING|"par8"
pname9=STRING|"par9"
ptype0=STRING|"0"
ptype1=STRING|"0"
ptype10=STRING|"0"
ptype11=STRING|"0"
ptype12=STRING|"0"
ptype2=STRING|"0"
ptype3=STRING|"0"
ptype4=STRING|"0"
ptype5=STRING|"0"
ptype6=STRING|"0"
ptype7=STRING|"0"
ptype8=STRING|"0"
ptype9=STRING|"0"
pvalue0=STRING|"{line(0)}"
pvalue1=STRING|"{line(1)}"
pvalue10=STRING|"{line(10)}"
pvalue11=STRING|"{line(11)}"
pvalue12=STRING|"{line(12)}"
pvalue2=STRING|"{line(2)}"
pvalue3=STRING|"{line(3)}"
pvalue4=STRING|"{line(4)}"
pvalue5=STRING|"{line(5)}"
pvalue6=STRING|"{line(6)}"
pvalue7=STRING|"{line(7)}"
pvalue8=STRING|"{line(8)}"
pvalue9=STRING|"{line(9)}"
sql=STRING|"""INSERT INTO simmons_pricechange "",""(Location, SiteName, PanelID, "",""PriceChangeTime, Grade, Name, "",""OldPriceCash, NewPriceCash, CashPriceDifference, "",""OldPriceCredit, NewPriceCredit, CreditPriceDifference, PriceChanged)"",VALUES,""( :par0, :par1, :par2,"",""  :par3, :par4, :par5,"",""  :par6, :par7, :par8,"",""  :par9, :par10, :par11, :par12 )"""
timeout=STRING|"60"

[Actions\Action9]
ActionID=STRING|"A_FLOW_ENDIF"
Enabled=INTEGER|-1
Name=STRING|"End If"


Save the text of the task to a file and use menu Task->Import to import the task into RoboTask.
Of course you have to change Connection string in SQL Query action

[Oleg]

Also look at the similar topic: viewtopic.php?f=1&p=7052