cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ragavanrajan
Super User
Super User

Create Entity fields from Excel spreadsheet

Hi Guys, 

 

         We have an Excel application which is really huge and have many tabs with at least 55 columns on each tabs. As a first step I have taken one of the spreadsheet tab which have 60+columns and some sample data. 

 

In CDS > I have created the entity. 

 

To Achieve: 

 

1. Is there anyway I can use my spreadsheet to create fields in an entity ? (Note: Not mapping fields and loading sample data) 

2. I have checked XRMToolbox but not sure which is the appropriate tool to use to create fields

 

Kindly advise is this possible. 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

With Dataflows, you can generate the entities and its fields as per my previous post. You can delete the entity if you already created it and it will get re-generated with the fields.

Another method if you do not want to delete the entity is with the Attribute Factory tool in XrmToolBox which is helpful to bulk create your fields (attributes) for an entity: https://www.xrmtoolbox.com/plugins/Javista.AttributesFactory/ So you create the entity which no fields and then use this tool to bulk create the fields/attributes. Hope this helps...

View solution in original post

5 REPLIES 5
EricRegnier
Super User
Super User

Hi @ragavanrajan,

You can easily achieve that with Dataflows which has the option to generate your entity and map each column to a field. You can also schedule your dataflow or run on-demand to import the data. Here are the high level steps to create your dataflow:

  1. In make.powerapps.com, expand Data and select Dataflows
  2. Click "New Dataflow" in the top command bar, pick a name and click Create
  3. Select Excel as the data source
    2020-09-01_17-52-06.png
  4. Easiest is to copy your Excel file to your OneDrive for business and browse to it. 
  5. Pick your spreadsheet and you can even edit your query with Power Query to like filter out records if need be.
  6. Next screen, you can map the fields/columns to an existing entity or create a new one
    map-to-standard-entity.png
  7. Click OK and pick a refresh frequency or run manually and you're done!

Hope this helps!

v-xida-msft
Community Support
Community Support

Hi @ragavanrajan ,

Regarding the needs that you mentioned, I think Power Query in CDS and XRMTool box both could achieve your needs.

 

# 1, using Power Query in CDS. Please check and see if the following article would help in your scenario:

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/data-platform-cds-newentity-pq

 

# 2, using XRMTool Box to import data. Firstly, you need to install "Data Import ++" component in your XRMTool Box. 

10.JPG

Then open the Data Import ++ plugin, then you could browse Excel file from your local device, then import it to your existing CDS Entity:

11.JPG

 

The XRMToolBox would be easy and faster than Power Query functionality in CDS. The XRMToolBox also has some limits in importing data into CDS Entity:

Spoiler

- Lookup fields: You can only map String or Guid field types from within the related entity.

- OptionSet fields: If different optionset fields are present in your excel file, all of them should be of the same data type: Optionset VALUES or Optionset LABELS.

- Create CRM Action: You cannot force a guid even if mapping a guid excel column to the record's guid field. The CRM will generate a new Guid for the record.
- If you want to use XRMTool box to import data, you must create the Entity in your CDS firstly.

Please check the following blog for more details:

https://www.d365tips.com/post/xrmtoolbox-dataimport

 

# 3, Using the "Get data"-> "Get data from Excel" option to import data into existing CDS Entity. Please check the following article for more details:

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/data-platform-import-export

 

Regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you @EricRegnier  and @v-xida-msft . Sorry for my wordings. I have rephrased my question now. I need to "Create fields" based on Excel file. Thanks again for your support. Please advise 

With Dataflows, you can generate the entities and its fields as per my previous post. You can delete the entity if you already created it and it will get re-generated with the fields.

Another method if you do not want to delete the entity is with the Attribute Factory tool in XrmToolBox which is helpful to bulk create your fields (attributes) for an entity: https://www.xrmtoolbox.com/plugins/Javista.AttributesFactory/ So you create the entity which no fields and then use this tool to bulk create the fields/attributes. Hope this helps...

Awesome @EricRegnier  thank you for your quick response. Much appreciated. 

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (1,536)