How to copy selected rows from one excel to another empty excel file

[WASEEM_KHAN]

Hi,

I have large data in one excel file with many columns. I have to do the following tasks:

1. For example, there are two columns in this excel file, “C” and “M”. I have to pick those rows where entries of C and M columns differ from each other.

2. Then I have to copy these rows into a new excel file but only keep three columns for example “G”, “H” and “V” in the whole excel file.

What I have did so far, I convert the excel into data table and then apply for each row loop on data table. Within "for each row ", I am checking the condition where entries of C and M are not same and if condition gets true then I am trying to add this row to a new data table. After that I have plan to use write range activity to write it into excel and then filter the desired columns.

But I am getting error at add row activity that “Add Data Row: Input array is longer than the number of columns in this table.”

Any help, how to solve this problem?

[prasath_S]

Hi @WASEEM_KHAN

Please try this

In Assign

dtTable = dtTable.asenumerable.where(function(d) d(“C column header”).tostring <> d(“M column header”).tostring).defaultview.totable(false,“g column header”,“h column header”,“v column header”)

Replace header according to your excel.

Thanks

[WASEEM_KHAN]

Hi!
@prasath_S Thanks for your reply.
Should I use this assign activity inside “if condition” and I do not need to use “Build DataTable” and “Add Data Row” activities in this case?
Could you please elaborate a little bit your instruction code? What is “d” here?
I would highly appreciate your feedback in this regard.

[prasath_S]

Hi @WASEEM_KHAN

you dont need to use for each row and if condition ,

after read range use this in assign (dtTable is ypur datatable)

d = temporary variable used when using linq we can give anything here

[WASEEM_KHAN]

Thanks for your prompt response. I used your expression but I am getting error:
‘defaultview’ is not a member of ‘System.Data.EnumerableRowCollection(Of System.Data.DataRow)’. Main.xaml

Any help in this regard?

[WASEEM_KHAN]

I am using UIpath studio 2020.10.4.

[prasath_S]

Please try this,

1. Lets assume C Column header - CaseNames, M Column header - Email

2. The syntax in assign,

dtTable1.AsEnumerable.Where(function(d) d(“CaseNames”).ToString = d(“Email”).ToString).CopyToDataTable.DefaultView.ToTable(False,“Email”,“CaseNames”)

3. The output .

Thanks

[WASEEM_KHAN]

@prasath_S

dtTable1.AsEnumerable.Where(function(d) d(“CaseNames”).ToString = d(“Email”).ToString).CopyToDataTable.DefaultView.ToTable(False,“Email”,“CaseNames”)

Thank you so much. It worked like a charm:)

[WASEEM_KHAN]

@WASEEM_KHAN

Then I have to copy these rows into a new excel file but only keep three columns for example “G”, “H” and “V” in the whole excel file.

I am facing problem in this step. I want to write the output of assign activity in new excel file not in a new sheet. How can I achieve this? All the logic is within excel application scope.
I tried with write range activity to write the output data table to a new sheet. But it is writing all the columns of table into the sheet. So , I do not want to write all columns and also not intop a sheet but in a new excel file. Any help?

[prasath_S]

After excel application scope use write range and write the dtTable1 to new file, it’s should only write the columns we selected…

If you still facing problems let me know

Thanks

[WASEEM_KHAN]

Thanks for your reply. I used another excel application scope after the previous one and inside it , I used write range activity and its working. Did I follow your guideline?

And one another thing, how to retain the formatting that I was having in my original excel file. In original excel file, the headers were highlighted with different colors. Any idea? And thanks for your solutions.

[prasath_S]

@WASEEM_KHAN for keeping colors we need different logic an require little bit of coding you can use write a macro code (using invoke VBA activity) or vb.net code (using invoke code activity)

Thanks

[prasath_S]

@WASEEM_KHAN could you mark my conversation as solution so it would be help for others when they search…

[WASEEM_KHAN]

Hi!
Thanks for your message. I marked the solution.

[prasath_S]

@WASEEM_KHAN if you can mark this as solution, for others it will be helpful.

Thanks