Home > Software design >  PowerShell string replacement with csv as search and replace pattern for a separate file
PowerShell string replacement with csv as search and replace pattern for a separate file

Time:08-12

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.

CodePudding user response:

I found my own answer finally. The problem was that the string was getting replaced in realtime but it was not updating the variable prior to trying to write the result to the $dstFile.

So my final script looks like the following:

$file = "C:\Users\jeffery.grantham\OneDrive - NIC USA, Inc\Documents\peer-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.grantham\Downloads\v5.master"
$dstFile = "C:\Users\jeffery.grantham\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 = [string]$prd2010[$i]
    $2016 = [string]$prd2016[$i]
    $content = $content.replace("$2016", "$2010")
    $content | Set-Content -Path $dstFile -Force
}

I hate when I find my own answer less than an hour after posting a question, but hopefully, finding this will help someone else in the future attempt to do the same thing or something similar.

CodePudding user response:

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
  • Related