0

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

Improve this question

1 Answer 1

Reset to default
1

the date format is typically the ISO format 8601 yyyy-MM-ddThh:mm:ssZ

(the above page documentation is actually wrong as it states minutes twice)

e.g.

(get-date).ToString("yyyy-MM-ddThh:mm:ssZ")

output

2017-10-11T11:56:53Z

to convert a specific date string,

[datetime]::parseexact('01-Jul-16', 'dd-MMM-yy', $null).ToString('yyyy-MM-ddThh:mm:ssZ')

For parsing users, you may need EnsureUser:

See More:

Powershell; copy list item across site collection with people picker fields

Error handling in ensureuser() method

CAML Query for Date Range - Not Working

update:

based on your settings, you need something like:

[datetime]::parseexact('13/09/2017 15:26:00', 'dd/MM/yyyy HH:mm:ss', $null).ToString('yyyy-MM-ddThh:mm:ssZ')

or, using your $row variable, full line for assignment,

$item["Legacy Created Date"] = [datetime]::parseexact($row."Legacy Created Date", 'dd/MM/yyyy HH:mm:ss', $null).ToString('yyyy-MM-ddThh:mm:ssZ')

based on my findings, your problem was due to having a 24h clock. you have to map the hour with HH instead of hh, otherwise it will effectivelly throw,

Exception calling "ParseExact" with "3" argument(s): "String was not recognized as a valid DateTime."

Improve this answer
9
  • Cool, thanks - but how do I apply that to my code? - I am getting date/time information from a CSV file and it's been added to a date/time field in SharePoint.
    – Humble-Self
    Oct 11, 2017 at 11:33
  • I guess you have 2 options. either "fix" the csv to be compliant with that format (2017-10-11T11:56:53Z), or you parse the cell value into a DateTime valid format and then use tostring like above to convert it to the ISO format. example added
    – Tiago Duarte
    Oct 11, 2017 at 11:38
  • Thanks, I do appreciate your help. Your talking to a complete n00b on Powershell, so you need to handhold me through it, looking at my code specifically, where do I need to insert that snippet you gave an example of to parse the date/time form the csv? do i then need to change the code for adding the item from the file in to the list?
    – Humble-Self
    Oct 11, 2017 at 12:08
  • you can start by giving us an example of how your date string looks and what format it is in (e.g. year month day, month day year, etc)
    – Tiago Duarte
    Oct 11, 2017 at 12:24
  • Thanks, The example of the date string is in my original explanation set out above, this is how it looks in the CSV: 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) This is the code i am using to import $item["Legacy Created Date"] = $row."Legacy Created Date"
    – Humble-Self
    Oct 11, 2017 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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