How to Read a Text File into a PowerShell Script
- Hi, I am wondering if anyone has some suggestions as to how I can take a text file or an excel file and read the data from that into variables in a powershell script. We will be implementing four Exchange 2007 servers, each with 50 storage groups per server and 50 databases per storage group. Rather than have to repeat the New-StorageGroup and New-MailboxDtabase cmdlet FIFTY times in a script, each command duplicated with different SG, DB, path names, etc, I was looking for a way to create a variable for things like server name, SG name, DB name, EDB file path, etc, and pull this information from a text or excel file when the script is run. Is there anywhere out there that someone has a template for this type of script I am looking for? We will be placing 10 SG on 5 LUNS, totaling 50 SG and 50 DB. So each needs its own cmdlet written to be created. If there is an easy way to pull this data from a file, it would save us a lot of time for the 200 SGs and DB we need to create. Thanks!
-Michael
All Replies
Starting with creating the storage groups, assuming you've got an Excel spreadsheed with columns for Server, SGName,LogPath, and SystemPath (with exactly those labels) then:
Save it in somefolder as SGdata.csv, the from EMS:
Import-csv "c:\somefolder\SGdata.csv" |% {
new-storagegroup -Name $_.SGName -Server $_.Server -LogFolderPath $_.LogPath -SystemFolderPath $_.SystemPath
}
Run up another spreadsheet with the data you need for the databases, and repeat the same process. Just make sure the column headings on your csv match the properties you're using to set the parameters of the new-mailboxdatabase cmdlet. I'd avoid embeddes spaces in the column headings. They'll mess with the parser, and you end up having to quote all the properties (eg $_."SG Name").- Thanks for the info. So I also can use this with the -StandbyMachine switch to establish SCR? I assume the answer is yes, since you explained how to set up the variables and that's all there is to it. If I have written it as follows, and list the first row in an excel spreadsheet with the exact names of the variables - such as. This will save us a lot of time if it does indeed work! (I assume also I can save the xls sheet as a csv). Do you know if I can pipe the results of the script to a text file?
Server SGName LogPath SystemPath StandbyMachine ReplayLagTime TruncationLagTime
Import-CSV
"C:\SGData.csv" |% {New-StorageGroup -Server: $_.Server -Name: $_.SGName -LogFolderPath: $_.LogPath -SystemFolderPath: $_.SystemPath -StandbyMachine $_.StandbyMachine -ReplayLagTime $_.ReplayLagTime -TruncationLagTime $_.TruncationLagTime}
You can easily add the SCR config by just adding mre columns oto your spreadsheet.
As far as saving to .csv, just do a File | SaveAs and choose CSV (Comma delimited) or CSV (MS-DOS).
The easiest way to save the output is to add a "start-transcript" before you run the script:
start-transcript "C:\somedir\somefile.txt"
All the console output will be saved in that file until you exit the session, or do a stop-transcript. You may want to add the -verbose switch to the new-storagegroup and new-mailboxdatabase commands to diplay more information.- Edited bymjolinor Wednesday, November 04, 2009 7:05 PMtypos
- One more tip - if you want to do a "dry run" of the scirpt first, add the -whatif switch to the new-storagegroup and new-mailboxdatabase commands. Then it won't actually try to create them, it will just tell you what it would have done if the -whatif swithc wasn't there. When it looks right, remove the -whatif and re-run it.
- Mjolinor,
Thanks for all your help! I really appreciate i!
