- Posts: 27
From CSV to EXCEL: to append the records
- Gennaro
- Topic Author
- Offline
- User
Less
More
14 years 1 month ago #2039
by Gennaro
From CSV to EXCEL: to append the records was created by Gennaro
I am using flowheater to import a CSV file into a Microsoft Excel file.
Because the Excel file already contains some records,
Is there a way to append the records in the Excel file instead of overwrite them?
Regards.
Because the Excel file already contains some records,
Is there a way to append the records in the Excel file instead of overwrite them?
Regards.
Please Log in or Create an account to join the conversation.
- FlowHeater-Team
- Offline
- Admin
14 years 1 month ago #2040
by FlowHeater-Team
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Replied by FlowHeater-Team on topic Re:From CSV to EXCEL: to append the records
by now the
flowheater excel adapter
doesn´t support this feature. but you could do this with the
.net script heater
and the following vb.net script code.
move one .net script heater into any current pipe/connection. double click on the heater to open the config dialog. switch the script language to vb and copy the script code above in the text area.
the script code opens the excel workbook (if exist) and search for the first empty row by comparing the first column. you can change this; see the comment in the script.
note: the script change the property rowfrom in the flowheater definition; see the screenshot marked in red. if you save the definition after execution by the execute and test window these are the next offset for searching the first free row.
i've made a simple example; please have a look to the attachment excel-append.zip.
Code:
dim firstcall as boolean = true
public function dowork() as object
' get the write adapter including excel properties
dim adapter as exceladapter
adapter = adapterwrite
if adapter.onlytest = true or firstcall = false or file.exists(adapter.database) = false then
' return the input value to the excel adapter
dowork = invalues(0).getvalue()
exit function
end if
firstcall = false
' create an excel object
dim excel as object
excel = createobject("excel.application")
' open excel workbook
excel.workbooks.open(adapter.database)
' get the defined excel worksheet
dim worksheet as object
worksheet = excel.activeworkbook.worksheets(adapter.worksheet)
' search for first free row
dim startrow as integer
startrow = adapter.rowfrom
' if this column is empty we have found our first row :-)
' you can change it e.g. 1 = column a, 2 = column b, ...
dim checkcolumn as integer
checkcolumn = 1
' search for first free
do while true
dim range as object
range = worksheet.cells(startrow, checkcolumn)
dim cellvalue as string
cellvalue = string.empty
if range.value2 <> nothing then
cellvalue = range.value2.tostring()
end if
if cellvalue.length() = 0 then
exit do
end if
startrow = startrow + 1
loop
' set the new start row in the excel adapter
adapter.rowfrom = startrow
excel.quit
' return the input value to the excel adapter
dowork = invalues(0).getvalue()
end function
move one .net script heater into any current pipe/connection. double click on the heater to open the config dialog. switch the script language to vb and copy the script code above in the text area.
the script code opens the excel workbook (if exist) and search for the first empty row by comparing the first column. you can change this; see the comment in the script.
note: the script change the property rowfrom in the flowheater definition; see the screenshot marked in red. if you save the definition after execution by the execute and test window these are the next offset for searching the first free row.
i've made a simple example; please have a look to the attachment excel-append.zip.
Attachment excel_append.zip not found
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Attachments:
Please Log in or Create an account to join the conversation.
- Gennaro
- Topic Author
- Offline
- User
Less
More
- Posts: 27
14 years 1 month ago #2043
by Gennaro
Replied by Gennaro on topic Re:From CSV to EXCEL: to append the records
Thank you very much for your help.
regards
regards
Please Log in or Create an account to join the conversation.
Time to create page: 0.269 seconds