Import HTML into a SharePoint list as rich text

Question

What I have:

• SQL Server 2008 R2 database with a table containing some columns whose values are raw HTML
• SharePoint 2007 list with standard rich text columns (not Enhanced Rich Text)
• Office 2007, Visual Studio 2008, Visual Studio 2012

What I want:

• Import the HTML from my database columns into SharePoint list columns as rich text

The fun part:

I'm not exactly a developer, and I don't have administrative rights to the SharePoint box, so I can't actually use web services. The closest I've been able to come is to use Access 2007 to write to my existing SharePoint 2007 list, but there are snags:

• Access can't seem to import HTML (or anything else) directly as rich text -- not from ODBC or from an HTML table. And once it's in as HTML, there's no feature to convert to rich text; I'd have to use VBA or something. (?)
• Excel can also import from an HTML table, but gets stuck on carriage returns -- it doesn't import correctly.
• Assuming I open my HTML table in Word, I can still only copy-paste the values within each cell, one at a time -- copy-pasting all at once reverts it all to plaintext within Access.

Besides berating me for lacking the skills to do this in a smarter way, what kind of power user-type options do I have? Is there some way I'm missing to select HTML into my Access database as rich text? This seems like an Access question, but my end goal is to get some data into SharePoint. Is there an easier way than the path I've established so far?

tl;dr:

How do I get a bunch of raw HTML from a bunch of columns in a database into a SharePoint list as rich text?

Answer 1

The thing is, SharePoint Text fields do not allow tags like script and style. That's why you need to remove them first. It might be tricky to do if you are not a developer. But, just in case you are ready to do some coding with C#, here is the helpful method I used for removing prohibited tags:

    public static string RemoveProhibitedTags(string input)
    {
        return RemoveTags(input,
            new[] {"script", "style", "any_other_evil_tags"});
    }

    public static string RemoveTags(string input, string[] tags)
    {
        if(input == null || tags == null || tags.Count() == 0)
        {
            return input;
        }
        int key = tags.GetComplexHashCode();
        Regex regex;
        if(!Regexes.TryGetValue(key, out regex))
        {
            string pattern =
                tags.Select(
                            tag =>
                                String.Format(@"(<(?<{1}>{0})\b[^>]*>.*?</\k<{1}>>)|(<{0}\b[^/>]*/?>)", tag, tag.Replace(':', '_'))).
                    Aggregate((tag1, tag2) => String.Format(@"({0})|({1})", tag1, tag2));
            Regexes[key] =
                regex = (new Regex(pattern, RegexOptions.Singleline | RegexOptions.IgnoreCase | RegexOptions.Compiled));
        }
        return regex.Replace(input, String.Empty);
    }

Also, there might be a table or an image somewhere in your html. It's only allowed in enhanced fields.

So, my point is: if you can remove all prohibited tags somehow, you can still export the data via Excel. You might also need to temporarily change the SharePoint Field type to allow enhanced content. Just for the time of migration. It will make sure your migration does not fail because of a sneaky table or image tag.

OR you can create a very simple console application that:

1. Iterates through all SQL raws you need to export;
2. Removes all prohibited tags from HTML with the method I've included above;
3. Creates SharePoint list Items filling the SharePoint fields with the safe HTML.

All these steps are pretty easy to implement even for non-developer considering the massive knowledge base available on the Internet :)