cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lewishm
Level: Powered On

Importing 5k+ records from Excel

I'm trying to import 100k records into CDS(2) for Apps environment through 'Get Data' -> 'Excel' but gives error "Something went wrong" followed by a Job ID.  The file is on a SPO site which I provide the filepath in the Get Data section.

 

@sabinn- I tried cutting down the records in another spreadsheet to 5k but same issue.  Any ideas?

 

Thanks,

Lewis

 

5 REPLIES 5
Power Apps Staff CWesener
Power Apps Staff

Re: Importing 5k+ records from Excel

 

Adding @millopis from the PowerQuery team.

Thanks,
Clay.
lewishm
Level: Powered On

Re: Importing 5k+ records from Excel

@millopis - any ideas?

 

Thanks

Power Apps Staff millopis
Power Apps Staff

Re: Importing 5k+ records from Excel

Just to play it on the safe side, let's go down to 5 rows to be completely sure that the issue isn't related to file size.

 

If that still doesn't work, I will loop in a few folks from our Dev team to investigate the error.

 

Thanks,
M.

lewishm
Level: Powered On

Re: Importing 5k+ records from Excel

@millopis - just tried with .xlsx spreadsheet with 5 rows of data (exc. headings) and it generated a different error (attached).

 

To confirm, steps I completed:

1) Get Data

2) Excel

3) Enter SharePoint file URL (ending with ".xlsx")

4) Click on Sheet

5) Transform table -> Use first row as headers

6) Load to new entity

7) Select primary field

 

Then in load progress I get the error.

 

I noticed that at step 1 stage there is now the direct option of "Get data from Excel" - that's new! 

Highlighted
Power Apps Staff AngeloMollame
Power Apps Staff

Re: Importing 5k+ records from Excel

The error is:

 

Microsoft.OData.ODataException: Cannot convert the literal '9.9' to the expected type 'Edm.String'. ---> Microsoft.OData.ODataException: Cannot convert a value of type 'Edm.Decimal' to the expected target type 'Edm.String'.
 
My guess is that the column that you are trying to load is of type Any (icon ABC123), for which a String destination field is created, but because the column actually contains a number (9.9), there is a type mismatch between number and string.
 
Can you try changing the type of the column to Decimal Number?
 

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Users online (4,424)