Get the total number and export the list to csv or other format

Question

Get the total number and export the list to csv or other format

IT 140

I use the below command to find out how many users/account under specific Department .

I need your kind help to amend or new command to find the total number and how to export the list into CSV file or other format.

Get-Contact -Filter "Department -eq 'Year 8'" | Get-MailContact | Format-List Name,CustomAttribute15

Answer accepted by question author

1 additional answer

Your answer

Answer 1

This adds a 3rd column named "Total" that will be empty. At the end of the contacts the total number of contacts will be found in the "Total" column and the Name and CustomAttribute15 columns will be empty.

$c = 0
Get-Contact -Filter "Department -eq 'Year 8'"|
    ForEach-Object{
        $c++
        Get-MailContact $_.distinguishedname |
            Select-Object Name,CustomAttribute15,Total
} | Export-CSV c:\junk\somefilename.csv -NoTypeInformation
[PSCustomObject]@{
    Name = ""
    CustomAttribute15 = ""
    Total = $c
} | Export-CSV c:\junk\somefilename.csv -NoTypeInformation -Append

Answer 2

Rich Matheisen 48,026

Something like this might be what you're trying to fo:

$c = 0
Get-Contact -Filter "Department -eq 'Year 8'"|
    ForEach-Object{
        $c++
        Get-MailContact $_.distinguishedname |
            Select-Object Name,CustomAttribute15
} | Export-CSV c:\junk\somefilename.csv -NoTypeInformation
Write-Host "There are $c contacts in the department"

EDIT: Changed Get-MailContact $contact.distinguishedname to Get-MailContact $_.distinguishedname

IT 140 Reputation points

2023-12-05T08:15:11.46+00:00

Hi Rich,

thanks for your suggestion. Do I have to put your commands in a text file ?

I am getting the below error message when I run your command in PowerShell
Rich Matheisen 48,026 Reputation points

2023-12-05T15:44:51.02+00:00

No, you just have to change the path used in the Export-CSV cmdlet -- unless, that is, if you have a directory named "Junk" as a child of the C: drive (which you apparently do not). ;-)

IT 140

Hi Rich, I though the command will automatically create anew directory called junk. I apologize.

The last line did not run with the command as you can in the below. Is there a to save the whole command in a file then run it please?

> Write-Host "There are $c contacts in the department"
> 
> 
> ```

I just created a directory called **junk** then ran the command. I got the result but each record duplicated around 10 times.

please check the attached

![User's image](/api/attachments/f0cf536a-d017-4b4b-be2c-a8ea92e161f6?platform=QnA)

![User's image](/api/attachments/04b50b7f-4b18-47a5-8ca7-58e079412f97?platform=QnA)

Rich Matheisen 48,026 Reputation points

2023-12-06T02:47:49.8833333+00:00

You can't place an image inside a "code" section. I can't see the image you posted.

You can copy the code from my post and save it in a file with a ".ps1" extension. To run it, start PowerShell, change to the directory that contains the file and enter ".\The-File-Name-You-Chose.ps1". That should run the script.
IT 140 Reputation points

2023-12-07T11:47:45.1466667+00:00

sorry for late reply.

Thanks a lot. I saved your command into a .ps1 file then call it from the powershell console. I got the exact number of members under DEPARTMENT 8 , but each name in the exported excel sheet repeated 14 times.
Rich Matheisen 48,026 Reputation points

2023-12-07T15:44:41.52+00:00

I'm a little surprised it even worked! Did you have a variable name "$contact" already defined and populated in the PowerShell session?

In any case, I changed the code in my answer -- give that a try. What I changed is noted at the bottom of the answer.

Is there any reason why you used "Get-Contact" and "Get-MailContact" in your original code? You can use just the Get-MailContact and avoid the need to use the 2nd Get-MailContact. Just curious.
IT 140 Reputation points

2023-12-07T20:35:53.44+00:00

Hi Rich,

Amazing the code is working fine. I only have a small request if you add to the command please.

Can you add total to the excel file rather than printing it on powershell command please?
Rich Matheisen 48,026 Reputation points

2023-12-08T20:05:10.96+00:00

The answer is "yes".

But the total is neither a "Name" or "CustomAttribute15". Slapping a number into one of those columns wouldn't make sense.
IT 140 Reputation points

2023-12-08T21:05:32.8166667+00:00

Please pardon my lack of knowledge.

Can you guide me on how to add the total to the exported excel file since it is possible please?
Rich Matheisen 48,026 Reputation points

2023-12-08T22:32:58.95+00:00

Where, in the CSV, file would you like to see the total? Would you, for example, like to see it as a running total in a third column (that would be the easiest to do)? As a separate row in the CSV (in the 1st, or 2nd column)? Something else? Be specific when proposing changes (and especially initially when detailing what you need). If you're not specific you're going to get something that's disappointing to you and annoying to whoever you ask to do the work.
IT 140 Reputation points

2023-12-08T22:50:22.44+00:00

At the bottom of the last line or in a new columns. I am happy with any with any of these options.

I appreciate your help and understanding that sometimes something requested by the management.

Share via

Get the total number and export the list to csv or other format

1 additional answer

Your answer