Import sharepoint list from Excel, column name is changed

[Timy2604]

Can someone tell me why do i get different column name when importing list from excel. Column name is changed to: field_columnname. So when i reference column name from PowerApps with function ShowColumns(datasource,"DepartmentID"), it reports error, but if I change to ShowColumns(datasource,"field_DepartmentID"), it works. I really have no idea where "field_" coming from, is it hardecode by purpose?

[WarrenBelz]

Hi @Timy2604 ,

How did you get the data into SharePoint  - imported it as a new App or exported it from Excel? I just tested both and got the proper names of the fields.

[Timy2604]

From Sharepoint, i choose New -> List and come up table as below, and i choose From Excel. My Excel file contains several tabs with table format data. I pick one and it's automatically loaded up as shown above

[WarrenBelz · Accepted solution]

Hi @Timy2604 ,

You are correct - it is a while since I did one - I use the legacy import process which needs an old browser supporting ActiveX and it works fine. The easiest way however depending on the size of your data is to simply create the list in SharePoint, edit in Grid View with the field lined up as per your spreadsheet and paste it all in. You need to do about 100 lines at a time to avoid getting throttled.

The alternative is to simply refer to the fields with their name (as renaming them in SharePoint does not fix the issue)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[Timy2604]

Thanks for advise. It works. I have to create new List from sharepoint, manually populate all columns, then go to edit mode, and paste all the rows. It does take time, but at least there is a workaround.

[HEATFreight]

When I import a SharePoint list from an Excel table, the column headers all get changed to "field_#", where # is the column number. The table contains a first row of column headers. Previously I have imported similar tables into SharePoint and the absolute column references were "field_ColumnName", where ColumnName is the actual column header from the Excel table.

Well I had to makes some changes to get the column headers of several tables (each table in its own worksheet) to match each other. For instance, I need them all to have either "State" or "Province" but not a mix of both. Or I need the postal code columns to all be "Zip Code", instead of some being "Postal Code" and others being "Zip", etc. etc.

I just need column headers to match. Each table has a different set of headers, but they share many of the same column categories. I just need the corresponding headers to match exactly. So I have started editing these tables to fix the mismatched column headers, but when I delete the old version of the SharePoint list and import the revised table into a new list (with the same exact list name), the absolute column references are no longer coming in as "field_ColumnName" like they used to. Now they're all "field_#". This is a huge problem for me!

The data from these lists needs to populate galleries and forms within a Power Apps canvas app, and we use Power Automate flows to query SharePoint for the list data and return it to the canvas app (we call a parent flow from the canvas app which calls a child flow to query SharePoint, thus ensuring we're always in the creator's context rather than user's context. We don't directly query SharePoint lists from within the canvas app because then each user would be required to have their own SharePoint connection...).

Working with columns that get renamed from "ColumnName" to "field_ColumnName" was annoying, but we learned to live with it because it's intuitive enough to type a column name into some Power Apps function and look for the corresponding auto-suggested "field_ColumnName". That's slightly confusing and annoying but really no big deal. It gets a whole lot more confusing if we have to translate back and forth from descriptive column names to arbitrary numbers!

How can we prevent SharePoint from changing our descriptive .xlsx table column headers to arbitrary numbers upon import? This is ridiculous! So maddeningly frustrating.

Does this have anything to do with having previously imported (and then deleted) Excel tables with very nearly the same set of column headers and exactly the same list names? The original lists were deleted (sent to SharePoint recycling bin). The Excel tables were revised to fix the mismatched column headers between corresponding fields in each table. Then the Excel tables were imported into brand new SharePoint lists with the exact same list names as the original lists that we deleted. Most of the column headers in each new list are exactly the same as the old list, with only a couple of them being different from that original list. But every single column now gets an absolute reference that's just a number rather than the column name. I think we've deleted and reimported lists before without seeing this issue. Not entirely sure what's different this time🤷‍

Edit: Just tried importing an Excel table as a brand new SharePoint list and left the auto-generated random string appended to the list name (thinking maybe some conflict arose due to reusing the exact same list name as the deleted list), but nope! Changing list name doesn't have any effect.

Edit2: Restored one of the original lists from Recycle Bin so that we could open it for comparison, then imported the exact same Excel table that was used to make the original list (no changes whatsoever to that data or column headers) into a brand new list. To avoid conflict between the duplicate list names, I allowed SharePoint to append a random string to the list name, whereas usually we delete that random string. We thought using the unedited Excel table would result in the old "field_ColumnName" style of absolute column reference that we prefer, but to my surprise we got "field_#" instead. So it seems that the changes to the Excel file itself have absolutely nothing to do with the issue because the issue occurs with the exact same Excel file which previously worked. There may have been some global SharePoint settings changed since the last time we built a list via Excel import. Could it be that some SharePoint setting is causing this?

Edit 3: Ooo after perusing the SharePoint site settings, I think maybe "site columns" could have something to do with this...? There is some overlap between columns that I see in the "site columns" list and the columns which I'm trying to update with matching headers for corresponding columns in several lists. I think I need to make all of the shared fields, as in the fields which several tables have in common but whose headers don't match perfectly, into site columns and use the site column for each of them instead of what SharePoint generates automatically, but I don't know how to make that work with the "Import from Excel file" list creation method. I were manually adding each column, I could add them as site columns or use existing site columns, but importing an entire table full of data doesn't present any opportunity to specify site columns... I think I'm more confused now than I was before!

[HEATFreight]

Aha! This solved my problem, found from this comment.

[WarrenBelz]

Hi @HEATFreight ,

Beware of exporting form Excel - my experience is that it does not allow the adding of a SharePoint Integrated Form to the list.

[HEATFreight]

@WarrenBelz I'm not exactly sure what you mean, are you talking about this?

Why would I want that, like what additional functionality do I get or what's the selling point for using that as opposed to connecting a SharePoint list to a Power Apps canvas app form the normal way?

I have messed around with that "SharePointIntegration" control, but could never figure it out because it seemed like it was creating a new Power App, but maybe not... I could never find the new app it supposedly created. I don't really understand what it does and haven't had any gaps in functionality that would push me to learn more. So, I'm not currently using it and have no idea what I'm missing. Is the purpose to create a common form configuration for a particular list such that anywhere you use that list you get the same form configuration with no extra effort? Would I benefit if my lists only appear in one form within one canvas app?

The problem with not exporting directly from Excel is that the static field names all get changed to either "field_ColumnName" or "field_#", whereas if you export directly from Excel you get static names exactly matching your column headers! Is there any other method to force SharePoint to use my exact column headers for static names? I remove all spaces and special characters from column headers, and when I export from Excel –voila!– perfectly consistent column headers and static names. Only other way I have found to do it is manually adding columns (yuck!). And the one exception I have found is when a column header is already in use by some system parameter, like I've had "Name" convert to "Name1" for some reason.

Also, not sure I understand the "read-only connection" setting when exporting from Excel... Does that cause the original Excel table to sync with the SP list?

[WarrenBelz]

@HEATFreight ,

Yes - I just did another test here and when the SharePoint list is exported from Excel, you do not receive the option of creating a SharePoint Integrated Form to replace the standard Edit/View/New form.

This is the menu received when exporting from Excel

and this is what it looks like in all other Lists

so you simply cannot create the Power Apps form.

The list naming is a Bug I believe and was started after my initial post below - I have reported it here.

I simply create the list and then paste in the records - it does not take long and give exactly the desired results.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps