Advent Challenge #5

[Luiza (Luiza Draghicean)]

Welcome to the 2020 Advent Challenge #5!

All users are welcome! (You can still join. You don’t need to participate in previous challenges)

Earn Stars to increase your chances to win a prize

More info here

Jingle-Bell, the assistant of Santa, hasn’t heard of version control and therefore made a terrible mistake. Initially Santa kept all the kid’s names wishes in an Excel sheet, however Jingle-Bell suddenly started to make a new version of it and now it’s all a mess. Santa Inc. doesn’t have resources to deliver twice to the same kid neither can his professionalism stand it.

Files needed: Wishes.zip (13.5 KB)

Photo by Tim Mossholder on Unsplash

Advanced:

Make a UiPath workflow that compares the two attached Excel sheets and make a new version, where the duplicates are removed, and the unique names afterwards are merged.

Beginner:

Santa doesn’t expect you to be a UiPath expert (yet). Build a workflow that read the the creation date of both files and delete the latest created. Hint: Get creation date of and excel file

Deadline:

Submissions must be posted in this thread and will be accepted until 2020-12-15T22:59:00Z

Bonus points will be awarded for sport references, creative solutions and responses

[jeevith (Jeevith Hegde, Ph.D.)]

Last I heard, version control is still quite a taboo in the elf world. But when things get duplicated Santa knows whom to rely on; UiPath

Looks like the list of kids who behaved well this year is quite short. So it did not take long to sort things out for Santa this time around.

1. We read the two .xlsx sheets files using the System Workbook Read Range activity.

2. Then we merge the two datatables into one, which also contains duplicates rows.

3. We use the “Remove Duplicate Rows” activity. Although, kids would have loved that to happen! Santa this year has to be environmentally friendly - “Green”!

Looks like these kids are inn for a treat this winter. Is it me or did some of the UiPath Community Moderators disguise themselves as kids? Well, we will never know, will we?

Here is my submission for the Advanced Advent Challenge #5: Advanced Advent Challenge 5.zip (21.5 KB)

[prashanthig]

Beginner AdventChallenge#5

Oh no, I made a mistake

[Oops ! It’s Ok to make mistakes Santa. “We all learn from doing mistakes”. If u can cheer up I can fix it]

[Let’s get start with the issue… Lets extract files from compressed folder first]
[Here you go! we did it…]

!

[ Oh no! Oh No! now Data is duplicated with multiple files!!! I can’t identify the Original copy of it! ]
[ Ok no problem let me identify this and resolve it, better u spend some time with kids. I will call you when I’m done. ]

[Hmmmm! we can make this possible with FileMetaDataReader.activities.
Oh my goodness! we have finished our task so far. ]

Beginner_5Challenges|690x460

[Now lets compare CreadtedDate and delete the duplicates…Here you go! we are done.]

Thats great ! let have a look at kids list and spread some joy…

Lets have our joyful ride…

Happy Automation & Cheers.

[monsieurrahul (Rahul)]

Before we proceed, let us understand what Jingle Bell likes.

Jingle Bell is a small happy boy who works for Santa during the holiday season. He is a big fan of Chris Jericho, a wrestler and just like Jericho, our boy Jingle Bell likes making lists. He never lets anyone else write the list for him. He in fact has a personal list where writes about people he doesn’t like and steals their Christmas gifts!

Here’s how much he enjoys making them:

Recently, he has messed up his Chirstmas list real badly and Santa is not happy about it.

: Jingle Bell, what have you done? Christmas is near and I need the list of kids and their wishes.

: I’m sorry Santa, I got all confused! Umm…let me… correct it…

: Fine, I’ll take care of it!

: But, how can you go through such a long list?

: Ha ha ha, my robot friend can do this for me. Let me call him now.

: Hello Santa, how may I help you?

: I need help with finding duplicates in these files. I need to deliver gifts to those kids, fulfill their wishes.

: Sure Santa, let me process them.

: Okay buddy, after you’re done, give that workflow to my assistant Jingle Bell, he might need it.

: Alright Santa, let me finish these tasks for you.

Advent Challenge #5, Beginner:

Workflow Screenshot:

Approach Used:
For each file available in the folder, take its created date and add it to a list. Sort the list, and take the last item. For each file, if its created date time matches this last item, delete it.

Bot Run Screenshot:

: Done, now task 2.

Advent Challenge #5, Advanced:

Workflow Screenshot:

Approach Used:
Read the wish list A and B using Read Range activity, store them in separate DTs. Merge them using Merge DataTable activity and then, remove the duplicates using Remove DataTable activity. Once done, write them to Excel using Write Range activity.

