formatting excel sheet for upload to sharepoint

Importing and Exporting Data from SharePoint and Excel

I recently started a consulting job for a construction company to set their Excel quotation template. But even the best Excel based template in the world wasn't going to solve all their issues.

After a bit of investigation, I came to the decision that what they really needed was a custom quote app which could exist built fairly hands with Microsoft PowerApps.

PowerApps tin can easily connect to and save data to many different data sources, so later on a scrap more investigating, I decided I would use SharePoint lists to store all the quote data from the app.

I had heard about SharePoint before, just I never really knew what it was.

I had too come across it in Excel since Excel Tables have the ability to consign to SharePoint.

In this mail, I'm going to share with you what I found out about SharePoint and Excel.

What is SharePoint?

Equally the name might advise, information technology'southward a place to share stuff.

It's a browser based platform from Microsoft that can business firm all the content (data, files, folders, photos, documents etc.) related to your piece of work to go far easier to share and interact with others on the same work.

You tin can call up of it every bit an internal company website (intranet) that can assistance ameliorate your arrangement'south data management. A sort of Wikipedia style server or data portal. It's also a bit like OneDrive in that information technology tin store and manage your files in the cloud, just with some more advanced collaboration features.

I set my SharePoint site speedily and didn't carp to edit or customize anything. This is what you get straight out of the box.

In that location'southward a lot of customization possible, but I'm only using it to store a couple information lists so didn't retrieve it was worth spending any fourth dimension on.

How Do I Get SharePoint?

SharePoint doesn't come with whatever of the Office 365 Habitation products. Information technology's really only intended for business use so information technology'southward but bachelor with an Office 365 Business account subscription.

In fact, you'll have to vanquish out for the Office 365 Business organization Premium account if you desire to use SharePoint. It'due south not bachelor with the regular business account.

What is a SharePoint Listing?

A SharePoint list is similar to an Excel Table, information technology'due south a container for data.

SharePoint lists allow you to store information in list format with rows and columns.

You can use a listing to firm all your customer contact details, employee details, product pricing etc. If you lot can organize the data into a tabular array structure, and so it tin exist stored in a SharePoint list.

Unlike Excel tables, the columns in a SharePoint list allow you to set a column as a sure type of data like text, number, date, hyperlink etc… A SharePoint listing can even contain attachments so y'all could attach a photo to each employee in your employee details list.

Creating a New List in SharePoint

Creating a new list in SharePoint is like shooting fish in a barrel.

From the homepage of your SharePoint site, press the New button then cull List from the options. Give the list a name and description and printing the Create button.

You will now have a blank SharePoint list!

All SharePoint lists comprise a Title column, and this should unremarkably be reserved for a field that contains unique values.

You tin customize your new list by calculation columns, assigning information types to each column and then adding information to the listing.

The new list might appear in your site navigation carte du jour, but if not you tin can use the Site Contents menu item to navigate back to the list from any place on your SharePoint site.

Exporting an Excel Table to a SharePoint Listing

If your data already exists in Excel, then in that location is an easier way to create a new SharePoint listing.

You tin export data from Excel to SharePoint. This way, you lot can avoid the tedious task of creating and assigning information types to columns.

If your information already has cavalcade names in Excel, then these are also exported to your new SharePoint list. The Consign sorcerer will also estimate what SharePoint data types to apply for the columns based on the data.

Create an Excel Tabular array for the Data

Showtime, the data is going to need to exist in a table format.

Select the data then go to the Insert tab and choose Table. Another way to turn your data into a table is to apply the Ctrl + T keyboard shortcut.

Make sure you lot name the table after by going to the Table Tools Design tab and giving it a new proper noun in the Properties department.

Export the Excel Table to a SharePoint List

Select the table and go to the Table Tools Design tab and select the Export command then choose Export Table to SharePoint Listing.

The first stride in the Export to SharePoint List wizard.

1. Enter the URL for your SharePoint site.
2. Check the box if yous want to create a read-only connection to the SharePoint list. This means when you add information to the list on your SharePoint site, the new data will show upwards in your Excel tabular array when you right click and refresh the table.
3. Enter a Name for the new list.
4. Enter a Clarification for the new list. This is an optional entry.
5. Press the Side by side button to review the information types selected earlier exporting.

Your SharePoint URL should be something like https://yourcompany.sharepoint.com/sites/yoursitename.

In stride two of the export magician, review the information types for each of the columns in the information. If a selected data type is incorrect, y'all'll need to cancel the export and either fix the key cell in the table or change the unabridged column of data in the table.

When you lot're happy with the data types in the export wizard, printing the Cease button to complete the export.

How large the listing is volition bear on how long the export takes. A handful of rows will exist nearly instant just a couple thousand rows can take several minutes to export.

When the consign process completes successfully, you'll get a pop up notification with a link to the location of the new list.

You can click on this link and the SharePoint list will open in your default browser. Alternatively, y'all can find the listing from the Site Contents navigation card.

Refreshing an Excel Table with a Read-Only Connection to a SharePoint List

