22926

how to get unique values in excel using powershell

<h3>Question</h3>

i have following code, i this i will read a excel sheet and i will get values but i want a unique values for one column that is MOD_user using that i can send out mail

#select excel file you want to read $file = "C:\Gopi_Kishan\DBBR\Build\Build_Scripts\test.xlsx" $sheetName = "Sheet1" #create new excel COM object $excel = New-Object -com Excel.Application #open excel file $wb = $excel.workbooks.open($file) #select excel sheet to read data $sheet = $wb.Worksheets.Item($sheetname) #select total rows $rowMax = ($sheet.UsedRange.Rows).Count #create new object with Sno, Market, Object Name, Object_Type, Issue_id, Mod_user, Reviewed properties. $myData = New-Object -TypeName psobjectl $myData | Add-Member -MemberType NoteProperty -Name Issue_id -Value $null $myData | Add-Member -MemberType NoteProperty -Name Mod_user -Value $null $myData | Add-Member -MemberType NoteProperty -Name Reviewed -Value $null $myData | Add-Member -MemberType NoteProperty -Name Request_number -Value $null $myarray= "gkishan" #create empty arraylist #$myArray = @() for ($i = 2; $i -le $rowMax; $i++) { $objTemp = $myData | Select-Object * #read data from each cell $objTemp.Sno = $sheet.Cells.Item($i,1).Text $objTemp.Issue_id = $sheet.Cells.Item($i,5).Text $objTemp.Mod_user = $sheet.Cells.Item($i,6).Text $objTemp.Reviewed = $sheet.Cells.Item($i,7).Text $objTemp.Request_number = $sheet.Cells.Item($i,8).Text #Echo $objTemp.Market #echo $objTemp.name #echo $objTemp.Object_Type $MARKET_NAME = $objTemp.Market $OBJECT_NAME = $objTemp.name $OBJECT_TYPE = $objTemp.Object_Type $Issue_id = $objTemp.Issue_id $Mod_user = $objTemp.Mod_user $Reviewed = $objTemp.Reviewed $ReqNumber = $objTemp.Request_number #Write-Host $MARKET_NAME ' - ' $OBJECT_NAME ' - ' $OBJECT_TYPE ' - ' $Issue_id ' - ' $Mod_user ' - ' $Reviewed #Add-Content $path\$FileLogdate.txt $MARKET_NAME' - '$OBJECT_NAME' - '$OBJECT_TYPE' - '$Issue_id' - '$Mod_user' - '$Reviewed }

i want to get the unique values from Mod_user

can any one help


<h3>Answer1:</h3>

You can try:

$objTemp.Mod_user = $sheet.Cells.Item($i,6).Text $unique_mod_users = $objTemp.Mod_user | Select-Object -Unique

You can then use $unique_mod_users in this way:

$Mod_user = $unique_mod_users

Or simply use it directly.

来源:https://stackoverflow.com/questions/61892326/how-to-get-unique-values-in-excel-using-powershell

Recommend

  • MS Access SQL Not Working as desired
  • How to add end screen with YouTube Data API?
  • Friendly_Id and Reserved Words — How can i replace the reserved word?
  • A “regex for words” (semantic replacement) - any example syntax and libraries?
  • Retrieving size of datatype from std::type_info
  • Visual Studio 2012 C++ compile error with Boost Signal2
  • Assigning variable and using it in range
  • create user accounts in office 365 using c#.net code dynamically
  • Inno setup makes sqlite3 database read-only
  • My Xcode4 cannot find CCRibbon
  • PHP: Text Processing preg_match function
  • Importing Excel Charts from Excel to PowerPoint causes `RPC_E_SERVERFAULT` on some machines
  • How to get strings in parentheses into array with java
  • Rails Active storage Unable to autoload constant ActiveStorage::Blob::Analyzable
  • Python: Graph using NetworkX and mplleaflet
  • Html Multiple Input Elements With Same Name
  • Homomorphic filtering in OpenCV
  • Multiple canvases (pages) in Fabric.js
  • Allowing audio files in Spring MVC 3.0?
  • how to add dashed border on highcharts “area” graph for every point
  • playing mp3 from nsbundle
  • How to define a custom accuracy in Keras to ignore samples with a particular gold label?
  • How to use Typescript with libraries like Ampersand.js that parse configs to build prototypes
  • Set SelectedIndex of ListView in FlipView_SelectionChanged event
  • Reload Page with Javascript after Database changes
  • ASP.NET MVC 2 actions for the same route?
  • Multiplying polynomials/simplifying like terms
  • How to define something in JavaScript [closed]
  • I am consuming a WCF service that requires headers from a .NET 2 website. How can I programmatically
  • Amazon Elastick BeanStalk error: Failed to create the AWS Elastic Beanstalk application version
  • Can a PHP script be scheduled to run at a specific time or after a specific amount of time has expir
  • How to decleare char *const argv[] in swift [duplicate]
  • php “page caching” solution suggestions for CMS Applications
  • Background transfer download task failed when app was closed
  • ssh remote server login script
  • Grails - How to implement a foreign key relationship not using an id column?
  • Jersey serializes character value to ASCII equivalent numeric string
  • CAS 4 - Not able to retrieve the LDAP groups after successful authentication
  • What does the “id” field in an Android “Google Play Music” broadcast intent correspond to?
  • JavaScript RegExp Replace