Power Automate

Unanswered

Convert xlsx file to csv when file is automatically added to SharePoint Library

(0) Share

Report

Posted on by wr4

Hi,

I have a SharePoint library where an xlsx file is uploaded every morning. I need to convert that file to csv.

I know that the file can be added in OneDrive and if there is a table within the file then that can be used to convert it to csv.

However, I don't know the name of the file until is it uploaded in the morning and each file has a different set of numbers at the end (example: CFD Needs Report Daily_1286512763.xls)

Is there a way to run the flow once a new file is uploaded in the specific folder and then read the data in the file (or create a table) and then convert it to csv?

Thanks

Categories:

Building flows

All responses (7)

Answers (0)

Sort by

DamoBird365 8,942 Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @wr4

Your tricky bit here is getting the data from the Excel file because unless it is in a table, you will need to use an Excel Script which can be called from Power Automate but requires some basic knowledge of Javascript/TypeScript. Triggering a Cloud Flow on a new file being created is straight forward, just use the when a file is created in a folder trigger:

I've a demo of Excel Scripts here where i populate an excel template with data from the Cloud Flow, but the principle is similar. You can use TypeScript (details here) to read the data from the Excel file and pass it back to the Flow and then create a CSV. Equally ou could get Excel to that fork for you and just get Excel to pass back the string.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here
wr4 4 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @DamoBird365
Thanks for your response. If I get the file to come in with a table how would I convert it to csv. Currently it is converting it but to a file format. I used 'Get files (properties)' to pick up all the xlsv files that start with the same beginning name.
If I only use 'Get file content' then I have to pick a specific file and I don't know the name of the file since it is added the next morning. If I use dynamic content in this action I get the error that it has to be an array.
How do I get the new file to be a csv?

Thanks
Paulie78 8,424 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

I've got an OfficeScript which pulls out the contents of an Excel file to a JSON array, should work for your file (but a table would still be better). Is the format of the file always the same every time (same number of columns etc).

Ideally, if it is possible, if you could send me a sample of a typical file (or post it on here). I can test it, see if it works and then adjust if required, then post you back a flow that does what you want.

wr4

4 on at

Like (0)

Report

Copy link

Link copied!

Hi,

Thank so much for your response. Yes it has the same number of columns and headers. The number of rows of data is different each time. I pasted the column headers below.

How would it work if the file comes in with a table? Currently, I filter out all the files that start with "CFD Needs Report Daily_" but the create file is not displaying as csv.

Need ID

WOID

Description

Location

Building

Area

Area Number

Group/Classification

Type

Asset Type

Equipment

System Category

System

Building Component Code

Included In FCI

Status

Priority

Score

Source

Estimated Cost

Inflated Estimated Cost

Estimated Completion

Estimate Notes

Actual Completion

Resolution

Justification

Custom Category

Actual Cost

Includes Attachment

sstone 4 on at

Like
a
(0)

Report
Copy link

Link copied!

I have the same question - my file will always be formatted the same but it won't come in an excel table. Could I see the code you've written?
DamoBird365 8,942 Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @sstone

Ive since done the following video Add a Table to an Excel File
https://youtu.be/ZejstvPiFZw and the script is on the description.

Damien
sstone 4 on at

Like
a
(0)

Report
Copy link

Link copied!

This is great! Thank you!