cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Timy2604
Frequent Visitor

Import sharepoint list from Excel, column name is changed

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?

 

Timy2604_0-1636777406735.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Most Valuable Professional
Most Valuable Professional

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

 

 

View solution in original post

12 REPLIES 12
WarrenBelz
Most Valuable Professional
Most Valuable Professional

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.

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

 

Timy2604_0-1636782881893.png

 

WarrenBelz
Most Valuable Professional
Most Valuable Professional

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

 

 

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
Kudo Kingpin
Kudo Kingpin

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
Kudo Kingpin
Kudo Kingpin

WarrenBelz
Most Valuable Professional
Most Valuable Professional

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.

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

HEATFreight_1-1640621377549.png

 

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?

HEATFreight_5-1640621667073.png


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
Most Valuable Professional
Most Valuable Professional

@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

WarrenBelz_2-1640639152521.png

 

and this is what it looks like in all other Lists

WarrenBelz_1-1640639043179.png

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

 

 

 

 

 

Helpful resources

Announcements

Exclusive LIVE Community Event: Power Apps Copilot Coffee Chat with Copilot Studio Product Team

  It's time for the SECOND Power Apps Copilot Coffee Chat featuring the Copilot Studio product team, which will be held LIVE on April 3, 2024 at 9:30 AM Pacific Daylight Time (PDT).     This is an incredible opportunity to connect with members of the Copilot Studio product team and ask them anything about Copilot Studio. We'll share our special guests with you shortly--but we want to encourage to mark your calendars now because you will not want to miss the conversation.   This live event will give you the unique opportunity to learn more about Copilot Studio plans, where we’ll focus, and get insight into upcoming features. We’re looking forward to hearing from the community, so bring your questions!   TO GET ACCESS TO THIS EXCLUSIVE AMA: Kudo this post to reserve your spot! Reserve your spot now by kudoing this post.  Reservations will be prioritized on when your kudo for the post comes through, so don't wait! Click that "kudo button" today.   Invitations will be sent on April 2nd.Users posting Kudos after April 2nd. at 9AM PDT may not receive an invitation but will be able to view the session online after conclusion of the event. Give your "kudo" today and mark your calendars for April 3rd, 2024 at 9:30 AM PDT and join us for an engaging and informative session!

Tuesday Tip: Unlocking Community Achievements and Earning Badges

TUESDAY TIPS are our way of communicating helpful things we've learned or shared that have helped members of the Community. Whether you're just getting started or you're a seasoned pro, Tuesday Tips will help you know where to go, what to look for, and navigate your way through the ever-growing--and ever-changing--world of the Power Platform Community! We cover basics about the Community, provide a few "insider tips" to make your experience even better, and share best practices gleaned from our most active community members and Super Users.   With so many new Community members joining us each week, we'll also review a few of our "best practices" so you know just "how" the Community works, so make sure to watch the News & Announcements each week for the latest and greatest Tuesday Tips!     THIS WEEK'S TIP: Unlocking Achievements and Earning BadgesAcross the Communities, you'll see badges on users profile that recognize and reward their engagement and contributions. These badges each signify a different achievement--and all of those achievements are available to any Community member! If you're a seasoned pro or just getting started, you too can earn badges for the great work you do. Check out some details on Community badges below--and find out more in the detailed link at the end of the article!       A Diverse Range of Badges to Collect The badges you can earn in the Community cover a wide array of activities, including: Kudos Received: Acknowledges the number of times a user’s post has been appreciated with a “Kudo.”Kudos Given: Highlights the user’s generosity in recognizing others’ contributions.Topics Created: Tracks the number of discussions initiated by a user.Solutions Provided: Celebrates the instances where a user’s response is marked as the correct solution.Reply: Counts the number of times a user has engaged with community discussions.Blog Contributor: Honors those who contribute valuable content and are invited to write for the community blog.       A Community Evolving Together Badges are not only a great way to recognize outstanding contributions of our amazing Community members--they are also a way to continue fostering a collaborative and supportive environment. As you continue to share your knowledge and assist each other these badges serve as a visual representation of your valuable contributions.   Find out more about badges in these Community Support pages in each Community: All About Community Badges - Power Apps CommunityAll About Community Badges - Power Automate CommunityAll About Community Badges - Copilot Studio CommunityAll About Community Badges - Power Pages Community

Tuesday Tips: Powering Up Your Community Profile

TUESDAY TIPS are our way of communicating helpful things we've learned or shared that have helped members of the Community. Whether you're just getting started or you're a seasoned pro, Tuesday Tips will help you know where to go, what to look for, and navigate your way through the ever-growing--and ever-changing--world of the Power Platform Community! We cover basics about the Community, provide a few "insider tips" to make your experience even better, and share best practices gleaned from our most active community members and Super Users.   With so many new Community members joining us each week, we'll also review a few of our "best practices" so you know just "how" the Community works, so make sure to watch the News & Announcements each week for the latest and greatest Tuesday Tips!   This Week's Tip: Power Up Your Profile!  🚀 It's where every Community member gets their start, and it's essential that you keep it updated! Your Community User Profile is how you're able to get messages, post solutions, ask questions--and as you rank up, it's where your badges will appear and how you'll be known when you start blogging in the Community Blog. Your Community User Profile is how the Community knows you--so it's essential that it works the way you need it to! From changing your username to updating contact information, this Knowledge Base Article is your best resource for powering up your profile.     Password Puzzles? No Problem! Find out how to sync your Azure AD password with your community account, ensuring a seamless sign-in. No separate passwords to remember! Job Jumps & Email Swaps Changed jobs? Got a new email? Fear not! You'll find out how to link your shiny new email to your existing community account, keeping your contributions and connections intact. Username Uncertainties Unraveled Picking the perfect username is crucial--and sometimes the original choice you signed up with doesn't fit as well as you may have thought. There's a quick way to request an update here--but remember, your username is your community identity, so choose wisely. "Need Admin Approval" Warning Window? If you see this error message while using the community, don't worry. A simple process will help you get where you need to go. If you still need assistance, find out how to contact your Community Support team. Whatever you're looking for, when it comes to your profile, the Community Account Support Knowledge Base article is your treasure trove of tips as you navigate the nuances of your Community Profile. It’s the ultimate resource for keeping your digital identity in tip-top shape while engaging with the Power Platform Community. So, dive in and power up your profile today!  💪🚀   Community Account Support | Power Apps Community Account Support | Power AutomateCommunity Account Support | Copilot Studio  Community Account Support | Power Pages

