Endless loop while processing CSV file

[sarnusch]

Hello,
I am having trouble with the following task:
At first a new column is added to an existing CSV file that should contain the article price. The new data is saved in a variable with 'CSV Save Data'.
Then I want to read the variable data with 'CSV Data Loop' and extract the article number and a date.
These two information are then used in a SQL query to get the price of the article at the extracted date.
So far the task works and I can show the price in the log.

Then I want to save the price to the new column in the variable with 'CSV Write Cells' and 'CSV Save Data' but the system seems to be in an endless loop when I use the column headers.
After deactivating the column headers I get an additional line between my two initial lines:

Screenshot 2024-10-30 125856.png (14.1 KiB) Viewed 2802 times

I can not figure out what I am doing wrong.

Task:

Code: Select all

;*****************************
;* RoboTask Task file
;* Do not edit in text editor!
;*****************************
 
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|0
CatID=INTEGER|1729260962
Comment=STRINGLIST
ContinueOnError=INTEGER|0
DisableOnError=INTEGER|0
DoNotStopWhenShutdown=INTEGER|0
Events=FOLDER
ExternalName=STRING|"Task154"
Hide=INTEGER|0
ID=INTEGER|1167695131
LogOnAsUser=INTEGER|1
Name=STRING|"Copy of toom Preisprotokoll"
OnErrorTaskID=INTEGER|0
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
StepPause=INTEGER|0
ToLog=INTEGER|3
UnicodeFormat=INTEGER|1
WriteGeneralLog=INTEGER|0

[Actions]
Action1=FOLDER
Action10=FOLDER
Action11=FOLDER
Action12=FOLDER
Action2=FOLDER
Action3=FOLDER
Action4=FOLDER
Action5=FOLDER
Action6=FOLDER
Action7=FOLDER
Action8=FOLDER
Action9=FOLDER

[Actions\Action1]
ActionID=STRING|"A_CSV_OPEN"
Enabled=INTEGER|-1
Name=STRING|"CSV Open Data"
Params=FOLDER

[Actions\Action1\Params]
commentchars=STRING|"#"
delimiter=STRING|"2"
encoding=STRING|"0"
filename=STRING|"D:\fliesenmax\Protokolle\test2.csv"
headerline=STRING|"0"
linecount=STRING|"0"
quotechar=STRING|"0"
skipcomments=STRING|"0"
skipempty=STRING|"1"
skipfirst=STRING|"0"
source=STRING|"1"
strictdelimiter=STRING|"1"

[Actions\Action10]
ActionID=STRING|"A_CSV_SAVE"
Enabled=INTEGER|-1
Name=STRING|"CSV Save Data"
Params=FOLDER

[Actions\Action10\Params]
_rt_variables_produced=STRING|"CSVDATA"
delimiter=STRING|"2"
notchangeformat=STRING|"1"
quotechar=STRING|"0"
savetofile=STRING|"1"
savetovariable=STRING|"1"
savevariable=STRING|"CSVDATA"
strictdelimiter=STRING|"1"
targetencoding=STRING|"0"
targetfilename=STRING|"D:\fliesenmax\Protokolle\toom_preisprotkoll_neu.csv"

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

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

[Actions\Action2]
ActionID=STRING|"A_CSV_ADDCOLUMN"
Enabled=INTEGER|-1
Name=STRING|"CSV Add Column"
Params=FOLDER

[Actions\Action2\Params]
commentchars=STRING|"#"
delimiter=STRING|"2"
encoding=STRING|"0"
fillmode=STRING|"0"
headerline=STRING|"0"
linecount=STRING|"0"
quotechar=STRING|"0"
skipcomments=STRING|"0"
skipempty=STRING|"1"
skipfirst=STRING|"0"
source=STRING|"0"
strictdelimiter=STRING|"1"
totheend=STRING|"1"

[Actions\Action3]
ActionID=STRING|"A_CSV_SAVE"
Enabled=INTEGER|-1
Name=STRING|"CSV Save Data"
Params=FOLDER

[Actions\Action3\Params]
_rt_variables_produced=STRING|"CSVDATA"
delimiter=STRING|"2"
notchangeformat=STRING|"1"
quotechar=STRING|"0"
savetofile=STRING|"0"
savetovariable=STRING|"1"
savevariable=STRING|"CSVDATA"
strictdelimiter=STRING|"1"
targetencoding=STRING|"0"
targetfilename=STRING|"D:\fliesenmax\Protokolle\toom_preisprotkoll_neu.csv"

[Actions\Action4]
ActionID=STRING|"A_CSV_LOOP"
Enabled=INTEGER|-1
Name=STRING|"CSV Data Loop"
Params=FOLDER

