Import XML or CSV to SQL

Discuss RoboTask here
Post Reply
deesloop
Posts: 70
Joined: Wed Feb 25, 2009 3:44 am

Import XML or CSV to SQL

Post by deesloop »

Basically I get a CSV file sent in via ftp - basically 2 rows (A header and the data)
It's named as Audited-DOCREF or Closed-DOCREF
What I want to do it update SQL database record DOCREF with the data

So it logs into sql and does a count of records number DOCREF
If it find them then it deletes that recor

It should then add the contents of row 2 frm the CSV into the database

This would either create new records or replace existing.

I have tried using bulk insert

BULK INSERT mcls015
FROM {FILELIST}
GO

But that doesn't work.
How can I read a CSV file and pus it into SQL?
The field order of the excel file will match the database.

Thanks
Oleg
Site Admin
Posts: 3000
Joined: Thu Jan 01, 1970 1:00 am
Contact:

Re: Import XML or CSV to SQL

Post by Oleg »

SQL syntax depends on your database.
I think that you should use standard algorithm and add row-by-row

SQL like this:

Code: Select all

insert into <tbl_name>
(col_name1 , col_name2,  ... col_nameN)
values (value1, value2, ... valueN)
The algorithm of the task like this:

Code: Select all

text loop // on your CSV file
    SQL Query // with SQL above 
end loop
Oleg Yershov
deesloop
Posts: 70
Joined: Wed Feb 25, 2009 3:44 am

Re: Import XML or CSV to SQL

Post by deesloop »

Yes that's exactly what I'm looking at. Thanks.

However I've an issue saying that the file cannot be opened when trying to extract the values for ASQL upload
The file does exists and the filename assigned to filelist seems to match.
That variable is then used later to delete the CSV file so it can't be that.

Error is

E: 07/06/2018 09:44:12: Cannot open file "C:\FTPInkwrx\MCLS015\Audited-6300595.csv
E: 07/06/2018 09:44:12: ". The filename, directory name, or volume label syntax is incorrect

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
Action2=FOLDER
Action3=FOLDER
Action4=FOLDER
Action5=FOLDER
Action6=FOLDER
Action7=FOLDER
Action8=FOLDER
Action9=FOLDER

[Actions\Action1]
ActionID=STRING|"A_DB_OPEN"
Enabled=INTEGER|-1
Name=STRING|"DB Open"
Params=FOLDER

[Actions\Action1\Params]
connectionstring=STRING|"Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=TabletForms;Data Source=SQLBOX"
override=STRING|"1"
password=STRING|"2801316022272863579116858"
userid=STRING|"RecordsAdmin"

[Actions\Action10]
ActionID=STRING|"A_DB_CLOSE"
Enabled=INTEGER|-1
Name=STRING|"DB Close"

[Actions\Action11]
ActionID=STRING|"A_FILE_DELETE"
Enabled=INTEGER|-1
Name=STRING|"Delete File"
Params=FOLDER

[Actions\Action11\Params]
Count=STRING|"1"
deletedirs=STRING|"0"
file0=STRING|"{FILELIST}"
hidden=STRING|"0"
subdir=STRING|"0"
system=STRING|"0"
ToRecycleBin=STRING|"0"

[Actions\Action2]
ActionID=STRING|"A_STR_EXTRACT"
Enabled=INTEGER|-1
Name=STRING|"Get Document ID"
Params=FOLDER

[Actions\Action2\Params]
amount=STRING|"7"
from=STRING|"29"
source=STRING|"{FILELIST}"
variable=STRING|"DOCID"

[Actions\Action3]
ActionID=STRING|"A_DB_EXECSQL"
Enabled=INTEGER|-1
Name=STRING|"Check database for existing"
Params=FOLDER

[Actions\Action3\Params]
assignvar=STRING|"1"
paramcount=STRING|"0"
sql=STRING|"""SELECT [DocRef]"",""  FROM [TabletForms].[dbo].[MCLS015]"",""  where docref={DOCID}"""
timeout=STRING|"60"
varname=STRING|"EXISTS"

[Actions\Action4]
ActionID=STRING|"A_FLOW_IF"
Enabled=INTEGER|-1
Name=STRING|"If record exists"
Params=FOLDER

[Actions\Action4\Params]
case=STRING|"0"
connection=STRING|"0"
count=STRING|"1"
operator=STRING|"1"
type=STRING|"0"
value1=STRING|"EXISTS"
value2=STRING|"0"

[Actions\Action5]
ActionID=STRING|"A_DB_EXECSQL"
Enabled=INTEGER|-1
Name=STRING|"Delete record"
Params=FOLDER

