Filtering 2 Exel Files and Create new File

Hi Community,

I have a system reporting tool which will generate file. Every time I generate a report it appends any new transaction in the report.
I need help in creating a new file, based on the FILE1 generated. How will I be able to extract the Appended transaction(FILE2) every time a new file is being generated. I believe I can use the header posting_date and use the timestamp.

Any suggestion on what is the most effective way of handling this.

File_1.xlsx (10.0 KB) File_2.xlsx (11.4 KB)

Last Transaction from File 1
image

1 Like

Hi @VegitlX_HuNteR

For this you can use “Filter Datatable” Activity

Here you can filter the data as per you condition
image

And when ever the condition meets you can extract that data from the specified columns which you will mention
image

Then it will create the new DataTable which you can use to generate New excel and can store the data in new Excel
image

Hope this may help to solve your issue
Mark as solution if this helps you and like it :innocent:

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

1 Like

Hy @VegitlX_HuNteR,

Do you want to extract the last row of the file and move it somehere else?
If so, use the ‘Read Range’ Activity inside and Excel Application Scope.
Get the Last row data use like this:
MyPostingDate = DT(DT.Rows.Count-1)(“Postting date”).toString

Use the ‘Apply filter activity’ to filter the data table
Inside another ‘excel application scope’ use the write range or append range to post your data.

Is it clear for you?

1 Like

@Pratik_Wavhal, Thank you for responding in my post.

Let us say from FILE 1 that is the original file. File 2 contains Appended information. How exactly I will use the filter data table to get only the latest transaction.

Hy @VegitlX_HuNteR,

To move data from File 1 to File 2
Read the data from File 1 with a Read Range Activity inside an Excel Application Scope, it generates a data table variable
Inside another excel application scope, with file 2, use the append data table activity to past the data table in the desired variable

If it is clear for you please like my post and mark it as a solution, it helps me
Any questions please let me know

Regards

Hi William,

The scenario is that FILE 1 contains the Original list. File to contains the Original list and the appended data. I will like to only get the appended data from File 2. See reference file i posted.

@VegitlX_HuNteR,

Do you want to move data from one file to the other?
Whitch file is going to recieve the data? Which will provide the data?
I can prepare something if you want

Regards

FIle 1 is the first generated report. Now after 15min I will generate another report which is FILE 2. If you check the file 2 it contains the data from file 1 and a new record is added. I would like to get only the new record and save it to a file with the same header parameters. The reason I do this is I don’t want to create a duplicate once I upload the FILE 2 to a database system.

Ok @VegitlX_HuNteR, I think I uderstood it now

You want to get all the rows from file 2 that are not in file 1 and move it to another file?
Please confirm my understanding so I will prepare something for you

Thanks

yes, that is correct :slight_smile: . I just need the data that is not in FILE1

ok @VegitlX_HuNteR, please standy by I will get back to you shortly

1 Like

Hy @VegitlX_HuNteR,

Please have a look at my workflow, the Excel contains the results you need

Excel_Move_Unique_Data.zip (47.9 KB)

Any questions please let me know
I ask you to like my post and mark my answer as solution, it helps me :slight_smile:

Regards

Hi @VegitlX_HuNteR

I have attach the .Xaml which gives the 100% output by fulfulling all the conditions in a simple way
File_1.xlsx (10.0 KB)
File_2.xlsx (11.4 KB)
File1File2NewSequence.xaml (13.7 KB)
abc.xlsx (8.0 KB)

File 1 Data Timings :-

File 2 Data Timings :-

File 3 Data Timings :- i.e. abc.xlsx (File 2 Data - File 1 Data = File 3 Data)

Hope this may help to solve your query
Mark as solution if this helps you and like it :innocent:

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

1 Like

Hi @Pratik_Wavhal and @William_Blech_Sister thank you both.
Both logic did work not sure how I will mark both as solution :slight_smile:

1 Like

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