This week, I ran into a problem where Power Automate corrupts the contents of an Excel file used in a flow. I knew I had seen this problem before, but I couldn’t remember what caused it. Since I don’t want to solve this problem a third time, it gets a blog post.
I initially started blogging because I got tired of resolving the same problem over and over again. Instead, solve it once, write about it. Then, not only is the solution cemented in my brain but it’s available for other people and Google is a much better search engine than Windows File explorer.
Use Case
Here’s a look at the section of the Power Automate flow that was giving me problems. Ultimately, what the complete flow does is monitors an Outlook inbox for the arrival of a specific email. Then, it grabs the Excel attachment. If the Attachment Name contains the words ‘Inventory Position’, we process. The Excel attachment is unfortunately in .xls format, which no one likes anymore. So, PA saves the .xls file on our shared drive. Then, I use a PAD flow to convert the file type from .xls to .xlsx, which I wrote about here. There is also an API call to Alteryx to process the data in that Excel file. Then, PA deletes both the .xls and the .xlsx file, as they aren’t needed anymore.
The Problem
My problem is that the Excel file (in the .xlsx) format came out looking like this, which obviously caused my Alteryx job to fail. Alteryx was expecting actual data, not this. The save .xls file might have also looked like this. I didn’t test for that, I just saw this in the .xlsx and stopped because I knew I had seen this before.
The Solution
I hit up my coworkers to ask if they remembered this error, and eventually, something one of them said reminded me this happens when you Resubmit a flow with File System actions. Now, I can’t precisely explain the conditions under which this happens. Is it the Create file action PA doesn’t like? Is it something else? Could I prevent this by adding another action? If you know, please comment. Regardless, the solution to this problem is to send forward a new trigger event to run the flow. When you resubmit, this is what happens. Now you know how to deal with the problem with Power Automate corrupts file contents.
Pingback: 4 Functions to Simplify Date & Time in Power Automate » The Analytics Corner