cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
peterharket
Frequent Visitor

Cumbersome upload of excel file to dataverse table

I would like to port my employer's excel "database" to a more robust system, and dataverse/power apps look like a great option - on paper. Apparently, you can have tables with 1M rows per table, and that would more than accommodate our needs. However, I have major problems with importing the excel sheet into a table. I have tried the "Import data from Excel" option (dropping the file into the "upload here" slot) and I have tried the "Import data" option (power query). The first option just throws errors left and right. And if it goes through, the resulting data is a mess with data being lost and ID's not being properly reflected (the first rows in the ID row are 10, 100, 1000 - skipping everything in between). With the second option, the columns are better preserved, but data is also lost apparently here. I was only able to import around 36k rows, from my excel sheet consisting of 500k rows. 

 

Does anyone know a better way to get all the data into the table? 

1 ACCEPTED SOLUTION

Accepted Solutions

SOLUTION:

I realize now that I had not let the dataflow run its full course before looking for data at the end of the list. In terms of the weird data (1001 for example), this is just a strange naming convention for 1 when datasets become very large, so the data is actually there. 

View solution in original post

3 REPLIES 3
AhmedSalih
Super User
Super User

@peterharket, The way that I have been using is to use the Dataflows for Importing data from Excel Files.

  • Create a Custom Table(s) in Dataverse with the same Schemas of the Excel Sheets:
  • Use Import Data from the Dataverse Table 

AhmedSalih_0-1656703707206.png

  • Make sure your Excel Columns Names have no Spaces(as Best Practice)
  • Follow the Power Query Steps and Import the Table to Existing Table
  • Map the Excel Columns to the Dataverse Table.

Daniel Christian has this good video to show you how >> https://www.youtube.com/watch?v=21ONiff4hxo

 

 

Regards,
Ahmed
If my reply helped you, please give a 👍. And if it has solved your issue, please consider Accept it as the Solution to help other members in the community find it more.

 

Thanks for the quick reply Ahmed, but this is the second approach I took, and the resulting data is still lossy/incomplete when I use "import data". But do you think it would help if I create the table in dataverse first, and then import the data? Should that really matter?

SOLUTION:

I realize now that I had not let the dataflow run its full course before looking for data at the end of the list. In terms of the weird data (1001 for example), this is just a strange naming convention for 1 when datasets become very large, so the data is actually there. 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Users online (2,772)