Skipping Excel 2

In this post, the second in the Skipping Excel series, I’ll walk you through the details of the next iteration of this process.

In case you are just joining this series, in the first post Skipping Excel 1, I reviewed the manual process of getting my e-commerce transactions into QuickBooks Online. It took about 30 minutes a day to copy/paste. Then, I got it to 15 minutes a day by downloading the daily transactions in a batch to Excel, cleaning up the file, and then uploading it to QuickBooks Online. Then, I got it to 5 minutes a day by using Power Query to clean the file.

Now, in this post, we’ll take the next step of skipping Excel all together to completely automate the process.

Video

Flow

Now, the best case scenario is that the two applications have a direct integration. In my case, the two applications I use didn’t have a built-in direct integration. That means I needed to use an intermediate step … Zapier. In case you haven’t explored Zapier, it is an online platform that connects apps. There are thousands of Zapier-supported applications. And it can pass information between them.

In my particular situation, while QuickBooks Online does connect to Zapier, my e-commerce system does not. That means I needed a way to get my e-commerce sales transactions into Zapier … for that I used another intermediate step … Mailparser.

In case you haven’t explored Mailparser, you basically send email to it and it will locate and extract the desired data fields. As you guessed it, Mailparser does connect to Zapier. So, here is how data could flow between my e-commerce system and QuickBooks:

When a sale is made in my ecommerce system, it can send a confirmation email to Mailparser. Mailparser can extract the key data fields out of the email text and forward them to Zapier. Zapier can then send them to QuickBooks Online.

In this post, I’ll walk through the Mailparser details. In the final post, I’ll walk through the Zapier details. So, let’s jump into Mailparser and see how it works.

Mailparser

After creating an account with Mailparser, you’ll create a new Inbox by clicking the Create Inbox button.

Once you do, mailparser will provide you with the corresponding email address. It will show a random series of letters, perhaps something like:

[email protected]

The idea is that you will have your system automatically send specific transactional emails to that inbox. You can create one new inbox for each type of transaction. Each transaction type will have a unique email format with different rules for extracting the data fields.

For example, it could be that your system automatically cc’s that inbox on all new sales. Or, perhaps you define a rule in your email program based on sender/subject to forward to Mailparser. Regardless of how you get a copy of the emails sent to your new Mailparser address, the next step is to define the rules that teach Mailparser how to extract the key data fields you need.

To do so, we’ll need to send a sample email to Mailparser and then define the rules.

Send Sample

After you create your new Mailparser inbox, you’ll want to send a sample email to it. So, you simply forward a copy of the type of email you want this specific inbox to handle.

Once you’ve sent the sample, Mailparser will take a look at it and do its best to automatically create the rules that extract the data fields you are likely to want.

For example, I sent a copy of the order confirmation email:

And Mailparser automatically created rules in order to parse out the most likely data fields.

For example, here is what a parsed email looks like … you’ll notice the data fields are highlighted:

By default, the automatic rules are very impressive. However, you are able to remove unnecessary data fields, edit existing rules, or add a new custom rule.

Think of it like this: each data field is extracted using a sequence of filters. So, Mailparser will essentially parse each message multiple times. Each time, it uses a set of filters that locates one specific data field.

Rules

Here is our list of rules … one rule per data field:

Each rule has a series of steps or filters.

Custom Rule

For example, let’s say we send Mailparser copies of sales emails with the following format:

Let’s say we want to extract the product code but, by default, Mailparser doesn’t recognize it as a field. We could create a new custom rule to extract the Product Code (EUV1OD1) field. We could apply filter steps such as these:

1. Remove all lines before the word Product
2. Remove all lines after the word Sub-Total
3. In the remaining line, remove all text before the –
4. Extract the remaining text until the next column

Or something similar. There are many filters available, and many ways to go about defining the rules. Mailparser includes a comprehensive set of filters that you can use to locate the exact piece of data you need.

Next Steps

Now, as emails are sent to that specific Mailparser inbox, Mailparser will extract the data fields based on the set of rules. In the next post, I’ll show you how to send the parsed email data to Zapier and onto QuickBooks Online (or just about any other Zapier-supported online application).

Comment below

If you use Mailparser, please share by posting a comment below. Or, if you have any basic Mailparser questions, let me know by posting a comment below.