Copy and Paste from .txt or .csv using specified row count

excel
uiautomation
activities

#1

Hi Community,

My head is about to blow off on this one since I haven’t been able to figure this one out yet.

I’m looking to copy and paste either a .txt or .csv (depending on which one is easier) having the automation only select and copy 500 rows/selections at a time. I would like to have the automation start the process over from the last row that the script ended at earlier to restart the process. Needless to say, the script/automation will need to stop automatically once there are no more rows left on the .txt or .csv.

If someone knows how to help me with this it would be very much appreciated. (If you can explain the method for doing this with both .txt and .csv if possible, it would go a bit further to fully understand the process.)


#2

Hi there,

You left out a few details in your description like where are you wanting to output the data? For example, an existing spreadsheet or text file with data or new files.

Typically, if you have the input data saved somewhere like in a .txt or .csv, then you don’t need to use Copy to retrieve the data you want. Instead I would suggest using either Read Text file to a string or Read Range to a datatable.

To only take 500 lines at a time you can probably use the data as an array with .Take and .Skip

Let’s use a variable called ‘c’ that will be used to count how many times you take 500 lines.
‘data’ will be the variable with the information from the file we want to get.

Below are 2 samples of pseudocode to accomplish this

As a string with .txt, .CSV, or .XLS extension:

Read Text file
Assign c=0
Do
Write Text file => String.Join(System.Environment.Newline,data.Split(System.Environment.Newline(0)).Skip(c*500).Take((c+1)*500))
Assign c=c+1
While c*500 < data.Split(System.Environment.Newline(0)).Count

As a datatable with .CSV, .XLS, or .xlsx extension:

Read Range
Assign c=0
Do
Write Range => data.AsEnumerable.Select(Function(x) x).ToArray.Skip(c*500).Take((c+1)*500).CopyToDataTable
Assign c=c+1
While c*500 < data.Rows.Count

So basically, it loops through and skips the last 500 lines and takes the next 500 lines (shown as .Skip().Take()) until you have hit the bottom of the data.

If you are wanting to output this data to an existing file, then you will need to add an additional Read Text file or Read Range in the loop so you can append your new data of 500 lines to it. There are a few ways to accomplish this as well. (Note: If desired, Set Clipboard can store your string to use Paste in a file)

Thank you. I hope this is helpful.

Regards.

C