I have previously tried to setup a way of working with databases with PowerShell. Recently I came across a way to use a simple CSV file to store data and then fetch it from PowerShell. This post will cover a few pointers I picked up along the way as well.
The first thing I did was to create the CSV file, which works as a rudimentary database for the purpose. I started by creating the datasheet in Excel and converting it to a CSV file. Perhaps you even have a complete file from the beginning that you got from some statistics page for example.
How you can work with a CSV file
The code was written on one computer first, where the file got a comma delimiter. Then I rewrote the code on another computer which had semicolon as delimiter, this caused trouble for my script. Because PowerShell defaults to the comma delimiter, so you need to specify that in your script when you run the Import-CSV cmdlet. Store the data in a variable and set the delimiter, that way you can return later and troubleshooting becomes easier if it has trouble reading the data. In my case it detected the file, but I got no indication what was wrong at first all that happened was that PowerShell didn't fetch the data from the CSV, most likely because it could find the individual columns.
Here is what it can look like if you have the script and the CSV in the same root folder. Look into the CSV file to see which delimiter to use.
$Imported_CSV = Import-CSV -path .\database.csv -delimiter ","
With this simple line you now have read in the data into your variable and you can start work with it. Some inspiration for this part come from this video. To count the entries, simply run $Imported_CSV.Count and see it as representing the amount of rows. With this knowledge you can also inspect individual lines by calling on their index, remember to count from 0 which is the first row of content, not the header. Using classic PowerShell index enumeration, [-1] is the last row. You can thus cook up something like this:
($Imported_CSV[-1].Name) to get the data in the name column for the last object. This requires you to know the column name in advance. To find out the column names you could run the following code:
$Imported_CSV[0].Psobject.properties.name
With the index, I could also change a specific datapoint. When I then call on the main variable again, it lists the changed datapoint. Using the Export-CSV command I could then also save the change to the file.
($Imported_CSV[1].Name) = "CMD";
$Imported_CSV # Shows the change #
You can extract all rows from one or more columns using a standard Select-Object, and with this you could apply different grouping, sorting and formatting approaches. Here is an example of getting the data:
$Imported_CSV | Select-Object -Property Age, Name
When you have designed your data segment (using the index and the properties for example), pipe your selected data into Export-CSV and set the delimiter you want to use. If you run Get-Process or Get-Service you could extract that data into a CSV as well.
The row in your Excel file (also the CSV) with the name of the columns (such as name, age, location) is called the header, if the top row is not the header row you can use the parameter -header for that. There are a few things to keep in mind when using this functionality which you can read more about here.
To add a column we can use the following code. Simply put it goes through each row and adds an extra "cell" on the rightmost side. You can give the $row variable a different name, but I liked its simplicity. The "Header" is simply the name of the new column. The property value is what value that should be given for all rows in the new column.
foreach ($row in $Imported_CSV) {Add-Member -InputObject $row -NotePropertyName "Header" -NotePropertyValue "DefaultValue"}
To add a new row to the CSV file you create a new object that is appended. For the new row you enter the values for each column.
$newrow = [pscustomobject]@{
Name = "PowerShell"
Age = "7.5.0"
Location = "PC"
}
$Imported_CSV += $newrow
Finally you can export your file as mentioned earlier through Export-CSV:
$Imported_CSV | Export-CSV -path "C:\temp\exportfile.csv" -NoTypeInformation -Delimiter "," -Encoding Default;
The script concept idea
My script lets the user search with a GUI that connects to the "database" file to work with it. The GUI lets you select different columns in which you can look for matching data. Column names could be "name", "age" and "location" for example and are connected to radio buttons. Essentially you tick a radio button corresponding to a column, you write a search word and then it gets all the data connected to that particular data point. An easy way to search for something you know, to get the rest of the data that you might not know.
The search function is tied to a search button. By using "$textbox.text" the search function matches that search term to the column that you chose. Finally it returns the data in another text box. In my example I decided that it should return all connected data.
Other functionality I added was a reset button that quickly resets the different buttons and text boxes. In the bottom I have a status bar that gives basic information and it could be used for more detailed error messages for example.
