Problem importing data from Excel - a string turns into the number 0

[aa12]

Hello,

I have an Excel file that I am using as source of data. It has 4 columns (5 with __PowerAppsID__). The column I have a problem with is named listId. It is a string like this: xxxxxx-xxx-x-... A long random string of characters. I have 10 rows in the Excel file with this structure.

When I import it with OneDriveForBusiness, every row shows up in my PowerApp in that column as 0. I don't know why. I have other Excel files that do not behave like this when I import them. 

When I manually enter a new row, and enter listId as a number, like 1, that does show up correctly. That is all the other info I have.

Any ideas?

Thanks!!!

[Drrickryp]

@aa12 

It may be that PowerApps is assuming your listID is a number.  Does your string start with a number or text? 

[aa12]

The listID is a string. However, lots of them start with a number character. Some start with [a-f]. I do agree that it thinks listID is a number.

[Drrickryp]

@aa12 

Take a look at Shane Youngs video here about why Excel is a terrible data source.  He addresses some of the problems at about 8minutes into it. https://www.youtube.com/watch?v=RtmZM3tm2Zs .  When the table is initially brought into PowerApps it decides what kind of data it is and it may not be possible to change it. 

[aa12]

Yes, at 8:36 he describes the problem that I have. Once Excel makes a choice regarding data type, it can't be changed. Moving to Sharepoint is the better idea.

[aa12 · Accepted solution]

OK I made a workaround. My Excel file had the column listID. The Id's were ordered from top to bottom like this: 

3c3897f9-f4eb-4326-8d4b-a7b9c7410220

1bc70c39-b42c-4ca7-857d-f58db383f5f7

97272c2c-acf0-40ed-875d-6e8aa8350ff1

Several more entries were below.

I found one that started with letter, fc2axxx-x.... I moved it to the top.

f5c658d5-7aca-4076-b439-8b89664eb945

39e764ec-c749-4da3-8a6c-89b2470690f8

0728e562-33fb-45b6-b916-da34ba25415a

I re-added the data source. Problem solved.

I suppose that PowerApps or Excel looks at the first letter in the listId column, and then decides what the data type is from that. In my case, moving the listId that started with a letter to the first position solved the problem.

Not a good long term solution, but it does work.

[Drrickryp]

@aa12 

No need to thank me.  Stop by the gift shop on the way out and pick up a souvenir for the children.