As described in this post – https://www.ehansalytics.com/blog/2020/2/15/avoid-using-excel-xls-files-as-data-source –
there are issues to be aware off when you use xls files instead of xlsx in Power Query. See also this thread
Answering the twitter started by Imke Feldmann (https://twitter.com/TheBIccountant) thread by Ruth Pozuelo (go follow her excellent youtube – channel – link) encouraged me to write this post – – as I claimed we can convert the xls files to xlsx using Power Automate.
So here is a guide on how to do it
Convert xls files to xlsx
In the scenario I will use a trigger when an e-mail is received and use a rest API provided by https://cloudconvert.com/.
OBS – This is a paid service where you pay by the minute the conversion takes – price from $0.02 to $0.01 per minute.
First we start by selecting to build an automated flow and select the trigger “When a new email arrives (V3)”
Set the advanced options to only trigger when Attachments is included and include the attachments in the following steps in our flow.
As the next step I use an Azure Blob storage to store the file in the e-mail. When selecting the output from the previous step – power automate will automatically create an Apply to each container in which we can refer to each attachment in the mail.
In the Create Blob Action I connect to a blob storage and load the attachment into a preload folder
Now add a step where we create an HTTP request within the Apply to each container
In order to use the cloud convert REST API we need first to create a Process and use the process ID to create the conversion – documentation here
In the body property you specify your own APIKey and tell the process you want to do is a conversion from xls format to xlsx.
Next – add another HTTP request
We use a POST request again
And in the URI we use the result from the previous step as it returns a unique address to the process id from cloud convert
In the Body property we specify where
Here is the final overview of the steps needed.
Now send an email to the inbox you have connected your trigger to run.
And in our preload folder we can see the files
And in the converted folder we have the converted xlsx files
Hope this can help you converting your xls files to xlsx.
This will also make it much easier if you want to be able to setup
9 thoughts on “Converting xls files to xlsx file using #powerautomate and avoid the pitfalls in #powerquery using xls”
Thanks Eric! That is super useful, and Ruth for elbowing you do get the post done 🤣
My first question was about what the cloud service can access in terms of your data. Here’s a link for anyone who’s interested, looks like they don’t see your data at all:
And second question was price, free for up to 25 conversions per day.
Thx Brian 🙂
Pingback: Converting XLS Files to XLSX with Power Automate – Curated SQL
Really great article! I will give it at try. Thanks for the inspiration.
Is there any reason (other than license pricing) that you dont use the UI flow solution: https://youtu.be/IacquJvZ20s
(The missing license is my only barrier, but it seems relatively straight forward)
Hi Kaare, my solution runs completely in the cloud where the UI flow will require it to run on your local machine where you have Excel installed.
The api also support saving the file directly to azure blob which would require some extra difficult step using UI flows.
Hi Eric, Great article! I’m running into issues with a 403 error ‘Invalid APIKEY’. I set the key up in the the Cloudconvert portal, and lovingly copied it out. Did you experience any issues with the key? ALso they seem to be on v2 of the API, I was wondering if your flow still works with the new API?
Hi Jonathan – in order for it to work with v2 of the API it has to be done in a another way. It is a bit simpler and their documentation explains it quite well and it can now be done in one flow response step instead of two / Erik
What is the cost of this blob cache using in power Automate and blob cache in Azure.
Hi – its cheap – you can calculate the price here https://azure.microsoft.com/en-us/pricing/details/storage/blobs/?&ef_id=CjwKCAjwlcaRBhBYEiwAK341jeMsDWoUFjAuSJ8fKjPj5GO766wqQrxFV912ba2HhYluaz1F0Qng3hoCvDQQAvD_BwE:G:s&OCID=AID2200175_SEM_CjwKCAjwlcaRBhBYEiwAK341jeMsDWoUFjAuSJ8fKjPj5GO766wqQrxFV912ba2HhYluaz1F0Qng3hoCvDQQAvD_BwE:G:s&gclid=CjwKCAjwlcaRBhBYEiwAK341jeMsDWoUFjAuSJ8fKjPj5GO766wqQrxFV912ba2HhYluaz1F0Qng3hoCvDQQAvD_BwE#pricing