5

I want to copy a list items one by one to another list using PowerShell. Some items in source list are misssing. I don't want to create and delete empty items for missing ID values in source list. I want to add new items to destination list with specific ID. Can I do this using PowerShell?

Example:

Source List Items:

ID - Title
1    title1
3    title3

If I add items to destination list using loop each items, it will be like this:

ID - Title
1    title1
2    title3

But I want to get this. I want to skip ID=2 value and set ID myself to 3.

ID -  Title
1     title1
3     title3
  • I'm not sure I understand well your question. But since you can retrieve the list items, and loop into it, I would say yes. $list = $web.lists["Your List Name" $listItems = $list.Items Then $listItems contains all your list items. Just open your new list, and put each items in the new list. Hope this can help. – Nico May 2 '14 at 7:14
  • Thank you for your answer. I think this is not the solution for my problem. Let me try to explain my problem with an example. I will update my question with example. – newbie May 2 '14 at 7:18
4

Thanks for your update, more understable for me !

A dirty solution would be to loop until you create enough items to get the ID you want, and then delete those items, and the create the real one, which will get the ID you need I guess.

Else, when you create an Item, can't you specify its ID ? I guess not, else you wouldn't ask this question.

Edit while writing : I think this can solve your problem ! I googled it a lot, and it seems that Item's ID are readonly field, you need to bypass this, and you can set the ID of your Item with :

#Get your list
$list=$Lists["Your List"]

#Disable the readonly on ID field
$list.Fields[SPBuiltInFieldId.ID].ReadOnlyField = false;

#Or maybe have a try with something like this instead
#$list.Fields["ID"].ReadOnlyField = $false

$list.Update();

#Add your item, and choose your item ID with $itemID
$item=$list.AddItem()
$item[SPBuiltInFieldId.ID] = $itemID;

#Update your item
item.Update();

#Put back the readonly on the ID field
$list.Fields[SPBuiltInFieldId.ID].ReadOnlyField = true;
$list.Update();

I've not test it, but it seems pretty good. (here the link that helps me)

Hope I help after 1 hour of useless comments !

Edit2 : If you can tell me if this works great, because I'm sure that one day, I will need it !

Edit3 : Maybe you have to add this at the beginning, and don't forget to put it as default at then end of your script :

web.AllowUnsafeUpdates = true;
site.AllowUnsafeUpdates = true;
  • There is no ReadOnlyField attribute for built-in columns. ReadOnlyField=$false works fine for columns that I add but doens't work for ID and Title columns and throws the exception below Missing or invalid array index expression. At line:1 char:14 + $yeni.Fields[ <<<< SPBuiltInFieldId.ID].ReadOnlyField = + CategoryInfo : ParserError: ([:String) [], + FullyQualifiedErrorId : MissingArrayIndexExpression I will search for a while. If I find a solution, I will share here. – newbie May 2 '14 at 9:06
  • It seems that the ID field is named "ows_ID" if I'm not wrong. When you retrieve all fields. I think you will find the solution. ReadOnly attribute is the point. Did you read the link I posted ? This is not the only guy who posted stuff like that, so it might be the right solution. Jsut find how to do it :) Good luck ! – Nico May 2 '14 at 9:10
3

Tried and Tested !!!

Below code works. It creates a new list item with an ID which is not autogenerated. Based upon answer by @Nico.

class Program
{
    static void Main(string[] args)
    {
        string siteUrl = "<your_site_url>";
        string listName = "<your_list_name>";
        string webRelativeUrl = "<your_subsite_relative_url>";
        int itemID = 3;// You can give any ID you want !!

        using (SPSite site = new SPSite(siteUrl))
        {
            using (SPWeb web = site.OpenWeb(webRelativeUrl))
            {
                SPList list = web.Lists.TryGetList(listName);

                // The ID field is readonly field, set to to read and write mode.
                list.Fields[SPBuiltInFieldId.ID].ReadOnlyField = false;
                list.Update();

                SPListItem item = list.AddItem();
                item["Title"] = "This item Id's is not auto-generated by SharePoint, it explicitly specified";
                item[SPBuiltInFieldId.ID] = itemID;
                item.Update();
                list.Fields[SPBuiltInFieldId.ID].ReadOnlyField = true;
                list.Update();
            }
        }
    }
}
1

There is a column NextAvailableId in the table AllListAux in the content database that keeps track of the next available ID for every list.

I don't recommend to change ID field's property ReadOnlyField to False. Yes, that way you can set the ID of the created item to the id you want but you will face problems later when creating items because this way NextAvailableId is not updated in the database.

Consider this case:

  1. Current value of NextAvailableId is 6
  2. You add new item with your code by setting ReadOnlyField to False and setting value 8 for ID. Here NextAvailabeId is still 6
  3. Users add items with the UI which will increase NextAvailableId. When adding the third item (NextAvailableId=8) they will get an exception.

We know that we should not change the content database manually so updating NextAvailabeId manually is not recommended.

The safest way is to create and delete dummy items until you reach the ID you want. If its a one time job then I don't see a problem with creating/deleting dummy items.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.