How to Convert Excel XLSX to CSV in Power Automate
If we’re working with spreadsheet data on a daily basis, we’re probably quite accustomed to converting between Excel XLSX format and CSV.
On the one hand, XLSX supports the rich-text formatting and advanced data presentation features we depend on the Excel application for, but on the other, CSV simplifies the storage of tabular data into lightweight, plain-text files that travel faster across networks and boast compatibility with a much wider range of applications. Both formats have distinct advantages, and the format we use varies from context to context.
Convert XLSX to CSV in Power Automate
Converting to and from CSV format is a necessity in the data world — but thankfully, it doesn’t need to be an exceedingly technical process. In Power Automate, we can handle XLSX to CSV conversions without writing a single line of code.
In this article, we’ll demonstrate an easy-to-use solution for converting multiple XLSX documents to CSV format at once. After we make our conversion, we’ll have the option to save our resulting data as .csv files (this returns Microsoft Excel CSV files) or .txt files (this returns comma-delimited data in a plain text file).
We’ll use a manually triggered, instant cloud flow in this context, but we can easily automate our flow using similar logic later on.
In my example flow, I’ll be converting a folder containing four XLSX workbooks to CSV, and I’ll be saving the resulting CSV files with .csv extensions in a separate folder.
We’ll begin our flow by retrieving XLSX files from our target folder. We’ll first use the SharePoint List folder action to grab file IDs (and other useful metadata) from each file in our XLSX folder.
Next, we’ll add a SharePoint Get file content action. We’ll use the file IDs (Id) returned by the List folder action to bring file bytes from our target folder into the flow. Because we’re dealing with multiple files at once, Power Automate will automatically wrap our Get file content action in a For each control.
Within the For each control, we’ll now add our XLSX to CSV conversion action. To find this action, we’ll search for “Cloudmersive” connectors, and we’ll locate the Cloudmersive Document Conversion connector.
We’ll click “See more” to view the actions list, and we’ll then search for our XLSX to CSV action. This action is called Convert Excel XLSX Spreadsheet to CSV. Once we find it, we’ll go ahead and select it.
Before we can configure our XLSX to CSV conversion request, we’ll first need to create and authorize our API connection. To do that, we’ll need a free Cloudmersive API key, which we can get by creating a free account on the Cloudmersive website. With a free API key, we’ll be able to make up to 800 conversions each month without any additional commitments.
Once we’ve created our connection, we’ll enter our XLSX file bytes and file names into the Input file and File Name parameters. If we click “Show all” to view the advanced parameters, we’ll find an option to set the text encoding for our resulting CSV documents. The description for this advanced parameter gets cut off unfortunately; the default text encoding is UTF-8, and other possible values we can set are ASCII and UTF-32.
To wrap up our flow, we’ll add a Create file action within the For each control, and we’ll use OutputContent from our Convert XLSX to CSV action to set the contents of our new files. As I mentioned earlier, I’ll be saving my new documents as .csv files, which means they’ll be automatically formatted as Microsoft Excel Comma Separated Value files in my file system.
In my example, I’m using Power Automate’s Slice function to rework the original file names into strings that no longer contain the original .xlsx extensions. I’m adding the new .csv extension to the end of that string.
After we save and test our flow, we’ll find our new .csv documents in our target folder, and we’re all done!
