Extract one Excel sheet (on Sharepoint) to a CSV file - Power Automate flow.

(0) Share

Report

Posted on by maciekomis

Hello,

On Sharepoint, I have an Excel file with multiple sheets.

What I need to achieve is to extract ONE of the sheets from said Excel file, convert it to CSV file and place in a different folder on the same Sharepoint.

I could only find posts about converting the whole file Excel file to CSV, not just one of the sheets.

Ideally, to make is scalable, to select the sheet, I could use something like extract sheet with name like "Sales Report*".

The * meaning the sheet name must begin with "Sales Report".

Can anyone help?

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (0)

maciekomis 2 on at

Like (0)

Report

Thank you for your input, but I'm looking for a subcription-free solution.

Was this reply helpful? Yes No
Suggested answer

M.COONAN 4 on at

Like (1)

Report
It took some creativity and a few more steps than I would prefer, but I found a solution without any loops, tables, office scripts, hard-coded columns, or 3rd party components:

NOTE: This is set up to export each Excel sheet to a CSV - if you just need the first sheet, or a specific sheet by name, you can just add a condition to the loop.

Define variables up front: folder and file in SharePoint / OneDrive, a blank Sheet ID and a unique placeholder for use later

Use "Get Worksheets" to list the tabs in the workbook and (critically) get the OData path to use them with the Graph API (enter "item()?['@odata.id']" in the formula editor)
A few OData ID / URL notes, for your reference (no action required):

The OData ID will look like /drives('{drive-id}')/items('{item-id}')/workbook/worksheets('{sheet-id}')

The API equally accepts /drives/{drive-id}/items/{item-id}/workbook/worksheets/{sheet-id}

For a SharePoint library this will be prefixed by /sites('{site-id}') or /sites/{site-id}

Use the Graph API usedRange endpoint to query the values in the data range within the worksheet

Parse the Graph API response for downstream processing using the schema below

{ "type": "object", "properties": { "@@odata.context": { "type": "string" }, "@@odata.id": { "type": "string" }, "text": { "type": "array", "items": { "type": "array", "items": { "type": "string" } } } } }

Join the elements within each nested array, then join all rows with a line break. To handle special characters within the array values, we need a creative formula to double the double quotes then enclose each value in double quotes itself. This is where the placeholder comes in to avoid replacing the double quotes enclosing the field.

concat( // Add surrounding quotes to the row values '"', // Replace placeholder with double quotes replace( // Replace each double quote with 2 as escape character replace( // Join nested array with placeholder and comma separator join( item(), concat( variables('Placeholder'), ',', variables('Placeholder') ) ), '"', '""' ), variables('Placeholder'), '"' ), '"' )

In the join action, add a line break by using the "return" / "enter" key rather than entering a character like "\n" or "\r\n".

Lastly, use the output of the join action to create (or update) a file in OneDrive or SharePoint.

Easy as that!

2 people found this reply helpful.

Was this reply helpful? Yes No
ulflun 3 on at

Like (0)

Report

Nice work @M.COONAN. I'm new to this - when you say "Get Worksheets" to list the tabs in the workbook and (critically) get the OData path to use them with the Graph API (enter "item()?['@odata.id']" in the formula editor) - would you mind explain further?

I've tried to browse files and also put item()?['@odata.id] in the Code view I can see parameters like "file", "Source" etc. but I don't seem to be able to trigger listing Sheet IDs anywhere.

Was this reply helpful? Yes No