Combine multiple Excel Sheets (selected columns) using Knime

I have multiple Excel Workbooks (File 1, File 2,…), each of which has 2 common sheets - Sheet1 and Sheet2.

I want to merge Sheet1 from all Workbooks into a single Sheet1 (new Workbook)
(1) The column headers and sequence of all Shee1 are exactly the same.
(2) I only want to copy selected columns from all Sheet1 i.e. A, D, G, H, I,… into corresponding A D G H I… columns of combined Sheet1.
(3) I want to start from exactly the 7th row of each Sheet1 and then end with the row that has some data in Column A i.e. When the first blank in Column A is reached, the loop should move to the next Sheet1 in another Workbook
(4) The copied columns have to be pasted into the corresponding columns of the combined Sheet1. This combined file already has some formulae in the other Columns and hence this pasting from Table to combined Sheet1 has to be correctly done.

I have presently written a VBA Code for this which I run in the Workbook where I want the Combined data. I am pasting the same below. I want to replace this MACRO with KNIME, if possible.

Private Sub CommandButton1_Click()

Dim FolderPath As String
Dim FileName As String
Dim i As Long
Dim lcurrentrow As Long
Dim lrow As Long
Dim wb As Workbook
Dim list_file As Workbook

FolderPath = Application.ThisWorkbook.Path & “”
Set list_file = Workbooks.Open(FolderPath & “List of project files.xlsx”)
lcurrentrow = 7 'Row number in destination sheet at which pasting has to start. anything above this is left as it is

MsgBox (“Open List File”)

For i = 1 To 6 Step 1 'i points to Row in the “List of project files.xlsx” and must be equal to the number of files being consolidated

FileName = list_file.Sheets("Sheet1").Range("B" & i + 1).Value

Set wb = Workbooks.Open(FolderPath & FileName)

MsgBox (FileName)

With wb.Sheets(“Cash-in Data”) 'select the sheet from the source workbook that needs to be copied

    lrow = 7                                    'Row number in source sheet from where copying has to start
    Do Until .Range("E" & lrow).Value = vbNullString
          
        For n = 0 To 68 Step 1

            If (n = 0 Or n = 1 Or n = 2 Or n = 3 Or n = 4 Or n = 5 Or n = 7 Or n = 8 _
                Or n = 10 Or n = 11 Or n = 13 Or n = 16 Or n = 17 Or n = 18 Or n = 20 _
                Or n = 23 Or n = 24 Or n = 26 Or n = 27 Or n = 28 Or n = 30 Or n = 31 Or n = 32 _
                Or n = 49 Or n = 53 Or n = 54 Or n = 61 Or n = 65 Or n = 66 Or n = 67 Or n = 68) Then
            
            'select those columns from where we want to copy data
            
            ThisWorkbook.Worksheets("Cash-in Data").Range("A" & lcurrentrow).Offset(columnoffset:=n).Value = .Range("A" & lrow).Offset(columnoffset:=n).Value
    
            End If
            
        Next n
 
        lcurrentrow = lcurrentrow + 1
        lrow = lrow + 1
    
    Loop

End With

wb.Close True                                   'close the source workbook

Next i

list_file.Close True 'close the source workbook

Set wb = Nothing 'free the momory
Set list_file = Nothing 'free the momory

Sheets(“Commands”).Activate
ActiveSheet.Cells(1, 1).Select

End Sub

Hi @mayankk,
welcome to the KNIME Forum!
I fear I’m only provide a solution for the first points, maybe somebody else has ideas for the last one. I don’t know if you can paste into an existing Excel file (the Excel Writer options are either append at the bottom of the file or overwrite). Would performing the calculations in KNIME (e.g. with Math Formula) be an option?
The Excel Reader node should be able to solve the combining and the filtering of point (3). You can select the mode “files in folder”, which allows to read several files (I assume you have all the files you want to read in the same folder). You can select which sheet to read (by default it will be the first one). In the sheet area section, you can select to “Read only data in rows from” 7, by default until the last row with data available (should it be the first blank row, or really the first row where only the value in A is empty?). You can also check if the table contains the column names somewhere, if it does you need to adapt the row number.

Maybe someone else can help from here? Or you try to generate the full excel sheet (including the calculations) with KNIME.

Best regards,
Daniela

3 Likes

@daniela_digles Is there a way for Knime to combine the Excel files just based on common field names across all the files and ignore field names that do not occur across all the Excel files? In the Excel reader, we can have it read multiple files (for example if the files have the same name in them) but it will fail if one of those files has an extra field in it that the other files don’t.

Hi @Shmelky, on the Advanced Settings tab, there is a point for “Options for multiple files”. You can deactivate the “Fail if specs differ” to allow additional columns that are not included in all the files. If you only want the columns that are present in all the files, you could use the “Take columns from: Intersection” option on the Transformation tab.

1 Like

Thanks! That worked perfectly!

2 Likes

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.