It seems that SharePoint stores the data internally this way, and if you change a field type, nothing actually happens with the data itself, and the only thing which changes is the Type property.
I've been fiddling around this for some time, unfortunately with no lack. For example, changing field type to "Integer" will cause nulling all the "big" values without any possibility of recovery.
Another evidence of this fact is following quote from MSDN:
Blocked operations when list exceeds the list view threshold
- Add/remove/update a list column: All columns, including lookup and calculated columns, in addition to many kinds of updates, such as
a type change or a uniqueness change. Some updates, such as a name
change, are not blocked because they do not affect every item in the
list.
So, I infer that conversion cannot be done natively through SharePoint interface.
Thus, I think the most appropriate way to achieve the goal here will be to write a custom solution (it can be a PowerShell script or a SharePoint Console Application) which will fix the data after the native conversion. I'm sure this kind of conversion will NOT take very long, even for 200 thousand items.
If you need this to be done really quickly you can consider a solution which involves using SharePoint Batch List API, so you could change items batchly, by 150 items per request. This will speed up the conversion significantly, especially if you will care to add to the batch only those items which need to be changed.
Also, please don't forget to include your number column into index (List Settings => Indexed columns), this way it will perform much faster and also you will be able query it (queries which involve non-indexed columns in very large lists are not allowed).
I've created the following PowerShell script and tested it on my own list with 15000 generated items in it (more 13000 items had "bad" number format). It updates about 6000 items per minute, which is, I reckon, quite acceptable.
function FixNumberFormatMethod($id, $listId, $fieldName, $number)
{
$value = [Int64]$number;
return "<Method ID=""u" + $id + """><SetList>" + $listId + "</SetList><SetVar Name=""Cmd"">Save</SetVar><SetVar Name=""ID"">" + $id + "</SetVar><SetVar Name=""urn:schemas-microsoft-com:office:office#" + $fieldName + """>" + $value + "</SetVar></Method>`n"
}
function BatchRequest($text)
{
return [System.String]::Format("<?xml version=""1.0"" encoding=""UTF-8""?><ows:Batch OnError=""Return"">{0}</ows:Batch>", $text);
}
$web = get-spweb http://localhost
$list = $web.Lists["Test List"]
$numberFieldName = "NumberColumn"
$items = $list.GetItems($numberFieldName)
$m = 0
$methods = ""
foreach ($i in $items)
{
$number = [System.String]$i[$numberFieldName];
if ($number.Contains("e"))
{
$m++;
$methods = $methods + (FixNumberFormatMethod $i.ID $list.ID $numberFieldName $number)
if ($m -eq 150)
{
$r = BatchRequest $methods
$web.ProcessBatchData($r)
$m = 0;
$methods = ""
}
}
};
The result as it appears in the list (a bitsy part, of course):
