cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

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
Highlighted
Super User III
Super User III

Re: Is importing Excel in Common Data Service possible?

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.

 

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

 

 

View solution in original post

5 REPLIES 5
Highlighted
Super User III
Super User III

Re: Is importing Excel in Common Data Service possible?

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.

 

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

 

 

View solution in original post

Highlighted
New Member

Re: Is importing Excel in Common Data Service possible?

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?

 

Highlighted
Super User III
Super User III

Re: Is importing Excel in Common Data Service possible?

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

Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Is importing Excel in Common Data Service possible?

Hi @dnjtdjq , @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.

Highlighted
Super User III
Super User III

Re: Is importing Excel in Common Data Service possible?

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

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Users online (4,899)