Read, Write, & Manipulate Data in Excel with Kofax RPA

Kofax RPA is a powerful robotic process automation platform with a simple drag-and-drop software robot designer. Kofax RPA automates manual tasks and activities like accessing web portals or desktop applications, copying and transposing data, and completing complex logic that replaces the need for human-driven actions.

In this Office Hours presentation, RPI Consultants provides demonstrations and an interactive Q&A related to reading and writing data in Excel using Kofax RPA. This function is useful for many solutions and processes, such as accessing the National Change of Address database to validate vendor or employee information.

Download PDF

Quinn Huntsman:
All right, again, thanks everyone who is joining us here today for another one of RPI’s Office Hours. Today, we are going to be showing some cool automation with Kofax RPA and Microsoft Excel. First, I want to tell you a little bit about what Kofax RPA is just in case you don’t know. It is an RPA tool, which means robotic process automation. It has an awesome single or simple to use drag and drop interface designer. You don’t really have to know how to write code or anything to interact or build with it, and it eliminates manual tasks that end users would be typically doing like logging into websites, exporting data.

It can write reports. It can read from reports, all kinds of cool stuff. Today, we’ll be focusing on using the embedded Microsoft Excel driver and fewer in the design studio here to interact with Microsoft Excel. Without further ado, go ahead and dive in here. We want to tell you about some of our upcoming webinars in Office Hours. We have a digital signing solutions webinar coming up here shortly, May 6th at 11:00 AM Central, followed by a 1:00 PM webinar, what to do with SharePoint, and then we have another Office Hours about halfway through May on digital signatures.

Then at the end of May, we are going to circle back around and do another RPA Office Hours on data integrations, which I really look forward to doing that. That’ll be working with APIs, writing to databases, that kind of thing. If you like this one, we hope to see you at all the webinars and including the end of May one on RPA as well. We also wanted to tell you about we host a Kofax RPA workshop. This is an on-site engagement. That’s only one day long, but we cover quite a bit in it. It includes some post on-site documentation, and road mapping gives you and your organization an idea of what you could do in the future with RPA.

We do help set you up with a one-year development license from Kofax for RPA. This is really easy to obtain. You can fill out a form on Kofax’s website, and they will send you the installers for Kofax RPA and the licensed file and help me get going there on that. We’ll also demo some products up and training as well as do some actual robot building and prototyping to show you how RPA can help provide some automation within your organization. If you are interested and would like to schedule or ask more about the Kofax RPA workshop, please do contact us at [email protected]

A little bit about me. My name is Quinn Huntsman. I’m a business analyst with RPI. I’m about halfway through my second year. I’m a certified Kofax technical solution specialist for RPA. I love working with RPA. It’s a lot of fun to build with RPA, and you’ll see that today. I also have experience in perceptive content upgrades, administration and troubleshooting. A fun fact about me is I’m a lover of Queen as you might be able to tell by the shirt. I love robots, food and playing video games with my friends.

We want to use this opportunity to remind you that this Office Hours for Kofax RPA and Microsoft Excel is meant to be interactive, so we encourage you to submit questions, challenges, any kind of specific product functionality that you’re curious about during our Office Hours presentation. We’ll do our best to field those questions as we go here. A little bit more about Kofax RPA before we get started. It allows you to build software robots that never stop working and never make mistakes depending on how you built them, obviously. It allows you to replace manual tasks, decisions and activities such as signing into web portals and desktop applications.

There are two different kinds of robots you can decide to build, depending on if you’re working with a desktop app or a web portal. You’ll see that here in a bit. It allows you to perform front-end activities, functions and lookups, and work with APIs and relational databases as well. It also comes with the awesome drag and drop designer, where you don’t really have to know any code to build robots, and then it’s also part of the overall Kofax Intelligent Automation Suite such as total agility.

Again, there is a free trial license you can obtain from Kofax. You’ll submit a form, and they’ll provide you with the installers as well as the license file. You can use that to develop pretty cool stuff for yourself. I encourage you all to check that out. A little bit about what we’re going to do today specifically, we’re going to demo reading and writing data into and from Excel. We are going to have a vendor data sheet. These vendors are a mix of active and inactive vendors. What we’re going to do is split out the active from the inactive into two separate spreadsheets.

We’re going to write all of their pertinent metadata as well to that spreadsheet. We’re actually going to have RPA save those files on our desktop. It’ll show some of the decision points that RPA can handle. It will show some of the Excel capabilities on where RPA can work with reports, all kinds of cool stuff. Just one second, switch over here to our sandbox, and we can get started. Alright, so here is the RPA workflow designer. This is the IDE called Design Studio. I want to give you just a quick tour of what you’re seeing here, because I know it can look like a lot right out of the box.

On the left over here, if you’ve ever worked in something like Visual Studio, this is our project view. This is how we stay organized by different projects here, and then all the components that make up these projects such as the robot files themselves, different types, which are like the data model backbone of what you’ll be working with for variables, and then other things that we probably won’t get into today too much such as database mappings and device mappings, which allow you to point to different databases or remote devices so that you can use the desktop automation service to hook into those.

Here in the very middle is the workflow designer. As you can see, it is comprised of all these different boxes and shapes. These are steps or group of many steps. The lines here show the progression of the robot and the flow of it. You can see there’s some different branching here, which demonstrate the decision points and logic that you’re able to implement with Kofax RPA. At least, I think, it’s very user friendly, easy to pick up and learn quickly. Again, you don’t have to know any code really to work with this. There are points where that’s a benefit, but you don’t need to know it at all.

Down here in the bottom middle of the screen, it’s showing our different tabs that we’re working with. If you’re working with a website, it’ll show the website here that you’re working with, or if you’re working with a desktop application. Here, in our case, we’re going to be working with three different Excel sheets, so you’re going to see a tab for each of those. Once we get started here, it’ll show it as we go in real time. Over here on the bottom right, we have our variables pane. You can see that we are going to be working with and storing some vendor data.

This vendor variable has been created from a type which again is like a data model. These are all the different properties of a vendor that we’re going to be extracting, storing and working with here. The main thing that we’re going to be working with is a vendor status, which will show us either A for active or I for inactive. That’s what we’ll be using to have RPA perform some decision point branching. All right, so first, I want to show you the finished product here, talk a little bit about it, and show you it run just so we can see what we’re going to be building and what you can expect.

We have three different spreadsheets that we’re going to be using to put data out into when we’re done. One of those spreadsheets will contain all the active vendor, and then one of the spreadsheets will contain all of the inactive vendors. Sorry, I thought we had a question there. It’s just the other notification. Get back on track here. You can see there’s some steps. We’ll build these in a second, so I’m not going to click through all the configuration for each of these yet. There are some steps for opening these different spreadsheets using these variables over here.

These are variables of an Excel type. That might sound confusing, but really, what that means is we have this container that RPA is expecting to be able to output an Excel sheet from or for you to work with as an Excel sheet when you are developing here in Design Studio. I can click through them here to start giving you an idea. We’ll prepare our execution. That allows us to start working here in design mode. You can see highlighted on this open active list step, that’s going to open my blank Excel variable to start giving us a sheet to work with, and then we have some steps for each of these to set the headers for the Excel sheet.

This is the data. These are the column headers that we’re expecting to write data into the sheet for. We’re going to do this process twice to give ourselves two Excel sheets. You can see them pop up here in the window. We have an active list we can write to later on, and an inactive list we can write to later on. From there, I’m going to open up my full vendor list. This is the Excel sheet that contains both active and inactive vendors. What it’s going to be opening here is literally this xlsx we have on my local workstation here.

As you can see, there’s a mix of active and inactive vendors here. This is what we’re going to use to read from and split all these vendors out. We’re goin