[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2792
  • Last Modified:

paste HTML text with tags as rich text into sharepoint Multiple lines of text

we are trying to add many records from an old website database into a new sharepoint list via datasheet view (we have the old database records in excel with columns matching the new sharepoint list columns).
The old database includes a couple of fields with HTML code tags, and we are wanting to avoid having to manually copy the rich text from the webpage for every record...

What options could we use to achieve this?
0
northtecicts
Asked:
northtecicts
  • 6
  • 3
1 Solution
 
nmarunCommented:
I thought you could just copy and paste into the datasheet view.
Copy column wise. I just tried this and it works. Select one column in excel and paste that column in the datasheet view. Make every column as 'optional' before pasting the data. You can change them back once you're done copying.
0
 
northtecictsAuthor Commented:
Sorry I mustn't have been very clear on the problem..
Yes we can copy and paste the cell data into sharpoint datasheet fine - our problem is that the source data includes all of the HTML tags, which is then escaped by sharepoint and displayed as the item instead of rendered..

I'm guessing what we need is some tool or app that can convert text wrapped in HTML into rich text format within excel..

I thought about using find and replace, but there are many different tags and I'm not sure how for example you replace a
 tag with alt+enter character in excel (when cell is set to wrap text) etc..
0
 
nmarunCommented:
My bad. I din't read thoroughly. All you need to do is to encode the values.. replace '<' with '<' and '>' with '>'.

This is going to be a detailed post. Here are the steps you need to follow:

Create a console application and add components to read an Excel file as well as the Microsoft.SharePoint.dll (available on the SharePoint server).

Read the contents of the excel sheet in a custom class and add them to a list.
http://www.codeproject.com/KB/cs/Excel_Application_in_C_.aspx

Using CAML, add the items to the SharePoint list.

I've posted the entire code here for the CAML part. Go through the code in detail.

// provide the site name
private const string SiteName = "http://server/site";
// provide the list name
// how to get the list guid: 
// http://weblogs.asp.net/jimjackson/archive/2008/02/11/get-a-sharepoint-list-guid-from-the-browser.aspx
private const string ListGuid = "9AD5B9E3-F84E-49E6-8249-C62571E9246A";
 
static void Main(string[] args)
{
    // I've created this dummy class
    // class CustomItem
    // {
    //    public string Title {get; set;}
    //    public string Col1 { get; set; }
    //    public string Col2 { get; set; }
    // }
    // in your case, this class could contain the columns of your excel sheet
    List<CustomItem> customItems =new List<CustomItem>();
    CustomItem customItem;
    customItem = new CustomItem
    {
        Title = "Title1",
        Col1 = "Col1",
        // basically you need to 'encode' these values before you add them.
        Col2 = "<b>bold1</b><i>italics1</i>".Replace("<", "&lt;").Replace(">", "&gt;")
    };
    customItems.Add(customItem);
 
    customItem = new CustomItem
    {
        Title = "Title2",
        Col1 = "Col2",
        Col2 = "<b>bold2</b><i>italics2</i>".Replace("<", "&lt;").Replace(">", "&gt;")
    };
    customItems.Add(customItem);
 
    customItem = new CustomItem
    {
        Title = "Title3",
        Col1 = "Col3",
        Col2 = "<b>bold3</b><i>italics3</i>".Replace("<", "&lt;").Replace(">", "&gt;")
    };
    customItems.Add(customItem);
    AddItems(customItems);
}
 
private static void AddItems(List<CustomItem> customItems)
{
    var batch = new StringBuilder();
    batch.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?><ows:Batch OnError=\"Return\">");
 
    for (var i = 0; i < customItems.Count; i++)
    {
        batch.AppendFormat("<Method ID=\"A{0}\">", i + 1);
        batch.AppendFormat("<SetList>{0}</SetList>", ListGuid);
        batch.AppendFormat("<SetVar Name=\"ID\">New</SetVar>");
        batch.AppendFormat("<SetVar Name=\"Cmd\">Save</SetVar>");
        // the text following the # symbol is the internal name
        // of the column of the SharePoint list
        batch.AppendFormat(
            "<SetVar Name=\"urn:schemas-microsoft-com:office:office#Title\">{0}</SetVar>",
            customItems[i].Title.Trim());
        batch.AppendFormat(
            "<SetVar Name=\"urn:schemas-microsoft-com:office:office#_x0043_ol1\">{0}</SetVar>",
            customItems[i].Col1.Trim());
        batch.AppendFormat("<SetVar Name=\"urn:schemas-microsoft-com:office:office#_x0043_ol2\">{0}</SetVar>",
            customItems[i].Col2.Trim());
        batch.AppendFormat("</Method>");
    }
    batch.Append("</ows:Batch>");
 
    using (var spSite = new SPSite(SiteName))
    {
        using (var spWeb = spSite.OpenWeb())
        {
            // this method of adding items to a SP list seems to be fastest
            // do a read on CAML if you want to know more about it
            var Result = spWeb.ProcessBatchData(batch.ToString());
            // make note of the 'Result'.. might help you in case things go wrong
        }
    }
}

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
nmarunCommented:
Use the code below to find out the Internal names of the columns (see comment on line 57-58)

