Create Multiple arrays from a CSV file

Question

Currently I have a working solution:

#Declare Variables
$PMArray = @()
$SMArray = @()

#Get Input File and put information in 2 Arrays, 1 for Personal Mailboxes
#and 1 for Shared Mailboxes

Import-Csv "C:\inputfile.csv" -Delimiter ';' | foreach {
    $InputMBXType =  $_."MailboxType"
    $InputL =  $_."Level"
    $InputSMTP =  $_."PrimarySMTPAddress"

    if ($InputMBXType -eq "Personal Mailbox") {
        $PMObject = New-Object psobject
        $PMObject | Add-Member NoteProperty 'MailboxType' $InputMBXType
        $PMObject | Add-Member NoteProperty 'Level' $InputL
        $PMObject | Add-Member NoteProperty 'EmailAddress' $InputSMTP

        $PMArray += $PMObject
    }

    if ($InputMBXType -eq "Shared Mailbox") {
        $SMObject = New-Object psobject
        $SMObject | Add-Member NoteProperty 'MailboxType' $InputMBXType
        $SMObject | Add-Member NoteProperty 'Level' $InputL
        $SMObject | Add-Member NoteProperty 'EmailAddress' $InputSMTP

        $SMArray += $SMObject
    }
}

#Split Arrays by department

#Personal Mailboxes
$ABCpmarray = $PMArray | Where-Object {$_.Level -eq "ABC"}
$DEFpmarray = $PMArray | Where-Object {$_.Level -eq "DEF"}
$GHIpmarray = $PMArray | Where-Object {$_.Level -eq "GHI"}

#Shared Mailboxes
$ABCsmarray = $SMArray | Where-Object {$_.Level -eq "ABC"}
$DEFsmarray = $SMArray | Where-Object {$_.Level -eq "DEF"}
$GHIsmarray = $SMArray | Where-Object {$_.Level -eq "GHI"}

#Split Array in batches of defined number per batch
[int]$splitat = 50

#ABCpmarray
$runcount = [math]::Ceiling($ABCpmarray.count/$splitat)
for ($LN=0; $LN -lt $runcount; $LN++) {
    [int]$begin = $($LN * $splitat)
    [int]$end = $(($LN +1) * $splitat) -1
    $count = "{0:D2}" -f $LN
    $sel = $ABCpmarray[$begin..$end]
    $sel | select EmailAddress | Export-Csv -Path "C:\ABC-Personal-$count.csv" -NoTypeInformation
}

#ABCsmarray
$runcount = [math]::Ceiling($ABCsmarray.count/$splitat)
for ($LN=0; $LN -lt $runcount; $LN++) {
    [int]$begin = $($LN * $splitat)
    [int]$end = $(($LN +1) * $splitat) -1
    $count = "{0:D2}" -f $LN
    $sel = $ABCsmarray[$begin..$end]
    $sel | select EmailAddress | Export-Csv -Path "C:\ABC-Shared-$count.csv" -NoTypeInformation
}

#DEFpmarray
$runcount = [math]::Ceiling($DEFpmarray.count/$splitat)
for ($LN=0; $LN -lt $runcount; $LN++) {
    [int]$begin = $($LN * $splitat)
    [int]$end = $(($LN +1) * $splitat) -1
    $count = "{0:D2}" -f $LN
    $sel = $DEFpmarray[$begin..$end]
    $sel | select EmailAddress | Export-Csv -Path "C:\DEF-Personal-$count.csv" -NoTypeInformation
}

#DEFsmarray
$runcount = [math]::Ceiling($DEFsmarray.count/$splitat)
for ($LN=0; $LN -lt $runcount; $LN++) {
    [int]$begin = $($LN * $splitat)
    [int]$end = $(($LN +1) * $splitat) -1
    $count = "{0:D2}" -f $LN
    $sel = $DEFsmarray[$begin..$end]
    $sel | select EmailAddress | Export-Csv -Path "C:\DEF-Shared-$count.csv" -NoTypeInformation
}

Unfortunately all the departments are hard-coded in the script.

I would like to use a CSV file with all the departments. Based on the CSV, new arrays must be created and filled with content.

Possible duplicate of How to parse string from column in csv in Powershell — mkierc
– mkierc, Commented Dec 29, 2015 at 14:39
@mkierc I have read the post that you have suggested, but my question is different from the post that you sugest. — Thomas Muller
– Thomas Muller, Commented Dec 29, 2015 at 14:52
@ThomasMuller While that might be true it is worth it to explain why. Not just simply stating it is different. — Matt
– Matt, Commented Dec 29, 2015 at 14:55
There is a lot of code here that is repeated. For the purpose of an MCVE can you reduce this to a simple set and show how you manually created them? Is $_.Level -eq "ABC" what you meant by hardcoded department? What is $PMArray and $SMArray — Matt
– Matt, Commented Dec 29, 2015 at 14:58
A good first step would probably be to refactor the array filtering and the for loops into a function. — Ansgar Wiechers
– Ansgar Wiechers, Commented Dec 29, 2015 at 22:55

