Insert row(s) into CSV file in Sharepoint Online

[igbins09]

Hi, i need help with Inserting a new row/Updating a csv file.

I have a flow that triggers on a new email, it picks out the info i need using the compose action and split expression and then presents it in the form of an Array and then creates a new csv file (if the file doesn't exist).

Now, my issue is inserting a new row into the same file when a 2nd, 3rd or multiple emails are received. I've tried so many things like "append to array variable", "update file", "get file content" & "filter array" conditions and nothing seems to work. Pls any help is needed.

Thanks

[Pstork1]

There aren't any built-in actions to make this easy to do.  But if you know what the columns should be the process is as follows

1) Get the file content of the CSV file

2) Initialize a string variable with the CSV file content

3) Append a comma delimited line of the values for the next row to the string variable

4) Create a new CSV file to overwrite the original with the string variable as the file content.

[igbins09]

HI @Pstork1 , the above didn't work because 

1. I'm working with sharepoint , yours is onedrive

2. "initialize variable" doesn't work inside of a condition but that's okay, i can put it outside of the condition

[Pstork1]

1) Initialize Variable does need to be done in the mainline of the flow.  But you can use either Append or Set variable inside a condition after the variable is initialized.

2) The solution uses Create File and Get File Content. There are versions of those that will work the same way for files stored in SharePoint. OneDrive is just easier to Demo. 

[igbins09]

hi @Pstork1 , i was wondering you could show me because i've played around with a lot of demos for the past week and i can't seem to successfully add rows into a csv file whether i use arrays or string variables to append. There is always an issue.

The farthest i've gotten luckily is adding a second row but that didn't work by the time i tried adding a 3rd and so on. At this point, i'll appreciate a visual representation

[Pstork1 · Accepted solution]

I posted screenshots of how to do it in OneDrive. Here's the same thing but using SharePoint.

1) Get the file content from the CSV file in SharePoint and save it in a string Variable

2) Once you do that you can Append a new line as a string of comma delimited values to the CSV string variable.  Then use the string variable to create a new CSV file. The one thing that changes is that Create File in SharePoint can't overwrite an existing file so you have to add an extra step to delete the OLD CSV before creating the new one.

[igbins09]

Thanks alot @Pstork1 . this helped a lot. I was able to use it to complete my flow with just a little tweak. I appreciate the help. My little tweaks below (incase someone needs it) were:

1. Surprisingly, i did not need to use "delete file"" action because mine overwrites so i didn't add that action

2. i added decodeUriComponent('%0A') function right infront of the Appended String variables because i was getting line breaks

3. Apparently "File content" and "Body" is the same but NOTE: "Body" and "body" aren't the same thing. 
for some reason, "File Content" got renamed to "Body" when i went back to look at my flow. I got confused when building my flow when i saw "Body" in the solution, so i went ahead to use "body" which didn't work. I ended up using "File Content" which later gets renamed to "Body" when you close and open up your saved flow

[aaaaaaaDDD]

@Pstork1 , I was looking for the solution to this problem and landed on this thread. What if the string variable reaches size limit of 100 MB? In my case there are more than 100k records.

[Pstork1]

YOur problem will be that Power Automate won't be able to handle a single loop that large anyway. If you have an Office license the biggest loop you can have is 5,000 items. If you have a Premium loop you are still limited to 100,000 items. So you won't be able to handle 100k+ records with Power Automate no matter what license you have without breaking them down into multiple loops.  In your case I think you might need to look for a different application  to handle a data source that large.  Data Flows might be an option.

[aaaaaaaDDD]

@Pstork1 , I am thinking to use skip tokens to loop through the dataverse table rows. Since I want to export these records in single CSV file, I am looking for options to append data to a file in SharePoint. Graph API could be an option but I am not sure how to append to already existing data.

[Pstork1]

I am not aware of a method to append data to an existing file either without recreating the file, which would require keeping the contents in memory.