Excel & SharePoint

-

Objective

I needed to prepare an Excel file for importing into SharePoint.

Excel

  • First, I need an Excel formula to populate a short name (255 characters) from a much longer name ( found in "col").
  • The length of the short name is constrained by SharePoint's "single line of text" field.
  • The challenge is to find acronyms within col or if am acronym can't be found, to truncate col to 255 characters.
  • Example: 
GNU is Not Unix (GNU) => GNU
This is another name => This is another name
  • This is the formula to use. 
= IF(ISNUMBER(FIND("(";[col]));MID([col];FIND("(";[col]) + 1; FIND(")";[col]) - FIND("(";[col])-1);LEFT(CLEAN(TRIM([col]));255))

Importing into SharePoint

The only error free way to do this is to use Excel to export into SharePoint.
  • Create an Excel table and select it
  • Go to "table tools" tab and choose the "export table to SharePoint list" option within "export".


Comments

Post a Comment

Popular posts from this blog

Understanding ITIL Service Management the UML way…

-
Image
I originally had a great deal of difficult reconciling the differences amongst ITIL Change Management, Incident Management, and Problem Management. Having researched on the subjects and understanding them better now, I’ve tried to put the concepts together using a UML class diagram to capture my understanding and to keep it imprinted. Similarly, I wanted to differentiate the various categories of changes (Standard, Emergency, Normal) using an easy to understand & self-explanatory manner – a UML Activity Diagram!
Read more

When is a Sequence Number Not Sequential?

-
A sequence number or an identity column guarantees the following: Number served is unique. Number served is sequential (ascending order) - Identity columns guarantees this; sequence number in Oracle RAC mode doesn't, unless ORDER is used. However: It does not guarantee the sequence is gap-free Sequence number/ identity columns do not partake in transactions Number loss/ gaps happen due to the following: Served number does not get utilised (typically due to transaction rollback) Server restart/ failures - database servers tend to cache sequences in memory for performance reasons. E.g. Oracle Sequence number set-up to be CACHEd in memory. According to Oracle 11gR2 Database Documentation : for the Sequence database concept, use of Oracle Sequence does not guarantee gap-free set of numbers.   If your application requires a gap-free set of numbers, then you cannot use Oracle sequences . You must serialize activities in the database using your own developed cod...
Read more

How to depict (Professional-Looking) Logical Network Diagrams in Astah

-
Image
I have always had to create (professional-looking) UML diagrams using Visio-like icons. You’ve got to admit that showing a bunch of boring UML boxes hardly interests anyone less technical (like anyone whose designation has the words “manager” or “director”). This is actually possible in Astah! I’ve created a template for future projects that allows me to produce logical network diagrams that look like the following: To create this diagram, you need to do the following: Assign various icons to stereotypes. I created the following for my use: Create a new UML Deployment Diagram Every element in the diagram is created as a Node The nodes are subsequently assigned the appropriate <<stereotype>>. E.g. Internet is <<internet>>, WebZone FW is <<firewall>>, Web LB is <<loadBalancer>> Ensure the “Icon Notation” is set to “Customized Icon” for each node Instead of the boring boxes, the corresponding icons automatically gets displayed!
Read more