Lydon Solutions

Construction Project Management Software Solutions

Microsoft Excel

How to Import Data from Excel into SharePoint

Tips from the Field |

There are a few ways to import data from Excel into SharePoint – learn when to use each method based on your needs.

We previously shared how to export data from SharePoint into Excel. But what if you need to import data from Excel into SharePoint? Importing your Excel data into SharePoint enables a ton of extra functionality.

Here’s a common example:

Maybe a project manager on your team created a huge spreadsheet to track data on a project such as permits or materials. Over time the spreadsheet has gotten out of control. Keeping information up to date and sharing it with stakeholders is a pain.  So you’d like to add some additional functionality like workflows based on field changes. You also want to share it somewhere and set permissions for who can access and edit data.

Putting this spreadsheet into SharePoint would allow you to do all of the above and more.

So what is the best way to import data from Excel into SharePoint?  That answer depends on the contents and complexity of your Excel data. Below are two common methods – and one bonus method.

Method One: Use the Import Spreadsheet App in SharePoint

The Import Spreadsheet App is a useful tool for basic spreadsheets. The tool creates the list with column titles in SharePoint for you and automatically imports your data.

But be careful. The Import Spreadsheet App works best when your data is a simple list with single text field data. It can get easily hung up when trying to import a list that mixes multiple data types across columns and even in a given column. Why? Because by using this app, you are letting SharePoint make the call as to how to handle the column data types.  This can lead to unexpected results.

For example, the Import Spreadsheet App often stumbles on single-line text types in date type columns or vice versa.  So if one of your spreadsheet columns includes dates and times, the tool will make that entire column a date/time data type when importing it to SharePoint. But if some of the fields in that column have text entries (e.g. “NA” or “TBD”), you will start getting errors when editing and viewing the list. And then you have the tedious chore of troubleshooting that list in SharePoint.

Don’t get me wrong, the tool can be a useful timesaver for simple spreadsheets. However, if your spreadsheet is more complex, you might spend more time fixing errors after the import.

Method Two: Create a Custom List

A better approach to import data from Excel into SharePoint – especially complex data and spreadsheets – is to use the Custom List App. This allows you to create the list the way you want it. This process takes a little more effort upfront, but saves you headaches later.

Here’s how it works:

Step 1 –First, you will need to go to Site Contents and Add an App to enable the Custom List App.

Step 2 – Select the Custom List App.

Step 3 – Define the Columns Title and Types to represent your spreadsheet in your Custom List.

Step 4 – Create a view that matches the columns in the order of your spreadsheet.  You can create a Datasheet View or Standard View and switch to Datasheet or Quick Edit.

IMPORANT: Make sure to include all the columns in your view that are required or the import will not work!

Step 5 – Highlight the data from your Excel file and copy it (Control-C). Then paste (Control-V) the data in an open row in the Edit View of the List.

Step 6 – Make sure all of the items paste without errors. Any errors will show as red circles to the left of the row. When done, click “Stop editing this list.”

Presto, the above steps are a copy-and-paste method to import data from Excel into SharePoint.

Method 3 – Set up an automated process to import data from Excel into SharePoint 

Here’s a bonus method for you. Did you know it’s possible to have a list update automatically from Excel into SharePoint? There are many options to automate importing Excel data into SharePoint.

Get a free consultation to learn more.

Want more tips and tricks for construction project management professionals?

Our new Tips from the Field series features handy information for construction project managers. Subscribe to our newsletter to get our latest tips and tricks delivered directly your inbox each month.

And be sure to check out last month’s installment to learn how to easily reorganize your files in SharePoint.

10 Reasons Construction Project Management Information Systems Fail

How-To |

Most construction specific Project Management Information Systems (PMIS) implementations fail. This will not be welcome news if your company just spent tens of thousands of dollars implementing one. But we meet a surprising number of construction managers and owners who are unhappy with their current PMIS deployment. They typically come to us for help after discovering that their fancy new project management system is too costly and complex to implement. Worse yet, no one at their company wants to use it because it doesn’t help them do their jobs better.

We’ve had these conversations many times over the years – enough to know that the construction management landscape is sadly riddled with expensive and ineffective PMIS solutions.

