RoboTask forum

Posted: **Tue Dec 16, 2014 11:15 am**

I have receive a csv file (ftp download every 30 minutes) that contains temperature reading at 15 minute intervals (the first 2 lines and the last lines are blank). The reading are for the last 7 days but I only need to load any readings I don't already have, in to the database table. The reason for 7 days is if there are any issues at my end I have 7 days to resolve them before I start missing data. So the file that is about 680 lines long I normally will only need the last 2 temperature readings. Details on the csv and database are below.

I know how to retrieve the file every 30 minutes. But don't know how to parse the file and select only the required records and insert them into the table.
My thoughts are to retrieve that last record in the database table to a variable and then start parsing the csv file until I get to that record and then do only action the lines after it (now sure if this is the best way). What other options if any and could you write a task to demonstrate how it would work.

CSV file: acacia_bay_temperature.csv
The first field: "ACACIA_WT" needs to be discarded as I don't need it.
The second field: is the date of the reading in yyyymmdd format and needs to be combined with the time field (below) and converted to a datetime for loading in the database.
The third field: is the time of the reading in HHMM format and needs to be combined with the date field (above) and converted to a datetime for loading in the database.
The forth field: is the temperature and needs to be stored as a decimal in the database.

Database Table: temperatures
First field: TempDateTime
Second Field: TempReading

csv file sample
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++not part of file just to show the beginning

ACACIA_WT,20141209,1435,15.9
ACACIA_WT,20141209,1450,15.7
ACACIA_WT,20141209,1505,15.7
ACACIA_WT,20141209,1520,15.7
ACACIA_WT,20141209,1535,16.3
ACACIA_WT,20141209,1550,15.8
ACACIA_WT,20141209,1605,16.2
ACACIA_WT,20141209,1620,15.9
ACACIA_WT,20141209,1635,15.8
ACACIA_WT,20141209,1650,16.0
ACACIA_WT,20141209,1705,16.0
ACACIA_WT,20141209,1720,16.1
ACACIA_WT,20141209,1735,16.3
ACACIA_WT,20141209,1750,16.5
ACACIA_WT,20141209,1805,16.5
ACACIA_WT,20141209,1820,16.5
ACACIA_WT,20141209,1835,16.5
ACACIA_WT,20141209,1850,16.5

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++not part of file just to show the end

Posted: **Tue Dec 16, 2014 2:14 pm**

See my example below. I optimized it for MySQL database.
The simplest way to use javascript.
I wrote the simple function MakeDT. It calculates date-time value for SQL expression. See the step #3

Code: Select all

function MakeDT(datestr, timestr)
{
  dt = datestr.slice(0,4) + "-" + datestr.slice(4,6) + "-" + datestr.slice(6);
  dt = dt + " " + timestr.slice(0,2) + ":" + timestr.slice(2);

  return dt;
}

The full task:

Code: Select all

 
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|317955339
Comment=STRINGLIST
ContinueOnError=INTEGER|0
ExternalName=STRING|"Task1166"
Hide=INTEGER|0
ID=INTEGER|1085110895
LogOnAsUser=INTEGER|1
Name=STRING|"Temperatures (parse CSV and put into DB)"
OnErrorTaskID=INTEGER|-1
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
WriteGeneralLog=INTEGER|0

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

[Actions\Action1]
ActionID=STRING|"A_VARIABLES_CREATE"
Enabled=INTEGER|-1
Name=STRING|"Create local variable ""DT"" with value """""
Params=FOLDER

[Actions\Action1\Params]
expand=STRING|"0"
varkind=STRING|"1"
varname=STRING|"DT"

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

[Actions\Action2\Params]
destvar=STRING|"LINE"
line0=STRING|"ACACIA_WT,20141209,1435,15.9"
line1=STRING|"ACACIA_WT,20141209,1450,15.7"
line10=STRING|"ACACIA_WT,20141209,1705,16.0"
line11=STRING|"ACACIA_WT,20141209,1720,16.1"
line12=STRING|"ACACIA_WT,20141209,1735,16.3"
line13=STRING|"ACACIA_WT,20141209,1750,16.5"
line14=STRING|"ACACIA_WT,20141209,1805,16.5"
line15=STRING|"ACACIA_WT,20141209,1820,16.5"
line16=STRING|"ACACIA_WT,20141209,1835,16.5"
line17=STRING|"ACACIA_WT,20141209,1850,16.5"
line2=STRING|"ACACIA_WT,20141209,1505,15.7"
line3=STRING|"ACACIA_WT,20141209,1520,15.7"
line4=STRING|"ACACIA_WT,20141209,1535,16.3"
line5=STRING|"ACACIA_WT,20141209,1550,15.8"
line6=STRING|"ACACIA_WT,20141209,1605,16.2"
line7=STRING|"ACACIA_WT,20141209,1620,15.9"
line8=STRING|"ACACIA_WT,20141209,1635,15.8"
line9=STRING|"ACACIA_WT,20141209,1650,16.0"
linecount=STRING|"18"
sourcetext=STRING|"1"

[Actions\Action3]
ActionID=STRING|"A_SCRIPT_JSEVALUATE"
Enabled=INTEGER|-1
Name=STRING|"JS Evaluate"
Params=FOLDER

[Actions\Action3\Params]
expression=STRING|"MakeDT(""{Line(1)}"", ""{Line(2)}"")"
line00000000=STRING|"function MakeDT(datestr, timestr)"
line00000001=STRING|"{"
line00000002=STRING|"  dt = datestr.slice(0,4) + ""-"" + datestr.slice(4,6) + ""-"" + datestr.slice(6);"
line00000003=STRING|"  dt = dt + "" "" + timestr.slice(0,2) + "":"" + timestr.slice(2);"
line00000005=STRING|"  return dt;"
line00000006=STRING|"}"
line00000008=STRING|"//LogMessage(MakeDT(""20141216"", ""1536""));"
linecount=STRING|"9"
loadfromfile=STRING|"1"
variable=STRING|"dt"

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

[Actions\Action4\Params]
assignvar=STRING|"0"
connectionstring=STRING|"Provider=MSDASQL.1;Persist Security Info=False;Data Source=MySQLTest"
override=STRING|"0"
password=STRING|"2005622153201102011111153"
sql=STRING|"""insert into temperatures (TempDateTime, TempReading)"",""values ('{dt}', {line(3)})"""
timeout=STRING|"60"

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

If it's necessary I can make the task without JS Evaluate action. But it will be more complex.

Posted: **Tue Dec 16, 2014 7:59 pm**

awesome, thanks for such a quick response. I'll give it a go later today.

RoboTask forum

parse a csv file and insert or update SQL database table

parse a csv file and insert or update SQL database table

Re: parse a csv file and insert or update SQL database table

Re: parse a csv file and insert or update SQL database table