Challenges faced after exporting Excel data to SharePoint


In previous article, we have seen how to connect to SharePoint site from excel for exporting data. Today, let us see the challenges faced in SharePoint after exporting the data.

  1. Number columns with greater than or equal to 4 digits in excel – Lets consider, Application ID column with value 20158 for better understanding. As number column in excel are associated with number column in SharePoint, they are displayed with commas as 20,158
    • To remove the commas, never try converting it into Single line of text in SharePoint because it will be converted into exponential form as 20+158e
    • If you would like to show them without commas, create a calculated column with the below formula:
    • =TEXT([Application ID], “0”)
    • By using this formula, Commas will be removed but calculated columns will be shown only in display form
    • So, before exporting it is recommended to convert number field with more than 4 digits in excel to text column as to associate it with single line of text. Now, to prevent the users from entering number in Single line of text field, follow the below steps:
    • Go to List->List settings and click on Application ID column
    • It takes us to Change Column page for Application ID where we need to enter the below formula in Data validation section and click ok
    • =ISNUMBER([Application ID]+0)
  2. Person or Group column in SharePoint for user names like Manager in excelAs text columns are associated with  Single line of text, they cannot be converted to Person or group column. What if we need to use People Picker(Person or Group) for user names. Below is a small workaround which will be useful if there are thousands of items.
    •  If number of items is less, create a person or group column, open edit form of each item and copy paste the value manually from single line of text to Person or group field. When there are large number of items(say 2000), create below two columns in SharePoint:
    • Flag – Yes/No type
    • Mgr – Person or group type in SharePoint
    • Go to SharePoint designer and create a workflow which should run whenever an item is changed
    • Create the below step:
      • If flag equals Yes
      • set Mgr(Person or group column) to Manager(Single line of text)
    • Now, go to Datasheet view of the list and simply select Yes and drag the Yes value till the last item
    • Advantage: It reduces the time than doing it manually
    • Disadvantages:
      • Workflow fails when exact match is not found meaning either the person has left the organization or their Id in active directory is changed
      • We need to manually fill for those items where workflow has failed
      • Remove the workflow from the list once all the user names are copied to Person or group column
  3. Missing “Add new item” link in SharePoint 2010 – To overcome this, create a Standard view and set it as default view

If the above post is helpful, do not forget to like my article and rate it.

Advertisements

Exporting Excel table to SharePoint


SharePoint is used everywhere especially for document and content management. Nowadays, data that were stored in excel are being moved to SharePoint , thus making the ease of access and management of data. To facilitate this, SharePoint has an app(list) called “Import Spreadsheet” as shown below:

import-spreadsheet creating-sp-list
import-dialog

This approach is known to everyone where we select Import Spreadsheet list, provide a name and description, browse the excel file, select the range of cells to be imported. But only few of us know how to connect from excel to SharePoint. This article will cover the later approach. Let us see how to do.

  • Open desired sheet in excel and select the range of cells to be moved to SharePoint(both rows and columns)
  • Click on Insert->Table. In create table dialog box, check the range and hit ok

create-table

  • Now, you will be able to see Design Tab in the ribbon.
  • Under External Table Data section, click on Export->Export Table to SharePoint List

design-and-export

  • In Export table step 1 dialog box, give the address of SharePoint site, check create a read-only connection box, provide a name and description for SharePoint list and click Next

step-1

  • “Create a read only connection to the new SharePoint list” box is used to keep the excel table up-to-date with the changes made in SharePoint list. This is only one way meaning, you cannot update changes from excel to SharePoint once it is exported. When you check the box, it creates two columns in the excel table
    • Item Type  – Item/Folder
    • Path – folder path for a list item
  • In step 2, we can verify the data types of excel columns associated with data types in SharePoint. For example, Student Name of type text in excel is associated with Single line of Text in SharePoint. After verification, click Finish button.

step-2

  • Table has been published successfully and two columns(Path and Item Type) are created in excel as said previously

exported-successfully

excel-table
Excel Table

sharepoint-list
SharePoint List
  • To demonstrate the power of “Create a read-only connection to the new SharePoint list”, let us edit the mark of student, Chitra in SharePoint list

edit-in-splist

  • Then go to excel and click Design->Refresh. Excel table is updated as depicted below

autoupdated-excel

  • To remove this read-only connection, click on Design->Unlink

unlink

Advantages:

  • This approach is useful when we need to check the associated datatypes(shown within braces) before exporting data to SharePoint. If we need to change the data type, cancel step 2, change datatype in excel and follow step 1 & 2 again

advantage

  • Creating a read-only connection allows to keep excel report up-to-date rather than often exporting updated data from SharePoint list.

If you found this post useful, please press like button, rate this article and share your comments.