Copy SharePoint List Column Values from One to Another using PowerShell

Question

_{Thursday, January 2, 2020 3:14 PM}

Hi, 

I need copy data from one column to another in same list in SPO. I know script for Sharepoint on Premise. 

https://www.sharepointdiary.com/2014/01/copy-list-column-values-from-one-to-another.html

Anybody some idea, how do this in online SP with Powershell?

Thanks

Daniel 

All replies (11)

_{Friday, January 3, 2020 2:51 AM}

Hi Daniel,

Below PowerShell for your reference:

#region Variables

$Username = "UserName"

$Password = "PassWord"

$siteURL = "Site"

#region Credentials

[SecureString]$SecurePass = ConvertTo-SecureString $Password -AsPlainText -Force

[System.Management.Automation.PSCredential]$PSCredentials = New-Object System.Management.Automation.PSCredential($Username, $SecurePass)

#endregion Credentials

Connect-PnPOnline -Url $siteURL -Credentials $PSCredentials

$list = "your list name";

$items = (Get-PnPListItem -List $list -Fields "Title")

foreach($item in $items) {

  $test= $item[“Title”(Source internal column name)]

  Set-PnPListItem -List $list -Identity $item -Values @{"Copy"(Destination internal column name) = $test}

}

Best regards,

Julie

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

SharePoint Server 2019 has been released, you can click

here to download it.
Click

here to learn new features. Visit the dedicated

forum to share, explore and talk to experts about SharePoint Server 2019.

_{Friday, January 3, 2020 8:58 AM}

Hi Daniel,

The code provided by Julie is almost right, except the last part, where to column names are defined. It's a PNP module issue, where PowerShell uses internal name for a column instead of the visible one, producing errors.

To make the script work, you need to find the internal name of the columns with this command:

$items = Get-PnPListItem -List $list
$items[0].FieldValues

In my case, the original name of the column was "Col2" and it the internal PNP name was "_x0043_ol2", so only capital letter "C" was changed. Here's the command outcome - all columns are at the beginning of the list, so it shouldn't be diffcult to locate them.

Key                       Value
*                      *
ContentTypeId             0x0100D71FE8CEFD91044AA4926A6EA4745686
**Title **                    a1
_ModerationComments
File_x0020_Type
ComplianceAssetId
Target_x0020_Audiences    d7529e7c-4bf9-45ae-b781-6afacebc008d;;;;
_x0043_ol1
**_x0043_ol2 **               aa

Now the updated code from Julie for my list looks like this:

Connect-PnPOnline -Url $siteURL -Credentials $PSCredentials

$list = "TargetedList for AdeleV";

$items = (Get-PnPListItem -List $list -Fields "_x0043_ol2")

foreach($item in $items) {

  $test= $item["_x0043_ol2"]

  Set-PnPListItem -List $list -Identity $item -Values @{"_x0043_ol1" = $test}

}

More information about the issue is available here

Hope that helps! :)

Cheers,

Marcin

_{Sunday, January 5, 2020 9:33 PM}

Hi Marcin and Juliet, 

thank you for script in PS. Could I use thit script in SPO library in same way with same parametres?

In list this scripts works fine. I tried script on SPO library and there script didn´t work. 

Thanks for your help!

Daniel 

_{Monday, January 6, 2020 9:14 AM}

Hi Daneil,

Is there any error message when running the PowerShell in SharePoint library?

Please remember to change the retrieved field section of the script:

$items = (Get-PnPListItem -List $list -Fields "retrieved Column’s internal name")

It works well in library per my test, please provide detailed information for investigating the issue.

Best regards,

Julie

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

SharePoint Server 2019 has been released, you can click

here to download it.
Click

here to learn new features. Visit the dedicated

forum to share, explore and talk to experts about SharePoint Server 2019.

_{Monday, January 6, 2020 3:04 PM}

Hi Juliet , 

I have Document Library with Content Type. 

I have two column. Column 1 is clasic text field a Column 2 is search field (search in another list with a customer codebook).

I need copy data from Column 1 to Column 2.

Yes, of course. There is error from PowerShell. 

Set-PnPListItem : Input string was not in a correct format.

