Importing Data into a SharePoint List

SharePoint Joel’s recent post, Managing Large Lists in SharePoint for Users and Site Admins got me interested in testing the 5000 list view item limit.

It also gave me a good opportunity to put fakenamegenerator.com to the test. The site quickly provided me with a list of 6000 random names.

Now, how to get them into a SharePoint list?

First, I tried creating an empty SharePoint project in Visual Studio, and adding a feature receiver to insert the items. The only issue is that I wasn’t sure how to parse the file from within the feature receiver, so I just created an array with 6000 members by copying-and-pasting a slightly modified CSV file into the constructor. But when I tried deploying it, I got this error:

Error:
Error occurred in deployment step 'Activate Features': Unhandled exception was thrown by the sandboxed code wrapper's Execute method in the partial trust app domain: An unexpected error has occurred.

I checked the list, and 1368 of 6000 list items had been added. I tried deploying it a couple more times, and it added 1682 items and 1629 items. What was causing the error?

I took a look at the associated ULS log entries:

• Resource "IdlePercentProcessorTime" for monitored process "ipc://5a551cc1-06e5-4241-8c77-55a3a93f82db:7000 (PID=2764" has value: 71.21212 which is above absolute resource limit: 10.
• The worker process "ipc://5a551cc1-06e5-4241-8c77-55a3a93f82db:7000", SHIM/PROXY PID 2764/1100 has been terminated because resource "IdlePercentProcessorTime" is above hard threshold value
• Stopping shim process. Shim process name: "SPUCWorkerProcess" Shim PID: "0x0ACC" Shim service url: "ipc://5a551cc1-06e5-4241-8c77-55a3a93f82db:7000"
• Stopping proxy process. Proxy process name: "SPUCWorkerProcessProxy" Proxy PID: "0x044C" Proxy service url: "ipc://00657311-1135-413b-bda0-839e7794fae6:7000"

I saw the IdlePercentProcessorTime and focused on ProcessorTime–I thought the server was working too hard. I tried putting the thread to sleep for 100 milliseconds between items, but that time it only added 219 items!

Right: IdlePercentProcessorTime. The process had even more idle time, because it was sitting around with a sleeping thread. Apparently, sandboxed solutions can’t just tie up processor threads. (Check out Quotas and Locks for Sandboxed Solutions for more info.)

I could deploy the data to the list in 6 batches of 1000, but that’s an inelegant solution. Besides, what if I wanted to insert 100,000 records instead?

Rather than mess with any of that, why not just bypass the sandbox restrictions and make it a farm solution? This time I got a different error:

Error occurred in deployment step 'Add Solution': failed to load receiver assembly "AddLotsOfNames...."

There were zero items in the feature, so there was no assembly. I addressed this by adding an empty module. The 6000 names were added to the list.

Using a feature receiver to add all the names is less than ideal, though. A console application would be better. I built a console app that would take a string as a command line argument and use that as the filename for the input data. (If I wanted to re-use the file, I could take other command line arguments, such as the SharePoint site and list address into which the data should be inserted.)

Visual Studio notified me of errors right away. My code from the feature receiver relied on a reference to Microsoft.SharePoint. I tried to add the reference to my console app, but the reference wasn’t listed. Why not? The console app was set to target .NET Framework 4, but the Microsoft.SharePoint component is in .NET Framework 3.5. Once I targeted the right framework, I was able to add the reference.

The console application compiled, but produced a run-time error:
An unhandled exception ('System.IO.FileNotFoundException') occurred in AddPeopleConsole.exe [5184]

This was a little confusing, because my first thought was to check the name and path of the data file, which was correct. It turns out that the console app is set, by default, to compile for an x86 (or 32-bit) platform. My development environment has a 64-bit processor, so I changed the platform target to Any CPU.

Here’s the working code:

namespace AddPeopleConsole
{
  class Program
  {
    static void Main(string[] args)
    {
      string filename = args[0];
      var reader = new StreamReader(File.OpenRead(@filename));

      using (SPSite siteCollection = new SPSite("http://sp2010dev/"))
      {
        using (SPWeb web = siteCollection.RootWeb)
        {
          SPList list = web.GetList("/Lists/Contacts/");
          while (!reader.EndOfStream)
          {
            var person = reader.ReadLine();
            // Separate the person data into discrete values
            string[] values = person.Split(',');
            // Add an empty item to the list
            SPItem item = list.AddItem();
            // Assign values to the item
            item["Title"] = values[0];
            item["FirstName"] = values[1];
            item["WorkPhone"] = values[2];
            item["Email"] = values[3];
            // Save the item, i.e. put the person in the list
            item.Update();
          }        
        }
      }
    }
  }
}

Testing in my development environment isn’t very useful, in this case. The 5000 item limit on a list view is to protect the system from performance degradation, as a query of over 5000 items will cause the entire table to lock (for the duration of the query). On a single-user dev environment (or even a multi-user dev environment), these table locks would probably go unnoticed.

If I ever need to insert a huge number of records from a text file, though, I’m prepared!