Kinh Nghiệm Hướng dẫn How to export Excel data to SharePoint list Chi Tiết
Bạn đang tìm kiếm từ khóa How to export Excel data to SharePoint list được Update vào lúc : 2022-11-13 12:01:00 . Với phương châm chia sẻ Bí kíp về trong nội dung bài viết một cách Chi Tiết Mới Nhất. Nếu sau khi đọc tài liệu vẫn ko hiểu thì hoàn toàn có thể lại phản hồi ở cuối bài để Admin lý giải và hướng dẫn lại nha.
I recently started a consulting job for a construction company to fix their Excel quotation template. But even the best Excel based template in the world wasnt going to solve all their problems.After a bit of investigation, I came to the conclusion that what they really needed was a custom quote app which could be built fairly easily with Microsoft PowerApps.PowerApps can easily connect to and save data to many different data sources, so after a bit more investigating, I decided I would use SharePoint lists to store all the quote data from the app.I had heard about SharePoint before, but I never really knew what it was.I had also come across it in Excel since Excel Tables have the ability to export to SharePoint.In this post, Im going to share with you what I found out about SharePoint and Excel.As the name might suggest, its a place to share stuff.Its a browser based platform from Microsoft that can house all the content (data, files, folders, photos, documents etc.) related to your work to make it easier to share and collaborate with others on the same work.You can think of it as an internal company website (intranet) that can help improve your organizations data management. A sort of Wikipedia style server or information portal. Its also a bit like OneDrive in that it can store and manage your files in the cloud, but with some more advanced collaboration features.
I set up my SharePoint site quickly and didnt bother to edit or customize anything. This is what you get straight out of the box.Theres a lot of customization possible, but Im just using it to store a couple data lists so didnt think it was worth spending any time on.SharePoint doesnt come with any of the Office 365 trang chủ products. Its really only intended for business use so its only available with an Office 365 Business account subscription.In fact, youll have to shell out for the Office 365 Business Premium account if you want to use SharePoint. Its not available with the regular business account.A SharePoint list is similar to an Excel Table, its a container for data.SharePoint lists allow you to store data in list format with rows and columns.You can use a list to house all your customer contact details, employee details, product pricing etc. If you can organize the data into a table structure, then it can be stored in a SharePoint list.Unlike Excel tables, the columns in a SharePoint list allow you to set a column as a certain type of data like text, number, date, hyperlink etc A SharePoint list can even contain attachments so you could attach a photo to each employee in your employee details list.Creating a new list in SharePoint is easy.
From the homepage of your SharePoint site, press the
New button then choose
List from the options. Give the list a name and description and press the
Create button.
You will now have a blank SharePoint list!All SharePoint lists contain a
Title column, and this should usually be reserved for a field that contains unique values.You can customize your new list by adding columns, assigning data types to each column and then adding data to the list.The new list might appear in your site navigation menu, but if not you can use the
Site Contents menu item to navigate back to the list from any place on your SharePoint site.If your data already exists in Excel, then there is an easier way to create a new SharePoint list.You can export data from Excel to SharePoint. This way, you can avoid the tedious task of creating and assigning data types to columns.If your data already has column names in Excel, then these are also exported to your new SharePoint list. The Export wizard will also guess what SharePoint data types to use for the columns based on the data.
Create an Excel Table for the Data
First, the data is going to need to be 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 use the Ctrl + T keyboard shortcut.
Make sure you name the table after by going to the
Table Tools Design tab and giving it a new name in the
Properties section.
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 List.
The first step in the Export to SharePoint List wizard.Enter the
URL for your SharePoint site.Check the box if you want to create a
read-only connection to the SharePoint list. This means when you add data to the list on your SharePoint site, the new data will show up in your Excel table when you right click and refresh the table.Enter a
Name for the new list.Enter a
Description for the new list. This is an optional entry.Press the
Next button to review the data types selected before exporting.Your SharePoint URL should be something like ://yourcompany.sharepoint/sites/yoursitename.
In step 2 of the export wizard, review the data types for each of the columns in the data. If a selected data type is incorrect, youll need to cancel the export and either fix the key cell in the table or change the entire column of data in the table.When youre happy with the data types in the export wizard, press the
Finish button to complete the export.How big the list is will affect how long the export takes. A handful of rows will be near instant but a couple thousand rows can take several minutes to export.
When the export process completes successfully, youll 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, you can find the list from the
Site Contents navigation menu.
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 connection to the new SharePoint list.If you select this option then youll be able to refresh your Excel table with any new or changed data from the SharePoint list.
Select the table and go to the
Table Tools Design 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 list.You can also move data the other way by exporting from SharePoint to Excel.
Theres a big
Export to Excel button in each list.
Press this button and the browser will tải về a
query.iqy file.
Open this file and Excel will launch and prompt you to choose where to load the data (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 same read-only setup that was an option when exporting from Excel. If you missed enabling that option, no worries because you can achieve the same thing by exporting from your SharePoint list.Power query can be used to connect to all sorts of data sources and SharePoint lists are one of them.This will give you more control over which data in the list you import as 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 option is also available under the
From Other Sources menu as
From SharePoint List.
You will be prompted to enter the URL to the SharePoint site. This should be of the format ://yourcompany.sharepoint/sites/yoursitename. Then you can press the
OK button.
This will open the power query navigator preview where you can select the list you want to import. Once selected, a preview of the list will show on the right.A large number of columns in the data preview will look unfamiliar. This is because SharePoint lists contain a lot of extra columns even if they arent being used.For example, every SharePoint list will have an ID column which is a unique identifier automatically created for each row in the list.You will need to edit the query slightly to avoid importing all these unfamiliar columns. Press the
Transform Data button to open the power query editor.
There are a lot of extra columns, so it will usually be easier to select the columns you want to keep (hold the Ctrl key to select multiple columns). Then right click on any of the selected column headings and choose
Remove Other Columns from the menu.You can also do any other type of data transformation available with power query if you would like.
Once youre finished with the query, you can save it and load the data into Excel. Go to the
trang chủ tab in the power query editor and press the
Close & Load command to save the query and load the data into an Excel table in your workbook.SharePoint can store more than just lists, it can also store all types of files in a document library.You can use use power query to import and combine data from multiple data files like Excel workbooks or CSVs.
What is a SharePoint Document Library or Folder?
A SharePoint document library is very similar to a thư mục on your computers hard drive. Its a place to store all your related files.You might set up a document library for all your monthly sales files.
Here I have a document library called Sales and in a thư mục called 2018Q1 in which I have 3 Excel workbooks.
Each file has sales data by day for the particular month.
Export a Folder to Excel
If you click on the
Export to Excel button found in the SharePoint thư mục this will tải về a
query.iqy file.
When you open this file it launches Excel and creates a read-only connection to the list of files in the thư mục. 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 thư mục names. This will open up the SharePoint file or thư mục.
Create a From SharePoint Folder Query
Youll 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
Get Data choose
From File choose
From SharePoint Folder.
Now youll need to input the site URL (not the thư mục URL).
The Navigation preview will show a list of all files in the site and youll need to use power query to filter down to the files you want. Press the
Transform Data button.
Filter on the
Folder Path column to the desired thư mục. Then further filter on any file
Name or
Extension to get exactly the files you want.
When only the files you 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 can then select which sheets to import and view a preview of the data on the right side. Press the
OK button to combine the files.You can then
Close & Load the query to import all the data. Now if you add a new monthly sales files to the thư mục and refresh the query, the new files data will be included in the querys output.ConclusionsSharePoint is a great place for storing files for collaboration and data thats shared throughout the company.But it might not be the best place for further using or analyzing the files or data.Thats ok though, SharePoint and Excel are very well integrated. Its easy to push and pull data from SharePoint to Excel.You can quickly get data using the export options in either Excel or SharePoint. If youre comfortable with power query, you can even use that.
Clip How to export Excel data to SharePoint list ?
Bạn vừa tìm hiểu thêm nội dung bài viết Với Một số hướng dẫn một cách rõ ràng hơn về Review How to export Excel data to SharePoint list tiên tiến và phát triển nhất
Share Link Down How to export Excel data to SharePoint list miễn phí
Bạn đang tìm một số trong những Chia Sẻ Link Cập nhật How to export Excel data to SharePoint list miễn phí.
Hỏi đáp vướng mắc về How to export Excel data to SharePoint list
Nếu sau khi đọc nội dung bài viết How to export Excel data to SharePoint list vẫn chưa hiểu thì hoàn toàn có thể lại phản hồi ở cuối bài để Admin lý giải và hướng dẫn lại nha
#export #Excel #data #SharePoint #list