Search code examples
powershellcsvsharepoint

PowerShell string replacement with csv as search and replace pattern for a separate file


I'm trying to find a way to reliably replace all occurrences of a string found in a file with data from a column in a CSV using one column as the search pattern with data from the same row on the next column for the replace pattern. The new data is then written to a new file as to keep the original intact. The purpose of this is to simplify exchanging IDs between environments that are hardcoded into the Master pages of a SharePoint site collection. Here's what I have so far.

$file = "C:\Users\jeffery\Documents\ids.csv"
$csv = Import-Csv -Path $file -Delimiter `,
$prd2016 = $csv.'2016 PRD ID'
$stg2016 = $csv.'2016 STG ID'
$prd2010 = $csv.'2010 PRD ID'
$srcFile = "C:\Users\jeffery\Downloads\v5.master"
$dstFile = "C:\Users\jeffery\Downloads\v6.master"
Set-Variable 2010,2016
$content = Get-Content -Path $srcFile

For($i=0; $i -lt $prd2016.Count; $i++){
    Clear-Variable 2010
    Clear-Variable 2016
    $2010 = $prd2010[$i]
    $2016 = $prd2016[$i]
    $content.replace("$2016", "$2010") | Set-Content -Path $dstFile -Force
}

I've also tried nested loops and using foreach loops to no avail as of yet. Any help will be greatly appreciated. Also, here's some sample data to assist with any answers.

CSV Data: Navigation,a5a0c64c-17b1-4cba-a8ff-a6a61d8466f3,a66d1d48-ab5e-4aed-9eb9-e8763b88ff2a,2d3cd026-7e2a-4241-8500-abd9a83a0803

Source file data:

<WebPartPages:DataFormWebPart runat="server" IsIncluded="True" AsyncRefresh="false" NoDefaultStyle="TRUE" ViewFlag="8" Title="Navigation" PageType="PAGE_NORMALVIEW" __markuptype="vsattributemarkup" __WebPartId="{9CDA54AA-5C9F-4E62-A0D6-BE149C8B27F0}" partorder="2" id="g_9cda54aa_5c9f_4e62_a0d6_be149c8b27f0" listname="{a5a0c64c-17b1-4cba-a8ff-a6a61d8466f3}" pagesize="1" chrometype="None" __AllowXSLTEditing="true" WebPart="true" Height="" Width="">
    <DataSources><SharePoint:SPDataSource runat="server" DataSourceMode="List" UseInternalName="true" UseServerDataFormat="true" selectcommand="&lt;View&gt;&lt;Query&gt;&lt;Where&gt;&lt;Eq&gt;&lt;FieldRef Name=&quot;Title&quot;/&gt;&lt;Value Type=&quot;Text&quot;&gt;Top Nav&lt;/Value&gt;&lt;/Eq&gt;&lt;/Where&gt;&lt;/Query&gt;&lt;/View&gt;" id="dataformwebpart8"><SelectParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="{a5a0c64c-17b1-4cba-a8ff-a6a61d8466f3}"/><asp:Parameter Name="MaximumRows" DefaultValue="1"/></SelectParameters><DeleteParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="{a5a0c64c-17b1-4cba-a8ff-a6a61d8466f3}"/></DeleteParameters><UpdateParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="{a5a0c64c-17b1-4cba-a8ff-a6a61d8466f3}"/></UpdateParameters><InsertParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="{a5a0c64c-17b1-4cba-a8ff-a6a61d8466f3}"/></InsertParameters></SharePoint:SPDataSource></DataSources>
    
    <datafields>@Title,Title;@Navigation,Navigation;@ID,ID;@ContentType,Content Type;@Modified,Modified;@Created,Created;@Author,Created By;@Editor,Modified By;@_UIVersionString,Version;@Attachments,Attachments;@File_x0020_Type,File Type;@FileLeafRef,Name (for use in forms);@FileDirRef,Path;@FSObjType,Item Type;@_HasCopyDestinations,Has Copy Destinations;@_CopySource,Copy Source;@ContentTypeId,Content Type ID;@_ModerationStatus,Approval Status;@_UIVersion,UI Version;@Created_x0020_Date,Created;@FileRef,URL Path;@ItemChildCount,Item Child Count;@FolderChildCount,Folder Child Count;@AppAuthor,App Created By;@AppEditor,App Modified By;</datafields>
    <XSL><xsl:stylesheet xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:d="http://schemas.microsoft.com/sharepoint/dsp" version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:SharePoint="Microsoft.SharePoint.WebControls" xmlns:ddwrt2="urn:frontpage:internal">

Thank you in advance for any and all help with this query.


Solution

  • You're jumping through some serious hoops to avoid using the objects Import-Csv gives you and foreach.

    You're also overwriting the destination file every time you loop, which is unnecessary.

    $srcFile = "C:\Users\jeffery.grantham\Downloads\v5.master"
    $dstFile = "C:\Users\jeffery.grantham\Downloads\v6.master"
    $file = "C:\Users\jeffery\Documents\ids.csv"
    
    $csv = Import-Csv -Path $file
    
    $content = Get-Content -Path $srcFile -Raw # Read the file into a single string.
    
    foreach ($row in $csv) {
        $content = $content.Replace($row.'2016 PRD ID', $row.'2010 PRD ID')
    }
    
    $content | Set-Content -Path $dstFile -Force