Get CSV file and import to SharePoint list without third party connectors

[carudev]

Hello Guys!

I need to automatically get a CSV file that reaches me daily by email, and every time that email arrives I need to get the CSV and import to SharePoint List. And I need to do this without using any external connectors. HELP !!!

PS: Would I be able to get only a few columns from the CSV and not the whole one? Any ideas?
Thanks! 🙂

[Paulie78]

I can give you a basic summary of the steps required, but there is quite a lot to do if you are new to PA:

1. Use the "When an email arrives V3" action, modify the settings so that it only triggers if the email contains an attachment.
2. Filter the attachment array so that only CSV files remain.
3. Check that the filtered array is not empty (i.e. there were no CSV files).
4. If it is populated, you now need to parse that CSV file.
   1. If you are new, you can use encodian to parse the CSV for you (or even if you are not new). @Jay-Encodian is really helpful.
   2. You can parse the CSV yourself in PA, but it is much more complex.
5. When you have got your parsed CSV you can add the new items to a SharePoint list quite easily.

Most of the stuff above is a forum question in its own right, so I suggest you watch a some of the @JonL getting started videos on youTube and then just try and build the flow up. When you get stuck on a particular stage, people will help you out.

But it is perfectly possible, relatively difficult if you are new to the platform. But once you have done it, it will seem easy and you will be able to put together other flows with what you have learned. 

[carudev]

Omg, i'am like.. SUPER NEW in PA! ~freaking out~

I tried to use Parse CSV, but it asks me for a few different fields than what I saw in the examples. He asks me for:

Connection Name (?)
API key (?)

Do you know what those fields would be like?

[Jay-Encodian · Accepted solution]

Thanks @Paulie78 for the mention

Hey @carudev 

You're prompted for a connection name and apikey initially as you need to create a connection to the Encodian connector 🙂 - this following short video explains all: https://support.encodian.com/hc/en-gb/articles/360012267353-Create-an-Encodian-Connector-in-Flow

You can obtain an apikey here: https://www.encodian.com/apikey

Once you have done this you'll have access to parse the CSV file which is fairly simple... there is a good guide here: https://blog.encodian.com/2020/01/parse-a-csv-file-and-add-content-to-a-sharepoint-list/

I've also just created the following video which should help 🙂

HTH

Jay

[Paulie78]

That is a really good video! Covers just about everything the original posted needs to know.

I prefer to filter the attachment array instead of using a condition like this:

But obviously both ways are equally valid

[carudev]

Thank you guys! 😄

That was really helpful @Paulie78  and @Jay-Encodian ! 🙂

With Encodian I was able to do this in the development and testing environment. BUT, now to put this flow in a real company environment, I would have to do it (Parse CSV) manually and without connectors. Do you know how this could be done?

Thanks!!!

[Jay-Encodian]

Hi @carudev 

Check out this post: https://manueltgomes.com/microsoft/powerautomate/how-to-parse-csv-file/

We used to use similar approaches for our customers before we built the action... the reason we created the action is the manual approach is complex to implement, hard to update, hard to support and we uncovered lots of issues with data... think escape chars, speech marks, etc. which can be very very hard to manage manually.

I'd suggest the cost of your time and the support overhead wouldn't provide a cost effective solution 🙂

HTH

Jay

[campbelldw]

This solution worked perfectly for me however I received an error message due to the size of my csv content that I am attempting to pass to the SP List.  Below is the error message - is there an alternate solution that can handle more than 5000 rows since the Parse JSON auto converts to Apply to each?

Error Message:

"Unable to process template language expressions for action 'Apply_to_each' at line '1' and column '22263': 'The number of foreach items limit exceeded for action 'Apply_to_each': maximum '5000' and actual '27054'.'."

[carudev]

Hi, @campbelldw ! 😄

I believe that to deal with lists larger than 5,000 lines, it is necessary to purchase the premium license of Power Automate, as there is this limitation of 5,000 requests.

Here the microsoft checks that can help you::::

https://docs.microsoft.com/en-us/power-automate/limits-and-config

[Paulie78]

Hi @campbelldw  you could split the array into an array of arrays and keep the number below 5,000 always.

[Tyler-Kolota]

Anyone with similar needs who absolutely doesn't want to use a 3rd party connector can check out my template flow here: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-to-Dataset/m-p/1508191

The main limitation is it can not take a CSV table with more complex things like arrays or JSON objects in it. Otherwise it should work pretty well.

Of course, this template could also be modified to use the SharePoint batch create method too: https://www.tachytelic.net/2021/06/power-automate-flow-batch-create-sharepoint-list-items/