Check excel(file1.xlsx) file that contains specific value and Move the entire row to a new excel sheet(file2.xslx)

Help Studio
, ,
1

I have an excel file(file1.xlsx) that contains data and there is a specific column “Account Type” that contains the dynamic value. I would like to remove the entire row that contains the value of PEP, DOSRI, and RPT and move it to a new file (file2.xslx)

file1.xlsx → origin of data

image
image548×572 180 KB

file2.xsls → Data that was extracted from file1.xlsx
image

file1.xlsx should look like this after extracting the details
image

Can you provide a sample XAML that I can reference to in developing this exercise script.

Thanks in advance.

2

Can you send the sample input file, so that we can work on it??

3

The easiest way you can achieve this is by using datatables:

  1. read both xlsx files into datatables (=> dtfile1 and dtfile2)
  2. filter dtfile1 using filter datatable activities by records of interest
  3. iterate through filtered rows and take two actions 1. insert row in dtfile2 and 2. delete row in dtfile1
  4. write back to xlsx using write range both dtfile1 > file1.xlsx and dtfile2 > file2.xlsx

Hope I explained it clearly, but I can add details if required.

4

Hi Manish,
Below is the sample file.

Mixed_943B7CED4E7D6FDDF2F2C4227BF4912A17-03-2020_02-28-36.xlsx (9.2 KB)

5

can you provide a sample xaml file.

6

check this
AddHeaders.xaml (11.7 KB) Mixed_943B7CED4E7D6FDDF2F2C4227BF4912A17-03-2020_02-28-36.xlsx (10.5 KB)

7

I tried this and this is exactly what the output I’m expecting.

Thanks @ImPratham45.

8

Most Welcome!

Happy Automation! :blush:

9

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