cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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
Highlighted
Power Apps
Power Apps

Re: Importing 5k+ records from Excel

 

Adding @millopis from the PowerQuery team.

Thanks,
Clay.
Highlighted
Helper I
Helper I

Re: Importing 5k+ records from Excel

@millopis - any ideas?

 

Thanks

Highlighted
Power Apps
Power Apps

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.

Highlighted
Helper I
Helper I

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
Power Apps

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
Check this Out

Announcing

Class of 2020- Season 2

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Users online (8,247)