cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Is importing Excel in Common Data Service possible?

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??

 

1 ACCEPTED SOLUTION

Accepted Solutions

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

 

 

View solution in original post

5 REPLIES 5

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

 

 

View solution in original post

Anonymous
Not applicable

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?

 

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

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.

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

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (1,435)