Sign in
 
HomeWindows Server 2012Windows Server 2008 R2Windows Server 2003LibraryForums
Windows Server TechCenter >
> 
> 
string;# added to exported csv from SharePoint & column order not as expected

Întrebare string;# added to exported csv from SharePoint & column order not as expected

  • Wednesday, May 09, 2012 6:56 PM
     
      Has Code
    Sign In to Vote
    0

    Hello

    I have a csv export file which exports a list from sharepoint to a csv (see script below)

    $MyWeb = Get-SPWeb http://test.portal.co.uk/
$MyList = $MyWeb.Lists["Replication Calendar"]
$exportlist = @()
$Mylist.Items | foreach {
$obj = New-Object PSObject -Property @{
	"Subject" = $_["Subject"] 
	"Start Date" = $_["Start Date"]
	"Start Time" = $_["Start Time"]
	"End Date" = $_["End Date"]  
	"End Time" = $_["End Time"] 
	"Categories" = $_["Categories"]     
	"Location" = $_["Location"]                   
}
$exportlist += $obj
$exportlist | Export-Csv -path 'C:\Filename.csv' -NoTypeInformation
}

    <xmlns:texthelpns rwthpgen="1">However my csv file ends up like this with the columns in the wrong order and string;# is added.</xmlns:texthelpns><xmlns:texthelpns rwthpgen="1">Also the same start date/time and end date/time is used in all of the rows when they should be different.</xmlns:texthelpns>

    "Start Date","End Date","Subject","Start Time","End Time","Location","Categories"
"string;#09/05/2012","string;#09/05/2012","Test Full Subject","string;#19:00","string;#20:00","Test Location","Sports Fixtures"
"string;#09/05/2012","string;#09/05/2012","Test Event","string;#19:00","string;#20:00","Test Hall","FQE"
"string;#09/05/2012","string;#09/05/2012","Tester Night","string;#19:00","string;#20:00","Test Field","Exams/Coursework - External"


    <xmlns:texthelpns rwthpgen="1">This has only happened since I added the following calculated fields to the SharePointlist (</xmlns:texthelpns>Technet Thread Link<xmlns:texthelpns rwthpgen="1">):</xmlns:texthelpns>

    • <xmlns:texthelpns rwthpgen="1">Start Date e.g. </xmlns:texthelpns>09/05/2012
    =TEXT(Start,"dd/mm/yyyy")
    • Start Time e.g. 13:30
    =TEXT(Start,"hh:mm")
    • End Date e.g. 10/05/2012
    =TEXT(End,"dd/mm/yy")
    • End Time e.g. 14:29
    =TEXT(End,"hh:mm")

    Please could anyone let me know where I am going wrong?

    Thanks


    • Edited by Powerballer Wednesday, May 09, 2012 6:57 PM
    •  
     

All Replies

  • Wednesday, May 09, 2012 9:40 PM
     
      Has Code
    Sign In to Vote
    0

    I imagine sharepoint is doing that because you've converted a datetime object to a string.

    the simple solution is probably to just rip it out 

    "Start Date" = $_["Start Date"] -replace "string;#"

    Justin Rich
    http://jrich523.wordpress.com
    PowerShell V3 Guide (Technet)
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

     
  • Wednesday, May 09, 2012 10:08 PM
     
      Has Code
    Sign In to Vote
    0

    Thanks jrich , I think I've sorted it now. I've changed the code to include a select query at the bottom and used get-date to obtain the date fields and then used replace on the times.

    $MyWeb = Get-SPWeb http://test.portal.co.uk/
$MyList = $MyWeb.Lists["Replication Parent Calendar"]
$exportlist = @()
$Mylist.Items | foreach {
$obj = New-Object PSObject -Property @{
	"Subject" = $_["Subject"] 
	"Start Date" = get-date ($_["Start"]) -uformat "%d/%m/%Y"
	"Start Time" = $_["Start Time"] -replace "string;#"
	"End Date" = get-date ($_["End"])  -uformat "%d/%m/%Y" 
	"End Time" = $_["End Time"] -replace "string;#"
	"Categories" = $_["Categories"]     
	"Location" = $_["Location"]                   
}
$exportlist += $obj
} 
$exportlist | select "Subject", "Start Date", "Start Time", "End Date", "End Time", "Categories", "Location" | Export-Csv -path 'C:\Filename.csv' -NoTypeInformation
$MyWeb.Dispose()

    I've managed to sort get-date for Start and End Date so that the correct value is displayed without the replace being implemented. Could anyone advise me how to fix Start and End time without using -replace string?

    The current csv file which has all the information, which I just need to make some minor formatting alterations for it to work with the external website:

    "Subject","Start Date","Start Time","End Date","End Time","Categories","Location"
