Cut, Copy rows in Excel that are starting with numbers in Coulmn 'A' and paste in another excel and then delete the empty rows from the previous excel

Help Activities
, ,
1

Cut, Copy rows in Excel that are starting with numbers in Column ‘A’ and paste in another excel and then delete the empty rows from the previous excel.

2

hi @ayushi_jain3

Can you share some dummy Excel File with input and output sheets.
2 to 3 rows would be sufficient.

Thanks

3

@ayushi_jain3

  1. Read the data into datatable dt
  2. Use for each row in datatable activity and assign a variable to index in the for loop properties
  3. Inside that use the condition if to check if the first cell contains number System.Text.RegularExpressions.Regex.IsMatch(currentrow(0).ToString,"^\d+")
  4. On then isde use copy range and give range as "A" + index.ToString + "G" + index.Tostring (change the G as per the column you need in excel)
  5. Then use delete rows with same range as above or give the row number which is index.ToString

Cheers

4

Sample File.xlsx (48.1 KB)

5

Hi @ayushi_jain3

You can use this expression:

dtRecords.AsEnumerable.Where(function (x) (x(“REPORTING_PAYROLL”).ToString).IsNumeric=True).CopyToDataTable

Here’s the sample workflow and the file(you can check the output sheet:
Sample File.xlsx (46.3 KB)
ExcelRowFiltering.xaml (5.7 KB)

Happy Automation!

In case if this serves your purpose please mark this as solution! :smiley:
Cheers!

6

Hey Adil,

Thanks for the solution, It worked except for one row. It didn’t appear in the output sheet:
image

8

Hi @ayushi_jain3

Sorry I misunderstood the requirement a bit,

Here’s the updated sequence and file with output sheet
ExcelRowFiltering.xaml (6.3 KB)
Sample File.xlsx (46.4 KB)

Expression updated to:

dtRecords.AsEnumerable.Where(function (x) System.Text.RegularExpressions.Regex.IsMatch(x(“REPORTING_PAYROLL”).ToString.Trim.Chars(0).ToString,“[0-9]”)).CopyToDataTable

Hope this helps! :slight_smile:
Happy Automation!

9

That was fast :slight_smile: Thanks. This solution helped and gave the required output !

10

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