Bot Run Screenshot:

Excel Screenshot:

: I’m done, Santa!

: Thank you once again, my friend!

: Here’s the workflow for your future reference, Jingle Bell: UiPath-Advent-Day5.zip (24.9 KB)

: Thanks, robot! I appreciate your help but, you can’t just make my lists. Guess what?

YOU JUST MADE THE LIST!

: Oh no!

And we know what he does with that list.

Thanks for this @Luiza, @Pablito, @Vibhor.Shrivastava, @loginerror, @Steven_McKeering, @AndersJensen and others. Appreciate your efforts!

Cheers,
Rahul

[prashanthig]

Advance Challenge#5

Happily came over to grab wish list from :Jingle bells:

:jingle: was so afraid as to share the mistake he made…however he told the truth…so santa was much worried about resources to deliver gifts to kids.

Lets try to help him with automation…Guess he will be happy after retrieving the record
was sitting alone thinking about ideas

: He was little happy after looking at the automation process…To make him fully satisfied
let’s show output too…

Hooray! he was thinking to sing his old song out of cheers as the output was like he expected

Here you go, u can have entire list with kids wishes in this zip… that’s so cool… Find attachments here

AdvanceChallenge_5.zip (36.1 KB)

“Happy automation& cheers”

[rpavanguard]

Advent Challenge #5 | Advanced | C#

Santa is annoyed at Jingle Bell, but he is a Maradona fan. To honor the Football Great and get hi attention, Santa invokes the ‘divine free-kick’ of NAPOLI vs Juventus 1985 and kicks the Wishes.zip file into the \incoming folder of Santa_Stream

The Football great maneuvers the files and unzips them into the \Wish folder. The idea here is to untangle the files to clear up Jingle Bells mess . Diego needs all the expertise he put in at NAPOLI vs Lazio in 85.

And as unique as Maradona’s magic is, each unzipped folder gets a unique ID

The football great navigates through the mess in the files and dedupes them - 2 files are not a problem because he beat 4 Belgians to score an awesome goal for his team during ARGENTINA vs Belgium 1986 World Cup

As the final act, the Great Master scores the ‘Goal of the Century’ of ARGENTINA vs England 1986 World Cup to kick the Deduped File into the \outgoing Santa_Stream folder !!!

Santa got his wish of getting all of the Wishes deduped!

With this code, hopefully Santa can do it himself the next time.

Cheers!
UiPath_Advent2020_Advanced5_CSharp_rpavanguard_Maradona_Style.xaml (20.2 KB)

[prasath17 (Guru)]

Beginner:

Santa booked his ticket for the cricket match and very eager to see watch the “Boxing Day” test match between India Vs Australia on 26th…When he opened the ticket zip he got two tickets so he has to delete the recently booked ticket.

Let’s use dictionary to solve this problem…

Here you go Santa, Duplicate ticket has been deleted…

Santa is dreaming about watching the match

XAML: Advent_Challenge5_Beginner.zip (63.0 KB)

[stefano_negro]

Advanced Challenge 5

Santa! We heard you have some problem so we came here to help you, even if it’s winter!

Ok… we are a little scared by the temperature, but we’ll get into work right now!

The first step is to unzip the file and extract the data from the Excel…done!

So, now the hard part…we have to merge all this data and check the rows one by one? You’re kidding, it will take a lot of time, and we like to go fast!

With the help of UiPath activities we are able to merge all the informations in just one file

Lastly, we have to create a new Excel and write the Data, but with a little bit of Jamaica inside!

So we did it!

See you later Santa, and don’t forget to come to Jamaica, even if it’s not your ideal climate!

AdvChallenge5.xaml (9.7 KB)

[Adee (Muhammad Adeel)]

Beginner
Hello Santa it would help you to delete the latest created file
Advance
this flow help you to delete the duplicates in two files and give the result
begineerAdvent5.xaml (12.0 KB) excel.xaml (6.0 KB)

[Veena_Siva]

Beginner
Santa is all excited to deliver kids gift and he just realized the mess and need help in choosing the right file from below

Santa need not worry, UiPath can find an easy solution.
Approach used: Iterate through the folder, compare FileCreatedDateTime and store the file to be deleted.On loop exit delete the file.
Here is the execution result

Santa is very happy and have now time to go for a match

Below is the code
Challenge5.xaml (8.7 KB)

[sarikayaebru (Ebru)]

Panic in Santa Inc.

Hey Jingle-Bell,

