Excel Module Examples

# Display all data in list view
Import-Csv .\csv_file.csv

# ---------------------------------------------------------------------------
# $data = Import-Csv .\csv_file.csv
# $data.GetType()

# OUTPUT
# IsPublic IsSerial Name                                     BaseType
# -------- -------- ----                                     --------
# True     True     Object[]                                 System.Array
# ---------------------------------------------------------------------------
# $data[0]

# OUTPUT
# S_No       : 1
# Hostname   : ROBOSCR001
# IP_Address : 192.168.29.218
# Location   : Noida
# Roles      : DC
# Up_Status  : UP
# ---------------------------------------------------------------------------
# $data[0] | ft

# OUTPUT
# S_No Hostname   IP_Address     Location Roles Up_Status
# ---- --------   ----------     -------- ----- ---------
# 1    ROBOSCR001 192.168.29.218 Noida    DC    UP
# ---------------------------------------------------------------------------
# $data[0].Hostname

# ROBOSCR001
# ---------------------------------------------------------------------------

# Display data in table
Import-Csv .\csv_file.csv | Format-Table

# It will display only IP address and remove the header
(Import-Csv .\csv_file.csv)."IP_Address"
# or
$lists = Import-Csv .\csv_file.csv
$lists."IP_Address"

# It will display only IP address with header
Import-Csv .\csv_file.csv | Select-Object "IP_Address"
# or
(Import-Csv .\csv_file.csv | Select-Object "IP_Address").IP_Address

# It will display only Noida location data
Import-Csv .\csv_file.csv | Where-Object { $_."Location" -eq "Noida" } | Format-Table

# It will display excluding Noida location data
Import-Csv .\csv_file.csv | Where-Object { $_."Location" -ne "Noida" } | Format-Table

# Read IP address line by line
Import-Csv .\csv_file.csv | ForEach-Object { Write-Host $_."IP_Address"; Start-Sleep -Seconds 1 }

# Display data is hashtable format
Import-Csv .\csv_file.csv | ForEach-Object { Write-Host $_ ; Start-Sleep -Seconds 1 }

# Script1.ps1
$names = Import-Csv .\csv_file.csv
foreach ($name in $names) {
 #$name
 $name
#  $name_1 = $name.Hostname
#  $name_2 = $name.'IP_Address'
#  Write-Host "$name_1 $name_2"
}


# ADD NEW COLUMN
# Script1.ps1
$names = Import-Csv .\csv_file.csv
foreach ($name in $names) {
 $name
 Add-Member -InputObject $name -MemberType NoteProperty -Name "FullName" -Value $fullname
}
$names = Export-Csv -Path ".\output.csv" -NoTypeInformation


# Script1.ps1
Import-Csv .\csv_file.csv | ForEach-Object {
  Write-Host $_."IP_Address"
  Test-Connection -TargetName $_."IP_Address" -Count 1
  Start-Sleep -Second 1
}

# Script2.ps1

Import-Csv .\csv_file.csv | ForEach-Object {
  Write-Host $_."IP_Address"
  if (Test-Connection -TargetName $_."IP_Address" -Count 2 -Quiet)
  { Write-Host "OK"; $a = "OK" } else
  { Write-Host "Down"; $a = "Down" }
  Start-Sleep -Second 1
  [pscustomobject]@{ S_No = $_.'S_No'; Hostname = $_.'Hostname'; IP_Address = $_.'IP_Address' ; Location = $_.'Location'; Roles = $_.'Roles'; Up_Status = $a } | Export-Csv .\csv_file2.csv -Append -NoTypeInformation
}


# Script3.ps1

Import-Csv .\csv_file.csv | ForEach-Object {
  Write-Host $_."IP_Address"
  if (Test-Connection -TargetName $_."IP_Address" -Count 2 -Quiet)
  { Write-Host "OK"; $a = "OK" } else
  { Write-Host "Down"; $a = "Down" }
  Start-Sleep -Second 1
  [pscustomobject]@{ S_No = $_.'S_No'; Hostname = $_.'Hostname'; IP_Address = $_.'IP_Address' ; Location = $_.'Location'; Roles = $_.'Roles'; Up_Status = $a } | Export-Csv .\csv_file2.csv -Append -NoTypeInformation
}


###
Import-Csv .\csv_file.csv | ForEach-Object {
  Write-Host $_."IP_Address"
  if (Test-Connection -TargetName $_."IP_Address" -Count 2 -Quiet)
  { Write-Host "OK"; $a = "OK" } else
  { Write-Host "Down"; $a = "Down" }
  Start-Sleep -Second 1
  $new_csv = @(
    [pscustomobject]@{
      S_No       = $_.'S_No'
      Hostname   = $_.'Hostname'
      IP_Address = $_.'IP_Address'
      Location   = $_.'Location'
      Roles      = $_.'Roles'
      Up_Status  = $a
    }
  )
  $new_csv | Export-Csv -Path csv_file3.csv -Append -NoTypeInformation
}