Hi,
I'm evaluating RoboTask to see if can achieve the following
task for our small business.
1. Log onto FTP server daily, and download the latest
version of a tab delimited .txt file to our local File
Server. (I've got this part working fine in RoboTask).
2. Upon detection of the new file being downloaded onto the
local server, RoboTask should then take all the data in the
Tab Delimited File, and append each row to an existing table
MS Access database.
Basically, the .txt file contains the orders on our
website for the previous day. The MS Access table then
stores the entire set of orders for the full year, being
appended to each day with the latest set of data.
Is it possible for RoboTask to automate the above? If so,
I'd be extremely grateful for DETAILED instructions from
someone kind enough to do so. From what I've read, I guess I
need to do something with SQL Query functions in RoboTask,
which is where I freeze, as I'm not into SQL at all.
(ps - I've only just started to use RoboTask, so again, dont
assume I know how to do simple things at this stage, I'm
still getting to grips with the interface and so on).
Many thanks in anticipation,
Jon
Append data to existing table in Access
Append data to existing table in Access
You should process the file sequential line by line in the loop.
Algorithm is simple:
Text Loop // and save current Line to variable CurrentLine
SQL Query
End Loop
The query text will be like this:
insert into MyTable (Field1, field2, Field3) values ({CurrentLine(0)},{CurrentLine(1)}, {CurrentLine(2)})
If each line of the text is standard comma-separated string you can use it as array.
See more detail here
Also you can correct the line if it's necessary (see Delimit variable action)
the whole task will be such
FTP Logon
FTP Download
Text Loop
SQL Query
End Loop
Algorithm is simple:
Text Loop // and save current Line to variable CurrentLine
SQL Query
End Loop
The query text will be like this:
insert into MyTable (Field1, field2, Field3) values ({CurrentLine(0)},{CurrentLine(1)}, {CurrentLine(2)})
If each line of the text is standard comma-separated string you can use it as array.
See more detail here
Also you can correct the line if it's necessary (see Delimit variable action)
the whole task will be such
FTP Logon
FTP Download
Text Loop
SQL Query
End Loop
Append data to existing table in Access
Thanks very much for the above.
This got me 90% of the way there, but, I'm experiencing a
final error which is preventing me from successfully
completing the automation.
Firstly, to be clear, I should say that to get the above
working I found I had to wrap the expression
{CurrentLine(0)} in quotes, such that it read like this -
insert into Table1 values ('{CurrentLine(0)}', '{CurrentLine(1)}', '{CurrentLine(2)}')
This works fine when I use a test table with 3
columns which are set to 'text' datatype format in the
table structure.
However, when I use it on my production data, which has
97 columns in a mix of 'text', 'date/time', and 'number'
data-types... I get the following error.
E: 08/02/2011 16:48:05: Error has occured: [Microsoft]
[ODBC Microsoft Access Driver] Data type mismatch in
criteria expression
I'm guessing therefore the the wrapping my values in ' ' marks, sets them to 'text' strings.
Thus trying to write a text string to a field who's datatype is set to Number in the table
structure, results in a datatype mismatch error? Given this, I thought the obvious answer
would be to remove the ' ' marks from the values which are set to Number in the table
structure. This would give me something as follows in my test table;
insert into Table1 values ('{CurrentLine(0)}', '{CurrentLine(1)}', {CurrentLine(2)})
(Where the 3rd and final column of the Table has been set to a datatype of Number)
Sadly this didnt work, I then got a different error...
E: 09/02/2011 11:35:21: Error has occured: [Microsoft][ODBC Microsoft Access Driver] Too few
parameters. Expected 1
Can anyone please offer up the final piece of advice that will help me to finish this project?
Cheers, Jon.
This got me 90% of the way there, but, I'm experiencing a
final error which is preventing me from successfully
completing the automation.
Firstly, to be clear, I should say that to get the above
working I found I had to wrap the expression
{CurrentLine(0)} in quotes, such that it read like this -
insert into Table1 values ('{CurrentLine(0)}', '{CurrentLine(1)}', '{CurrentLine(2)}')
This works fine when I use a test table with 3
columns which are set to 'text' datatype format in the
table structure.
However, when I use it on my production data, which has
97 columns in a mix of 'text', 'date/time', and 'number'
data-types... I get the following error.
E: 08/02/2011 16:48:05: Error has occured: [Microsoft]
[ODBC Microsoft Access Driver] Data type mismatch in
criteria expression
I'm guessing therefore the the wrapping my values in ' ' marks, sets them to 'text' strings.
Thus trying to write a text string to a field who's datatype is set to Number in the table
structure, results in a datatype mismatch error? Given this, I thought the obvious answer
would be to remove the ' ' marks from the values which are set to Number in the table
structure. This would give me something as follows in my test table;
insert into Table1 values ('{CurrentLine(0)}', '{CurrentLine(1)}', {CurrentLine(2)})
(Where the 3rd and final column of the Table has been set to a datatype of Number)
Sadly this didnt work, I then got a different error...
E: 09/02/2011 11:35:21: Error has occured: [Microsoft][ODBC Microsoft Access Driver] Too few
parameters. Expected 1
Can anyone please offer up the final piece of advice that will help me to finish this project?
Cheers, Jon.
Last edited by widget on Wed Feb 09, 2011 6:42 am, edited 1 time in total.
Append data to existing table in Access
Is anyone able to help with the above? I'm struggling to
find a solution to the data type mismatch which could mean
I'm unable to justify purchasing the full version of
Robotask, as it's looking like it won't be able to achieve
the goals of my automation which would be a real sham...
unless anyone can suggest what the problem is and how I can
fix it? Many thanks, Jon
find a solution to the data type mismatch which could mean
I'm unable to justify purchasing the full version of
Robotask, as it's looking like it won't be able to achieve
the goals of my automation which would be a real sham...
unless anyone can suggest what the problem is and how I can
fix it? Many thanks, Jon
Append data to existing table in Access
Can you send me the example of your comma-separated text (only 2-3 lines) to oleg (at) robotask . com?
Append data to existing table in Access
I made the test database with the table
Structure:
ID - autonumber
DateField - DateTime
Numfield - Number
StrField - Text(255)
Test comma-separated text is:
12.02.2011,"Simple text",211
23.02.2011,"Simple text 1",212
25.12.2011,"Mery Christmas",213
I used SQL expression:
insert into table1
(DateField , StrField, numfield)
values
("{c_line(0)}", "{c_line(1)}", {c_line(2)})
It worked without problem.
You can set datefield as string if the string corresponding to your Date-Time format
Whole text of the task below:
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|86893127
Comment=STRINGLIST
ContinueOnError=INTEGER|0
ExternalName=STRING|"Task1044"
Hide=INTEGER|0
ID=INTEGER|1422181772
LocalVariables=STRING|"c_line"
LogOnAsUser=INTEGER|1
Name=STRING|"SQL Query..."
OnErrorTaskID=INTEGER|0
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
WriteGeneralLog=INTEGER|0
[Actions]
Action1=FOLDER
Action2=FOLDER
Action3=FOLDER
[Actions\Action1]
ActionID=STRING|"A_LOOP_TEXT"
Enabled=INTEGER|-1
Name=STRING|"Text Loop"
Params=FOLDER
[Actions\Action1\Params]
destvar=STRING|"C_LINE"
file=STRING|"C:\incoming\commatext.txt"
line0=STRING|"12.02.2011,""Simple text"",211"
line1=STRING|"23.02.2011,""Simple text 1"",212"
line2=STRING|"25.12.2011,""Mery Christmas"",213 "
linecount=STRING|"3"
sourcetext=STRING|"1"
[Actions\Action2]
ActionID=STRING|"A_EXECSQL"
Enabled=INTEGER|-1
Name=STRING|"SQL Query"
Params=FOLDER
[Actions\Action2\Params]
assignvar=STRING|"0"
connectionstring=STRING|"Provider=Microsoft.Jet.OLEDB.4.0;Da ta Source=C:\incoming\test.mdb;Persist Security Info=False"
override=STRING|"0"
password=STRING|"2210421130213912113223179"
sql=STRING|"""insert into table1 "",""(DateField , StrField, numfield)"",values,""(""""{c_line(0)}"""", """"{c_line(1)}"""", {c_line(2)})"""
timeout=STRING|"60"
[Actions\Action3]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"End Loop"
Structure:
ID - autonumber
DateField - DateTime
Numfield - Number
StrField - Text(255)
Test comma-separated text is:
12.02.2011,"Simple text",211
23.02.2011,"Simple text 1",212
25.12.2011,"Mery Christmas",213
I used SQL expression:
insert into table1
(DateField , StrField, numfield)
values
("{c_line(0)}", "{c_line(1)}", {c_line(2)})
It worked without problem.
You can set datefield as string if the string corresponding to your Date-Time format
Whole text of the task below:
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|86893127
Comment=STRINGLIST
ContinueOnError=INTEGER|0
ExternalName=STRING|"Task1044"
Hide=INTEGER|0
ID=INTEGER|1422181772
LocalVariables=STRING|"c_line"
LogOnAsUser=INTEGER|1
Name=STRING|"SQL Query..."
OnErrorTaskID=INTEGER|0
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
WriteGeneralLog=INTEGER|0
[Actions]
Action1=FOLDER
Action2=FOLDER
Action3=FOLDER
[Actions\Action1]
ActionID=STRING|"A_LOOP_TEXT"
Enabled=INTEGER|-1
Name=STRING|"Text Loop"
Params=FOLDER
[Actions\Action1\Params]
destvar=STRING|"C_LINE"
file=STRING|"C:\incoming\commatext.txt"
line0=STRING|"12.02.2011,""Simple text"",211"
line1=STRING|"23.02.2011,""Simple text 1"",212"
line2=STRING|"25.12.2011,""Mery Christmas"",213 "
linecount=STRING|"3"
sourcetext=STRING|"1"
[Actions\Action2]
ActionID=STRING|"A_EXECSQL"
Enabled=INTEGER|-1
Name=STRING|"SQL Query"
Params=FOLDER
[Actions\Action2\Params]
assignvar=STRING|"0"
connectionstring=STRING|"Provider=Microsoft.Jet.OLEDB.4.0;Da ta Source=C:\incoming\test.mdb;Persist Security Info=False"
override=STRING|"0"
password=STRING|"2210421130213912113223179"
sql=STRING|"""insert into table1 "",""(DateField , StrField, numfield)"",values,""(""""{c_line(0)}"""", """"{c_line(1)}"""", {c_line(2)})"""
timeout=STRING|"60"
[Actions\Action3]
ActionID=STRING|"A_LOOP_END"
Enabled=INTEGER|-1
Name=STRING|"End Loop"
Append data to existing table in Access
For the purpose of anyone following this thread, the two
things that were needed here were;
1. Routine to ignore the first line in the txt database
(which were the headers, and as such were reading in
characters instead of numbers in the number fields of
table)
2. Ensure my tabbed file was the proper format (not just
something knocked up in Notepad, but a proper excel "save
as".
This all appears to be working now. Thanks to Oleg for
the guidance.
things that were needed here were;
1. Routine to ignore the first line in the txt database
(which were the headers, and as such were reading in
characters instead of numbers in the number fields of
table)
2. Ensure my tabbed file was the proper format (not just
something knocked up in Notepad, but a proper excel "save
as".
This all appears to be working now. Thanks to Oleg for
the guidance.