Excel row by row

HI all

I have more than ten thousand records in my excel column ,i need to copy entire column perform sub string and copy to other column in same excel sheet.
iam using

1)excel application scope
2)for each
3)get row item
3)writing range

But it taking more than 1 hour to complete 500 records …There is any alternative way to perform this operation in lesss time without using macro or vb code?

Hi @rohanjs94

Use Excel application scope
Use select range
Use send hot key as Ctrl+c and use select range as empty and paste it into another column
Try this

HI ashwin

i need to perform substring for each row and then copy paste…
example it has 16 digit…i need to extract first 8 digit and paste to other column

Rohan s

Are you extracting it using a table? Doing stuff in excel is really slow.

  1. Hide excel this will speed things up (Hit the check box)
  2. Do all changes to the DataTable (Not in excel).
  3. Replace whats in the excel with the DataTable.

If you need help post an example file with say a few made up references.

1 Like

Assuming you just want the first 8 characters, it would look something like this:

Excel application scope (check box so excel is NOT visible)
  Read range --> save as dt1
  Assign NewDT (this is a datatable variable) = dt1.clone
  For each row in dt1
    Add Data row activity
      Datatable: NewDT
      Value: Strings.Left(row.item("YourColumnName").ToString,8)
  Next row

Write Range --> put it in a new sheet, or overwrite the existing one, your choice

Here is an example using table’s. This does about 10k records in less than 2min

FastProcessingUsingDataTable.zip (1.0 MB)

I did not check the hide excel option but that will reduce time taken as well.

Hi all

thanks alot its perfectly
working now

1 Like

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