[Actions\Action4\Params]
_rt_variables_produced=STRING|"CURROW,ROW"
commentchars=STRING|"#"
conditionmode=STRING|"0"
conditionscount=STRING|"0"
delimiter=STRING|"2"
encoding=STRING|"0"
filename=STRING|"D:\fliesenmax\Protokolle\toom_preisprotkoll.csv"
Format=STRING|"0"
headerline=STRING|"0"
line00000000=STRING|"{CSVDATA}"
linecount=STRING|"1"
linevarname=STRING|"CURROW"
quotechar=STRING|"0"
rowvarname=STRING|"ROW"
skipcomments=STRING|"0"
skipempty=STRING|"1"
skipfirst=STRING|"0"
source=STRING|"2"
strictdelimiter=STRING|"1"

[Actions\Action5]
ActionID=STRING|"A_ROBOTASK_LOG"
Enabled=INTEGER|-1
Name=STRING|"Log Message"
Params=FOLDER

[Actions\Action5\Params]
message=STRING|"{ROW}"
type=STRING|"3"

[Actions\Action6]
ActionID=STRING|"A_ROBOTASK_LOG"
Enabled=INTEGER|-1
Name=STRING|"Log Message"
Params=FOLDER

[Actions\Action6\Params]
message=STRING|"{CURROW(8)}, {CURROW(1)}, {DateTimeToFormat({CURROW(33)},yyyymmdd)}"
type=STRING|"3"

[Actions\Action7]
ActionID=STRING|"A_LOOP_DATASET"
Enabled=INTEGER|-1
Name=STRING|"Dataset Loop"
Params=FOLDER

[Actions\Action7\Params]
_rt_variables_produced=STRING|"DEKPFIELDVALUE"
commatext=STRING|"0"
connectionstring=STRING|"Provider=SQLOLEDB.1;Password=Max2014;Persist Security Info=True;User ID=ViewUser;Initial Catalog=ACLogicDB;Data Source=FLM-SYNLOGIC"
override=STRING|"0"
paramcount=STRING|"2"
password=STRING|""
pname0=STRING|"param1"
pname1=STRING|"param2"
ptype0=STRING|"0"
ptype1=STRING|"0"
pvalue0=STRING|"{CURROW(8)}"
pvalue1=STRING|"{DateTimeToFormat({CURROW(33)},yyyymmdd)}"
sql=STRING|"""SELECT TOP 1 WERTNEU"",""FROM VIEW"",""WHERE ARTIKEL = :param1 AND CONVERT(VARCHAR,DATUM,112) <= :param2"",""ORDER BY DATUM DESC"""
timeout=STRING|"60"
var2=STRING|"DEKPFIELDVALUE"
withmemo=STRING|"1"

[Actions\Action8]
ActionID=STRING|"A_ROBOTASK_LOG"
Enabled=INTEGER|-1
Name=STRING|"Log Message"
Params=FOLDER

[Actions\Action8\Params]
message=STRING|"{DEKPFIELDVALUE(0)}"
type=STRING|"3"

[Actions\Action9]
ActionID=STRING|"A_CSV_WRITECELLS"
Enabled=INTEGER|-1
Name=STRING|"CSV Write Cells"
Params=FOLDER

[Actions\Action9\Params]
cellcount=STRING|"1"
col0=STRING|"34"
commentchars=STRING|"#"
delimiter=STRING|"2"
encoding=STRING|"0"
headerline=STRING|"0"
line00000000=STRING|"{CSVDATA}"
linecount=STRING|"1"
quotechar=STRING|"0"
row0=STRING|"{ROW}"
skipcomments=STRING|"0"
skipempty=STRING|"1"
skipfirst=STRING|"0"
source=STRING|"2"
strictdelimiter=STRING|"1"
val0=STRING|"{DEKPFIELDVALUE(0)}"

[Events]
Event1=FOLDER

[Events\Event1]
Enabled=INTEGER|-1
EventID=STRING|"E_GENERAL_SHEDULER"
Name=STRING|"Scheduler(25.10.2024 19:30:00)"
Params=FOLDER
UniqueID=INTEGER|-1782231192

[Events\Event1\Params]
periodicity=STRING|"0"
startdate=STRING|"45590"
time0=STRING|"70200000"
timecount=STRING|"1"
wakeup=STRING|"0"

[Oleg]

Look at my variant (below)
Problems:
1. You opened CSV file and work with these data
2. All rest CSV actions must use source "From previous CSV action": CSV add column, CSV data loop, CSV write cells, CSV save data. That is you make some changes to CSV data sequentially in the same data array and save it at the end.
3. Thus the step #3 is unnecessary and even harmful
4. How many rows does your SQL return? I think than you need only 1-st row. So I put Break into dataset loop. You can put only one value into one cell.
5. Move CSV save data to the end of task. Save to file only.

