Jump to a specific row in excel and copy 'n' rows

datatable
excel
studio

#1

Hi All,

I need to copy the 1st 500 rows from a spreadsheet and paste in an ERP application. Then move on to the 501st line and copy the next 500 rows and so on.

How do I achieve this in UiPath Studio? I am storing all the rows in a datatable.

Appreciate your help! Thanks.


#2

Is this what you need, @oshinkavdia?
A sample : While Select.zip (9.3 KB)

May be you can use while there is row to select,
and inside it you can use Select Range Activity and set the range dynamically.


#3

For copy paste, you can try

sb = new StringBuilder()

For each - dt.Select.Skip(500).Take(500)
{
   sb.Append(String.Join(Convert.ToChar(9), row.ItemArray)+Environment.NewLine)
}

Set to clipboard - sb.ToString

Control+V on your application


#4

Thanks @whyyouandi. But when I tried to paste (using send hotkey - ctrl + v) the first 500 rows in the ERP application, some XML code got pasted instead of the actual data. Am I supposed to use some clipping region activity to achieve this?


#5

@vvaidya Will this work for multiple columns as well? Also, do I need to paste the code in set to clipboard activity?


#6

Hello @oshinkavdia, I don’t think we need to set clipping region for this,
but could you kindly try this manually (not using RPA):

  1. Open your Excel file, select the range you need, press Ctrl-C
  2. Paste the data to your ERP application
    Does this work when done manually?

#7

Not sure if it pastes correctly in your application, but the clipboard paste works fine in excel.

Sample : dtCopyPaste.xaml (15.7 KB)


#8

@whyyouandi Yes it works when you paste it manually.
I checked and it looks like rows are not getting copied from the excel using select range and hotkey. Can you please check your code once? Thanks!


#9

Hello @oshinkavdia,

You can give a try,

  1. Open excel worksheet,
  2. Type following in name box of the excel
    Name%20Box
  3. Hit enter then it’ll select rows from 1 to 500. next value will be A501:D1000
  4. Copy selected rows and Paste them ERP application. if you’re still able to paste xml code then choose Special Paste will be available.

Any doubts don’t hesitate to ask :slight_smile:

Sincerely,
Pankaj


#10

Thanks @whyyouandi , @vvaidya , @Pankaj.Patil - The code worked. I just used ‘send hotkey(Ctrl+c)’ right after select range.


#11

Awesome feeling to hear this :slight_smile: