While Automation Anywhere is fantastic at Excel Automation, there are instances where it falls short but it’s not due to negligence.

It isn’t practical to develop Actions or Commands for every conceivable operation, because maintaining that would be a nightmare, let along developing them which is why I believe it’s important for RPA Developers to learn a bit of scripting. VBA is incredibly useful, and no it’s not outdated. The only thing outdated here is people’s mentality.

Chasing after the new and improved won’t guarantee success.

Find something and stick with it until you become an expert at it, before branching off.

Coming back to VBA, as long as Microsoft Excel exists, it will always be around to haunt us with its bulky syntax.

If you still operate under the assumption that Excel, Macros and VBA are slowly going obsolete, then here is a YouTube channel that just might change your mind.

But I Don’t Like Coding.

If that’s the case then you may conclude that the process isn’t feasible and go along your merry way.

But seriously, you don’t need to like coding to learn it.

Lots of folks these days rush into things without dedicating themselves to the grind.

It’s when you get better at something that you start liking it.

Nothing just clicks – if anything, you have to spend some time getting to know each other before moving onto anything serious.

She won’t reveal her secrets to just anyone, you know…

What were we talking about again?

Pivoting Back To the Table at Hand

If you are a Business Analyst, you know how indispensable a tool pivot tables are.

If you aren’t a Business Analyst, that means you know nothing about Pivot Tables and that is okay.

I didn’t know what they were either, but that was until a client approached me to develop an automation involving Pivot Tables which set off a panic attack because I didn’t know what the heck they were or how I was going to develop the solution.

Why did I experience a panic attack?

Because I was the Developer, Business Analyst and Solution Architect receiving a lunchbox salary package that wouldn’t even get me a proper lunchbox.

But hey, at least I achieved unity in trinity.

I was about to resort to Plan B(ackout), but my manager went with Plan A(bsolutely Feasible) so I had to improvise and figure something out.

Spoiler alert: I did.

Pivoting Back Yet Again to the Table at Hand

To put it simply, a pivot table is the easiest way to get insights from vast quantities of data.

Want to find out the total sales per product?

Pivotize em’.

Need to figure out which product had the highest turnover?

Pivotize em’.

Need to figure what the profit percentages are for each product to the total turnover?

Pivotize em’.

Now that you have an idea of what a pivot table does, let’s move onto the next segment.

Solution Time!

There are many ways to achieve the solution – the most common being preparing a template and simply copy excel data to it.

That is the most convenient way to automate the pivotization(a totally legit word) of data, but if I did that then I wouldn’t be able to scare the living daylights out of the interns.

People these days think RPA is just click and drag, and something has to be done about it, but that is a topic for another day.

Strange As It May Sound…

XML can be used to analyze data, however I’m using that term loosely.

XML is fantastic at storing and transferring structured data, which is what it was built for, but that doesn’t mean we can’t get a little creative with it.

But the real reason I decided to explore XML again was because I did such a horrible job the last time around.

I will show you how to convert Excel to XML, and in the next post I will show you how to convert that XML data into Pivot table-ish data.

Reason I split things up into digestible segments, is so that you can…you know.

Digest them.

Excel Data To XML(I Won’t Go Off Topic Again Promise)

First, click that Shiny Button below.

A workbook will magically appear in your Downloads Directory.

How does it work?

Nobody knows!

Stop Talking To Me Like I’m Nine.

Alright, alright.

Log into your Control Room and create a New Taskbot by heading over to Automations.

Now, drag in an Excel Advanced: Open Action and browse for the File we just downloaded.

Next, use the Excel Advanced: Extract Sheet as Data Table Action(make sure Contains Headers remains unchecked)before wrapping this section up with an Excel Advanced: Close Action.

This works quite well, but if you want to be specific about the column values, you will have to either use a Loop: For Each Row in Excel Advanced and assign the items you want to the XML segment, or you can Extract the columns you want by using Excel as a Database.

Now that we have our Table, all that’s left is to create our XML, but before we do any of that, you have to understand how XML is structured.

It usually comes with a declaration but this is optional.

Then comes Elements.

<ElementName>NotElementName</ElementName>

The element and attributes are what make up the body of our XML, which assumes a tree like structure if you squint hard enough.

This bit of logic is used to convert the Headers into a list of Element Names which we will use while populating the XML with data.

The Regular Expressions is used to remove any special characters and spaces because XML doesn’t like it when her Elements try to stand out from the crowd.

Next up are the Attributes, which belong inside Elements.

<Element isThisAnAttribute='Yep'>Not an Attribute</Element>

But these elements can’t exist by themselves, they have to belong to something.

That something is what we call the root.

<root>
       <Element Attribute='Howdy there'></Element>
</root>

The root is what compartmentalizes the XML and the story usually ends there, but since we are converting Excel data to XML we have to encapsulate every row item collection into a subroot of its own.

This can get confusing, as it slowly starts to resemble an onion with multiple layers.

<?xml version="1.0" encoding="UTF-8"?>
	<excelData> //main root
		<excelRow> //subroot
			<Product>Carretera</Product>
			<Discount_Band>None</Discount_Band>
			<Units_Sold>1618.5</Units_Sold>
			<Manufacturing_Price>3</Manufacturing_Price>
			<Sale_Price>20</Sale_Price>
			<Gross_Sales>32370</Gross_Sales>
			<Discounts>0</Discounts>
			<Sales>32370</Sales>
			<COGS>16185</COGS>
			<Profit>16185</Profit>
			<Date>01-01-2014</Date>
			<Month_Number>1</Month_Number>
			<Month_Name>January</Month_Name>
			<Year>2014</Year>
		</excelRow>
                <excelRow>
			<Product>Carretera</Product>
			<Discount_Band>None</Discount_Band>
			<Units_Sold>1321</Units_Sold>
			<Manufacturing_Price>3</Manufacturing_Price>
			<Sale_Price>20</Sale_Price>
			<Gross_Sales>26420</Gross_Sales>
			<Discounts>0</Discounts>
			<Sales>26420</Sales>
			<COGS>13210</COGS>
			<Profit>13210</Profit>
			<Date>01-01-2014</Date>
			<Month_Number>1</Month_Number>
			<Month_Name>January</Month_Name>
			<Year>2014</Year>
		</excelRow>
	<excelData>

Ready For the Main Logic?

If you’ve noticed, I have added a lot of tabs and newlines to improve its aesthetics and readability.

It isn’t necessary to include them, so don’t let the bulky syntax scare you.

First, we have to add the opening and closing subroots, which is represented by the items coloured in green.

$str_xmlData$$String:Tab$$String:Tab$<excelRow>$String:Newline$ //opening subroot tag
$str_xmlData$$String:Tab$$String:Tab$</excelRow>$String:Newline$ //closing subroot tag

The item highlighted in yellow is the content that will be present in between the element tags i.e., the actual excel data for that particular cell.

The item highlighted in blue is the counter being incremented. The counter is used to reference headers from the list we added it to earlier.

It’s initialized during each iteration and you have to figure out why that is(#Homework).

Finally, a closing tag for the main root is added and the output is written to a text file using the Log To File Action.

The Log To File will be removed in the next article, because we won’t need it.

Here is the entire workflow in List View:

In Conclusion

XML is rarely used for anything other than jamming it with Configuration Data.

I wanted try something a little out of the box, and we will explore that next week.