Is RPA a good fit?


#1

We have a process in which we receive data from multiple customers where the data comes in multiple ways. There is not a template that they all use, nor can we make that happen. The data typically comes in as “columns”. By this I mean visually looks like columns, but it is not all the same from the bot point of view - some use spaces to make it look like columns, some tabs, some copy in an excel table, etc.

Coding has been a nightmare. Our developer had coded the bot to read the emails in and parse them out into a table format so that they can all use the same code after the parsing is done. The issue we have is that each customer comes in differently so we are trying to make the code work for all customers.

Is there a better approach or is this process better suited for API?


#2

To decide this by doing the process assessment whether to implement RPA or not.
Below is the template for doing process assessment. Please have a look.

General_Process_Assessment.xlsx (24.5 KB)

Regards,
Karthik Byggari


#3

I would say the better approch would be to create a macro based workbook [ xlsm file ] which will load all data in to proper excel format [ u may use the a if else condition for all possible delimiters for this process- excel inbuilt macro is very powerful in this aspect] . once it is standardised , you may proceed with the bot again

so steps will be

  1. bot to monitor inbox and download in a folder
  2. bot to trigger a macro in xlsm file , which in turn standardise all files into excel and consolidate
  3. bot continue to process consolidated file

#4

@angie.dahl

Usually I don’t think that process like this are fit for RPA.
But if you manage to create a template for the data you’re receiving using word, excel or anything that will make all the data received stays on the same format, then the RPA will be the best option for you in my opinion.

Regards!


#5

My approaches:

Share a template with all teams. Also Robot needs to be prepared to reply to customers detecting wheter the template is correct or not and providing them the correct template & examples. So you´ll have a producer robot which receives emails, double check them and interacts with teams. After emails is validated, it upload the cases to the queue to the consumer robot.

Another opction would be try RegEx and keep going unstructured. :stuck_out_tongue:

First one its the better.

Regards,


#6

Have you tried using UiPath’s OCR capabilities to parse the data? Based on your question, I assume that you are opening many files in Word (or some other text editor), each of which has a different underlying format to achieve what looks like a “column.” OCR does not care about these underlying differences, it just reads the screen as an image, so what appears as a column to a human will appear as a column to the OCR scrape. If you decide to try this route, you may need to run some tests to see if the scrape is robust enough for your purposes. You could also experiment with different fonts to see which one the OCR engine works best with.

Alternatively, you could run some trials using the “Get Full Text” activity. This will automatically parse the data into a single string. Without some examples of the kinds of inputs you are getting I cannot make a specific suggestion, but you may be able to find some expression which consistently parses your input string into something usable (for example, replacing sequences of more than one " " space character with a carriage return would take care of the inputs that use multiple spaces to make the data look like columns while leaving the others unaffected).

EDIT: Here is one easy way that I found to convert data from any Word document and get it in a consistent format. Open the document in Word, then go to Save As and save as Plain Text. This should prompt you with a menu for conversion. Select “Insert Line Breaks” under the Options menu (image below), then hit save. I created a document that used spaces, tabs, and a pasted table from Excel (all in the same document) to create one column of data, and this method gave me a .txt file delimited by newline characters with some extra whitespace. Image below of input Word doc and output .txt. As you can see, the output file has some trouble processing the table, but overall much nicer than the original Word doc. As a thought on how to fix the table formatting, if you know how many columns are expected you could include some logic to read multiple single-item rows as one row.

Options menu:

Input Word doc:
image

Result:


#7

We did something similar, where we read in emails from many different customers and each one uses a different format. Our goal was to take their data and formulate it to a standardized format for our own system.

The approach we took was to create a set of parameters that can be different for each customer and store this info in a table (ie Excel file / config). The table would have each customer in first column and each column would represent a different parameter, so you might have columns for “ColParamSrc_Amount” and “ColParamDest_Amount”, and the Src_Amount parameter would represent the column index or name that the customer uses for the amount data and Dest_Amount would be the column index or name that you want to move that data to for your format. - there are various ways to do this same thing like using one column and an array or string delimiter and parse it correctly for your code.

And, also, some of these parameters may cause the process to branch toward different sets of logic, like if one customer attaches their data and one puts their data in the body of the email, this would require a switch or something similar.

So, then it depends on how consistent each customer is on their own format, and how much of that inconsistency you can build into your parameters.

Personally, I’m wired to think if you can follow logic in your head and make decisions, it can be done programmatically, which is why I think this can be done - AND, has been done with our project that did this similar thing. But, maybe I’m just being ambitious here… :thinking:

Regards.