Switching to CDS, Need help with importing Data

[Reylin]

Background
My team has been making PowerApps for several months now, but we've been using SharePoint as our main data source. Recently some of our databases became very large, and we kept running into delegation problems with our apps. They became very slow and it was hard to get the same functionality that we used to have. 
We decided to switch to Common Data Service to get that functionality back and increase our App speed/efficiency.

Question

How do you import Attachments from Sharepoint List to Common Data Service? 

Problem (in depth)

I originally imported the data from the Sharepoint Lists to CDS through Excel, but Excel doesn't do attachments. 
So I tried it with Power Automate, but I can't find a Flow action that adds attachments to CDS items.

Currently I'm trying to see if I can make a PowerApp that gets the Attachments from the Sharepoint List and adds them to the corresponding CDS entity through the Attachment Control... but this seems like a stretch, and definitely not what PowerApps was made for. 

Does anyone know a good way to import Attachments to CDS? I'm dealing with thousands of items, so it would be nice if there was an efficient way to do this. 

[Reylin]

@HSheild @v-xida-msft  Thank you so much for the help, both of you. 

So I finally got everything working. And while its not the best system for importing SharePoint (lookup values and option sets are a pain), it works and that is what matters. 

If anyone needs to do this sort of thing again, here is how I did it: 

The Variables are Arrays containing my Option Sets. I plug in the label and the corresponding CDS value for each option. 

I use a Filter Array action to grab the right CDS value based on the label from the SharePoint dynamic content with a simple:
*From varProvince item()?['label'] is equal to Sharepoint Dynamic Content

And then I use this code in the "Create New Record" action under the right Option Set field: 

body('Filter_varProvince')?[0]?['index']

Lookup fields are a bit trickier because there could be many more options. Here I am planning on using a "List Records" CDS action to grab the Entity that I'm looking up the data from. I'll use a Filter Query: Title eq (Sharepoint Dynamic Content here), and set the Top Count to 1 just to be safe. 
Then use the data from the "List Records" action to get the GUID for the lookup record, and plug that into the Lookup Field in the "Create New Record" action. 
Luckily this will be less work than the Local Option Sets. 
------------------------------
For the Attachments, I grab the attachment data with the 2 Sharepoint Actions (Get Attachments, and Get Attachment Content inside the Apply to Each action). Then I use "create a new record" action with the Notes entity. Title and File Name are the Display Name from of the attachments from Sharepoint. Plugin the corresponding CDS data in the Regarding and Regarding Type fields. IsDocument set to Yes. And lastly the actual data will be in the "Document" field. I used a Compose Action for this with the code: 

string(body('Get_attachment_content')?['$content'])

[luuminhvuong93]

Does cds solve the slow speed of sharepoint?

And does it require investment more

I have o365 e3

[jlindstrom]

the answer is ”it depends.” as a relational database CDs is faster for more complex data models with more data and relationships. And because you can leverage granular security and system views and more complex calculated fields and rollup fields you can offload more to the platform rather than having to build it into your app which will make your apps more performant

[jhanley]

Hey, just want to say thanks for sharing & starting this thread - it's really appreciated! My team's in the same position as you, having recently started developing a PowerApp sourced from SharePoint only to later encounter delegation issues, so are looking to endeavor on an importing exercise to bring our SP data across to CDS. This thread looks very helpful to get around the common issues that seem to crop up re. attachments, lookups and option sets - hopefully it will save us some pain!