Create Entity fields from Excel spreadsheet

(0) Share

Report

Posted on by ragavanrajan

7,044 Most Valuable Professional

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.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (1)

EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report
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:
In make.powerapps.com, expand Data and select Dataflows
Click "New Dataflow" in the top command bar, pick a name and click Create
Select Excel as the data source
Easiest is to copy your Excel file to your OneDrive for business and browse to it.
Pick your spreadsheet and you can even edit your query with Power Query to like filter out records if need be.
Next screen, you can map the fields/columns to an existing entity or create a new one
Click OK and pick a refresh frequency or run manually and you're done!
Hope this helps!

Was this reply helpful? Yes No
v-xida-msft on at

Like (0)

Report

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.

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

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 (Highlight to read)

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

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

Was this reply helpful? Yes No
ragavanrajan 7,044 Most Valuable Professional on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report

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

Was this reply helpful? Yes No
ragavanrajan 7,044 Most Valuable Professional on at

Like (0)

Report

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

Was this reply helpful? Yes No