Super User of the Month | Chris Piasecki

In our 2nd installment of this new ongoing feature in the Community, we're thrilled to announce that Chris Piasecki is our Super User of the Month for March 2024. If you've been in the Community for a while, we're sure you've seen a comment or marked one of Chris' helpful tips as a solution--he's been a Super User for SEVEN consecutive seasons!       Since authoring his first reply in April 2020 to his most recent achievement organizing the Canadian Power Platform Summit this month, Chris has helped countless Community members with his insights and expertise. In addition to being a Super User, Chris is also a User Group leader, Microsoft MVP, and a featured speaker at the Microsoft Power Platform Conference. His contributions to the new SUIT program, along with his joyous personality and willingness to jump in and help so many members has made Chris a fixture in the Power Platform Community.   When Chris isn't authoring solutions or organizing events, he's actively leading Piasecki Consulting, specializing in solution architecture, integration, DevOps, and more--helping clients discover how to strategize and implement Microsoft's technology platforms. We are grateful for Chris' insightful help in the Community and look forward to even more amazing milestones as he continues to assist so many with his great tips, solutions--always with a smile and a great sense of humor.You can find Chris in the Community and on LinkedIn. Thanks for being such a SUPER user, Chris! 💪🌠

Tuesday Tips: Community Ranks and YOU

TUESDAY TIPS are our way of communicating helpful things we've learned or shared that have helped members of the Community. Whether you're just getting started or you're a seasoned pro, Tuesday Tips will help you know where to go, what to look for, and navigate your way through the ever-growing--and ever-changing--world of the Power Platform Community! We cover basics about the Community, provide a few "insider tips" to make your experience even better, and share best practices gleaned from our most active community members and Super Users.   With so many new Community members joining us each week, we'll also review a few of our "best practices" so you know just "how" the Community works, so make sure to watch the News & Announcements each week for the latest and greatest Tuesday Tips!This Week: Community Ranks--Moving from "Member" to "Community Champion"   Have you ever wondered how your fellow community members ascend the ranks within our community? What sets apart an Advocate from a Helper, or a Solution Sage from a Community Champion? In today’s #TuesdayTip, we’re unveiling the secrets and sharing tips to help YOU elevate your ranking—and why it matters to our vibrant communities. Community ranks serve as a window into a member’s role and activity. They celebrate your accomplishments and reveal whether someone has been actively contributing and assisting others. For instance, a Super User is someone who has been exceptionally helpful and engaged. Some ranks even come with special permissions, especially those related to community management. As you actively participate—whether by creating new topics, providing solutions, or earning kudos—your rank can climb. Each time you achieve a new rank, you’ll receive an email notification. Look out for the icon and rank name displayed next to your username—it’s a badge of honor! Fun fact: Your Community Engagement Team keeps an eye on these ranks, recognizing the most passionate and active community members. So shine brightly with valuable content, and you might just earn well-deserved recognition! Where can you see someone’s rank? When viewing a post, you’ll find a member’s rank to the left of their name.Click on a username to explore their profile, where their rank is prominently displayed. What about the ranks themselves? New members start as New Members, progressing to Regular Visitors, and then Frequent Visitors.Beyond that, we have a categorized system: Kudo Ranks: Earned through kudos (teal icons).Post Ranks: Based on your posts (purple icons).Solution Ranks: Reflecting your solutions (green icons).Combo Ranks: These orange icons combine kudos, solutions, and posts. The top ranks have unique names, making your journey even more exciting! So dive in, collect those kudos, share solutions, and let’s see how high you can rank! 🌟 🚀   Check out the Using the Community boards in each of the communities for more helpful information!  Power Apps, Power Automate, Copilot Studio & Power Pages

Find Out What Makes Super Users So Super

We know many of you visit the Power Platform Communities to ask questions and receive answers. But do you know that many of our best answers and solutions come from Community members who are super active, helping anyone who needs a little help getting unstuck with Business Applications products? We call these dedicated Community members Super Users because they are the real heroes in the Community, willing to jump in whenever they can to help! Maybe you've encountered them yourself and they've solved some of your biggest questions. Have you ever wondered, "Why?"We interviewed several of our Super Users to understand what drives them to help in the Community--and discover the difference it has made in their lives as well! Take a look in our gallery today: What Motivates a Super User? - Power Platform Community (microsoft.com)

Top Solution Authors
Top Kudoed Authors
Users online (6,451)