Unanswered

Is importing Excel in Common Data Service possible?

(0) Share

Report

Posted on by Community Power Pla...

Hello All,

I have recently come across that using excel as a data source has its limitation as you can only import a maximum of 2,000 rows due to issues around delegation.

At this stage, I don't want to change to another data source and want to continue using excel without its limitations and I have found out about the Common Data Service.

I am not sure I am correctly understanding the concept or the fundamentals behind the service but want to know if I can import or use an excel file that is currently located in my one drive, and use it as a data source without worrying about importing only 2,000 rows.

For example, if I import an excel file currently located in my one drive and use inside the Powerapps, when the users add data or make an update, would the excel file located in my one drive receives those updates and apply all the changes so when I open the excel file, I can see the changes??

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

Joel CustomerEffective 3,224 on at

Like (2)

Report

can you import from a spreadsheet into CDS? absolutely. There are about half a dozen ways to do this.
1. in make.powerapps--go to data, entities, and click "Get data"
2. In make.powerapps, click the gear and go to advanced settings. go to data management--import data. this is my favorite way to import spreadsheets as it does a better job of resolving lookup fields than any of the other standard options.
3. In a model-driven app, go to any entity view, click export to excel in the command bar, choose export to excel online. This opens an in-line excel worksheet. You can paste your data in there and save directly to CDS.
4. If it is a perpetually updated spreadsheet, you can create a dataflow and import the data in on an incremental basis via PowerShell
5. The PowerApps excel add-in allows you to open CDS views directly from a spreadsheet and save and update records back to CDS. https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/data-platform-excel-addin
6. You can use power automate to move data from Excel to CDS.

So pick your poison, there are no shortage of options to get your excel data into CDS. but the main thing is get out of Excel. Excel is not a very robust data source for apps--it will lock the spreadsheet if anybody is using the app. Write your app to use CDs or SharePoint lists, or anything but Excel is my advice to you.

And it is not worth it to set up some complex process to sync the spreadsheet with CDS. Just leave the data in CDS and use the Excel add-in to see the latest data when you open the spreadsheet.

@Anonymous if this answers your question please mark it a solution so other people can find it

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hello @jlindstrom

Thank you for your kind answer.

Just a one follow-up question, if you don't mind.

Firstly, thank you for your advice on using more robust data sources, and I will gradually move to using the Sharepoint list.
After importing excel to the Common data service, and I use it as a data source in Powerapps, the original excel file that is located in onedrive has no connection with the Common data service. Am I correct?

So the changes or information added by the users are added to the CDS and I need a tool or other add-ins to check CDS views?

Was this reply helpful? Yes No
Joel CustomerEffective 3,224 on at

Like (0)

Report

Correct—once you import your spreadsheet there is no connection between the OneDrive file and cds. Use the excel add in to view the latest data in excel

Was this reply helpful? Yes No
Yahya Manasrah 515 on at

Like (0)

Report

Hi @Anonymous , @jlindstrom ,

Have spent long time migrating my Excel data to CDS, the only solid and robust procedure is as listed in point 3 of @jlindstrom answer. Others will never work as expected.

Was this reply helpful? Yes No
Joel CustomerEffective 3,224 on at

Like (0)

Report

Yes, legacy import utility is the best, and for the best results use xml spreadsheet 2003 format

Was this reply helpful? Yes No