How to merge datatables

Help
1

Hello all
I am trying to merge multiple datatables in single datatable using merge activity but while using this it merging the value in this forsm as shown in figure output

Although desired output shoud be in formDesiredoutput and here solumn name is not fixed… so how to resolve this issue? is there any other way?

2

Please use write range activity and mention each time the next available column as address.

first time, write range address “A1”
second time, “B1”

3

But these columns are no fixed here. There can be “n” number of columns

4

@mhk15, you could put in loop. Conver number to Alphabet and assign the range in “Write Range” Address.
If the file accessed is the second file, convert 2 to B and Write, if its 4th file, convert to D and write.

5

C# program to convert column number to column name.
You can use it in your workflow.

private string GetExcelColumnName(int columnNumber)
{
    int dividend = columnNumber;
    string columnName = String.Empty;
    int modulo;

    while (dividend > 0)
    {
        modulo = (dividend - 1) % 26;
        columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
        dividend = (int)((dividend - modulo) / 26);
    } 

    return columnName;
}

Regards,
Karthik Byggari

6

hi @mhk15

I’ll suggest you to use Invoke VBA activity and run vb script to delete empty cells.
So for the same follow these steps:

  1. Take Read Range activity —> read your excel sheet —> DT (DataTable - Output Variable)
  2. Copy Paste following code in Delete.txt and save it in project folder.
  1. Take Invoke VBA activity
    set parameters - CodeFilePath —> “Delete.txt”
    EntryMethodName —> “Delete”
  2. bang!! run it.

I’m attaching my sample wokflow here for your better understanding,
Delete Empty Cells.zip (18.3 KB)

check the sheet data before, and compare it after running it. :slight_smile:
111 Result—>112