Interestingly I've asked for date format to always be yyyy-mm-dd but I'm getting it as dd-mm-yy
I've manually edited the CSV to get yyyy-mm-dd but I'll have a look at using date function of robotask
Actually I'm not getting far with it.
I had thought I could use DateTimeToFormat({Date},yyyy-mm-dd)} where the date was Line(4) from the csv file
Also tried using SQL todate function - TO_DATE({Line(4)}, 'YYYY/MM/DD') but that's not working either.
Used a text windows to show contents of LINE(4) - this I thought to be the 5th item I nthe CSV file.
As it's a CSV file I had thought that LINE(0) was the first field, LINE(1) was second etc
Does reading a text file (even if its csv) make each word (separated by a space) a field in an array?
MY CSV file contains a lot of field that require users to enter a short description.
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
Events=FOLDER
ExternalName=STRING|"Task19"
Hide=INTEGER|0
ID=INTEGER|2102834449
LogOnAsUser=INTEGER|1
Name=STRING|"MCLS015"
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_LOOP_FILE"
Enabled=INTEGER|-1
Name=STRING|"File Loop"
Params=FOLDER
[Actions\Action1\Params]
createmode=STRING|"1"
date1=STRING|"20180607"
date2=STRING|"20180607"
destvar=STRING|"FILELIST"
DuringDays=STRING|"1"
DuringHours=STRING|"1"
DuringMinutes=STRING|"1"
DuringMonths=STRING|"1"
file0=STRING|"C:\FTPInkwrx\MCLS015\*.csv"
filecount=STRING|"1"
OlderDays=STRING|"1"
OlderHours=STRING|"1"
OlderMinutes=STRING|"1"
OlderMonths=STRING|"1"
savesize=STRING|"0"
searchkind=STRING|"0"
sort=STRING|"1"
sortby=STRING|"0"
sortorder=STRING|"0"
subdirs=STRING|"0"
timesize=STRING|"0"
WithoutPath=STRING|"0"
[Actions\Action10]
ActionID=STRING|"A_VARIABLES_CREATE"
Enabled=INTEGER|-1
Name=STRING|"Create local variable ""AUDITDATE"" with value ""{DateTimeToFormat({LINE(4)},yyyy-mm-dd)}"""
Params=FOLDER
[Actions\Action10\Params]
expand=STRING|"1"
varkind=STRING|"1"
varname=STRING|"AUDITDATE"
varvalue=STRING|"{DateTimeToFormat({LINE(4)},yyyy-mm-dd)}"
[Actions\Action11]
ActionID=STRING|"A_DB_EXECSQL"
Enabled=INTEGER|-1
Name=STRING|"Insert in SQL"
Params=FOLDER
[Actions\Action11\Params]
assignvar=STRING|"0"
paramcount=STRING|"0"
sql=STRING|"""INSERT INTO [TabletForms].[dbo].[MCLS015]"",""( [DocRef]"","" ,[ContractID]"","" ,[ContractName]"","" ,[Location]"","" ,[AuditDate]"","" ,[AuditTime]"","" ,[Auditor1]"","" ,[AuditorPosition1]"","" ,[Auditor2]"","" ,[AuditorPosition2]"","" ,[Status]"","" ,[AuditComment01]"","" ,[ToAction01]"","" ,[CloseComment01]"","" ,[AuditComment02]"","" ,[ToAction02]"","" ,[CloseComment02]"","" ,[AuditComment03]"","" ,[ToAction03]"","" ,[CloseComment03]"","" ,[AuditComment04]"","" ,[ToAction04]"","" ,[CloseComment04]"","" ,[AuditComment05]"","" ,[ToAction05]"","" ,[CloseComment05]"","" ,[AuditComment06]"","" ,[ToAction06]"","" ,[CloseComment06]"","" ,[AuditComment07]"","" ,[ToAction07]"","" ,[CloseComment07]"","" ,[AuditComment08]"","" ,[ToAction08]"","" ,[CloseComment08]"","" ,[AuditComment09]"","" ,[ToAction09]"","" ,[CloseComment09]"","" ,[AuditComment10]"","" ,[ToAction10]"","" ,[CloseComment10]"","" ,[AuditComment11]"","" ,[ToAction11]"","" ,[CloseComment11]"","" ,[AuditComment12]"","" ,[ToAction12]"","" ,[CloseComment12]"","" ,[AuditComment13]"","" ,[ToAction13]"","" ,[CloseComment13]"","" ,[AuditComment14]"","" ,[ToAction14]"","" ,[CloseComment14]"","" ,[AuditComment15]"","" ,[ToAction15]"","" ,[CloseComment15]"","" ,[AuditComment16]"","" ,[ToAction16]"","" ,[CloseComment16]"","" ,[AuditComment17]"","" ,[ToAction17]"","" ,[CloseComment17]"","" ,[AuditComment18]"","" ,[ToAction18]"","" ,[CloseComment18]"","" ,[AuditComment19]"","" ,[ToAction19]"","" ,[CloseComment19]"","" ,[AuditComment20]"","" ,[ToAction20]"","" ,[CloseComment20]"","" ,[AuditComment21]"","" ,[ToAction21]"","" ,[CloseComment21]"","" ,[AuditComment22]"","" ,[ToAction22]"","" ,[CloseComment22]"","" ,[AuditComment23]"","" ,[ToAction23]"","" ,[CloseComment23]"","" ,[AuditComment24]"","" ,[ToAction24]"","" ,[CloseComment24]"","" ,[AuditComment25]"","" ,[ToAction25]"","" ,[CloseComment25]"","" ,[AuditComment26]"","" ,[ToAction26]"","" ,[CloseComment26]"","" ,[AuditComment27]"","" ,[ToAction27]"","" ,[CloseComment27]"","" ,[AuditComment28]"","" ,[ToAction28]"","" ,[CloseComment28]"","" ,[TobeReviewedBy]"","" ,[AuditorSignature]"","" ,[AgentSignature])"",,""VALUES "",""('{Line(0)}','{Line(1)}','{Line(2)}','{Line(3)}', TO_DATE({Line(4)}, 'YYYY/MM/DD'),'{Line(5)}','{Line(6)}','{Line(7)}','{Line(8)}','{Line(9)}','{Line(10)}','{Line(11)}','{Line(12)}','{Line(13)}','{Line(14)}','{Line(15)}','{Line(16)}','{Line(17)}','{Line(18)}','{Line(19)}','{Line(20)}','{Line(21)}','{Line(22)}','{Line(23)}','{Line(24)}','{Line(25)}','{Line(26)}','{Line(27)}','{Line(28)}','{Line(29)}','{Line(30)}','{Line(31)}','{Line(32)}','{Line(33)}','{Line(34)}','{Line(35)}','{Line(36)}','{Line(37)}','{Line(38)}','{Line(39)}','{Line(40)}','{Line(41)}','{Line(42)}','{Line(43)}','{Line(44)}','{Line(45)}','{Line(46)}','{Line(47)}','{Line(48)}','{Line(49)}','{Line(50)}','{Line(51)}','{Line(52)}','{Line(53)}','{Line(54)}','{Line(55)}','{Line(56)}','{Line(57)}','{Line(58)}','{Line(59)}','{Line(60)}','{Line(61)}','{Line(62)}','{Line(63)}','{Line(64)}','{Line(65)}','{Line(66)}','{Line(67)}','{Line(68)}','{Line(69)}','{Line(70)}','{Line(71)}','{Line(72)}','{Line(73)}','{Line(74)}','{Line(75)}','{Line(76)}','{Line(77)"",""}','{Line(78)}','{Line(79)}','{Line(80)}','{Line(81)}','{Line(82)}','{Line(83)}','{Line(84)}','{Line(85)}','{Line(86)}','{Line(87)}','{Line(88)}','{Line(89)}','{Line(90)}','{Line(91)}','{Line(92)}','{Line(93)}','{Line(94)}','{Line(95)}','{Line(96)}','{Line(97)}')"""
timeout=STRING|"60"
[Actions\Action12]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"Close CSV"
[Actions\Action13]
ActionID=STRING|"A_DB_CLOSE"
Enabled=INTEGER|-1
Name=STRING|"DB Close"
[Actions\Action14]
ActionID=STRING|"A_FILE_DELETE"
Enabled=INTEGER|-1
Name=STRING|"Delete File"
Params=FOLDER
[Actions\Action14\Params]
Count=STRING|"1"
deletedirs=STRING|"0"
file0=STRING|"{FILELIST}"
hidden=STRING|"0"
subdir=STRING|"0"
system=STRING|"0"
ToRecycleBin=STRING|"0"
[Actions\Action15]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"End Loop"
[Actions\Action2]
ActionID=STRING|"A_DB_OPEN"
Enabled=INTEGER|-1
Name=STRING|"DB Open"
Params=FOLDER
[Actions\Action2\Params]
connectionstring=STRING|"Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=TabletForms;Data Source=SQLBOX"
override=STRING|"1"
password=STRING|"2801516022272863579116858"
userid=STRING|"RECORDSADMIN"
[Actions\Action3]
ActionID=STRING|"A_STR_EXTRACT"
Enabled=INTEGER|-1
Name=STRING|"Get Document ID"
Params=FOLDER
[Actions\Action3\Params]
amount=STRING|"7"
from=STRING|"29"
source=STRING|"{FILELIST}"
variable=STRING|"DOCID"
[Actions\Action4]
ActionID=STRING|"A_DB_LOOP"
Enabled=INTEGER|-1
Name=STRING|"DB Check for record"
Params=FOLDER
[Actions\Action4\Params]
commatext=STRING|"0"
paramcount=STRING|"0"
sql=STRING|"""SELECT [Status]"","" FROM [TabletForms].[dbo].[MCLS015]"","" where docref={DOCID}"""
timeout=STRING|"60"
var2=STRING|"RECORDS"
withmemo=STRING|"0"
[Actions\Action5]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"End Loop"
[Actions\Action6]
ActionID=STRING|"A_FLOW_IF"
Enabled=INTEGER|-1
Name=STRING|"If record exists"
Params=FOLDER
[Actions\Action6\Params]
case=STRING|"0"
connection=STRING|"0"
count=STRING|"1"
operator=STRING|"1"
type=STRING|"0"
value1=STRING|"EXISTS"
[Actions\Action7]
ActionID=STRING|"A_DB_EXECSQL"
Enabled=INTEGER|-1
Name=STRING|"Delete record"
Params=FOLDER
[Actions\Action7\Params]
assignvar=STRING|"0"
paramcount=STRING|"0"
sql=STRING|"""DELETE FROM [TabletForms].[dbo].[MCLS015]"",""where docref={DOCID};"""
timeout=STRING|"60"
varname=STRING|"EXISTS"
[Actions\Action8]
ActionID=STRING|"A_FLOW_ENDIF"
Enabled=INTEGER|-1
Name=STRING|"End If"
[Actions\Action9]
ActionID=STRING|"A_LOOP_TEXT"
Enabled=INTEGER|-1
Name=STRING|"Open CSV"
Params=FOLDER
[Actions\Action9\Params]
destvar=STRING|"LINE"
file=STRING|"{FILELIST}"
linecount=STRING|"0"
sourcetext=STRING|"0"
[Events]
Event1=FOLDER
[Events\Event1]
Enabled=INTEGER|-1
EventID=STRING|"E_GENERAL_SHEDULER"
Params=FOLDER
UniqueID=INTEGER|-81326141
[Events\Event1\Params]
periodicity=STRING|"0"
startdate=STRING|"43258"
time0=STRING|"0"
time1=STRING|"300000"
time10=STRING|"3000000"
time100=STRING|"30000000"
time101=STRING|"30300000"
time102=STRING|"30600000"
time103=STRING|"30900000"
time104=STRING|"31200000"
time105=STRING|"31500000"
time106=STRING|"31800000"
time107=STRING|"32100000"
time108=STRING|"32400000"
time109=STRING|"32700000"
time11=STRING|"3300000"
time110=STRING|"33000000"
time111=STRING|"33300000"
time112=STRING|"33600000"
time113=STRING|"33900000"
time114=STRING|"34200000"
time115=STRING|"34500000"
time116=STRING|"34800000"
time117=STRING|"35100000"
time118=STRING|"35400000"
time119=STRING|"35700000"
time12=STRING|"3600000"
time120=STRING|"36000000"
time121=STRING|"36300000"
time122=STRING|"36600000"
time123=STRING|"36900000"
time124=STRING|"37200000"
time125=STRING|"37500000"
time126=STRING|"37800000"
time127=STRING|"38100000"
time128=STRING|"38400000"
time129=STRING|"38700000"
time13=STRING|"3900000"
time130=STRING|"39000000"
time131=STRING|"39300000"
time132=STRING|"39600000"
time133=STRING|"39900000"
time134=STRING|"40200000"
time135=STRING|"40500000"
time136=STRING|"40800000"
time137=STRING|"41100000"
time138=STRING|"41400000"
time139=STRING|"41700000"
time14=STRING|"4200000"
time140=STRING|"42000000"
time141=STRING|"42300000"
time142=STRING|"42600000"
time143=STRING|"42900000"
time144=STRING|"43200000"
time145=STRING|"43500000"
time146=STRING|"43800000"
time147=STRING|"44100000"
time148=STRING|"44400000"
time149=STRING|"44700000"
time15=STRING|"4500000"
time150=STRING|"45000000"
time151=STRING|"45300000"
time152=STRING|"45600000"
time153=STRING|"45900000"
time154=STRING|"46200000"
time155=STRING|"46500000"
time156=STRING|"46800000"
time157=STRING|"47100000"
time158=STRING|"47400000"
time159=STRING|"47700000"
time16=STRING|"4800000"
time160=STRING|"48000000"
time161=STRING|"48300000"
time162=STRING|"48600000"
time163=STRING|"48900000"
time164=STRING|"49200000"
time165=STRING|"49500000"
time166=STRING|"49800000"
time167=STRING|"50100000"
time168=STRING|"50400000"
time169=STRING|"50700000"
time17=STRING|"5100000"
time170=STRING|"51000000"
time171=STRING|"51300000"
time172=STRING|"51600000"
time173=STRING|"51900000"
time174=STRING|"52200000"
time175=STRING|"52500000"
time176=STRING|"52800000"
time177=STRING|"53100000"
time178=STRING|"53400000"
time179=STRING|"53700000"
time18=STRING|"5400000"
time180=STRING|"54000000"
time181=STRING|"54300000"
time182=STRING|"54600000"
time183=STRING|"54900000"
time184=STRING|"55200000"
time185=STRING|"55500000"
time186=STRING|"55800000"
time187=STRING|"56100000"
time188=STRING|"56400000"
time189=STRING|"56700000"
time19=STRING|"5700000"
time190=STRING|"57000000"
time191=STRING|"57300000"
time192=STRING|"57600000"
time193=STRING|"57900000"
time194=STRING|"58200000"
time195=STRING|"58500000"
time196=STRING|"58800000"
time197=STRING|"59100000"
time198=STRING|"59400000"
time199=STRING|"59700000"
time2=STRING|"600000"
time20=STRING|"6000000"
time200=STRING|"60000000"
time201=STRING|"60300000"
time202=STRING|"60600000"
time203=STRING|"60900000"
time204=STRING|"61200000"
time205=STRING|"61500000"
time206=STRING|"61800000"
time207=STRING|"62100000"
time208=STRING|"62400000"
time209=STRING|"62700000"
time21=STRING|"6300000"
time210=STRING|"63000000"
time211=STRING|"63300000"
time212=STRING|"63600000"
time213=STRING|"63900000"
time214=STRING|"64200000"
time215=STRING|"64500000"
time216=STRING|"64800000"
time217=STRING|"65100000"
time218=STRING|"65400000"
time219=STRING|"65700000"
time22=STRING|"6600000"
time220=STRING|"66000000"
time221=STRING|"66300000"
time222=STRING|"66600000"
time223=STRING|"66900000"
time224=STRING|"67200000"
time225=STRING|"67500000"
time226=STRING|"67800000"
time227=STRING|"68100000"
time228=STRING|"68400000"
time229=STRING|"68700000"
time23=STRING|"6900000"
time230=STRING|"69000000"
time231=STRING|"69300000"
time232=STRING|"69600000"
time233=STRING|"69900000"
time234=STRING|"70200000"
time235=STRING|"70500000"
time236=STRING|"70800000"
time237=STRING|"71100000"
time238=STRING|"71400000"
time239=STRING|"71700000"
time24=STRING|"7200000"
time240=STRING|"72000000"
time241=STRING|"72300000"
time242=STRING|"72600000"
time243=STRING|"72900000"
time244=STRING|"73200000"
time245=STRING|"73500000"
time246=STRING|"73800000"
time247=STRING|"74100000"
time248=STRING|"74400000"
time249=STRING|"74700000"
time25=STRING|"7500000"
time250=STRING|"75000000"
time251=STRING|"75300000"
time252=STRING|"75600000"
time253=STRING|"75900000"
time254=STRING|"76200000"
time255=STRING|"76500000"
time256=STRING|"76800000"
time257=STRING|"77100000"
time258=STRING|"77400000"
time259=STRING|"77700000"
time26=STRING|"7800000"
time260=STRING|"78000000"
time261=STRING|"78300000"
time262=STRING|"78600000"
time263=STRING|"78900000"
time264=STRING|"79200000"
time265=STRING|"79500000"
time266=STRING|"79800000"
time267=STRING|"80100000"
time268=STRING|"80400000"
time269=STRING|"80700000"
time27=STRING|"8100000"
time270=STRING|"81000000"
time271=STRING|"81300000"
time272=STRING|"81600000"
time273=STRING|"81900000"
time274=STRING|"82200000"
time275=STRING|"82500000"
time276=STRING|"82800000"
time277=STRING|"83100000"
time278=STRING|"83400000"
time279=STRING|"83700000"
time28=STRING|"8400000"
time280=STRING|"84000000"
time281=STRING|"84300000"
time282=STRING|"84600000"
time283=STRING|"84900000"
time284=STRING|"85200000"
time285=STRING|"85500000"
time286=STRING|"85800000"
time287=STRING|"86100000"
time29=STRING|"8700000"
time3=STRING|"900000"
time30=STRING|"9000000"
time31=STRING|"9300000"
time32=STRING|"9600000"
time33=STRING|"9900000"
time34=STRING|"10200000"
time35=STRING|"10500000"
time36=STRING|"10800000"
time37=STRING|"11100000"
time38=STRING|"11400000"
time39=STRING|"11700000"
time4=STRING|"1200000"
time40=STRING|"12000000"
time41=STRING|"12300000"
time42=STRING|"12600000"
time43=STRING|"12900000"
time44=STRING|"13200000"
time45=STRING|"13500000"
time46=STRING|"13800000"
time47=STRING|"14100000"
time48=STRING|"14400000"
time49=STRING|"14700000"
time5=STRING|"1500000"
time50=STRING|"15000000"
time51=STRING|"15300000"
time52=STRING|"15600000"
time53=STRING|"15900000"
time54=STRING|"16200000"
time55=STRING|"16500000"
time56=STRING|"16800000"
time57=STRING|"17100000"
time58=STRING|"17400000"
time59=STRING|"17700000"
time6=STRING|"1800000"
time60=STRING|"18000000"
time61=STRING|"18300000"
time62=STRING|"18600000"
time63=STRING|"18900000"
time64=STRING|"19200000"
time65=STRING|"19500000"
time66=STRING|"19800000"
time67=STRING|"20100000"
time68=STRING|"20400000"
time69=STRING|"20700000"
time7=STRING|"2100000"
time70=STRING|"21000000"
time71=STRING|"21300000"
time72=STRING|"21600000"
time73=STRING|"21900000"
time74=STRING|"22200000"
time75=STRING|"22500000"
time76=STRING|"22800000"
time77=STRING|"23100000"
time78=STRING|"23400000"
time79=STRING|"23700000"
time8=STRING|"2400000"
time80=STRING|"24000000"
time81=STRING|"24300000"
time82=STRING|"24600000"
time83=STRING|"24900000"
time84=STRING|"25200000"
time85=STRING|"25500000"
time86=STRING|"25800000"
time87=STRING|"26100000"
time88=STRING|"26400000"
time89=STRING|"26700000"
time9=STRING|"2700000"
time90=STRING|"27000000"
time91=STRING|"27300000"
time92=STRING|"27600000"
time93=STRING|"27900000"
time94=STRING|"28200000"
time95=STRING|"28500000"
time96=STRING|"28800000"
time97=STRING|"29100000"
time98=STRING|"29400000"
time99=STRING|"29700000"
timecount=STRING|"288"
wakeup=STRING|"0"