This is part 2 of the series about importing Work Items into TFS 2010 using the Excel Add-in. For more information about the Excel Add-in for TFS, check out http://msdn.microsoft.com/en-us/library/dd286627.aspx.
One of the issues one might run into when importing Work Items into TFS 2010 using the Excel Add-in is the Excel limits. One of the most important limits is the cell contents limit. When importing from another system, this limit might be exceeded, resulting in truncated text. Excel does not warn when the length of the pasted text exceeds this limit. So it is necessary to identify this issue in the source.
Excel 2003, 2007 and 2010 have a 32,767 character limit per cell.
- See http://office.microsoft.com/[…]HP005199291.aspx for Office 2003
- See http://office.microsoft.com/[…]HP010073849.aspx for Office 2007
- See http://office.microsoft.com/[…]HP010342495.aspx for Office 2010
TFS, when using a field of type “PlainText” has a limit of about 2Gb, the limit of the ntext field in SQL Server;
- See http://msdn.microsoft.com/[…]ms187993(v=sql.100).aspx for SQL Server 2008
- See http://msdn.microsoft.com/[…]ms187993(v=sql.105).aspx for SQL Server 2008 R2
TFS stores PlainText fields in a table called WorkItemLongTexts.
There are two solutions to this problem;
- Create a custom piece of C#/VB code to import the data using the TFS API (see: http://msdn.microsoft.com/en-US/library/bb130322(v=vs.100))
- Identify the problematic records and manually paste the text using Visual Studio or Team Foundation Server Web Access