[Actions\Action5\Params]
assignvar=STRING|"0"
paramcount=STRING|"0"
sql=STRING|"""DELETE FROM [TabletForms].[dbo].[MCLS015]"",""where docref={DOCID};"""
timeout=STRING|"60"
varname=STRING|"EXISTS"

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

[Actions\Action7]
ActionID=STRING|"A_LOOP_TEXT"
Enabled=INTEGER|-1
Name=STRING|"Open CSV"
Params=FOLDER

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

[Actions\Action8]
ActionID=STRING|"A_DB_EXECSQL"
Enabled=INTEGER|-1
Name=STRING|"Insert in SQL"
Params=FOLDER

[Actions\Action8\Params]
assignvar=STRING|"0"
paramcount=STRING|"0"
sql=STRING|"""INSERT INTO TabletForms].[dbo].[MCLS015]"","" [DocRef]"",""      ,[ContractID]"",""      ,[ContractName]"",""      ,[Location]"",""      ,[AuditDate]"",""      ,[AuditTime]"",""      ,[Auditor1]"",""      ,[AuditPosition1]"",""      ,[Auditor2]"",""      ,[AuditPosition2]"",""      ,[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)}','{Line(4)}','{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\Action9]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"Close CSV"

[Events]
Event1=FOLDER

[Events\Event1]
Enabled=INTEGER|-1
EventID=STRING|"E_FILE_MONITOR"
Params=FOLDER
UniqueID=INTEGER|-918216666

[Events\Event1\Params]
chg=STRING|"1"
chga=STRING|"1"
chgsl=STRING|"1"
chgsm=STRING|"1"
chgtl=STRING|"1"
chgtm=STRING|"1"
del=STRING|"0"
folder1=STRING|"C:\FTPInkwrx\MCLS015"
foldercount=STRING|"1"
incmask=STRING|"*.csv"
interval=STRING|"5"
listmode=STRING|"2"
mon_files=STRING|"1"
mon_folders=STRING|"0"
new=STRING|"1"
newlistparameter=STRING|"FILELIST"
pass=STRING|"1"
passmode=STRING|"2"
required=STRING|"1"
saveresults=STRING|"0"
subfolders1=STRING|"0"
Oleg
Site Admin
Posts: 3000
Joined: Thu Jan 01, 1970 1:00 am
Contact:

Re: Import XML or CSV to SQL

Post by Oleg »

Hm-m-m...

1. Step #3. SQL query SELECT you should use DB Loop
some databases returns always 0 as Affected rows for Select query
2. Variable (parameter) FileList contains the list of files as text: one file name per line. File monitor returns the list of files. No matter one file or many.
This text contains at least one END-OF-LINE combination. So you have to use Text Loop action to process this file
I think that this is the cause.
Oleg Yershov
deesloop
Posts: 70
Joined: Wed Feb 25, 2009 3:44 am

Re: Import XML or CSV to SQL

Post by deesloop »

OK, Point 1 I'll look at later

Point 2.
I've added a textloop to process as a filelist
Each entry in file list should be assigned to CSVfile vairable

It seems to work but I'm having issues converting the csv file to date & time usage.Getting closer though ;)
Oleg
Site Admin
Posts: 3000
Joined: Thu Jan 01, 1970 1:00 am
Contact:

Re: Import XML or CSV to SQL

Post by Oleg »

... but I'm having issues converting the csv file to date & time....
It depends on you database kind, your system date-time format and your CSV file.

I recommend you to use parameters in your SQL. There are many problems with date-time values if you use SQL text explicitly.
Oleg Yershov
deesloop
Posts: 70
Joined: Wed Feb 25, 2009 3:44 am

Re: Import XML or CSV to SQL

Post by deesloop »

I'm in discussions with the 3rd party that produces the CSV
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

*Edit*
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

*Edit2*

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
But it appears that spaces cause issues I nCSV file - is that the case?
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.
If this is the case how do I use only commas to delineate each field?

Thanks for your pateince

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"
Oleg
Site Admin
Posts: 3000
Joined: Thu Jan 01, 1970 1:00 am
Contact:

Re: Import XML or CSV to SQL

Post by Oleg »

You have to transform the line to standard comma-separated string if CSV file contains non-standard lines.
Use Delimit variable action to transform any string with delimiters to standard comma-separated string. Put in before step #10

The example of standard comma-separated string:

Code: Select all

string-without-spaces,"String with spaces",another_string_without_spaces,"string with ""spaces"" and quotes"
You can use spaces in elements but you have to put it between quotes. Delimit variable action makes all necessary transformations automatically.
Oleg Yershov
deesloop
Posts: 70
Joined: Wed Feb 25, 2009 3:44 am

Re: Import XML or CSV to SQL

Post by deesloop »

OK, each CSV file is now a single line of text.
The equivalent of a single row in excel. No headers nothing.
It opens fine in excel and in notepad and looks to me like a CSV file should

Here's the contents of one file.

6300595,2158,Tomatin,Top compound,2018-06-04,12:32:00,John Smith,Health and Safety,,good,Audited,0,,,0,,missing ear defenders,1,,,0,,,0,,,0,,,0,,,0,,not fully fenced,1,,,0,,,0,,,0,,,0,,,0,,,0,,,0,,damaged cords,1,,,0,,,0,,,0,,,0,,all pat tested om,0,,,0,,,0,,,0,,,0,,,0,,,0,,#TobeReviewedBy#,signed,#AgentSignature#

So each field is separated by a comma and that's it.
Not sure wyy spaces are causing an issue.

Anyway I ran the delimiter on the data and it now correctly recognises the fields.
Just need to figure out the date & time fields so that the sql insert works
Oleg
Site Admin
Posts: 3000
Joined: Thu Jan 01, 1970 1:00 am
Contact:

Re: Import XML or CSV to SQL

Post by Oleg »

Look at my example how to convert into standard comma separated string and transform date-time into your system date and time

Code: Select all

;*****************************
;* RoboTask Task file
;* Do not edit in text editor!
;*****************************
 
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|1360203151
Comment=STRINGLIST
ContinueOnError=INTEGER|0
DoNotStopWhenShutdown=INTEGER|0
ExternalName=STRING|"Task119"
Hide=INTEGER|0
ID=INTEGER|1741476377
LogOnAsUser=INTEGER|1
Name=STRING|"fix comma separated and convert date-time"
OnErrorTaskID=INTEGER|-1
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
UnicodeFormat=INTEGER|1
WriteGeneralLog=INTEGER|0

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

[Actions\Action1]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""LINE"" with value ""6300595,2158,Tomatin,Top compound,2018-06-04,12:32:00,John Smith,Health and Safety,,good,Audited,0,,,0,,missing ear defenders,1,,,0,,,0,,,0,,,0,,,0,,not fully f..."""
Params=FOLDER

