We have some exciting news to share with you. We have started migrating features from our sister company, Docparser, over to the Mailparser platform. These features will give you a much more powerful set of tools for extracting and manipulating data from line item sections in your emails and documents.
What does this mean for you?
Many of you receive purchase orders and invoices by email and need to extract the data from email attachments and import them into your internal CRM solution. In this case, extracting data successfully from those email attachments would not be enough. You also need to put data in the correct format, as well as make sure that missing data has been set to default values, column headers have been specified, and output table columns are in the correct order.
These filters have been available to users of Docparser for some time. So we decided to port all table editing functionalities from Docparser to Mailparser.
Here are some scenarios that you might have encountered in the past:
Set values to empty cells
You have built your table but there will be times that some cells at a specific column may miss value. Fill Empty Cell With filter in the Refine Cell Values Menu proposes 3 ways to fix this problem for you.
- Set the value of an empty cell to the value of the cell in the same column in previous row
- Set the value of an empty cell to the value of the cell in the same column in the next row
- Set the value of an empty cell to a default value
Here is an example that demonstrates how this filter works:
Insert Column and Fill empty cells with some values
You are ready to export data to your CRM but it misses some important information such as country code. You need to add a new column and fill values for that column. Using the Insert Column filter in the Remove, Select & Insert Menu, you can add a new column at a specific position in the table.
Here is an example where a new column was added after column 3 in the table:
Then Using the Fill Empty Cell With filter in the Refine Cell Values Menu, you can fill empty cells with a static value.
Swap values between 2 columns
Your table is ready to be exported but you are not happy with the order of the columns. Within the Refine Cell Values Menu, select the Copy & Move Cell Content filter, then select Exchange Cell Content to swap values between 2 columns.
Copy values from one column to another
There may be cases where you want to append values from one column to another or replace values entirely from one column with another. Using the Copy & Move Cell Content filter within the Refine Cell Values Menu, you are able to easily append content to the beginning or end of the cell content of another column or replace entirely with the content of another column.
Find Pattern Matches
Let’s imagine that you have extracted data from an email body or email attachment and constructed a table template but wish to export a subset of the data. To filter table rows, you can apply a REGEX pattern to the table using the Find Pattern Matches filter in the Refine Cell Values Menu. Here is an example where we want to extract rows where code in the first column starts with 886-.
Then using Filter Rows by Values in the Remove, Select & Insert Menu, you can remove all rows that do not match your REGEX pattern.
Group & Merge rows
Often, purchase orders and invoices contain tables with sub-rows related to previous rows. These rows can be easily read and understood by humans but what about a software program that should extract this data and make those connections? What about cases where certain rows are not followed by sub-lines?
The Group & Merge rows filter within the Split, Merge & Transpose menu offers you several options to handle different cases.
First of all, you should be able to define how Mailparser should detect if there is a sub-row or a new row should be created. You can also specify when the current row ends. Once defined, you decide how the sub-row data should be merged into the main row. One choice is to append sub-rows to the end of the main row as following image depicts:
There is also an option to let you append cells from sub-rows to the main row that are not empty.
Another option is to merge the contents of cells in subrows to the corresponding cells in the same column in the main row.
Fill Cells With
Let’s say that you have defined the structure of the table but you notice that the code section in invoices that you receive does not match with the coding system defined in your payment solution. In the example below, you need to add a suffix to all product codes that you receive.
The Fill Cells With filter within the Refine Cell Values menu allows you to add a text value to the beginning or at the end of a specific column.
Here is an example where _OPL has been added to the end of the product codes:
Keep Table Section
As its name suggests, the Keep Table Section filter lets you extract a section within a table. Here you have several options available.
Section Starts & Ends Where: You can define the starting and ending rows based on your defined criteria.
Fixed Row Range: If you know the row numbers that you want to keep, then you can select them using this filter.
Crop Rows from Beginning & End: This option is useful when you need to eliminate a header from the beginning and the total section from the bottom of the table.
Keep First / Last [X] Rows: Let’s say that you are not sure how many rows will be within your table but you are only interested in header data at the beginning of the table or the total section at the bottom of the table. This filter will let you extract these rows easily.
Section Starts & Ends Where
Here is an example that demonstrates how you can define the section of the table that you want to extract:
Fixed Row Range
Here is an example where you always only need to extract rows 2 to 4.
Crop Rows from Beginning & End
Here is another example that header row should be cropped from the output table.
Keep First / Last [X] Rows
In the example above, we are only interested in extracting the last row from this table:
Name Column Headers
Finally, setting clear column headers will help understand table contents better. Whether you will download table content or integrate them with your CRM system, specifying understandable column header names will decrease the risk of errors.
Using the Name Column Header filter in the Split, Merge & Transpose Menu, you can easily rename column headers.
We will continue working on upgrading Mailparser with more filters to help you to make your data harvesting and data injection processes easier and more efficient. Please do not hesitate to contact us if you have any questions or comments about using Mailparser.