I will help you on the versioning before Santa heard. We’ll got it

Beginner:

First let’s take a look at out files and the creation times.

After that with a magical LINQ, we get the latest created file

And destroy it

The full workflow with the output:

AdventChallenge_5_Beginner.zip (57.0 KB)

Advanced

Read all the files in the input folder and merge them all!

Removing the duplicate rows

And here is your final wish list!

AdventChallenge_5_Advanced.zip (70.3 KB)

Cheers!

[anupamck (Anupam Krishnamurthy)]

As the year draws to an end, Santa’s football team, “The Eleven Elves”, faces off against Easter Bunny’s team, Bucktooth FC.

However, things don’t look very good. It is minute 89’ and Santa’s team is 1-0 down, thanks to an own goal by his defender, Jingle Bell.

Beginner challenge

However, Santa’s team wins a free-kick on the edge of the box. Santa brings in his power substitute Ronaldinho.

As the coach, Santa explains to him that a weakness needs to be found in the bunnies defense. One among the two of the Bucktooth central defenders is older, and Ronaldinho needs to float the freekick next to him so that one of Santa’s elves can head the ball in.

Ronaldinho racks his brain and recognizes that to find the older defender, he need to invoke a .NET query:

With utmost focus, Ronaldinho takes the free-kick:

The tactic works! The elves are level. The score is 1-1 and the game goes into extra time.

Advent Challenge 5 - Beginner.xaml (7.7 KB)

Advanced

In extra time, team Santa wins another free kick. This time, Santa notices a weakness in the rabbits’ wall formation. There is some duplication in their player marking, which creates a defensive weakness. If only the duplicate can be found out, Santa’s team can convert the second free-kick as well.

Ronaldinho comes up with another master-stroke - a simple query that eliminates duplicates from a datatable.

With this insight, the Brazilian wizard steps forward to take the kick.

…
…
…

GOOOOOOOOOOOOOOOOOOOOOOOL!

The crowd goes wild! Thankfully, Santa’s team clinches the game 2-1. Thank god for Ronaldinho’s two queries. It would have been embarrassing to lose a home game in the Christmas season.

Advent Challenge 5 - Advanced.xaml (9.3 KB)

[prasath17 (Guru)]

Advanced:

This time Santa ready to swing his tennis racquets and decided to play with the kids…And he had planned to share the gifts after the tennis match…

Santa decided to play sessions and One in the morning and one in the evening. .When he saw the list he realized that the some kids have been registered twice…

Santa needs to help from the Robot to identify the kids registered for both the sessions…Robot delivered the result quickly

Santa is happy and Satisfied the kids wishes as promised…

XAML : Advent_Challenge5_Advanced.zip (55.2 KB)

[abu.behlim (Abu Hurera Behlim)]

Advanced.

Santa was selected for the Football Club Barcelona in the month of October 2020.

And Today’s Match with Real Madrid has been tied with the Score of 0-0

But the Match Referee has decided for Penalty Shootout

Because In a penalty shoot-out, each team takes turns shooting at goal from the penalty mark, with the goal only defended by the opposing team’s goalkeeper. Each team has five shots which must be taken by different kickers.

But here is the problem with Santa’s players list. because the player’s list is messed up that santa was supposed to use for the Penalty Shoot-out due to some mistakes made by the new version of the list.

And now Santa is in big trouble . because Santa is not able to decide which player he will send for the Penalty Shoot-out asper their shoot-out skills because there are some duplicate names present in the list.

But suddenly Santa get an idea and he decide to discuss this problem with Magician Messi because Messi is a Captain of the Barcelona Team.

And Messi has a plan for this problem.

And he will be using his some Dribbling Skills to get a rid from this problem.

Messi decide to first merge both the files A and B after extracting the folder and then he will get the merge players names in the new File.

Then Messi read the Merge file.

After reading the Players Name list from Merged File for penalty shoot-out, he found that there are some duplicate players name.

And suddenly he got an Idea to remove all the duplicate players name with the help of his Magic. because he is the Magician of football .

here is the complete Magic flow of Messi the GOAT.

Workflow 1

Workflow 2

After Successfully removing the duplicate Names. Messi show the final Player’s Names to the Manager Santa

Here is the Player’s Name:

Now, Santa can easily decide which player will go first for the Penalty Shoot-out. And he is also very confident that his team will surely win the game against Real Madrid

Always

Barca Love

AdventChallenge_5_Advanced.zip (36.5 KB)

Beginner

After a Match Santa told Messi to delete the Mess file also which will be the latest modified file.