How did the construction industry get to this point? We outline 10 reasons below. If you’re considering updating or implementing a PMIS at your construction company, we hope these tips help make the process smoother. In our next blog post, we’ll also share our vision for how a proper PMIS solution should be designed and implemented.

Top Reasons why Construction Project Management Information Systems fail:

  1. The system is not flexible.  Many PMIS solutions force a team to manage a project in a specific way. The reality is that most companies, projects, and people do things differently. Processes also evolve over time. A PMIS system should be flexible and adaptable – without requiring expensive custom coding.
  2. Internal politics prevent adoption.  Multiple departments are involved with managing a project. Each one has its own specific workflows and software platforms. Implementing a “one-size-fits-all” PMIS across these disparate groups often leads to resistance and lack of adoption. But who can blame them? Deploying a new software tool that doesn’t integrate with other departments’ existing platforms is making more work for them, not less. So everyone falls back on manually importing and exporting Excel files to collaborate. Not exactly a gain in efficiency.
  3. The systems lack internal support.  Individuals who are involved with projects from requirements through implementation make ideal project champions for choosing the proper PMIS. These champions are critical to helping a product gain acceptance in the organization and creating grass roots acceptance.  Without internal support, systems “die on the vine” only to be replaced with another expensive system that never gets implemented.  The system is blamed for the failure and not the organization.
  4. Companies don’t understand what they have.  Project managers and executives are busy. They often simply don’t have the time or technical skills to evaluate the capabilities of their current tools. So they bring in a consultant. But most consultants make money selling fancy new software platforms as well as the services required to deploy and customize them. But this approach overlooks the most efficient and effective option: improving the PMIS solution that the company already owns.
  5. A company’s IT department “locks down” the application.  Once a PMIS is absorbed into a company and hosted on their internal servers, the IT department takes over managing the tool. Further customization and feature requests from users are too often stifled or delayed. Users then have one more reason to not use the tool.Blog Quotes Construction Project Management Information Systems
  6. Business requirements are not valued.  Many companies jump into buying an expensive PMIS solution thinking that the software itself will fix their issues. They then task the implementation of the system to those in the organization that know the least – usually the “newbies” since other subject matter experts are busy running projects.  But this immediately devalues the product.  A system is only as good as the people that develop and support it. There must be commitment to the entire software development lifecycle for a product to be successfully adopted in an organization. Without knowledge of the business processes, proper training, and acceptance by the organization, all enterprise software systems will fail
  7. The wrong team is implementing the solution.  Software development and implementation is a unique craft.  Most construction professionals think they can do it because they managed a construction project. But the truth is they lack the technical experience to do the implementation right. On the flip side, IT companies that don’t know construction and try to build solutions often fail because they build tools that are not grounded in how work is actually performed in the field. A PMIS will fail without the expertise of the right team that knows both construction and how to implement software.
  8. The solution lacks a unified vision.  Companies typically buy the “best of breed” software to do specific things (dashboards, cost, contracts, etc.). Then they end up using only 5% of each systems’ functionality because the rest of the features don’t fit their needs and don’t integrate with other systems. We’ve heard horror stories of team members using up to 10 different systems to do their job – many of which become corporate mandates that make doing their job even harder.  What ends up happening? These individuals enter the least amount of information they can get away in these mandated systems, but ultimately end up doing their real job in Excel. Again, less efficiency, not more.
  9. Management is sold an “out-of-the-box” lie.  Every consulting and software company will claim to offer a one-size-fits-all solution. But there is no such product.  Most, if not all, PMIS deployments are custom coded. Each and every change will be expensive and make the tool more difficult to update in the future. The consultants you hired to install and configure your new PMIS will never leave because the platform is too complex for your in-house IT to manage. Ultimately, the cost to develop and maintain the product will become its downfall.
  10. Companies try for the Hail Mary.  Companies too often try to do too much when they deploy PMIS solutions. Instead of getting the small win, they end up in a state of constant re-engineering.

A better way to implement your Construction Project Management Information System

As we can see, the challenges and resulting opportunities that the construction industry faces in choosing a new PMIS are numerous. Stay tuned, because in our next blog post we will explain how to successfully implement a PMIS solution. The final blog post in our series will share how construction professionals can use solutions already deployed at their organization – tools like Microsoft SharePoint and Excel – to avoid these pitfalls and achieve better results.