SharePoint Lists column name vs field_? on imported excel and why both work sometimes?

[BrianHFASPS]

Two questions / topics:

There seems to be differing opinions on the best way to get an Excel sheet into SharePoint while preserving the column names as field names. I only do it once a year and always forget.

1. Export from Excel to SharePoint - this seems to work but may disable some form features (which I don't care about)
2. Create a new list from an excel import - this seems to generate the field_? type column names that PowerApps sees.
3. Manually create the list and then open in Excel and paste contents in 100 rows at a time. This seems insane to me.

Is there some recommendations on which to do so that there is control of column / field names?

Second topic - How is my app referencing the column name instead of field_?

Somehow my app is using the column name rather than field name.

This is data view from within PowerApps showing the column names it is seeing.

This is the code that works but I can't explain why.

This is the column names in SharePoint.

Is there some trick to getting PowerApps to see the friendly SharePoint column names instead of the Field_?

[Amik]

@BrianHFASPS - my personal preference is to:

1. Create a new list with all of the non-complex column names from the Excel sheet.

2. Open Access and open the SharePoint List

3. Copy all of the columns from the Excel sheet

4. Navigate back to access and select All of the headers, and then select Paste

Regarding option 3, it may sound insane, but manually pasting 100 rows at a time using the grid view is very reliable and you get to preserve the Display Names.

Regarding question 2, I believe using any of the Collect functions will only return the Logical names as opposed to the Display Names. 

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

Imran-Ami Khan

[BrianHFASPS]

Thanks for the notes. The frustration for me is if I have a excel table with 50 columns wide and 1000 rows deep all these manual steps seem insane. There should be a better way.

[SebS]

@BrianHFASPS 

Do your columns not change? And You are looking only to find a quick way to transfer 1000 records from Excel to SPO? Also, do You have a specific process for it, like You need to clear all data in the List once a week or two and import fresh data from excel again?

[BrianHFASPS]

I only do it once a year and always have to relearn the process since I don't leave myself good enough notes. I am importing a list of students and teachers for an app to reference. Many of my other apps use SQL and importing a flat file and doing a merge takes me minutes. This process is always much more cumbersome.

[SebS]

@BrianHFASPS 

Create power Automate Instant Flow. Here is an example

Excel data need to be in the Table. Use List rows present in a table to pull all data, then simply Create an Item in Sharepoint Map your columns to Sharepoint Columns and run it. All data will be transferred. 1 year later just use the same flow if Columns in the list did not change it will always work if they change just adjust it and run again 🙂