I have 6 Excel files containing thousands of rows of data, and I want to merge them into a separate file.
Each file has:
The first 8 rows as introductory/header information.
The actual column headers are on row 9.
The last row contains a total for a column called Amount Posted.
I want to combine the data from all files into a new Excel file using the same header structure, and I also need the final file to include the correct total of the Amount Posted column.
The Amount Posted column contains various number formats, including negative values (e.g., -88,338), regular integers, and numbers with thousand separators.
I tried using a Merge Data Table / For Each File approach, but the results were not accurate and the totals did not match.
Does anyone have suggestions on the best way to handle this?
Thanks
Get the total row count from the merged DataTable, write the data using Write Range, and then use Write Cell (or add a DataRow) to insert a SUM formula for the Amount Posted column. Excel will automatically calculate the correct total.
I have created a similar workflow in the past, so let me share my experience with you.
Using the following algorithm, you can merge multiple Excel files with the same structure into a single file:
Use the Build Data Table activity to create the structure for the merged DataTable (mergedDT).
Use the Read Range X activity to read Excel Sheet 1 and store it in a DataTable named dt_1.
Use the For Each Row in Data Table activity to loop through dt_1 and repeatedly execute the following steps for each row:
(1) Convert the row into an array (arr_dr) using the syntax: arr_dr = CurrentRow.ItemArray
(2) Use the Add Data Row activity to convert the array arr_dr into a row and add it to the DataTable mergedDT.
Use the Read Range X activity to read Excel Sheet 2 and store it in dt_2. (Repeat this process for the remaining Excel sheets).
Finally, use the Write Range X activity to write the merged DataTable mergedDT back into an Excel sheet.
Build a datatable with required schema. (say DTMergedData)
Declare int TotalRowsCount = 0;
foreach(var File in InputFilesList)
{
1. Get the Last used row from the File(excel file) and store it in var : LastUsedRow
2. Read range (File) with read headers checked(pass the range also starting “A9:I”+LastUsedRow.ToString()) on and store this in a temporary Dt (Say DtTemp)
3. save the count of rows in a var TotalRowsCount = TotalRowsCount + DtTemp.Rows.Count
4. Merge data in DtTemp with DTMergedData.
}
repeat this till all files are read and merged.
Finally, write the DTMergedData to a new excel file with (add header checked).
TotalRowsCount has the count of the records that are merged from all files. (TotalRowsCount +1) give you the last row in the new excel file including headers, that is filled.. If you want to validate whether the total records read and written are same, you can do it after this step.
(TotalRowsCount +2) gives you the first empty row in excel.
write formula in the column Amount Posted and row number : (TotalRowsCount +2) for example
use write cell activity value: String.format(“=Sum(“P2:P{0}”)”,(TotalRowsCount +2).ToString())
The safest approach is to treat the introductory rows, detail rows, and total row as three separate things. Do not merge the complete used ranges because each file’s total row will then be included as data.
Suggested flow:
Read rows 1–8 from the first workbook only and keep them for the output header.
For each file, read the table starting at row 9 with AddHeaders/Has headers enabled.
Remove the source total row (preferably by checking a label such as Total; otherwise remove the last row only after validating that it really is the total).
Normalize Amount Posted before merging. Remove commas/currency symbols and parse the result as Decimal; do not leave some files as String and others as Double.
Create the master table from the first cleaned table using Clone, then use Import Row/ImportRow for each cleaned row. This avoids silent schema mismatches that can occur with Merge Data Table.
Calculate the final total from the master table, then write one new total row after all detail rows.
Before that expression, make sure every value has been converted to Decimal. For strings such as -88,338, remove the comma first (and handle parentheses if the source uses accounting format). Also log the file name and row number whenever parsing fails instead of converting the value to zero; otherwise the workflow can finish with an incorrect total and no obvious error.
Finally, write rows 1–8 once, write the master table beginning at row 9 with its column headers, and append the calculated total underneath.
I’d suggest keeping the approach simple: read each file starting from row 9, exclude the total row, append the data to one DataTable, and calculate the final total separately.
I’ve had better results by not using the existing “Amount Posted” total rows during the merge. For each Excel file, I read the data starting from row 9, skip the last/total row, and append only the actual transaction rows to a single DataTable.
For the Amount Posted column, I also make sure the values are converted to a numeric type before calculating the total. This is important because values such as -88,338 and numbers with thousand separators can sometimes be read as strings.
After all 6 files are processed, I write the combined DataTable to a new Excel file using the header from row 9, then calculate the total Amount Posted from the combined data and write that total at the bottom.
This avoids adding the individual file totals together, which can easily cause incorrect results if the total row is accidentally included in the merge.
I would also recommend validating the result by comparing:
Final Total = Sum of Amount Posted from all actual data rows
rather than relying on the totals already present in each source file.