2-21-2017 - 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.