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.


Leave a Comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s