Community · Accepted Answer · 2017-05-23 10:28:21Z

You really don't need separate arrays. From the looks of it you are just using these arrays to help parse the output into different files. The information is all there and you can parse directly, so to speak, from the Import-CSV. As shown by JohnLBeven we can use Group-Object to replace most of the logic you used in creating the arrays.

I mocked some data for testing using www.mockaroo.com

MailboxType      PrimarySMTPAddress         Level
-----------      ------------------         -----
Personal Mailbox [email protected]   ABC  
Shared Mailbox   [email protected]              ABC  
Shared Mailbox   [email protected]  ABC  
Personal Mailbox [email protected] DEF  
Personal Mailbox [email protected]       DEF  
Personal Mailbox [email protected]      DEF  
Shared Mailbox   [email protected]             DEF  
Shared Mailbox   [email protected]           DEF  
Shared Mailbox   [email protected]          DEF  
Shared Mailbox   [email protected]        DEF  
Shared Mailbox   [email protected]             DEF  
Personal Mailbox [email protected]      GHI  
Personal Mailbox [email protected]   GHI  
Personal Mailbox [email protected]        GHI  
Personal Mailbox [email protected]         GHI  
Personal Mailbox [email protected]           GHI  
Personal Mailbox [email protected]             GHI  
Personal Mailbox [email protected]     GHI  
Personal Mailbox [email protected]     GHI  
Shared Mailbox   [email protected]         GHI

$rawdata = Import-CSV 'C:\MOCK_DATA.csv' -Delimiter ';'
$exportFolder = "C:\temp\test"
# Max number of entries per output file.
[int]$carve = 3

# Group 
$rawdata | Group-Object MailboxType | ForEach-Object{
    # Data is now grouped by MailboxType.
    $currentType = $_.Name -replace "\s+Mailbox$"s

    # Group this data by Level now
    $_.Group |  Group-Object Level | ForEach-Object{

        # Carve up the elements into chucks. Each group then written to its own file.
        for([int]$index = 0;$index -lt $_.Group.Count;$index+=$carve){ 

            # Collect the current chuck and send it to its own file. 
            $_.Group[$index..($index + $carve - 1)] | 
                Select @{Name="EmailAddress";Expression={$_.PrimarySMTPAddress}} |
                Export-CSV -NoTypeInformation -Path ("$exportfolder\{0}-{1}-{2:D2}.csv" -f $_.Name, $currentType, ($index / $Carve + 1))
        }
    }
}

First group by the mailbox type. Then group by level. Carve up those arrays based on the $carve integer. Export the custom file name based on all of those attributes.

Using my above test data the following csv files were created

ABC-Personal-01.csv                                                                                                                        
ABC-Shared-01.csv                                                                                                                          
DEF-Personal-01.csv                                                                                                                        
DEF-Shared-01.csv                                                                                                                          
DEF-Shared-02.csv                                                                                                                          
GHI-Personal-01.csv                                                                                                                        
GHI-Personal-02.csv                                                                                                                        
GHI-Personal-03.csv                                                                                                                        
GHI-Shared-01.csv

Thank you Matt. Your script did the work. It looks much cleaner this way!

Matt · Accepted Answer · 2015-12-30 12:57:38Z

The Group-Object functionality does pretty much what you want; all data will be put into one object, but you can then use the group name to pull back the related group's array:
```
$AllArrays = $PMArray | Group-Object -Property Level

$AllArrays | %{
    "Group Name: $($_.Name)" 
    $_ | select -ExpandProperty Group | ft -HideTableHeaders 
}
```

You can save yourself some effort on the foreach loop by simply pulling the values from the CSV and filtering as below:

[psobject[]]$PMArray = Import-Csv "C:\inputfile.csv" -Delimiter ';' `
| ?{$_.MailboxType -eq "Personal Mailbox"} `
| select MailboxType, Level, @{Name = 'EmailAddress'; Expression = {$_.PrimarySMTPAddress}}

[psobject[]]$SMArray = Import-Csv "C:\inputfile.csv" -Delimiter ';' `
| ?{$_.MailboxType -eq "Shared Mailbox"} `
| select MailboxType, Level, @{Name = 'EmailAddress'; Expression = {$_.PrimarySMTPAddress}}

Collectives™ on Stack Overflow

Create Multiple arrays from a CSV file

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related