Export Sharepoint list to CSV, then create a working XML file

Question

I am trying to export a SharePoint (365 online) list using PowerShell. Export result should go to CSV. From there I want to grab the infromation and merge it into a XML for directory listing.

Directly Listing output should be.

<YeastarIPPhoneDirectory>     

<DirectoryEntry>    
  <Name>Reception</Name>    
  <Telephone>200</Telephone>    
</DirectoryEntry>


<DirectoryEntry>    
  <Name>User1</Name>    
  <Telephone>201</Telephone>    
</DirectoryEntry> 

<DirectoryEntry>    
  <Name>User2</Name>  
  <Telephone>202</Telephone>    
</DirectoryEntry>

My SharePoint list contains all the relevant info Name and ext number. The verbals are "user" and the extension number.

This then needs to be automated to run daily.

The code I have so far:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
$LiveCred = Get-Credential

$web = Get-SPWeb -identity "https://*.sharepoint.com/IT/Lists/VOIP%20Extentions/"

$list = $web.Lists["Voip%20Extentions"]

$ListItemCollection = @()
$list.Items | Where-Object { $_["Status"] -eq "In Progress" } | foreach {
    $ExportItem = New-Object PSObject
    $ExportItem | Add-Member -MemberType NoteProperty -Name "Wxt" -Value $_["Ext"]
    $ExportItem | Add-Member -MemberType NoteProperty -Name "Name" -Value $_["Name"]

    $ListItemCollection += $ExportItem
}
$ListItemCollection | Export-Csv "c:\Phonelist.txt" -NoTypeInformation

Please tell us what your code succeeds in or fails to do at the moment. Is ConvertTo-Xml what you're looking for? — Theo
– Theo, Commented Sep 16, 2019 at 10:55
The current code exports to plain txt or csv, either works, But i need to create an xml file from that csv to import into : — Darius
– Darius, Commented Sep 16, 2019 at 11:01
Why do you want to export the data to a CSV and then create the XML from that instead of creating the XML directly? Also, since the CSV export code is apparently working, please show the non-working XML conversion code, so that we may provide assistance with that. — Ansgar Wiechers
– Ansgar Wiechers, Commented Sep 16, 2019 at 11:26

Theo · Accepted Answer · 2019-09-16 11:42:21Z

To convert the data to XML, use the ConvertTo-Xml cmdlet.

# convert to XML and save to file
$ListItemCollection | ConvertTo-Xml -As String -NoTypeInformation | Out-File -FilePath 'c:\Phonelist.xml' -Encoding utf8

Also, I would change the way you collect the data in the $ListItemCollection variable to avoid using array concatenation with $ListItemCollection += $ExportItem like this:

$ListItemCollection = $list.Items |  Where-Object { $_["Status"] -eq "In Progress"} | ForEach-Object {
    [PsCustomObject]@{
        "Wxt"  = $_["Ext"]
        "Name" = $_["Name"]
    }
 }

Or customize the XML by using the XmlTextWriter class:

# Set The Formatting
$xmlsettings = New-Object System.Xml.XmlWriterSettings
$xmlsettings.Indent      = $true
$xmlsettings.IndentChars = "    "

# Set the File Name Create The Document
$XmlWriter = [System.XML.XmlWriter]::Create("D:\test.xml", $xmlsettings)

# Write the XML Decleration and set the XSL
$xmlWriter.WriteStartDocument()

# Start the Root Element
$xmlWriter.WriteStartElement("YeastarIPPhoneDirectory")
# write the data
$ListItemCollection | ForEach-Object {
    $xmlWriter.WriteStartElement("DirectoryEntry")
        $xmlWriter.WriteElementString("Name", $_.Name)
        $xmlWriter.WriteElementString("Telephone", $_.Wxt)
    $xmlWriter.WriteEndElement()                   # End <DirectoryEntry>
}
$xmlWriter.WriteEndElement()                       # End <YeastarIPPhoneDirectory> 

# End, Finalize and close the XML Document
$xmlWriter.WriteEndDocument()
$xmlWriter.Flush()
$xmlWriter.Close()

This creates an xml:

<?xml version="1.0" encoding="utf-8"?>
<YeastarIPPhoneDirectory>
    <DirectoryEntry>
        <Name>Reception</Name>
        <Telephone>200</Telephone>
    </DirectoryEntry>
    <DirectoryEntry>
        <Name>User1</Name>
        <Telephone>201</Telephone>
    </DirectoryEntry>
    <DirectoryEntry>
        <Name>User2</Name>
        <Telephone>202</Telephone>
    </DirectoryEntry>
</YeastarIPPhoneDirectory>

Hope that helps

Collectives™ on Stack Overflow

Export Sharepoint list to CSV, then create a working XML file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related