sharepoint list item can not change type (over 5k items)

[GaryLin30]

Hi,

I have a sharepoint list which currently has 6,000 items, I would like change a column type from text to number. However, it always fail to save. From the older column value, it contains many different value include symbol character and so on.

I have tried to save many times.

[#2]

@GaryLin30 

When attempting to change the column type of a SharePoint list item from text to number, you may encounter an error if the column contains values that cannot be converted to a number. Here are a few suggestions to address this issue:

1. Validate and Clean Data: Before changing the column type, review the existing values in the column to identify any non-numeric or incompatible characters. It's essential to clean and standardize the data in the column to ensure that all values can be converted to numbers. You can consider using formulas or scripts to validate and clean the data within the column.

2. Batch Updates: Instead of trying to update all 6,000 items in a single attempt, try updating the column type in smaller batches. Divide the list into smaller subsets, such as 500 items per batch, and update the column type for each batch separately. This approach may help pinpoint specific items causing the issue and allow you to resolve any problematic data.

3. Data Export and Re-import: Export the list data to a spreadsheet, clean the data in the column by removing any non-numeric characters, and then re-import the modified data back into the SharePoint list. This process will create new list items, and you can map the column as a number during the import, ensuring that only valid numeric values are added.

4. PowerShell Script: If the above methods don't work, you can use PowerShell scripting to update the column type. The script can iterate through the list items, validate the values, and update the column type accordingly. PowerShell provides more flexibility and control over the data transformation process.

Keep in mind that modifying the column type can have implications on the existing data and any dependent processes or workflows. It's important to thoroughly test the changes in a non-production environment before applying them to the live SharePoint list.

If you're uncertain about implementing these solutions or need further assistance, consider consulting with your organization's SharePoint administrators or reaching out to Microsoft Support for guidance tailored to your specific scenario. The text and the steps are the result of various AI's put together.

My answers are voluntary and without guarantee!

Hope this will help you.

[#3]

Hi @NikolinoDE ,

Thank you for response.

Can you give me some docs for how to update column type in batch update? I have already clean our list down to 1957 items and clean all not number type value to number zero, but it still failed. 

[#4]

@GaryLin30 

Here are some resources and example scripts that can help you update the column type in a batch update for a SharePoint list:

1. PnP PowerShell: The Patterns and Practices (PnP) PowerShell module provides additional cmdlets and functionalities specifically designed for SharePoint Online. You can use the Set-PnPListItem cmdlet to update list item properties, including the column type. PnP PowerShell documentation can be found here: PnP PowerShell documentation
2. Example Script: Here's an example PowerShell script that demonstrates how to update the column type for a SharePoint list in a batch update:

powershell code:

# Connect to SharePoint Online
Connect-PnPOnline -Url "https://yourdomain.sharepoint.com/sites/your-site"

# Get the list and retrieve all list items
$list = Get-PnPList -Identity "Your List Name"
$items = Get-PnPListItem -List $list

# Iterate through the list items and update the column type
foreach ($item in $items) {
    $item["YourColumnName"] = [int]$item["YourColumnName"]
    $item.Update()
}

# Execute the batch update
Invoke-PnPQuery

Please replace "https://yourdomain.sharepoint.com/sites/your-site" with the actual URL of your SharePoint Online site and "Your List Name" with the name or GUID of your target list. Also, update "YourColumnName" with the internal name of the column you want to update.

Additionally, for more detailed examples and guidance on using PowerShell to update SharePoint list item properties, you can refer to the official SharePoint Patterns and Practices (PnP) repository on GitHub:

PnP PowerShell - Examples

In the PnP PowerShell repository, you will find various sample scripts demonstrating different scenarios, including updating list item properties, that can be customized to fit your specific requirements.

Make sure to test the script in a non-production environment before applying it to your live SharePoint list. It is recommended to consult with your organization's SharePoint administrators or IT department for any specific considerations or guidelines for using PowerShell in your environment.

Remember to always have a backup of your data before making any significant changes to your SharePoint lists. The text and the steps are the result of various AI's put together.

My answers are voluntary and without guarantee!

Hope this will help you.