Dynamic data import / export

datenbank

simply more productive
Up to 10,000 records can be processed with the freeware version!
Welcome, Guest
Username: Password: Remember me
Welcome to the FlowHeater support forum.

Questions about using FlowHeater are answered here.

TOPIC: From CSV to EXCEL: to append the records

From CSV to EXCEL: to append the records 6 years 2 months ago #2039

  • Gennaro
  • Gennaro's Avatar
  • Offline
  • Junior Member
  • Posts: 25
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.
The administrator has disabled public write access.

Re:From CSV to EXCEL: to append the records 6 years 2 months ago #2040

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 240
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.
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

Attachments:
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.
The administrator has disabled public write access.

Re:From CSV to EXCEL: to append the records 6 years 2 months ago #2043

  • Gennaro
  • Gennaro's Avatar
  • Offline
  • Junior Member
  • Posts: 25
Thank you very much for your help.

regards
The administrator has disabled public write access.
Moderators: FlowHeater-Team
Time to create page: 0.080 seconds