- Posts: 27
From CSV to EXCEL: to append the records
- Gennaro
- Topic Author
- Offline
- User
-
Less More
14 years 7 months 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
-
Less More
- Posts: 444
14 years 7 months ago #2040 by FlowHeater-Team
Best wishes
Robert Stark
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
Attachments:
Please Log in or Create an account to join the conversation.
- Gennaro
- Topic Author
- Offline
- User
-
Less More
- Posts: 27
14 years 7 months 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.304 seconds