Excel Automation from one excel to other excel

Help Studio
,
1

I want to write the data from one excel to other excel with headers changed and 2 columns need to be combined
so how should i do it

like input excel has following 5 columns

  1. AAA
  2. BBB
  3. CCC
  4. DDD
  5. EEE
    and i want output excel to have following columns
  6. AAA column should now be ABC
  7. BBB column should now be DEF
  8. CCC column should now be GHI
  9. DDD+EEE (concatenation) column should now be JKL

so how to do this?

2

@Aishwarya_Bhargava According to the requirement, If you are working on Datatables manipulation and updating the Excel Sheet, then one way would be to Concatenate the DDD+EEE Column Values first and place it in the DDD Column itself. So that You wouldn’t have to worry about the EEE Column and you can delete it with Remove Data Column Activity.

Hence, you would have 4 Column Names to be replaced with 4 new Column Names.

Next, You can just use an Assign Activity to Update the Old Column Name to New Column Name in the Below way.

DT.Columns("AAA").ColumnName = "ABC"

Where DT is the Datatable variable name.
Similarly you can update the Other Column names as well.

However there are other methods to update the Column Names, and let us know your review on this method whether this method is suitable.

If this method is not helpful please let us know more about the requirement in detail.

3

how can i concatenate the 2 columns
the thing is
if DDD is empty or EEE is empty no concatenation i just need to write that cell exactly but if both are present then concatenation

how should i do this?

4

@Aishwarya_Bhargava This can be done using an If Condition.
So if you want the Columns to be concatenated when only both the Column Values are present, then below Interpretation of the code should be helpful in achieving the required logic.

For Each Row in DT
      If Not(String.IsNullOrWhiteSpace(row("DDD").ToString) or String.IsNullOrWhiteSpace(row("EEE").ToString))
      Then
         row("DDD") = row("DDD").ToString+row("EEE").ToString
      Else
         //Empty, No Code

There may be differences in the requirement as to what is put and understood.
Let us know if you’re still facing a problem, and please provide more details about the problem faced

5

i cant use for each as there might be lakhs of rows
and it will consume a lot of time

so is there any other time efficient way?

6

@Aishwarya_Bhargava Can you Check the below Workflow :
It uses a Invoke Code Activity where it Updates the Datatable using the For Each.
I have manipulated the Condition to only update the rows having both the DDD and EEE column Values.
Let us know if it works and if it is able to optimise the process better.
Update_InvokeCode_Demo_SetField.xaml (5.9 KB)

7

it is good when both the columns are filled but what if one of the columns are empty in that it is still concatenating
which should not happen in that case it should just copy the filled cell

8

@Aishwarya_Bhargava Can you Provide a Sample Excel File ?

9

sample.xlsx (9.6 KB)

In this input and expected output both are given

10

@Aishwarya_Bhargava Comparing your Expected output and the Statement that you have mentioned before, we needed to not Concatenate when Either one of the Columns are Empty, But the Expected output has Concatenated value (Value From EEE Column in 2nd Row is Moved to JKL, even though DDD Column was Empty).

image

If you need the Expected output in the same way then you wouldn’t need to add any condition to your flow :sweat_smile: . You could directly update it for all the rows.

11

but how do i do that?

your code works properly for concatenation when both cells have value but not when only 1 cell has value

so for that how can it be altered?

12

@Aishwarya_Bhargava Can you check the below updated workflow :
UpdateColumnValues.zip (10.1 KB)

Let us know if it didn’t work as expected.

13

it worked thankyou :slight_smile:

1 Like
14

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