// pass the SiteName and the ListName
private static void GetSPListColumnInternalNames(string SiteName, string ListName)
{
    //Get the site object
    var site = new SPSite(SiteName);
    // Get the web object
    SPWeb userList = site.OpenWeb();
    // Get User list object
    SPList GenericList = userList.Lists[ListName];
 
    foreach (SPField field in GenericList.Fields)
    {
        Console.WriteLine(string.Format("{0}: {1}", field.InternalName, field.Title));
    }
}

Open in new window

0
 
nmarunCommented:
field.InternalName is what you need to substitute in the first code snippet (lines 60-66).
0
 
northtecictsAuthor Commented:
This sounds exactly what I'm after, however I'm a beginner at this so can you please give me some more info on how / where I run this code?
Thanks very much
0
 
nmarunCommented:
Create another console application and paste the code from the second snippet (http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Server/MS-SharePoint/Q_24173547.html?cid=238#a23727803) in the Program.cs file right click on the References and 'Add References' and add Microsoft.SharePoint.dll to your project.

Call this method from Main. The call would be something like this:

GetSPListColumnInternalNames("http://server/sitename", "list name");
// add a readline command so you'll be able to see the columns
// hit the Enter key when you're done to end the program
Console.ReadLine();

This will list the internal names of all the columns of your list. We'll need these names later.

Create another console application in Visual Studio and add Microsoft.SharePoint.dll as reference to the project.

Replace the contents of the class Program with the first code snippet (http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Server/MS-SharePoint/Q_24173547.html?cid=238#a23727755). Also read the link I've provided (codeproject.com link) and see what you need to do in order to read from an excel sheet.

In my code, I've created a dummy class and added junk data to the list. In your case, you will have to write a class that matches the columns of the excel sheet and create a list of your custom class:
List<myCustomClass> customClass = new List<myCustomClass>();

In the AddItems method, replace the column names with the internal names found in the above project. The internal name is after the # character:

batch.AppendFormat(
 "<SetVar Name=\"urn:schemas-microsoft-com:office:office#Title\">{0}</SetVar>", customItems[i].Title.Trim());

That's it. Just make sure things look good and you're ready to run the program and make the transition.

I'm sorry it is complicated, but you'll learn a lot from this code just as I did. Take one step at a time and you should do just fine.

Good luck
Arun
0
 
northtecictsAuthor Commented:
Thanks for the help with this Arun - I'm got some time dedicated to try this out on Monday so thanks for the luck ;)

One other question - can this be used to replace other escaped characters such as these three (only ones I've come across so far but there's probably more)?

&#39;
&#160;
&quot;

Open in new window

0
 
nmarunCommented:
I believe you can. But first you want to make sure the first part works. Also, pass these escaped characters as they are and see if things look good after the transfer. If they do, I don't see a reason why you should change them.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now