none
Powershell replace text in multiple files lookup CSV RRS feed

  • Question

  • Hiya guys

    After some guidance please.

    I have a CSV with following details

    MailboxName, PRFNAME,TempConstant

    usera, a, usera.prf,TEMPLATEPRFUSER

    userb, b, userb.prf,TEMPLATEPRFUSER

    userc, c, userc.prf,TEMPLATEPRFUSER

    Im after creating multiple copies of a prf file with the PRFNAME

    Import-Csv $UsernamesCSV | % { Copy-Item "C:\TemplatePRF\Template.prf" "C:\TEST\$($_.NewPRFName)"}


    This creates multiple prfs named correctly based on the prfnames provided in the CSV

    Now I want to search for mailboxname=TEMPLATEPRFUSER and replace to "MailboxName" from my csv.

    so it will be
    usera.PRF
    Contents
    Mailboxname=user, a

    userb.prf
    contents
    mailboxname=user, b

    So the common field to replace will be TEMPALTEPRF but replace with the relevatnt mailboxname depending on prf name.

    After looking around I found the following

    Param (
        #$List = "C:\TemplatePRF\mailbox.csv",
        $Files = "c:\Test\*.prf"
    )
    $ReplacementList = Import-Csv $UsernamesCSV;
    Get-ChildItem $Files |
    ForEach-Object {
        $Content = Get-Content -Path $_.FullName;
        foreach ($ReplacementItem in $ReplacementList)
        {
            $Content = $Content.Replace($ReplacementItem.TEMPConstant, $ReplacementItem.mailboxn)
    		
    		
    			}
       Set-Content -Path $_.FullName -Value $Content
    }

    At the moment all the files will then have the content "mailboxname=" set as user,a and it does not seem to loop through the remaining and replace correctly.





    • Edited by BobbyWMG Thursday, March 6, 2014 5:47 PM
    Thursday, March 6, 2014 5:45 PM

Answers

  • It looks like something along these lines should work, though the CSV data you posted is currently invalid. If you want to have a comma in a field (such as user, a), it needs to be quoted. Otherwise the comma is treated as a delimiter in the CSV.  Generally, I would recommend just quoting everything, to avoid problems. That's what PowerShell does when you use Export-Csv. Here's the test file I used:

    "MailboxName","PRFNAME"
    "usera, a","usera.prf"
    "userb, b","userb.prf"
    "userc, c","userc.prf"
    

    I got rid of the TempConstant field; there's no reason to repeat that data on every line of the CSV file.  It's just hard-coded in the script right now.  Here's the code:

    $templateFile = 'C:\TemplatePRF\template.prf'
    $csvFile = 'C:\TemplatePRF\mailbox.csv'
    $outputDirectory = 'C:\TemplatePRF\New'
    
    if (-not (Test-Path -Path $outputDirectory -PathType Container))
    {
        New-Item -Path $outputDirectory -ItemType Directory -ErrorAction Stop
    }
    
    $templateContents = Get-Content -Path $templateFile -ErrorAction Stop
    
    Import-Csv -Path $csvFile |
    ForEach-Object {
        $record = $_
    
        $newContents = $templateContents -replace '(?<=mailboxname\s*=\s*)%TEMPLATEPRFUSER%', $record.MailboxName
        $newFile = Join-Path -Path $outputDirectory -ChildPath $record.PRFNAME
    
        Set-Content -Path $newFile -Value $newContents
    }
    

    • Marked as answer by BobbyWMG Monday, March 10, 2014 6:30 PM
    Monday, March 10, 2014 11:44 AM