Messi accepted the new challenge again because he is a G.O.A.T and he loves challenges.

And now he will be using detailed pass map to get the latest player’s name file list Date Time.

Let’s see the Messi’s Passmap workflow for deleting the latest file.

Workflow 1

Workflow 2

After using his magical power, he has deleted the latest created file (Highlighted in Red Underline).

Advent_Challenge_5_Beginner.zip (23.7 KB)

[StefanSchnell]

Advent Challenge #5 Beginner

Santa is sitting at home and watch the Muppets sport show. He is angry about Jingle-Bell, his assistant. Such mistakes should not happen, he thinks. There we are doing Scrum and working agile and still it happened, so close to Christmas. Santa admonishes Jingle-Bell, don’t do that again boy.

Santa decides to develop an Invoke Code activity to solve the challenge. So he starts the editor and develop a tiny code sequence.

After the developing and testing he transfers the code sequence to the Invoke Code activity.

The code is very easy to unterstand. Santa creates a data table and stores all Excel files from the current directory in it. He sorts the table according to the creation date. In the last step he deleted all Excel files with the exception of the youngest, which has been created last.

//-Begin----------------------------------------------------------------

string Dir = Directory.GetCurrentDirectory();

DataTable FileList = new DataTable(); 
FileList.Columns.Add("FileName");
FileList.Columns.Add("FileDateTime");

try {
  string[] Files = Directory.GetFiles(Dir, "*.xlsx");
  foreach(string File in Files) {
    DateTime FileCreationDate = Directory.GetCreationTime(File);
    FileList.Rows.Add(new object[] { File, FileCreationDate });
  }
  FileList.DefaultView.Sort = "FileDateTime ASC";
  FileList = FileList.DefaultView.ToTable();
  long i = 1;
  foreach(DataRow row in FileList.Rows) {
    if(i > 1) {
      File.Delete(row["FileName"].ToString());
      Console.WriteLine("File deleted: " + row["FileName"].ToString());
    }
    i += 1;
  }
} catch(Exception ex) {
  Console.WriteLine(ex.Message);
}

//-End------------------------------------------------------------------

All works well and expected. Santa feels good and watches more sports. An interview with Gonzo. Santa wonders himself what he would look like with that nose.

[StefanSchnell]

Advent Challenge #5 Advanced

Santa is a poor man, nothing has changed since Challenge #3. No Office and no Excel is available. So Santa has to convert the worksheets into CSV, in his opinion the better format anyway.

Santa wants to laugh, so he watches the comedians Laurel and Hardy, in sports activities. First the men’s ballet. How gracefully he thinks.

Santa codes a tiny Invoke Code activity to transfer the content of the CSV files into a new data table. The code is easy to understand. A loop over all CSV in the directory and the content of each CSV is added to a new data table.

//-Begin----------------------------------------------------------------

KidsWishes.Columns.Add("Kid");
KidsWishes.Columns.Add("Wish");

string[] Files = Directory.GetFiles(Directory.GetCurrentDirectory(), "*.csv");
foreach(string file in Files) {
  var lines = File.ReadLines(file);
  foreach (string line in lines) {
    if(line != "Kid;Wish") {
      KidsWishes.Rows.Add(new object[] { 
        line.Split(new string[] { ";" }, StringSplitOptions.None)[0],
        line.Split(new string[] { ";" }, StringSplitOptions.None)[1]
      });
    }
  }
}

KidsWishes.DefaultView.Sort = "Kid ASC";
KidsWishes = KidsWishes.DefaultView.ToTable(/*distinct*/ true);

//-End------------------------------------------------------------------

Proud is Santa. With two commands of the DataTable class he sorts the table and deletes the double entries.

With the Write CSV activity Santa saves the corrected content.

Last but not least Santa is watching men’s couple dancing. It’s like the dance of the decoration on our Christmas tree.

[anupamck (Anupam Krishnamurthy)]

@StefanSchnell I love your invoke code solutions!

Since the challenge is simple and easy to follow, it gives people like me a basis to follow how code snippets achieve in a small space what we otherwise use UI based workflows for.

[ziga.hanzic]

Some nice and interesting solutions here… But Grumpy will just do the work here.
Grumpy likes it simple (like version control in UiPath!)

Just let Jingle-Bell give grumpy the two packed lists, and Grumpy will make a brand new list for Santa to use!

There! All done!
And the next time you do this, just use this workflow yourself! Easy!
Grumpy.xaml (39.3 KB) project.json (1.5 KB)

[StefanSchnell]

Thank you very much @anupamck