✓ RPA Challenge 7 - Corrupted Data

Dear challengers!

Ready for the new RPA challenge? I hope you do because this one is a delicate one!

Goal of the challenge

The internal system got crazy and mashed the injuries report file into one big mess. Only a few rows remained in the correct format. First and second. And from the third one, the system went crazy. Cells got mixed in every possible order. Thankfully, only horizontally, so each row still belongs to one listing.

The goal is to repair the Excel table structure.
If you are good with regex, then this should not be a complicated challenge for you. You can solve it also using some basic string methods! The main task is to find a way how to recognize in what column the value goes. Think about patterns that are specific for each column.

Input data

corrupted.xlsx (11.5 KB)

Outputs

Use the pattern from the corrupted file headers to create a new one with cells in the right places.

Challenge difficulty: harder.

How to participate

When you are happy with your robot:

  • post output file here
  • send .xaml (or another source) file as a private message directly to me (click my profile picture → Message)

First three correct, original solvers earn 3 robopoints, next get 1 robopoint.
I will hold a leaderboard with your score and update it. Those on top positions will be badged by a special challenger badge :slight_smile:

The challenge deadline is 2.5. by the end of the day. You can still submit your projects after the deadline but you cannot earn robopoints. A new challenge is published every week or 2 weeks so don’t worry if you did not catch this one. Keep in mind, your submitted files may be published after the deadline so others can learn from solutions. :slight_smile:

PS: Are you having specific trouble with your other projects? Do not hesitate to Ask in our Ask category!

3 Likes

Very cool challenge again. Here attached my code.
challenge7.zip (18.1 KB)

2 Likes

Hey @roman.hruska , this was a really tough one, even it does not seem to be in beginnings.
I decided to go with regex, then I fought with Excel formatting, then with the logic how to extract it… This took me more time than I thought, but I could not stop before I can solve it. I learned couple of new things during that, so thank you for that! My result file is here:sorted_data111.xlsx (8.9 KB) , xaml in your inbox. woo. :clap:

2 Likes

Tick - tock! Time is up! :watch:

I have to say this was a tough one. But on the other hand - there is more than one solution out there!
The solutions provide by @radim.dom and @pmontez are correct!

I would like to attach my example solution in UiPath. I went with this way of solution:

  • 1st - I had to do one step manually and to save the XLSX file as CSV, since the formatting of the cells caused me trouble to extract the text with UiPath in same format as it is visible for human.
  • merge whole row into one string
  • regular expressions applied to that string (I saved them into XLSX file and iterate through them)
  • cut the founded part and associate it with the correct column
  • and some little magic here and there.
    Check the code: 0701_solution.zip (61.5 KB)

Thank you for all your tries and I hope you practiced something new! :slight_smile:

Check next challenges: :eagle:
RPA Challenge 8 - Word Chain
RPA Challenge 9 - Arkanoid!

1 Like