1

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?

  • become a developer :) – Arsalan Adam Khatri Oct 1 '13 at 6:13
  • does your HTML contain images, links or tables? Does it contain tags like <style> or <script>? And just out of curiosity, how many rows are there in you SQL table? How large are these html columns? – Denis Molodtsov Oct 1 '13 at 7:06
  • @Arsalan, working on it! :D – Cory Salveson Oct 1 '13 at 14:06
  • @Denis, thanks for clarifying -- HTML includes some <style>, some links, no <script>, no images, no tables. HTML is stored in a Text column type, maximum length in my current data is 1501 characters. (Multiple Lines of Text column in SP seems to be able to support more than that, and if I understand correctly is typed the same in the SP database.) I have around 13k rows right now, but would only be looking to import a few hundred at most. – Cory Salveson Oct 1 '13 at 14:22
0

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 :)

  • Thanks, you're confirming what my experience so far is suggesting: there isn't a nice way to do this without resorting to a coded solution. Access can update rich text on a SharePoint list, but there are too many contingencies involved in getting my content to that point for it to be a viable option for me. – Cory Salveson Oct 1 '13 at 18:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.