This article uses examples to discuss different methods of writing data into a table in PowerShell. In particular, we will discuss how to write user-defined data to a PowerShell table, read data from a file and write it to a table, format PowerShell output as a table, and write output into a tabular form data file like a CSV.
The code in this article will be saved on a PowerShell script and executed as such. To execute a PowerShell script, you need to save code in a .ps1 file, e.g., file1.ps1, and execute the code from PowerShell with the command
|
1 |
.\path\to\file1.ps1 |
Example 1: Writing data into PowerShell Table
The first way to write data into a table in PowerShell is to use the New-Object cmdlet to create a System.Data.DataTable object and write data into it.
File: write_to_table1.ps1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
# Create a new DataTable object $table = New-Object System.Data.DataTable # Define the columns of the table $columns = @( "Name", "Address", "Role" ) # Add the columns to the table - use Out-Null # to discard output from writing columns ForEach ($column in $columns) { $table.Columns.Add($column) | Out-Null } # Define the data as an array of hash tables $data = @( @{ Name = "Walshl"; Address = "Mumbai, India"; Role = "CFO" }, @{ Name = "Osteen Thee"; Address = "Berkley, Carlifonia"; Role = "Managing Director" }, @{ Name = "Victoria Smith"; Address = "Texas, USA"; Role = "CEO" } ) # Add the data to the table and do not display the output from writing rows # using Out-Null ForEach ($row in $data) { $table.Rows.Add($row.Name, $row.Address, $row.Role) | Out-Null } # Display the table and use -Autosize to adjust the width of columns based on data $table | Format-Table -Autosize |
Example 2: Reading Data from a Text File and Writing them to Table
In this example, we are reading the data from data1.txt with the contents shown below. The first row is the header row, and the data items are separated by a semi-colon.
Name;Address;Role Walsh;Mumbai, India;CFO Osteen Thee;Berkley, Carlifonia;Managing Director Victoria Smith; Texas, USA;CEO
The following PowerShell code reads the contents of the data1.txt file and outputs the data as a table.
File: write_to_table2.ps1
|
1 2 3 4 5 6 7 8 |
Get-Content -Path "../data1.txt" | Select-Object -Skip 1| ForEach-Object { $name, $address, $role = $_ -split ";" [PSCustomObject]@{ Name = $name Address = $address Role = $role } } | Format-Table -AutoSize |
In the code above, we use the Get-Content cmdlet to read the contents of data1.txt. Select-Object -Skip 1 is used to skip the first row of the data to prevent the header row from being written twice.
ForEach-Object loops through each line of the file and splits it using the semi-colon (;) to get the data points on each row.
[PSCustomObject] allows us to create custom objects with three properties Name, Address, and Role, and assign corresponding values of the split to each property.
After creating the custom objects, we pipe them to Format-Table to create a table. The -AutoSize parameter is used to resize the columns to fit the data dynamically.
We could also use System.Data.DataTable object in this example to write the contents of data1.txt into a PowerShell Table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# Path to the file containing the data $file = "../data1.txt" # Create an instance of the DataTable object $table = New-Object System.Data.DataTable # Create our columns. $table.Columns.AddRange(@("Name", "Address", "Role")) # Read the file line by line and add the data to the DataTable Get-Content $file | Select-Object -Skip 1 | ForEach-Object { $values = $_.Split(";") $row = $table.NewRow() $row["Name"] = $values[0] $row["Address"] = $values[1] $row["Role"] = $values[2] $table.Rows.Add($row) } # Display the table $table |
Since we know the delimiter for our data, we could also use Import-Csv to read data and then format the table using the Format-Table cmdlet.
|
1 2 |
$data = Import-Csv -Path "../data1.txt" -Delimiter ";" -Header "col1", "col2", "col3" $data | Format-Table -AutoSize |
With Import-Csv, you can define custom headers using the -Header parameter. If this parameter is not passed, the first row is used as the column headers.
Example 3: Sending the Output in Example 2 into CSV
If you want the table data in a CSV file, you can use the Export-Csv cmdlet, as shown in this example.
|
1 2 3 4 5 6 7 8 |
Get-Content -Path "..data1.txt" | ForEach-Object { $name, $address, $role = $_ -split ";" [PSCustomObject]@{ Name = $name Address = $address Role = $role } } | Export-Csv -Path "file1.csv" -NoTypeInformation |
In this case, the result is piped into a CSV file specified on the -Path parameter. The -NoTypeInformation parameter ensures that the resulting CSV does not include object type information on the first row.
Example 4: Formatting Command Output as a Table in PowerShell
The Format-Table cmdlet can be used to format PowerShell output as a table, as shown in the example below.
The example shows how Format-Table takes the Get-Host output (a list) and formats it into a table.
You can also select specific properties to show on the output using Select-Object. For example
|
1 |
Get-Host | Select-Object -Property Name, Version, CurrentCulture, DebuggerEnabled | Format-Table |
Instead of showing all the ten properties of the Get-Host object, the command above only picks 4.
You can also use options on Format-Table to control how your table will look like. For example, -AutoSize and -Wrap. The former dynamically adjusts the column size based on the length of the data, and the latter activates text wrapping for the table data.
|
1 |
Get-Host | Format-Table -AutoSize -Wrap |