CSV FileCSV File

Working with CSV Files

Hey everyone, it’s been a little while since my last post, but work’s been busy.  A recurring issue I see on TechNet is that plenty of people have trouble working with importing/exporting CSV files.  Specifically, I see questions about how to modify CSV files. Personally, I don’t see any value in modifying the CSV file directly, but rather importing the data from said file and working with the dataset natively in PowerShell.  Using one person’s thread as an example, he had a CSV file full of IP addresses that corresponded to his VM’s.  He wanted to add data about the VM based on the IP address, but wanted to know how to “modify the line” in the CSV file.    He had already written part of the script to create the CSV by pinging each computer in his IP range and outputting that to CSV.  For our test, here’s what our CSV looks like:

Now, let’s say we want to find the hostnames for each of these computers and add that to a new column. There are a couple ways we could do this, but in an enterprise environment, a good way to do it is to just ask AD who it is.   First, we create the empty “column” for our Hostname value.

Once that’s created, we need to fill it with values:

Keep in mind that you will see a lot of red text for any computer that doesn’t have an entry in AD. This could be a printer, a switch, etc.  If you’re using Windows 8.1+ or Server 2012R2+, you can use the resolve-dnsname cmdlet.
You can add more columns to the CSV by using the Add-Member cmdlet to your heart’s content, and when you’re done, you pipe your $dataset variable back to the export-csv cmdlet.
Really, once you start looking at CSV files as arrays of custom objects, they’re pretty easy to work with.

Peter Vanhaverbeke

I started my journey into automation out of laziness. It started with our desktop support lead telling us we needed to manually install Office 2013 on 250 computers and me downloading a copy of PsTools. I moved on from batch to PowerShell several years ago and haven't looked back. Don't even come at me with some VBscript you found online because VBScript was given to mankind by the devil.
Most days, you can find me writing scripts for people in exchange for bottles of reasonably priced Gin or drinking a growler of something local while playing Kerbal Space Program.

Add comment

twelve − 9 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

%d bloggers like this: