PowerShell Fundamentals: Navigating CSV Information

On this tutorial, we’re diving into the necessities of dealing with CSV recordsdata utilizing PowerShell, overlaying a wide range of situations you would possibly encounter in the true world. This information will assist sysadmins and IT professionals handle knowledge successfully, demonstrating the facility and suppleness of PowerShell in working with various kinds of CSV recordsdata.

To set the stage, let’s create a easy CSV file to work with.

@(
	[pscustomobject]@{
		FirstName = 'Joe'
		LastName = 'Jones,Barrett'
		Handle = '453 1st St'
		Metropolis = 'Phoenix'
		State = 'AZ'
	},
	[pscustomobject]@{
		FirstName = 'Jose'
		LastName = 'Melindez'
		Handle = '5663 Most important St'
		Metropolis = 'Atlanta'
		State = 'GA'
	}
) | Export-Csv -Path ~Staff.csv

And skim it with Get-Content material.

Get-Content material -Path ~Staff.csv

Nothing too fancy, only a CSV file with a few fictional staff. A couple of issues to notice that may turn out to be useful later; discover this CSV file makes use of commas as subject delimiters and quotes. The quotes enclose every subject worth, so you possibly can even use the delimiter, a comma on this case, within the sphere worth just like the “Jones-Barrett” instance. That’s one subject.

Additionally, discover I used the Get-Content material cmdlet. This cmdlet isn’t sensible; it reads a textual content file by default and barfs out the content material as an array of strings. Let’s try the kind of object Get-Content material returns for every CSV row.

Get-Content material -Path ~Staff.csv | Get-Member

You’ll see that it returns a easy string. Really, it’s an array of strings. If I exploit the not too frequent unary operator to stop PowerShell from unrolling the array….

,(Get-Content material -Path ~Staff.csv) | Get-Member

You possibly can see that Get-Content material returns an array of objects which on this case are strings. It’s an array due to the left and proper brackets.

If I have a look at a single object, you’ll see that it’s simply the entire line of the CSV file, together with the commas and quotes.

Get-Content material -Path ~/Staff.csv | Choose-Object -First 1

We would like objects, not uncooked strings, for every CSV file line. I would like objects with fields as object properties. To get that, I can use the Import-Csv cmdlet.

Import-Csv -Path ~/Staff.csv

That appears significantly better. It has intelligently parsed the whole CSV file, transformed the header row within the CSV file to object property names, and used the sphere values as property values. Let’s test the thing kind now.

Import-Csv -Path ~/Staff.csv | Get-Member

Now you can see that the output object kind is System.Administration.Automation.PSCustomObject; not only a string. The Import-Csv cmdlet knew the CSV file’s construction and was capable of convert it to a set of PSCustomObjects.

Right here’s one other kind of CSV file you would possibly run into.

Get-Content material -Path ~Staff-withcommasnoquotes.csv

Discover the distinction? No quotes and see the second row Jones,Barrett. That comma is definitely a part of Joe’s identify. The worth Barrett shouldn’t be his deal with.

FYI: I created that CSV file with this code:

@(
	[pscustomobject]@{
		FirstName = 'Joe'
		LastName = 'Jones,Barrett'
		Handle = '453 1st St'
		Metropolis = 'Phoenix'
		State = 'AZ'
	},
	[pscustomobject]@{
		FirstName = 'Jose'
		LastName = 'Melindez'
		Handle = '5663 Most important St'
		Metropolis = 'Atlanta'
		State = 'GA'
	}
) | Export-Csv -Path ~Staff.csv

(Get-Content material -Path ~Staff.csv).substitute('"','') | Set-Content material -Path ~Staff-withcommasnoquotes.csv

Let’s see how Import-Csv handles this.

Import-Csv -Path ~Staff-withcommasnoquotes.csv

That’s not proper. All the subject values to the suitable of the LastName subject are improper. Import-Csv is utilizing that comma in his identify as a subject delimiter. And that is the place the quotes are available. Quotes outline everything of a subject, the delimiter tells Import-Csv the place a subject ends and begins.

Let’s shift gears and now cowl one state of affairs chances are you’ll end up in. Recall that I discussed earlier {that a} CSV ought to technically have fields delimited by commas. Effectively, generally you’ll discover “CSV” recordsdata with completely different delimiters like tabs. By default, the Import-Csv cmdlet doesn’t like that.

For instance, I’ve a CSV file right here…

Import-Csv ~/Employeeswithtabs.csv

Issues don’t look proper. Import-Csv hasn’t reworked every line into an object. That’s as a result of it was in search of a comma as a subject delimiter. If I have a look at this file…

Get-Content material ~/Employeeswithtabs.csv

You’ll see the fields are delimited by tabs and never commas. Fortunately, there’s a fast repair. Merely use the Delimiter parameter.

Import-Csv ~/Employeeswithtabs.csv -Delimiter "`t"

The output seems to be significantly better now. Import-Csv is now in search of tabs as subject delimiters as an alternative of commas and is parsing the CSV file appropriately.

To spherical out this demo, let’s now cowl yet one more state of affairs; CSVs with no headers. Perhaps you’ve been despatched a CSV file from some historic HR database with no headers that appears one thing like this.

Get-Content material -Path ~Staff-noheaders.csv

Once you try and learn the CSV with Import-Csv, that is what occurs.

Import-Csv -Path ~Staff-noheaders.csv

Import-Csv has no subject names to affiliate with values, so it makes use of the highest row as headers and makes them property names. This is not proper. You would modify the CSV and add headers that means, however I all the time favor to depart the recordsdata unchanged as a lot as attainable. For Import-Csv to have legitimate headers, use the Header parameter.

Import-Csv -Path ~Staff-noheaders.csv -Header 'FirstName','LastName','Handle','Metropolis','State'

Significantly better! Once you use the Header parameter and specify a comma-delimited record, Import-Csv makes use of that set of values from left to proper as subject names slightly than the primary row of the CSV file. This parameter lets you parse CSV recordsdata with no headers with out modifying the unique file.

You need to now be in your technique to a significantly better expertise studying CSV recordsdata with PowerShell!