Code: Select all

;*****************************
;* RoboTask Task file
;* Do not edit in text editor!
;*****************************
 
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|0
CatID=INTEGER|601183744
Comment=STRINGLIST
ContinueOnError=INTEGER|0
DisableOnError=INTEGER|0
DoNotStopWhenShutdown=INTEGER|0
Events=FOLDER
ExternalName=STRING|"Task1543"
Hide=INTEGER|0
ID=INTEGER|-419888155
LogOnAsUser=INTEGER|1
Name=STRING|"Copy of toom Preisprotokoll (Version 2)"
OnErrorTaskID=INTEGER|0
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
StepPause=INTEGER|0
ToLog=INTEGER|3
UnicodeFormat=INTEGER|1
WriteGeneralLog=INTEGER|0

[Actions]
Action1=FOLDER
Action10=FOLDER
Action11=FOLDER
Action12=FOLDER
Action13=FOLDER
Action2=FOLDER
Action3=FOLDER
Action4=FOLDER
Action5=FOLDER
Action6=FOLDER
Action7=FOLDER
Action8=FOLDER
Action9=FOLDER

[Actions\Action1]
ActionID=STRING|"A_CSV_OPEN"
Enabled=INTEGER|-1
Name=STRING|"CSV Open Data"
Params=FOLDER

[Actions\Action1\Params]
commentchars=STRING|"#"
delimiter=STRING|"2"
encoding=STRING|"0"
filename=STRING|"D:\fliesenmax\Protokolle\test2.csv"
headerline=STRING|"0"
linecount=STRING|"0"
quotechar=STRING|"0"
skipcomments=STRING|"0"
skipempty=STRING|"1"
skipfirst=STRING|"0"
source=STRING|"1"
strictdelimiter=STRING|"1"

[Actions\Action10]
ActionID=STRING|"A_LOOP_BREAK"
Enabled=INTEGER|-1
Name=STRING|"Break"

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

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

[Actions\Action13]
ActionID=STRING|"A_CSV_SAVE"
Enabled=INTEGER|-1
Name=STRING|"CSV Save Data"
Params=FOLDER

[Actions\Action13\Params]
delimiter=STRING|"2"
notchangeformat=STRING|"1"
quotechar=STRING|"0"
savetofile=STRING|"1"
savetovariable=STRING|"0"
savevariable=STRING|"CSVDATA"
strictdelimiter=STRING|"1"
targetencoding=STRING|"0"
targetfilename=STRING|"D:\fliesenmax\Protokolle\toom_preisprotkoll_neu.csv"

[Actions\Action2]
ActionID=STRING|"A_CSV_ADDCOLUMN"
Enabled=INTEGER|-1
Name=STRING|"CSV Add Column"
Params=FOLDER

[Actions\Action2\Params]
columnname=STRING|"New field"
commentchars=STRING|"#"
delimiter=STRING|"2"
encoding=STRING|"0"
fillmode=STRING|"0"
headerline=STRING|"0"
linecount=STRING|"0"
quotechar=STRING|"0"
skipcomments=STRING|"0"
skipempty=STRING|"1"
skipfirst=STRING|"0"
source=STRING|"0"
strictdelimiter=STRING|"1"
totheend=STRING|"1"

[Actions\Action3]
ActionID=STRING|"A_CSV_SAVE"
Enabled=INTEGER|0
Name=STRING|"CSV Save Data // this is unnecessary "
Params=FOLDER

[Actions\Action3\Params]
_rt_variables_produced=STRING|"CSVDATA"
delimiter=STRING|"2"
notchangeformat=STRING|"1"
quotechar=STRING|"0"
savetofile=STRING|"0"
savetovariable=STRING|"1"
savevariable=STRING|"CSVDATA"
strictdelimiter=STRING|"1"
targetencoding=STRING|"0"
targetfilename=STRING|"D:\fliesenmax\Protokolle\toom_preisprotkoll_neu.csv"

[Actions\Action4]
ActionID=STRING|"A_CSV_LOOP"
Enabled=INTEGER|-1
Name=STRING|"CSV Data Loop"
Params=FOLDER

[Actions\Action4\Params]
_rt_variables_produced=STRING|"CURROW,ROW"
commentchars=STRING|"#"
conditionmode=STRING|"0"
conditionscount=STRING|"0"
delimiter=STRING|"2"
encoding=STRING|"0"
filename=STRING|"D:\fliesenmax\Protokolle\toom_preisprotkoll.csv"
Format=STRING|"0"
headerline=STRING|"0"
line00000000=STRING|"{CSVDATA}"
linecount=STRING|"1"
linevarname=STRING|"CURROW"
quotechar=STRING|"0"
rowvarname=STRING|"ROW"
skipcomments=STRING|"0"
skipempty=STRING|"1"
skipfirst=STRING|"0"
source=STRING|"0"
strictdelimiter=STRING|"1"

