Import data into the Microsoft Dataverse Table
We can import data into our Microsoft Dataverse database in bulk from various sources including Excel, Access, SharePoint, SQL, and many others. 1. Import Data from SharePoint list Before importing data in the dataverse, create an import data. To do this follow the following steps:
Step 1: Go to office.com and sign in.
Step 2: Click on app launcher icon, in the upper left corner of the window, and select SharePoint.
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-01.webp)
Step 3: The SharePoint site is opened in new tab. Open any Site.
Step 4: Select New > List in the command bar of the SharePoint site.
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-02.webp)
Step 5: Select Blank list from the Create a list popup.
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-03.webp)
Step 6: Give Name and Description to the list, we give "Teachers Data" as the name, and select Create.
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-04.webp)
Step 7: Our newly created list appears on the window, let's create some columns to upload in the dataverse.
Step 8: Select Add column next to the Title field.
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-05.webp)
Add the following column given below with their data types:
Name | Datatype |
---|---|
Teacher name | Single line of text |
Teacher Qualification | Single line of text |
Teacher Salary | Number |
Give the Name and Description to the column. Give the data type and click on Save button.
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-06.webp)
Step 9: Next, we want to add data so that we have some data to import. To add records to the list, click on + New.
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-07.webp)
Add the record and click on save.
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-08.webp)
Add some more records. The list is looking like the image below:
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-09.webp)
Step 10: Return to your SharePoint site's Home page and copy the URL from the browser. Now we're ready to import some data into our Dataverse table. Keep this browser tab open.
Import data into our Dataverse table Step 1: In a separate browser tab, go to powerapps.com, and in the left navigation pane, select Tables.
Step 2: Select the Teacher table, or create a new one.
In the teacher table, we have the following columns:
Column | Data type |
---|---|
S. No (Primary Name Column) | Autonumber |
Name | Single line of text |
Education | Single line of text |
Salary | Whole number |
The table contains the other standard columns.
Step 3: And then select Import and then Import data from the menu at the top of the screen.
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-10.webp)
Step 4: On the Choose data source window note that there are more than 40 options to choose from. In this example, we're going to select SharePoint list where we have data to import into our Teacher table.
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-11.webp)
Step 5: On the Connect to data source page, we need to enter the SharePoint page URL, which we have copied previously in the Site URL entry field. (After entering the URL, it may be necessary to put in the connection credentials.)
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-12.webp)
Choose the Authentication kind organizational.
Step 6: Then we select Next at the bottom right of the window.
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-13.webp)
Step 7: On the Choose data window that appears, we can now select one or more lists or document libraries that we want to import. Selecting from the list on the left shows all available columns of data available in the data source. Selecting Next takes us to the Queries screen where we can shape the data before importing. Look for the list we just created called Teachers Data and select it.
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-14.webp)
Step 8: By using the power query editor window, we can remove any of the columns that we don't wish to import, by using the Remove columns button at the top of the window. It's possible to select multiple columns at once by selecting columns while holding the Shift or Ctrl key and then selecting Remove columns. If we make a mistake, the Applied steps on the right side of the window keeps track of the changes, and we can cancel that step to undo the last action.
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-15.webp)
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-16.webp)
Step 9: With the remaining columns, select the Next button to move to the next step.
Step 10: At the Map tables window now appearing, we want to select Load to existing table.
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-17.webp)
Step 11: Select the Destination table from the dropdown. The table name has prefix in front of it with an underscore. The prefix depends on the environment.
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-18.webp)
Step 12: Notice how the Column mapping has the destination columns from our Teacher table.
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-19.webp)
Step 13: Next, we select the source columns that we want to import into our destination columns. That's why it's helpful to have similar names to make mapping easier. Each dropdown has a list of all available columns, so it's fairly easy to match if our names are close. Once all of our source columns are matched with the destination columns, select Next in the bottom right of the window.
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-20.webp)
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-21.webp)
Note: We do not map any source column to the autonumber column cr011_Sno.
Step 14: The Refresh settings provides us the ability to set up a refresh schedule if we want our data to regularly update. In this case, we'll leave it set to Refresh manually and select Publish at the bottom right of the window.
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-22.webp)
If we receive any warnings, pay attention to the messages, and go back to your query edit screen to shape the data.
Step 15: If we published and received no warnings, and the table screen refreshes, we should be able to see that the data was imported into the table.
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-23.webp)
Though this example used SharePoint as a source for importing data, the steps are similar with any data source we wish to import into our list.
2. Import data from an Excel file
We can also import data from the excel file (.xlsx) that is locally saved.
Step 1: Select the Import button at the top of the screen and select Import data from Excel.
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-24.webp)
Step 2: In the Import data pane that pops up, select Upload under the File entry field, locate the Excel workbook, and select Open.
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-25.webp)
Step 3: Wait a few moments as the file uploads. Next, we will see an error notification under the column Mapping Status saying "Mapping errors exist".
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-26.webp)
Step 4: Scroll to the right and Select Map columns.
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-27.webp)
The following window appears:
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-28.webp)
Step 5: Map the following Teacher columns to the associated Source values:
- Education: Teacher Qualification
- S. No: Title
- Salary: Teacher salary
- Name: Teacher name
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-29.webp)
Step 6: Select Save Changes at the top right of the screen. If successful, you'll see a success notification under Mapping status saying "Mapping was successful".
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-30.webp)
Step 7: Select Import at the top right of the screen. We have to wait while our data is being imported. If successful, we will see a notification saying "Import completed successfully." Close the window by selecting x at the top right of the window.
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-31.webp)
Step 8: To see the imported data refresh the browser screen, if it is not seen.
![Import data into the Microsoft Dataverse Table Import data into the Microsoft Dataverse Table](media/import-data-32.webp)