CODEWITHSUNDEEP
powershell
Kishan M
Kishan M

Reputation: 27

how to read unique values from excel column using powershell

i would like to read unique email id from a column and assign to a local variable, can anyone assist in doing that

my data will be like

enter image description here

i would like to get unique values from the excel and assign it to a variable using power shell

the variable should hold value in following way [email protected];[email protected];[email protected]

Upvotes: 0

Views: 2159

Answers (2)

Theo
Theo

Reputation: 61068

To read the values from an Excel column and return it as array of values, you can use this helper function:

function Import-ExcelColumn {
    # returns an array of Excel Column values
    [CmdletBinding()]
    Param (
        [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)]
        [string]$Path,

        [Parameter(Mandatory = $false, Position = 1)]
        [int]$WorkSheetIndex = 1,

        [Parameter(Mandatory = $false, Position = 2)]
        [int]$ColumnIndex = 1
    )

    # constants from https://learn.microsoft.com/en-us/office/vba/api/excel.xldirection
    $xlDown = -4121
    $xlUp   = -4162

    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $workbook  = $excel.Workbooks.Open($Path)
    $worksheet = $workbook.Worksheets.Item($WorkSheetIndex)

    # get the first and last used row indices
    $firstRow = $worksheet.Cells($worksheet.UsedRange.Rows.Count, 1).End($xlUp).Row
    $lastRow  = $worksheet.Cells($firstRow, 1).End($xlDown).Row

    # collect the values in this column in variable $result
    # start at $firstRow + 1 to skip the header itself
    $result = for ($row = $firstRow + 1; $row -le $lastRow; $row++) {  
        $worksheet.Cells.Item($row, $ColumnIndex).Value2
    }

    $excel.Quit()
    # IMPORTANT: clean-up used Com objects
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) | Out-Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()

    # $result is an array. PowerShell 'unravels' arrays when returned from a function.
    # to overcome this, prefix the returned array with a unary comma.
    return ,$result
}

After that, in your case use it like this:

$emailAddresses = ((Import-ExcelColumn -Path 'D:\Test\Map1.xlsx' -ColumnIndex 2) | Select-Object -Unique) -join ';'

to get a string:

[email protected];[email protected];[email protected]

Upvotes: 1

Vish
Vish

Reputation: 466

Please show the code you have attempted as a reference for everyone answering the qustion.

With that said, the below code should work for a comma separate value (.csv) file:

# Get CSV object
$csv_object    = Import-CSV $path_to_csv

# Find unique entries from email_id column
$unique_emails = $csv_object.email_id | Select -Unique

# Join them with ;
$delim_emails  = $unqiue_emails -join ";"

Upvotes: 0

Related Questions