I got some solid help on here before with working with PowerShell and SharePoint. While I would consider myself an experienced SysAdmin and a Knowledgeable SharePoint Admin (front-end, Workflow Design, General Admin) - I am a bit of a n00b at the PowerShell side of things. Could I request some help? I am a fast learner!
So I have this script that creates an item in a list from a CSV File, for each row in the file etc. I have got it 90% working. However, how do I declare a Date/Time Field? I am also having some strange issues with a column that is set to a person/group at the list level(error message below)
I have commented out the items in which have errors
here is the script in full:
Add-PSSnapin Microsoft.SharePoint.Powershell
$file = Import-Csv -LiteralPath "C:\Users\SPFarm\Desktop\OldMMFExport-CreatedOnAxxia.CSV"
$web = Get-SPWeb "http://SERVERNAME/Site/SubSite/AnotherSubSite/"
$list = $web.Lists["MMFImportTest"]
ForEach ($row in $file) {
$item = $list.Items.Add()
$item["Imported"] = $row.Imported
$item["Form Status"] = "File is Active"
$item["Legacy Item ID"] = $row."Legacy Item ID"
$item["Legacy Created By"] = $row."Legacy Created By"
#$item["Legacy Created Date"] = $row."Legacy Created Date
$item["Status of form when brought over"] = $row."Status of form when brought over"
$item["Client Number"] = $row."Client Number"
$item["Client Name"] = $row."Client Name"
$item["Proposed Matter Name"] = $row."Proposed Matter Name"
$item["Does Interaction have up to date contact details for the person providing instructions?"] = $row."Does Interaction have up to date contact details for the person providing instructions?"
$item["Have we agreed a non-standard client charge out rate for this matter?"] = $row."Have we agreed a non-standard client charge out rate for this matter?"
$item["Please detail and state if these rates to be applied as a rule on Axxia?"] = $row."Please detail and state if these rates to be applied as a rule on Axxia?"
$item["What is the estimated fee for this matter?:"] = $row."What is the estimated fee for this matter?"
$item["Did you consult with a(nother) partner on the proposed fee in advance of quoting?"] = $row."Did you consult with a(nother) partner on the proposed fee in advance of quoting?"
$item["NA Reason"] = $row."NA Reason"
$item["Please Explain Other Reason"] = $row."Please Explain Other Reason"
#$item["What is the estimated completion date for this matter?"] = $row."What is the estimated completion date for this matter?"
$item["Is this matter part of a large volume project?"] = $row."Is this matter part of a large volume project?"
$item["If this matter involves cross departmental coordination, has a project lead been nominated and a project plan drafted?"] = $row."If this matter involves cross departmental coordination, has a project lead been nominated and a project plan drafted?"
$item["Is the Account Payable information different from the Client File for this New Matter?"] = $row."Is the Account Payable information different from the Client File for this New Matter?"
$item["Accounts Payable Contact Name"] = $row."Accounts Payable Contact Name"
$item["Accounts Payable Billing Address"] = $row."Accounts Payable Billing Address"
$item["Does the client have a PO Reference to be used?"] = $row."Does the client have a PO Reference to be used?"
$item["Client PO Reference"] = $row."Client PO Reference"
$item["Please provide DMS document number for Letter of Engagement to the client"] = $row."Please provide DMS document number for Letter of Engagement to the client"
$item["Do the BW standard terms and conditions apply?"] = $row."Do the BW standard terms and conditions apply?"
$item["Please Provide Details"] = $row."Please Provide Details"
$item["Is the Partner responsible for supervising the matter the same as the Fee Earner?"] = $row."Is the Partner responsible for supervising the matter the same as the Fee Earner?"
## CHECK THIS ONE: $item["Fee Earner responsible for day to day file handling"] = $row."Fee Earner responsible for day to day file handling"
## CHECK THIS ONE: $item["Partner responsible for supervising the matter"] = $row."Partner responsible for supervising the matter"
$item["Is the partner approving different than the supervising partner?"] = $row."Is the partner approving different than the supervising partner?"
$item["Partner Approving Matter Inception"] = $row."Partner Approving Matter Inception"
$item["Has Part One Been Completed"] = $row."Has Part One Been Completed"
$item["Have you completed a conflict search per the firm's conflict checking Policy?"] = $row."Have you completed a conflict search per the firm's conflict checking Policy?"
$item["Is there any apparent conflict of interest in acting for the client on this matter?"] = $row."Is there any apparent conflict of interest in acting for the client on this matter?"
$item["If there is apparent conflict, how it can be managed?"] = $row."If there is apparent conflict, how it can be managed?"
$item["If there is apparent conflict, have we advised the client?"] = $row."If there is apparent conflict, have we advised the client?"
$item["If there is apparent conflict, has the client given proper authorisation to proceed to act?"] = $row."If there is apparent conflict, has the client given proper authorisation to proceed to act?"
$item["Chinese Walls Required?"] = $row."Chinese Walls Required?"
$item["Please Specify the details of the Chinese Walls required"] = $row."Please Specify the details of the Chinese Walls required"
$item["Have you issued ByrneWallace Terms of Business to the Client?"] = $row."Have you issued ByrneWallace Terms of Business to the Client?"
$item["Have you verified that this matter is covered by Service Level Agreement or matter engagement?"] = $row."Have you verified that this matter is covered by Service Level Agreement or matter engagement?"
$item["Is the scope of the work we proposed to undertake adequately defined?"] = $row."Is the scope of the work we proposed to undertake adequately defined?"
$item["Have you issued the Letter of Engagement to the client?"] = $row."Have you issued the Letter of Engagement to the client?"
$item["Have you checked that the Firm has requisite client documentation on file to satisfy Anti-Money Laundering regulations?"] = $row."Have you checked that the Firm has requisite client documentation on file to satisfy Anti-Money Laundering regulations?"
$item["Have you requested the requisite identity documentation from the client where it is not currently on file for the client?"] = $row."Have you requested the requisite identity documentation from the client where it is not currently on file for the client?"
$item["With reference to the Firm's Risk Assessment Checklist, have you considered all generic risks of taking on this work?"] = $row."With reference to the Firm's Risk Assessment Checklist, have you considered all generic risks of taking on this work?"
$item["With reference to the Firm's Risk Assessment Checklist, are there any higher or unusual risks of taking on this work?"] = $row."With reference to the Firm's Risk Assessment Checklist, are there any higher or unusual risks of taking on this work?"
$item["Provide higher risk additional details"] = $row."Provide higher risk additional details"
$item["Does the engagement include limitation of liability for the firm at €1.5m?"] = $row."Does the engagement include limitation of liability for the firm at €1.5m?"
$item["Specify reason and amount of limitation"] = $row."Specify reason and amount of limitation"
$item["I sign as Fee Earner"] = $row."I sign as Fee Earner"
$item["Ongoing Risk Assesment Completed?"] = $row."Ongoing Risk Assesment Completed?"
$item["With reference to the Firm's Risk Assessment Checklist, have you considered all generic risks of continuing this work?"] = $row."With reference to the Firm's Risk Assessment Checklist, have you considered all generic risks of continuing this work?"
$item["With reference to the Firm's Risk Assessment Checklist, are there any higher or unusual risks of continuing this work"] = $row."With reference to the Firm's Risk Assessment Checklist, are there any higher or unusual risks of continuing this work"
$item["Detail high or unusual risks of continuing this work"] = $row."Detail high or unusual risks of continuing this work"
$item["I sign ongoing risk as Fee Earner"] = $row."I sign ongoing risk as Fee Earner"
$item["Nominate Partner responsible for supervising ongoing risk"] = $row."Nominate Partner responsible for supervising ongoing risk"
$item["I sign ongoing risk as Partner"] = $row."I sign ongoing risk as Partner"
$item["Fee Earner Signature"] = $row."Fee Earner Signature"
#$item["Date Partner signed"] = $row."Date Partner signed"
$item["Partner Signature"] = $row."Partner Signature"
#$item["Date Fee Earner signed"] = $row."Date Fee Earner signed"
#$item["Date Created on Axxia"] = $row."Date Created on Axxia"
$item["Ongoing Risk Assessment Fee Earner signature"] = $row."Ongoing Risk Assessment Fee Earner signature"
#$item["Ongoing Risk Assessment Date signed"] = $row."Ongoing Risk Assessment Date signed"
$item["Ongoing Risk Assessment Supervising Partner signature"] = $row."Ongoing Risk Assessment Supervising Partner signature"
$item["Ongoing Risk Assessment Date Supervising Partner signed"] = $row."Ongoing Risk Assessment Date Supervising Partner signed"
$item.Update()
}
For all Colums that are set to Date/Time Fields:
$item["Date Partner signed"] = $row."Date Partner signed"
$item["Date Fee Earner signed"] = $row."Date Fee Earner signed"
$item["Date Created on Axxia"] = $row."Date Created on Axxia"
$item["Ongoing Risk Assessment Date signed"] = $row."Ongoing Risk Assessment Date signed"
$item["Legacy Created Date"] = $row."Legacy Created Date
$item["What is the estimated completion date for this matter?"] = $row."What is the estimated completion date for this matter?"
$item["Legacy Created Date"] = $row."Legacy Created Date"
I get this error message:
String was not recognized as a valid DateTime.
At line:11 char:5
+ $item["Legacy Created Date"] = $row."Legacy Created Date"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (:) [], FormatException
+ FullyQualifiedErrorId : System.FormatException
This is an example of format of the Date Field within the CSV: 13/09/2017 15:26:00 (dd/mm/yyyy hh:mm:ss)
It also seems to have an issue with these columns, that are set to people/group:
$item["Fee Earner responsible for day to day file handling"] = $row."Fee Earner responsible for day to day file handling"
$item["Partner responsible for supervising the matter"] = $row."Partner responsible for supervising the matter"
I get this error message:
Exception calling "Update" with "0" argument(s): "Invalid data has been used to update the list item. The field you are trying to update may be read only."
At line:75 char:5
+ $item.Update()
+ ~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SPException
When I comment out those particular snippets above, the code runs fine and starts adding items to the list, though, at random times throughout the script running, I get this error message:
Exception calling "Update" with "0" argument(s): "Invalid data has been used to update the list item. The field you are trying to update may be read only."
At line:75 char:5
+ $item.Update()
+ ~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SPException
It's much the same as the one that was mentioned previously for the people/group columns above, except this time the code is running and I cannot pinpoint the particular item in which I need to troubleshoot.
All help/guidance much appreciated!
Rob