"Test Full Subject","17/05/2012","09:00","17/05/2012","09:00","Sports Fixtures","Test Location"
"Test Event","09/05/2012","19:00","09/05/2012","20:00","FQE","Test Hall"
"Tester Night","14/05/2012","15:00","14/05/2012","16:00","Exams/Coursework - External","Test Field"

    Is there any way to remove the "" from the headers in the csv file so they appear like this?

    Subject,Start Date,Start Time,End Date,End Time,Categories,Location

    Also can the speech marks be removed from the dates and times so they appear like this but still appear for subject, categories and location fields?

    "Brass Band",16/05/2012,19:30,16/05/2012,22:00,"Ex. Letting","Refectory"
    Thanks

     
  • Thursday, May 10, 2012 2:07 AM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    Hi,

    $obj = New-Object PSObject -Property @{
	Subject = $_["Subject"] 
	Start Date = get-date ($_[Start]) -uformat "%d/%m/%Y"
	Start Time = $_[Start Time] -replace "string;#"
	End Date = get-date ($_[End])  -uformat "%d/%m/%Y" 
	End Time = $_[End Time] -replace "string;#"
	Categories = $_["Categories"]     
	Location = $_["Location"]                   
}

    Regards,

    Yan Li

    Yan Li

    TechNet Community Support

     
  • Thursday, May 10, 2012 9:19 AM
     
      Has Code
    Sign In to Vote
    0

    Hi Yan

    Thanks but unfortunately that does not work, I get when adding your code block to my script:

    PS C:\Users\sp_admin> .\export.ps1
Missing '=' operator after key in hash literal.
At C:\Users\sp_admin\export.ps1:7 char:12
+     Start Date <<<<  = get-date ($_[Start]) -uformat "%d/%m/%Y"
    + CategoryInfo          : ParserError: (:) [], ParseException
    + FullyQualifiedErrorId : MissingEqualsInHashLiteral

    Seems it requires the quotation marks around Start Date and Start to get the script to run.

    Thanks

     
  • Thursday, May 10, 2012 12:27 PM
     
     
    Sign In to Vote
    0
    I did a test with quoting property names as you have, and I don’t get that
    in my output.
     
    I am using V3, but I don’t think that makes a difference.
     
    also, you don’t need the select because that’s all of the data.
     
    have you shown the results in the window? ie just type $exportlist
     
    also take a look at what get-member returns
     
    $exportlist | gm
     
    just curious how it shows the property names
     
     
    Justin Rich
    http://jrich523.wordpress.com
    PowerShell V3 Guide (Technet)
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
     
  • Thursday, May 10, 2012 3:57 PM
     
      Has Code
    Sign In to Vote
    0

    Hi jrich

    Thanks for getting back to me

    Here is the results of $exportlist | gm

       TypeName: System.Management.Automation.PSCustomObject

Name        MemberType   Definition
----        ----------   ----------
Equals      Method       bool Equals(System.Object obj)
GetHashCode Method       int GetHashCode()
GetType     Method       type GetType()
ToString    Method       string ToString()
Categories  NoteProperty System.String Categories=Sports Fixtures
End Date    NoteProperty System.String End Date=17/05/2012
End Time    NoteProperty System.String End Time=09:00
Location    NoteProperty System.String Location=Test Location
Start Date  NoteProperty System.String Start Date=17/05/2012
Start Time  NoteProperty System.String Start Time=09:00
Subject     NoteProperty System.String Subject=Test Full Subject

    The results are displaying ok and as they should just having difficulty removing the quotation marks from Start/End Time/Date and the headers?

    If I remove the select then I get the column order in the csv as:

    "Start Date","End Date","Subject","Start Time","End Time","Location","Categories"

    <xmlns:texthelpns rwthpgen="1">Which is incorrect and I want the order to be as displayed in the code.</xmlns:texthelpns><xmlns:texthelpns rwthpgen="1">I also have to add quotations to my property names to get the powershell script to run.</xmlns:texthelpns>

    $MyWeb = Get-SPWeb http://test.portal.co.uk
$MyList = $MyWeb.Lists["Replication Parent Calendar"]
$exportlist = @()
$Mylist.Items | foreach {
$obj = New-Object PSObject -Property @{
	"Subject" = $_["Subject"] 
	"Start Date" = get-date ($_["Start"]) -uformat "%d/%m/%Y"
	"Start Time" = $_["Start Time"] -replace "string;#"
	"End Date" = get-date ($_["End"]) -uformat "%d/%m/%Y" 
	"End Time" = $_["End Time"]  -replace "string;#"
	"Categories" = $_["Categories"]     
	"Location" = $_["Location"]                                        
}
$exportlist += $obj
} 
$exportlist | Export-Csv -path 'C:\Filename.csv' -NoTypeInformation


    • Edited by Powerballer Monday, May 21, 2012 5:02 PM
    •  
     
  • Thursday, May 10, 2012 6:16 PM
     
     
    Sign In to Vote
    0
    very weird, my test obj shows basically the same
     
    $obj = new-object psobject -prop @{
    name="value"
    'test val' = get-date
    }
     
    actually, I change my mind, I've been opening my CSV in excel, and excel has
    been changing (removing) the quotes, if I open it in notepad everything is
    quoted..
     
    I guess if you want to get rid of the quotes you'll need to manually output
    it?
     
     
    Justin Rich
    http://jrich523.wordpress.com
    PowerShell V3 Guide (Technet)
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.