attempting to remove data from Microsoft List and re-add it back in from a CSV file

[galos]

My first attempt at a flow and I'm stuck at one part. My goal is to each night, delete all the data from a Microsoft List and then upload all the data from a CSV file. I first created the Microsoft List by importing the CSV file from Lists, and then properly selected each field, categorizing it as a text, number, date and multi-line text entries. To clarify, my CSV is all text, but in the MS List, I need to import some fields as a Date and some as a number and some as a long text.

I was able to remove all the data from the List in the flow, but when I try to add it back in, I keep getting stuck. 

Here is a screenshot of the flow - the error appears to happen somewhere around the compose part:

Flow starts with a time event (recurrence), then goes to a Get Items and "apply to each" is delete. That is working.

- Next Get file content is just the CSV file

- Then Compose has an expression of the following:   split(body('Get_file_content'),'\n')

- Next, in the below screenshot, you'll see I have Apply to each 2 as such:

So now when I run it, the following error appears:

I'm wondering, but not sure, if the data from the CSV is text but it is trying to insert it into certain fields in the List that are labelled as number or date. Any thoughts?

[v-mingkz-msft]

Hi @galos,

Do you want to read csv file and add item to SharePoint list?

You can use Parse CSV action and Parse JSON action to read the data from CSV file.

I did some test according to your requirements.

Following is my flow.

You just need one loop for output of Parse JSON.

Best Regards,

Kyrie

[galos]

Thanks @v-mingkz-msft for these suggestions. Am I assessing this correct? It looks like you're using Encodian, but doesn't that require a 3rd party API? Would data be sent to them, because there is data that cannot leave this organization for this process. Also, is this a free module or paid?

Thank you!

[galos]

Would anyone be able to suggest an alternative to encodian? We cannot use a 3rd party product for this. I would be happy if we could even just import  this CSV file into JSON format. This CSV file is large and complex. Fields within the CSV have commas and double quotes, so the first line of the CSV looks similar to this:

"Account","Owner","Amount"

However, when I click on the "Click to download" in Outputs for "Get file content" here:

the first line of a very long CSV output looks like this:

{"statusCode":200,"headers":{"Cache-Control":"no-cache","Pragma":"no-cache","Accept-Ranges":"bytes","ETag":"\"{0F81FC50-A3F5-4782-867F-216FE1ACA1FD},3\"","Location":"https://flow-apim-msmanaged-na-northcentralus-01.azure-apim.net/apim/onedriveforbusiness/51552976bc6e429484524f619bb37145/datasets/default/files/b%25123A2TBumL8xUSqnzHbohiFZ.012BIEMYSQ7H2ZIP5/content?inferContentType=true","x-ms-request-id":"acb14c78-27aa-4f44-8b65-434jkf4bc9","X-AspNet-Version":"4.0.30319","X-Powered-By":"ASP.NET","Timing-Allow-Origin":"*","x-ms-apihub-cached-response":"false","x-ms-apihub-obo":"false","Date":"Tue, 11 Jul 2023 23:48:58 GMT","Content-Length":"1514523","Content-Type":"text/csv","Expires":"-1","Content-Disposition":"attachment; filename=\"Sample.csv\""},"body":"\"Account\",\"Owner\",\"Amount\",\n\

 Any thoughts?

[galos]

for what it's worth, I found this website to be incredibly helpful:

https://www.c-sharpcorner.com/article/parse-csv-file-using-power-automate/

However, there is still one last issue. I was able to get this to work with a small CSV sample, but when I used the full file, which contains multiple carriage returns within one field, this fails. If anyone knows how to work around this, I welcome any feedback.

I have one field in the CSV entry that is text written in sentences. The fields are separated by commas, but each field is encapsulated by double quotes. within the double quotes of one field there are multiple carriage returns. I think the one step that involves splitting by carriage returns is where this breaks.

[CK6218]

You can try Excel "List rows present in a table" controller, i saw u using one drive to get the data so i assume your CSV file is auto sync to your onedrive, but the controller i mention require excel type and the data is in table format. If u want to parse csv without table inside, im not sure is there any free method to achieve this