[Actions\Action5]
ActionID=STRING|"A_ROBOTASK_LOG"
Enabled=INTEGER|-1
Name=STRING|"Log Message"
Params=FOLDER

[Actions\Action5\Params]
message=STRING|"{ROW}"
type=STRING|"3"

[Actions\Action6]
ActionID=STRING|"A_ROBOTASK_LOG"
Enabled=INTEGER|-1
Name=STRING|"Log Message"
Params=FOLDER

[Actions\Action6\Params]
message=STRING|"{CURROW(8)}, {CURROW(1)}, {DateTimeToFormat({CURROW(33)},yyyymmdd)}"
type=STRING|"3"

[Actions\Action7]
ActionID=STRING|"A_LOOP_DATASET"
Enabled=INTEGER|-1
Name=STRING|"Dataset Loop"
Params=FOLDER

[Actions\Action7\Params]
_rt_variables_produced=STRING|"DEKPFIELDVALUE"
commatext=STRING|"0"
connectionstring=STRING|"Provider=SQLOLEDB.1;Password=Max2014;Persist Security Info=True;User ID=ViewUser;Initial Catalog=ACLogicDB;Data Source=FLM-SYNLOGIC"
override=STRING|"0"
paramcount=STRING|"2"
pname0=STRING|"param1"
pname1=STRING|"param2"
ptype0=STRING|"0"
ptype1=STRING|"0"
pvalue0=STRING|"{CURROW(8)}"
pvalue1=STRING|"{DateTimeToFormat({CURROW(33)},yyyymmdd)}"
sql=STRING|"""SELECT TOP 1 WERTNEU"",""FROM VIEW"",""WHERE ARTIKEL = :param1 AND CONVERT(VARCHAR,DATUM,112) <= :param2"",""ORDER BY DATUM DESC"""
timeout=STRING|"60"
var2=STRING|"DEKPFIELDVALUE"
withmemo=STRING|"1"

[Actions\Action8]
ActionID=STRING|"A_ROBOTASK_LOG"
Enabled=INTEGER|-1
Name=STRING|"Log Message"
Params=FOLDER

[Actions\Action8\Params]
message=STRING|"{DEKPFIELDVALUE(0)}"
type=STRING|"3"

[Actions\Action9]
ActionID=STRING|"A_CSV_WRITECELLS"
Enabled=INTEGER|-1
Name=STRING|"CSV Write Cells"
Params=FOLDER

[Actions\Action9\Params]
cellcount=STRING|"1"
col0=STRING|"34"
commentchars=STRING|"#"
delimiter=STRING|"2"
encoding=STRING|"0"
headerline=STRING|"0"
line00000000=STRING|"{CSVDATA}"
linecount=STRING|"1"
quotechar=STRING|"0"
row0=STRING|"{ROW}"
skipcomments=STRING|"0"
skipempty=STRING|"1"
skipfirst=STRING|"0"
source=STRING|"0"
strictdelimiter=STRING|"1"
val0=STRING|"{DEKPFIELDVALUE(0)}"

[Events]
Event1=FOLDER

[Events\Event1]
Enabled=INTEGER|-1
EventID=STRING|"E_GENERAL_SHEDULER"
Name=STRING|"Scheduler(25.10.2024 19:30:00)"
Params=FOLDER
UniqueID=INTEGER|-1932228453

[Events\Event1\Params]
periodicity=STRING|"0"
startdate=STRING|"45590"
time0=STRING|"70200000"
timecount=STRING|"1"
wakeup=STRING|"0"

[sarnusch]

Hello Oleg,
thanks for your reply.
I was not aware that 'CSV Add Column' saves the action automatically.

I tested your script.
The prices are now inserted correctly in the new column but I still have the problem that the program inserts an extra line after each line:

Source:

Screenshot 2024-10-31 085836.png (11.94 KiB) Viewed 2706 times

Result:

Screenshot 2024-10-31 085853.png (12.7 KiB) Viewed 2706 times

The SQL returns just one row with one field. And I used the 'Break' action.
What can cause the extra line?

[Oleg]

Send the result file as-it-is (not screenshot)
And the complete log of the task

[Oleg]

Additionally
I think than you need to do CSV Write Cells to column number 35 (not 34)
Column and row numbers start from 1 (not from 0). It described in documentation

[sarnusch]

Uuh, right it is column number 35
The additional line still appears even with using column 35.

But I think I found the cause of the additional line.
The field in the databas is a value with comma like '7,74'.
The Dataset Loop was run twice perhaps because it seperated the value 7,74 to 7 and 74.
When I cast the value as for example varchar the additional line disappears.
So the problem is solved.