How to Read a Csv File Using Power Shell

Posey's Tips & Tricks

How To Use .CSV Files with PowerShell, Part 2

In the second part of this series, Brien shows how to import a .CSV file into a PowerShell array, including two methods for zooming in on merely the specific data you need and filtering out the residual.

In Office 1, I showed you how to use PowerShell to create a .CSV file. At present, I desire to prove you how to import a .CSV file into a PowerShell array.

For the purposes of this cavalcade, I will exist using a .CSV file containing a list of processes that were running on my machine, just the techniques I am most to show you should piece of work with whatsoever .CSV file so long equally it contains a header row followed past data rows.

The quick and easy way to import a .CSV file into an assortment is but to map a variable to the Import-CSV cmdlet. Here is an case:

$Processes = Import-CSV C:\Temp\Process.csv

After doing so, you can type the variable name and printing Enter to see its contents. You lot can see an case of this in Figure 1.

Figure ane: I have imported the contents of a .CSV file into an array.

Equally you look at the figure higher up, you lot will detect that when I outputted the array's contents, the output is divided into ii columns. The left column contains a header and the right column contains the data. Because PowerShell respects the column headers that are built into the file, information technology is possible to filter and format the data just as yous could if it were generated natively within PowerShell.

To give you lot an example, let's suppose that I wanted to create a table containing the proper name, description and vendor (company) associated with each process that I read in from the .CSV file. The control for doing so would look something like this:

$Processes | Select-Object Name, Description, Visitor | Format-Table

Yous tin see the command's output in Effigy 2.

[Click on image for larger view.] Effigy 2: This is an example of how you can filter and format data that was imported from a .CSV file.

The figure higher up illustrates that information technology is possible to import a large .CSV file, then filter out the information that you don't need. Conversely, all the same, information technology is likewise possible to simply import merely the portions of the .CSV file that you are interested in.

I'yard personally non a huge fan of this method considering it requires a bit more coding and because yous accept to create a separate assortment for each column that you want to import. Even so, this method does have its identify, specially if yous are merely interested in one cavalcade of the .CSV file'due south information.

The trick to using this method is that you lot have to employ the Import-CSV cmdlet in conjunction with a ForEach-Object loop. The loop parses the .CSV file one line at a time and gives yous the opportunity to add data to an assortment.

Let's suppose that I desire to create an array of names that have been imported from the .CSV file. Here is what the lawmaking looks like:

[email protected]() Import-CSV C:\Temp\Process.csv | ForEach-Object { $Names += $_.Proper noun }

As y'all tin run across, the beginning line of code creates an assortment called $Names. This line is really important. Without it, the script volition care for all of the names that are imported from the file as string data. The end issue will be that all of the names are mashed together in a unmarried variable. Figure 3 shows what information technology looks like if you forget to declare an array.

[Click on image for larger view.] Figure 3; This is what happens if you forget to prepare an array.

As you can see, defining the assortment upfront is really of import. At present, let'south look at the rest of the script.

The second line of code reads the .CSV file and and so sets up a ForEach-Object loop that parses the .CSV file i row at a time. The line of lawmaking that falls between the ii braces is executed each fourth dimension that the script examines a new row within the .CSV file. This row populates the $Names array with the contents of the Names column ($_.Names) from the .CSV file.

Notice that the operator used in this line of lawmaking is +=. If I had just used an equals sign, then the contents of the $Name variable would exist overwritten each time that the script parses a new line in the file. The += operator tells PowerShell to add together the data from the current line to whatsoever is already in the array.  You can run into an example of how this works in Figure 3.

[Click on image for larger view.] Effigy 4: The names are now displayed correctly.

So which technique should you be using? Either one is a viable option. Personally, I adopt to merely import the entire .CSV file and and then deal with the data. But if you are just interested in a single cavalcade of data, then the second technique that I showed you will make your life a lot easier.

About the Author

Brien Posey is a 20-fourth dimension Microsoft MVP with decades of IT experience. As a freelance writer, Posey has written thousands of articles and contributed to several dozen books on a wide variety of IT topics. Prior to going freelance, Posey was a CIO for a national chain of hospitals and health care facilities. He has also served every bit a network administrator for some of the country'southward largest insurance companies and for the Section of Defense at Fort Knox. In addition to his continued piece of work in IT, Posey has spent the last several years actively grooming as a commercial scientist-astronaut candidate in preparation to fly on a mission to study polar mesospheric clouds from space. Yous tin follow his spaceflight preparation on his Web site.

How to Read a Csv File Using Power Shell

Source: https://redmondmag.com/articles/2020/04/09/how-to-use-csv-with-powershell-2.aspx

Share this post