[Actions\Action1\Params]
expand=STRING|"0"
varname=STRING|"LINE"
varvalue=STRING|"6300595,2158,Tomatin,Top compound,2018-06-04,12:32:00,John Smith,Health and Safety,,good,Audited,0,,,0,,missing ear defenders,1,,,0,,,0,,,0,,,0,,,0,,not fully fenced,1,,,0,,,0,,,0,,,0,,,0,,,0,,,0,,damaged cords,1,,,0,,,0,,,0,,,0,,all pat tested om,0,,,0,,,0,,,0,,,0,,,0,,,0,,#TobeReviewedBy#,signed,#AgentSignature#"

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

[Actions\Action2\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|"line1"

[Actions\Action3]
ActionID=STRING|"A_DIALOG_MESSAGE"
Enabled=INTEGER|-1
Name=STRING|"Show ""{Line}"""
Params=FOLDER

[Actions\Action3\Params]
icon=STRING|"1"
msg0=STRING|"{Line}"
msg1=STRING|"***************"
msg2=STRING|"{Line1}"
msgcount=STRING|"3"
playsound=STRING|"0"
showmessage=STRING|"1"

[Actions\Action4]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""DT_OLD"" with value ""{Line1(4)} {Line1(5)}"""
Params=FOLDER

[Actions\Action4\Params]
expand=STRING|"1"
varname=STRING|"DT_OLD"
varvalue=STRING|"{Line1(4)} {Line1(5)}"

[Actions\Action5]
ActionID=STRING|"A_VARIABLES_SET"
Enabled=INTEGER|-1
Name=STRING|"Set variable ""DT_SYSTEM"" with value ""{FormatToDateTime({dt_old},yyyy-mm-dd hh:nn:ss)}"""
Params=FOLDER

[Actions\Action5\Params]
expand=STRING|"1"
varname=STRING|"DT_SYSTEM"
varvalue=STRING|"{FormatToDateTime({dt_old},yyyy-mm-dd hh:nn:ss)}"

[Actions\Action6]
ActionID=STRING|"A_DIALOG_MESSAGE"
Enabled=INTEGER|-1
Name=STRING|"Show ""date-time in line: {dt_old}"""
Params=FOLDER

[Actions\Action6\Params]
icon=STRING|"1"
msg0=STRING|"date-time in line: {dt_old}"
msg1=STRING|"system date-time: {dt_system}"
msgcount=STRING|"2"
playsound=STRING|"0"
showmessage=STRING|"1"
Oleg Yershov
Post Reply