Creating CSV that is using filtering

[alex5p]

I have a flow currently that will go into our sharepoint list and filter on certain columns, create a csv file, then email to a certain user.

I need to be able to filter on the excel sheet. Is there any way to get multiple sheets with my csv file? I am trying to filter on a column called "people" and have each person it finds to put a separate sheet. 

I know the "get Items" sharepoint has the filter query which i am currently using to filter on a column called "department" So when I look at it right now it shows only the items for our IT department.

[manuelstgomes]

HI @alex5p 

Parsing a CSV is tricky, so I created a template and an article explaining how to do it:

https://manueltgomes.com/microsoft/powerautomate/how-to-parse-csv-file/

Can you please check if and let me know if you have any questions?

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

[alex5p]

I think that could be useful. Do you know if there is any way to create different sheets?

[manuelstgomes]

HI @alex5p 

Yes, there is. You can use the action available in the Excel Online:

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

[Anonymous · Accepted solution]

I think you should get data from the SharePoint and don't create a CSV table. Create an excel xlsx template save it on the SharePoint and when flow runs copies the template gets filtered data from SharePoint and next use actions like add a row and create a worksheet, create table, etc.

If its not a solution write step by step what do you need.

[alex5p]

@manuelstgomes @Anonymous 

Thank you  both for your suggestions. I am extremely busy today so I don't have time to try this out, but on Monday I will and let you guys know my result.

Thanks again!

[alex5p]

@Anonymous  @manuelstgomes 

I had some time to work on this.

I had an idea of making a couple get items sharepoint connectors  with a filter and doing a create row/table for each specific filter for a new sheet with 1 single excel sheet.

I am having such a basic issue, but I had no idea why it is happening.

The o data filter query i am using is ReferredToName eq 'alex'

and I get this error:

Column 'ReferredToName' does not exist. It may have been deleted by another user.
clientRequestId: 92f88943-98dd-4692-a85f-131edf202ea9
serviceRequestId: 92f88943-98dd-4692-a85f-131edf202ea9

I know my column in sharepoint is called ReferredToName so it doesn't make sense why it says it doesn't exist Any Ideas?

[Anonymous]

Just use it without ODATA querry and check the name in the body of the action. It can be named in the background RefferedToNamex2034_odata or anything freak like that.

[alex5p]

@Anonymous I made significant progress on creating these different sheets. I noticed one thing which i am not sure of how to proceed. I will be running this flow about once a month. I noticed when the flow reruns it appends the data to the excel sheet so there will be duplicates on the excel sheet. I saw there is a delete a row, but i have about 40 columns so that wont work. Not sure can i just create a branch new excel document each time with a new sheet and a table attached to each sheet? Or is there a way to delete the old data from the excel sheet automatically at the start of the flow?

[Anonymous]

Generaly you can do that:

1. You have empty template excel with 1 empty row.

2. Copy file to new destination

3. Add a row - filepath: fileID from the action copy file

4. You can add 15 min delay on the end and you can use delete file action to delete filled file if you don't need backup

If you want to delete all rows what I don't recommend because the solution from top is more efficient and gives you possibility to track old data or is just faster:

1. List rows

2. Delete a row and use the array from the list rows.

3. Settings of the loop. Change parallelism to 50

[Anonymous]

Okay so that is where I am getting my issue. I have a flow that first adds rows to a sharepoint list. And then once a week I am simply trying to extract entries that were submitted LAST WEEK, into a CSV all by themselves. I have a filtered view of the sharepoint list that works perfectly, but when I pull in the list it just pulls all the data, not the filtered data. I tried to add an OData query on the import, but I can't because it relies on a calculated column.

Please help as this is urgent for our team. Thank you!