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
This is great! Thank you!
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
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?
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
|
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.
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
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