Use pipe breaks to perform various data operations in PowerShell

Recently in training PowerShell, when it comes to pipeline, I feel very useful for those of me who have been dealing with the data (database) for a long time, so write this blog post and record it.

Whether you write a bash script on Linux or write PowerShell on a window, the pipe symbol "|" is a very useful tool. It provides the ability to enter the output of the previous command as input to the next command. In data processing, we can also use pipe characters to perform various operations on the data.

Import&export Import and Export

First, the import and export is to be able to prepare data for subsequent data processing. In PowerShell we can also get all kinds of data with various GET-XXX commands, but not all operating systems and each version of PowerShell support a command. For example, the Get-volume command is used to obtain information for each disk, but this command cannot be run under Win7 and can only be run under Win8 or Win2012server.

The most common and simplest external data source is the CSV file. We can use the Export-csv command to convert objects in PowerShell to CSV format and persist to disk. For example, we export all of the current process information to a CSV file with the following command:

get-Process| Export-csv C:\test.csv-Encoding Unicode

(Note that if there is a Chinese content suggestion set encoding to Unicode or UTF8)

The Import-csv command is to import the external CSV file into memory. Compare the CSV file you just exported, and we'll take care of the file next. We can save the contents of the file to the variable $data. The command is:

$data=import-csv C:\test.csv-Encoding Unicode

Of course, we can do the type conversion first and then save it. The command is:

$data| Convertto-csv | Out-fileC:\test.csv-Encoding UTF8 Sorting sort

We have loaded the CSV content into the $data variable before, so if we want to sort by a field, we can use the Sort-object command.

For example, we want the name field to sort and output the sorted result, then the command is:

$data| Sort-object Name

can also be abbreviated as:

$data| Sort Name

If you need more than one field to sort, you can list the fields later and separate the fields with commas.

$data| Sort Name,handles

If this is a reverse order, then you need to add a parameter to the field after-descending

$data| Sort name–descending Selecting selection

Select the equivalent of the Select command in SQL. The corresponding PowerShell command is Select-object, which can be abbreviated as SELECT. Follow the command followed by the column name you want to select. If you want to select all columns, you can also use the * representation.

$data| Select NAME,VM

Select all columns, then the command is:

$data| SELECT *

If you select only the previous data, you can use the-first parameter. For example, we sort by handles, only look at the first 10 process record name and handles. The command is:

$data| Sort Handles | Select Handles,name-first 10

In addition to the parameter-last selected is the last few records,-skip can choose to skip a certain record.

Calculate Computed Columns

In select, we can use functions to perform operations on the columns in which the syntax is:

@{
N= ' New Column Name ';
e={$_. Xxxcalc}
}

The $_ is a representation of the current record.

For example, the VM column records the data in bytes, we first create a new column named "VM (MB)", whose value is converted to MB results, then we can write as:

$data| Select name,vm,@{n="VM (MB)"; e={$_. VM/1MB}} measuring metric

The measure may be a bit unclear, but it is the aggregation function in the corresponding SQL. For example, SUM, Max,min and the like, need to use the Measure-object command. For example, to see how many programs, the smallest handles, and the largest handles, the command is:

$data| Measure-object-property Handles-minimum-maximum

Now that you're talking about aggregate functions in SQL, it's natural to think of another keyword, group by. The corresponding command Group-object is also available in PowerShell. If we want to group by the name of the process, look at the total VM size for each process name. Then we can first group by name:

$data| Group-object Name

At this point we can see that the system returns 3 columns of the result: Count,name,group. and the VM value that we want to aggregate is in group. You need to use the Select command mentioned earlier.

$data| Group-object Name | Select name,count,@{n="TOTALVM"; e={($_. Group | Measure-object-property vm-sum). Sum}} Filter filtering

Filtering is equivalent to the WHERE statement in SLQ, using the Where-object command in PowerShell. can be shortened to where, or even abbreviated as "?". The comparison and logical operations we encounter in a common program are different in PowerShell, which is the parameter:

Comparison	Case-insensitive	case-sensitive
Equality	-eq	-ceq
Inequality	-ne	-cne
Greater than	-gt	-cgt
Less than	-lt	-clt
Greater than or equal to	-ge	-cge
Less than or equal to	-le	-cle
Wildcard Equality	-like	-clike

-and and-or are used for logical operations.

Still taking the $data of the previous load for example, we want to see the handles and name of the process starting with W, then the command is:

$data| ? {$_. Name-like' W* '}| Select Handles,name

If you have more than one condition, starting with W and a process with a VM larger than 100M, the command is:

$data| ? {$_. Name-like' W* '-and$_. Vm-GT100mb}| Select HANDLES,NAME,VM Enumeration Enumeration

An enumeration is equivalent to a foreach function in C #, or a cursor in SQL, with an operation or function for each row of data. The corresponding command in PowerShell is Foreach-object, which can be abbreviated as foreach, and further abbreviated as "%".

For example, if we want to change the VM to megabytes, we can operate on each row of data:

$data| % {$_. vm=$_. VM/1MB}

After you run the command, we'll look at $data and we'll see that the VM column has changed.

$data| Select NAME,VM

In addition, for the foreach command, there are two more useful parameter-begin–end, which are called before the For loop and after the loop is completed.

For example, we want to write a column to a file, we can create a file at-begin, record the start time, then append the content to the file in foreach, and finally write the end time:

$data| % -Begin{get-date | Out-fileC:\test.txt}-Process{$_. Name | Out-fileC:\test.txt-Append}-End{get-date | Out-fileC:\test.txt-Append}

Use pipe breaks to perform various data operations in PowerShell