+   Set-PnPListItem -List $list -Identity $item -Values @{"Customer" =  ...
+   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : WriteError: (:) [Set-PnPListItem], FormatException
    + FullyQualifiedErrorId : EXCEPTION,SharePointPnP.PowerShell.Commands.Lists.SetL 
   istItem

My script: 

#conetcttotenant

Connect-PnPOnline -Url https://web -UseWebLogin

#copyitems

$list="Smlouvy"


$items = (Get-PnPListItem -List $list -Fields "Z_x00e1_kazn_x00ed_k")

foreach($item in $items) {

  $test= $item["Z_x00e1_kazn_x00ed_k"]

  Set-PnPListItem -List $list -Identity $item -Values @{"Customer" = $test}

Key                                  Value                                           
                                                                             
                                                                  
                                                                   
Z_x00e1_kazn_x00ed_k                 Column1
                                                                          
Customer                             Column2

_{Wednesday, January 8, 2020 9:52 AM | 1 vote}

Hi Daniel,

Since the lookup column stores the id of the value instead of value itself so the error you mentioned occur.

Reference: Set-PnPListItem

In addition, look up column is aimed to retrieve value from another list, if values of column1(current library)not be included in customer codebook list, it doesn’t work.

Best regards,

Julie

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

SharePoint Server 2019 has been released, you can click

here to download it.
Click

here to learn new features. Visit the dedicated

forum to share, explore and talk to experts about SharePoint Server 2019.

_{Friday, January 10, 2020 1:18 AM}

Hi Daniel,

Is there any progress on this issue?

I am looking forward to your feedback if there is any update.

Best regards,

Julie

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

SharePoint Server 2019 has been released, you can click

here to download it.
Click

here to learn new features. Visit the dedicated

forum to share, explore and talk to experts about SharePoint Server 2019.

_{Monday, January 13, 2020 1:56 AM}

Hi Daniel,

Do you have any progress on this issue?

If you find any replies helpful to you, please remember to mark them as answers.

It will help others who meet the similar question in this forum.

Thank you for your understanding.

Best regards,

Julie

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

SharePoint Server 2019 has been released, you can click here to download it.
Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

_{Tuesday, January 14, 2020 8:10 AM}

Hi Julie, 

ok, I understand. Any solution how to do it?! Do you have any idea? Im open minded:-)

Daniel 

_{Wednesday, January 15, 2020 9:55 AM}

Hi Daniel,

If you don't have large number of items in the library, we can use Quick edit to copy and paste single line of text column values(values in this column should be included in lookup column value selected range) to lookup column value as shown below:

Best regards,

Julie

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

SharePoint Server 2019 has been released, you can click

here to download it.
Click

here to learn new features. Visit the dedicated

forum to share, explore and talk to experts about SharePoint Server 2019.

_{Tuesday, January 21, 2020 2:10 AM}

Hi Daniel,

Thanks for posting in this forum. It will benefit others who meet similar questions. 

Here I will provide a summary of this post for your information.

[Copy SharePoint List Column Values from One to Another using PowerShell]

Situation

====================

SharePoint Online

Question

====================

User needs to copy column data from one column to another in same list in SharePoint Online.

When user copying text data to lookup column, occur “Input string was not in a correct format” error.

Suggestions

====================

According to Marcin’s and my reply, here is the PnP PowerShell to copy data from one column to another column with column internal name, however, lookup column stores the id of the value instead of value itself, so copying text value to lookup column will occur format error, as a workaround, we could use OOB Quick Edit to copy data from text column to lookup column in the same list, and please note the text value should be included in the lookup selected range for copying.

#region Variables
$Username = "UserName"
$Password = "PassWord"
$siteURL = "Site"
#region Credentials
[SecureString]$SecurePass = ConvertTo-SecureString $Password
-AsPlainText -Force
[System.Management.Automation.PSCredential]$PSCredentials =
New-Object System.Management.Automation.PSCredential($Username, $SecurePass)
#endregion Credentials
Connect-PnPOnline -Url $siteURL -Credentials $PSCredentials
$list = "your list name";
$items = (Get-PnPListItem -List $list -Fields "Title”(Source
internal column name))
foreach($item in $items) {
  $test= $item[“Title”(Source internal column name)]
  Set-PnPListItem -List $list -Identity $item -Values @{"Copy"(Destination internal column
name) = $test}
}

How to find the internal name of columns:

$items = Get-PnPListItem -List $list

$items[0].FieldValues

Reference

====================

Set-PnPListItem

Best regards,

Julie

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

SharePoint Server 2019 has been released, you can click here to download it.
Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.