Trying Data Table columns into Excel

Help Activities
, ,
1

Hi support community,

I’m trying to grab a column from an Excel file A, and add it to Excel file B, but also add static data (hardcoded) to columns in Excel file B. The Excel file has 20 columns, and the data I’m trying to add are next to each other (Columns 1, 16 and 17).

I’ve completed the following:

  1. Get the Excel A data into a data table
  2. Filter out the columns I do not need using the Data Type activity Filter Data Table
  3. I also added two new columns to the DT for the static data called Role and Role Action.
  4. The DT has 3 columns: User, Role and Role Action
  5. I’m able to use the Excel activity Fill Range to get the data into Excel file B, but the columns from the DT are not in the right columns in the Excel file. One this activity completes it enters the User, Role and Role Action data into Columns 1, 2 and 3.

This is where I’m stuck. How do I take the DT data by column and insert into the appropriate columns in Excel?

Thanks so much for any guidance!

T

2

Hi @TSP

Can you share your input and expected output data in an excel file.

3

Hi mkankatala,

See attached screenshots of Excel file A.

Excel File A
Excel File A1078×186 18.9 KB

Thanks!

4

And here’s Excel file B.

T

Excel File B
Excel File B1761×177 26.7 KB

5

I could think of possible below solutions

  1. You have the flexibility to add columns in the final Datatable and arrange the columns matching with excel file using filter options.
    For adding columns - use activity Add Data Column

Do for all the columns available in the excel file

Then for rearranging based on the excel file - use filter datatable and set columns in the required order in Output Columns

image
image1044×682 40.8 KB

image
image1192×748 32.5 KB

  1. Other option would be to filter only required columns using the same above function and paste it separate to each of the range in excel file
6

Thanks sharazkm32!

I did think about adding all the additional columns to the data table, but thought it was not very efficient. That’ll be an additional 16 columns. I was hoping that I could write to the final Excel file by simply inserting the necessary data in their right columns.

I will give your advice a try.

T

7

Other option i could think of is

Split the data table using filter option - One specific for UserName. Lets say dtUserNameDetails

Other specific for RoleDetails. We can call it as dtRoleDetails

Next challenge is identifying the position of the column. As this is starting from A1 cell - use read range activity to get the details into a datatable (dtExcel) including the headers.

To identify the last column - use the expression dtExcel.columns.count
To identify the position of any column name - use the expression dt.columns.IndexOf(“ColumnName”)

with these details - you can insert the roles datatable to required index in the excel file

Kindly share your view on above solution