In the export table wizard there was an option to create a read-only connexion to the new SharePoint list.

If you select this choice then you'll be able to refresh your Excel table with any new or changed data from the SharePoint list.

Select the tabular array and go to the Tabular array Tools Pattern tab and press the Refresh button or right click on the table and select Refresh from the menu.

The data in the Excel table will be updated with the latest data from the SharePoint listing.

Exporting a SharePoint List to an Excel Table

You can too move data the other way by exporting from SharePoint to Excel.

There'due south a big Export to Excel push in each listing.

Press this push and the browser will download a query.iqy file.

Open this file and Excel will launch and prompt you to choose where to load the information (Table, Pivot Table or Pivot Chart) and choose the location to load it (an existing worksheet, new worksheet or new workbook).

This is actually the exact aforementioned read-merely setup that was an option when exporting from Excel. If you missed enabling that option, no worries because y'all tin achieve the same affair by exporting from your SharePoint list.

Importing a SharePoint List to Excel with Power Query

Power query tin exist used to connect to all sorts of data sources and SharePoint lists are 1 of them.

This will give y'all more than control over which data in the list you import equally compared to creating a read-only connection to the SharePoint list.

Create a SharePoint Power Query

To create a new SharePoint list query, go to the Data tab ➜ select Get Data ➜ choose From Online Services ➜ choose From SharePoint Online List.

This choice is too bachelor under the From Other Sources carte as From SharePoint List.

You volition exist prompted to enter the URL to the SharePoint site. This should be of the format https://yourcompany.sharepoint.com/sites/yoursitename. So yous can press the OK button.

This will open the power query navigator preview where you lot tin select the list yous want to import. Once selected, a preview of the listing will evidence on the correct.

A large number of columns in the information preview volition look unfamiliar. This is because SharePoint lists contain a lot of extra columns fifty-fifty if they aren't existence used.

For example, every SharePoint listing will have an ID column which is a unique identifier automatically created for each row in the list.

Y'all will need to edit the query slightly to avoid importing all these unfamiliar columns. Press the Transform Data button to open up the ability query editor.

At that place are a lot of extra columns, then it will usually be easier to select the columns you want to continue (hold the Ctrl key to select multiple columns). Then right click on any of the selected cavalcade headings and choose Remove Other Columns from the menu.

You tin as well do any other type of data transformation available with ability query if yous would similar.

Once you're finished with the query, you can relieve information technology and load the data into Excel. Become to the Habitation tab in the power query editor and press the Close & Load command to salve the query and load the data into an Excel tabular array in your workbook.

Importing Files from a SharePoint Document Library or Folder with Power Query

SharePoint tin can store more just lists, information technology can likewise store all types of files in a certificate library.

You can use use power query to import and combine data from multiple data files like Excel workbooks or CSV's.

What is a SharePoint Document Library or Binder?

A SharePoint document library is very like to a binder on your computers hard drive. It's a place to store all your related files.

Y'all might set up a certificate library for all your monthly sales files.

Hither I have a certificate library called Sales and in a folder chosen 2018Q1 in which I have 3 Excel workbooks.

Each file has sales data by 24-hour interval for the item month.

Export a Folder to Excel

If y'all click on the Consign to Excel button found in the SharePoint folder this will download a query.iqy file.

When you open this file it launches Excel and creates a read-only connection to the listing of files in the folder. You can refresh this just like when exporting a SharePoint List to an Excel Table.

This table only contains the names of files and not the actual contents of the files. There is a handy clickable hyperlink for each of the file or folder names. This volition open up the SharePoint file or folder.

Create a From SharePoint Folder Query

You'll need power query if you want to import and combine the data inside all the files.

To create a SharePoint Folder query go to the Data tab ➜ select Go Data ➜ choose From File ➜ choose From SharePoint Folder.

Now you lot'll need to input the site URL (not the binder URL).

The Navigation preview volition show a listing of all files in the site and you'll need to use power query to filter downwardly to the files you lot want. Press the Transform Information push button.

Filter on the Folder Path column to the desired folder. And then further filter on any file Proper name or Extension to get exactly the files yous want.

When only the files yous want to import and combine are left, click on the Combine Files toggle in the Content column heading.

Power query will import and combine the files based on a sample file. You tin then select which sheets to import and view a preview of the data on the correct side. Press the OK push to combine the files.

You can then Close & Load the query to import all the data. At present if you add a new monthly sales files to the folder and refresh the query, the new file'south data will be included in the query'south output.

Conclusions

SharePoint is a great place for storing files for collaboration and data that's shared throughout the visitor.

But it might not be the best identify for further using or analyzing the files or data.

That's ok though, SharePoint and Excel are very well integrated. Information technology'due south like shooting fish in a barrel to push and pull data from SharePoint to Excel.

You can chop-chop get data using the export options in either Excel or SharePoint. If you're comfortable with power query, you tin can even utilise that.

About the Author

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You tin notice other interesting manufactures from John on his blog or YouTube aqueduct.

comeausirsenes.blogspot.com

Source: https://www.howtoexcel.org/sharepoint-and-excel/

Share this post