Welcome to Day 6 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at //blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.
Frequently when working with PowerShell, you’ll want to use flat files to store data that you will take action on. Alternatively, you may want to export data to a file. Either way, today we’re going to cover it.
Exporting Data
There are many ways where PowerShell will export data into a file (to see all of the ways to export in PowerShell, run help export), but I’m only going to cover a few of them. The first method is the Set-Content cmdlet – this cmdlet will write to the specified file the data sent to it. For an example, let’s make a text file of all of the SQL Server instances that you want to work with later (we’ll use this file in later examples, so set the instance names correctly for your system – if it’s a default instance, use DEFAULT for the instance name). We’ll start the file off with a header line, then all of the instances, followed by writing this file to disk, and finally opening the file up in notepad:
|
1 2 3 4 5 6 7 8 9 10 11 |
$t = @' ServerName localhost\SQL2005 localhost\SQL2008 localhost\SQL2008R2 localhost\SQL2012 '@ Set-Content -path $env:TEMP\ServerList.txt -Value $t Notepad $env:TEMP\ServerList.txt |
The Set-Content cmdlet has a lot of flexibility and power, so be sure to read more about it with help Set-Content.
The next method that I’ll cover is creating delimited data. For this, we’ll use the Export-CSV cmdlet. (Despite its name, this cmdlet can export delimited data to other file extensions, and with other delimiters.) Earlier, we obtained a list of all of the processes running sqlservr. Let’s modify this to export the results into a colon delimited file:
|
1 2 3 4 5 |
Get-Process | ` Where-Object ProcessName –EQ 'sqlservr' | ` Export-CSV -Path $env:TEMP\SQLProcesses.txt -Delimiter ":" Notepad $env:TEMP\SQLProcesses.txt |
Here we can see that all of the properties returned from Get-Process are put into a file with colons as the delimiter.
The final method that I’ll cover is the Out-File cmdlet:
|
1 2 3 4 5 |
Get-Process |` Where-Object ProcessName -EQ 'sqlservr' |` Out-File $env:TEMP\SQLProcesses.dat Notepad $env:TEMP\SQLProcesses.dat |
You can see the difference between using Export-CSV and Out-File – Export-CSV exports all of the properties, and Out-File exports just the default properties.
Importing Data
Now we’ll cover the corresponding methods for retrieving information from files. The first method is the Get-Content cmdlet. This cmdlet will read all of a file into a variable or send it down the pipeline. Let’s read the ServerList.txt file that we created above:
|
1 |
Get-Content -Path $env:TEMP\ServerList.txt |
Pretty neat… but it reads the entire file. That’s not to helpful when trying to loop through a list of servers, so let’s try this instead:
|
1 2 3 4 5 6 |
$i = 0 ForEach ($Item in Get-Content -Path $env:TEMP\ServerList.txt) { $i += 1 Write-Host $i, $Item } |
Here I added a line variable number, just to show that this is actually working with one line at a time.
The next method is the Import-CSV cmdlet. When utilizing this cmdlet, the default is that the columns have headers. So, let’s start off by reading the same ServerList.txt file:
|
1 2 3 4 5 6 7 |
$Servers = Import-CSV -Path $env:TEMP\ServerList.csv $i = 0 ForEach ($Server in $Servers) { $i += 1 Write-Host $i, $Server.ServerName } |
The difference here is the header line… the Import-CSV has assigned the data in the file to the column ServerName.
Using Import-CSV to read in the delimited file we exported:
|
1 2 |
Import-CSV $env:Temp\SQLProcesses.txt -Delimiter ":" |` Select-Object Name, Id |
And with this, I’m wrapping up today’s session on importing and exporting.
