Skip to main content
simply more productive

The Data Specialist

Thanks to our intuitive transformation designer, you can convert data effortlessly to a variety of formats without knowing how to program. It’s not only efficient, it’s also easy to use.
Flowheater Download

From CSV to EXCEL: to append the records

More
13 years 5 months ago #2039 by Gennaro
I am using flowheater to import a CSV file into a Microsoft Excel file.
Becuase 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 to join the conversation.

More
13 years 5 months ago #2040 by FlowHeater-Team
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.
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 to join the conversation.

More
13 years 5 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

Please Log in to join the conversation.

Time to create page: 0.330 seconds

FlowHeater - The Data Specialist

Efficient data integration and transformation with FlowHeater – the perfect solution for a seamless transfer of your data.

Rechtliches

Support & Contact

Kontaktinformation

Telefon:0951 / 99339792 E-Mail:This email address is being protected from spambots. You need JavaScript enabled to view it.

Copyright © 2009-2024 by FlowHeater GmbH. All rights reserved.