All replies

  • I don't think any of us can figure out what you are trying to do.

    Can you explain it without any reference to code?

    What is in the files  and what needs to be replaced?


    ¯\_(ツ)_/¯

    Thursday, March 6, 2014 11:44 PM
  • Hi Apologies it did not make sense.

    I have a template .PRF file.

    And a CSV with usernames, surname, lastname

    What I would like to do is generate multiple PRFS, for each individual user listed in the CSV.

    The contents of the PRF then also needs to be changed.

    Mailbox=%REPLACE% needs to become MAILBOX=surname, firstname for each user.

    Hope this makes sense.

    The reason is for a mail migration of 150 users.


    Monday, March 10, 2014 10:25 AM
  • It looks like something along these lines should work, though the CSV data you posted is currently invalid. If you want to have a comma in a field (such as user, a), it needs to be quoted. Otherwise the comma is treated as a delimiter in the CSV.  Generally, I would recommend just quoting everything, to avoid problems. That's what PowerShell does when you use Export-Csv. Here's the test file I used:

    "MailboxName","PRFNAME"
    "usera, a","usera.prf"
    "userb, b","userb.prf"
    "userc, c","userc.prf"
    

    I got rid of the TempConstant field; there's no reason to repeat that data on every line of the CSV file.  It's just hard-coded in the script right now.  Here's the code:

    $templateFile = 'C:\TemplatePRF\template.prf'
    $csvFile = 'C:\TemplatePRF\mailbox.csv'
    $outputDirectory = 'C:\TemplatePRF\New'
    
    if (-not (Test-Path -Path $outputDirectory -PathType Container))
    {
        New-Item -Path $outputDirectory -ItemType Directory -ErrorAction Stop
    }
    
    $templateContents = Get-Content -Path $templateFile -ErrorAction Stop
    
    Import-Csv -Path $csvFile |
    ForEach-Object {
        $record = $_
    
        $newContents = $templateContents -replace '(?<=mailboxname\s*=\s*)%TEMPLATEPRFUSER%', $record.MailboxName
        $newFile = Join-Path -Path $outputDirectory -ChildPath $record.PRFNAME
    
        Set-Content -Path $newFile -Value $newContents
    }
    

    • Marked as answer by BobbyWMG Monday, March 10, 2014 6:30 PM
    Monday, March 10, 2014 11:44 AM
  • But office does that for you.  That is why it uses those in the template file.  When run the current users values are substituted.


    ¯\_(ツ)_/¯

    Monday, March 10, 2014 2:01 PM
  • Here is how to use a PRF file.  This will show you how the PRF file is designed and how to customize it.

    http://technet.microsoft.com/en-us/library/cc179062(v=office.14).aspx

    You build one file and it get used by all users.  All variables (%XXXX%) get replaced by  Outlook when it runs.  You do not do this manually.

    This is why you should post Office, Exchange and Outlook questions in the Office forum.  You will get the better methods for deploying Office products.

    In an Exchange environment none of the is necessary as Exchange does it all.  Just install Office.  Tell the user to enter their email when prompted.  Everything else is automatic and can be customized on the Exchange server or in Active Directory.


    ¯\_(ツ)_/¯


    • Edited by jrv Monday, March 10, 2014 2:08 PM
    Monday, March 10, 2014 2:07 PM
  • Many Many Many Thanks David.

    Exactly what I was after.

    The reason had to manually create the files was that the username does not match the mailbox name and file name was the be the username.

    But this was exactly what I was after many thanks.!


    Monday, March 10, 2014 6:45 PM
  • The way to deal with that is to set an environment variable to the altered username as a GPP. Outlook will pick it up.  It only using that value on first run.

    Also the alias can be used instaead of usrename.  The alias is how we identify the mailbox.

    What you are doing I sunnecessary with Exchange/Outlook 2010 and later.

    With Exchange 2010 we should just use  use the default connection method which automatically discovers the users mailbox via AD.  The PRF file is really only useful for POP3 mail.


    ¯\_(ツ)_/¯

    Monday, March 10, 2014 6:59 PM
  • Many Thanks JRV.

    I understand what you have mentioned. However the issue was that it is a migration sequence we are going through the current exchange server is not ours exchange 2007, autodiscover is enabled for the old environment we have no access on that network. We have taken over another company and are allowing the users access to our exchange environment but they are not on our network either hence the prf to define the exchange proxy settings etc.

    But Thank you also for your assistance.


    Tuesday, March 11, 2014 4:28 PM
  • That really has nothing to do with what I am referring to so forget it.  Takes some time later to review how Exchange 200y and later discover mailboxes by smtp mail address.

    ¯\_(ツ)_/¯

    Tuesday, March 11, 2014 4:43 PM