cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wr4
Frequent Visitor

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

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

4 REPLIES 4
DamoBird365
Super User
Super User

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:

 

DamoBird365_0-1617640561653.png

 

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
Frequent Visitor

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?

 

wr4_1-1617715976577.png

 

 

Thanks

 

Paulie78
Super User
Super User

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
Frequent Visitor

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. 

wr4_1-1617728884948.png

 

Need IDWOIDDescriptionLocationBuildingAreaArea NumberGroup/ClassificationTypeAsset TypeEquipmentSystem CategorySystem Building Component CodeIncluded In FCIStatusPriorityScoreSourceEstimated CostInflated Estimated CostEstimated CompletionEstimate NotesActual CompletionResolutionJustificationCustom CategoryActual Cost

Includes Attachment

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (1,733)