Auto-generate ID when import the excel/Sharepoint list to dataverse

[mmchx]

Hi All,

I'm working on the Dataverse for MS teams, and want to migrate the data from the Excel / SharePoint list to Dataverse.

I have an issue that when I imported data, it doesn't generate the ID like the SharePoint list. So, I tried creating the column with auto number type before importing the data. The result is it doesn't generate the auto number for me, it will generate it when I create the new row only.

How I can achieve that to import data with auto number generating? 

The purpose of auto number column is to create the unique id of each item like in the SharePoint list.

[AhmedSalih]

Hello, @mmchx

Create an ID Column in the Excel file and then map that column to your Dataverse auto-number column. Make sure that ID column has the sequence of numbers from 1-whatever the last record number is. For the Auto-number column in Dataverse, make sure that the seed value is one number after your Excel last records ID value. So when you create new record after the import, it will start after the last ID.

 

If my reply helped you, please give a 👍 , & if it solved your issue, please 👍 & Accept it as the Solution to help other community members find it more.

I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.

Visit my Blog: www.powerplatformplace.com

[mmchx]

Could you please show me some examples? 

As I tried, Created 30 records on the dataverse first and my auto-number was HC-0001 to HC-0030 then I imported 2 records with ID HC-0031 & HC-0032 (created on the excel file).

It mapped to the auto-number column correctly, but when I created 1 record directly on the dataverse. It generated the auto-number as HC-0031.

do I do something wrong or do I skip some steps? 

[AhmedSalih]

@mmchx, You Load the Excel data first, then your seed start one number after the last record in Excel. Excel should be HC-0001 to HC-0030 and then create those in Dataverse. Is this a migration process or will be ongoing integration?

[mmchx]

Actually, both of them.

First scenario

1. doing the migration process first which needs dataverse auto-generate the number when migrating the data.

2. User who can add/edit data to dataverse, can add record and dataverse needs to auto-generate the number after the last record from the migration process. but the issue occurs when a user added the record after the migration process. It doesn't auto-generate numbers correctly as I mentioned.

For example

I migrated data(Excel file) and created column "ID" in the excel file (HC-0001 to HC-0030), after the user added the new record directly to dataverse. the expected result is dataverse auto-generate number to HC-0031 but the dataverse generated HC-0001 instead.

Second scenario

1. I manually created 30 records directly to dataverse (HC-0001 to HC-0030)via Power app Excel add-in. (Dataverse auto-generated number correctly).

2. User would like to import the data to the dataverse via data flow by using the Excel file. which the user has already created the ID column and created the seed number in the excel file. (This one is in case the user has multiple records and they don't like to copy-paste.)

For example

I manually created on the dataverse -> the auto-number are HC-0001 to HC-0030, then the user imported 2 record from the Excel file to dataverse and already defined the seed number which is HC-0031 & HC-0032. Once imported completely, Ok it shows HC-0031 & HC-0032. But if the user created the record (directly on dataverse) after importing it auto-generated HC-0031 & HC-0032  so, this will be 2 records that have ID: HC-0031 and 2 records that have HC-0032.

[AhmedSalih · Accepted solution]

@mmchx, I just ran a test. Here is what I did:

I created this Excel File:

Then I created a DataFlow to load the Excel data into an Existing Dataverse Table.

I created the Dataverse table with only one custom column (Auto-Number) 

The seed was Test as Prefix and 1000. 

Ran the DataFlow:

The result:

I created the CC record in Dataverse. Records A to E were created via the Dataflow and the flow generated the ID value (of the AutoNumber Column) automatically.

 

If my reply helped you, please give a 👍 , & if it solved your issue, please 👍 & Accept it as the Solution to help other community members find it more.

I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.

Visit my Blog: www.powerplatformplace.com

[mmchx]

appreciated it. seems like I need to create the new ID column(auto-number) on the dataverse, instead of using the Primary column as ID column (auto-number)

[AhmedSalih · Accepted solution]

@mmchx , That is correct!

[Az8PnG]

Hi @AhmedSalih May I know why did u import your excel using dataflow instead of manually clicking on "Import from Excel" button?

[AhmedSalih]

Hello, @Az8PnG, the import errors using the import method are not clear sometimes. Also, in the Dataflows, you can do full ETL which I do recommend preparing your data before the load.