CODEWITHSUNDEEP
csvpowershell
user4317867
user4317867

Reputation: 2448

PowerShell splitting CSV into separate files

I have a CSV file with two columns.

Sample Data:

Computername,Collection Name
MyServer01,NA - Data - SRV - Patching - Prod - Sat 18:00
MyServer02,NA - Data - SRV - Patching - Prod - Sat 22:00
MyServer03,NA - Data - SRV - Patching - Prod - Sat 02:00
MyServer04,NA - Data - SRV - Patching - Prod - Fri 18:00
MyServer05,NA - Data - SRV - Patching - Prod - Fri 02:00
MyServer06,NA - Data - SRV - Patching - Prod - Sun 18:00

I am looking to split the CSV into separate files along the collection. As the above example, Sat 18:00

So far I have the code:

$data=Import-Csv .\servers2.csv

$data | Where-Object {$_ -like '*fri*' -and $_.'Collection Name' -notmatch 'all.da.servers'} | Select ComputerName,'Collection Name'

The end goal is multiple text files based on the collection. So Servers-Fri-1800.txt and Fri-1800.txt. The server names will live in Servers*.txt and the Fri-1800.txt would contain the date in the format 12-11-15 18:00

I wanted to ask first because something tells me this method might be horribly inefficent. -Thanks!!

-Edit, the desired output. **Worth noting these text files will be read by another Powershell script, hence the reason for thinking a PSCustom object with the filtered properties would be ideal.

Servers-Sat-1800.txt contains MyServer01 and Sat-1800.txt contains 12-11-15 18:00 Servers-Sat-2200.txt contains MyServer02 and Sat-2200.txt contains 12-11-15 22:00

-edit 2, add code preview. Is there an easier way to accomplish this?

$data = import-csv .\file.csv

$data | Where-Object {$_ -like '*sat?18:00*' -and $_.'Collection Name' -notmatch 'ignore.string'} |

% {"{0}" -f $_.Computername} | out-file Servers-Sat-1800.txt

Upvotes: 2

Views: 647

Answers (1)

FoxDeploy
FoxDeploy

Reputation: 13567

Try the Group-Object command. It is made to take a big mess of objects and group them together by a common property.

I took your sample and made a few more iterations

PCName,Collection
MyServer01, NA - Data - SRV - Patching - Prod - Sat 18:00
MyServer02, NA - Data - SRV - Patching - Dev - Sat 18:00
MyServer03, NA - Data - SRV - Patching - Dev - Sat 18:00
MyServer04, NA - Data - SRV - Patching - Prod - Sat 18:00
MyServer05, NA - Data - SRV - Patching - Prod - Sat 20:00
MyServer06, NA - Data - SRV - Patching - Prod - Sat 20:00

Saved it as a CSV called T:\comps.csv. Now, to organize into different CSVs for each collection.

$groups = import-csv T:\comps.csv | Group-Object -Property Collection 

ForEach ($group in $groups){
 $name = $group.Name -replace ':',''
 $group | select PCName | Export-Csv -Path "t:\$Name.csv"
}

Had to remove the ':' from the collection names, but the end result is a bunch of CSVs, named the right name, with the right members inside.

>dir t:\ *.csv

    Directory: T:\

Mode                LastWriteTime         Length Name                                                                                                                             
----                -------------         ------ ----                                                                                                                             
             12/11/2015   5:16 PM            375 comps.csv                                                                                                                        
-a----       12/11/2015   5:26 PM             98 NA - Data - SRV - Patching - Dev - Sat 1800.csv                                                                                  
-a----       12/11/2015   5:26 PM             99 NA - Data - SRV - Patching - Prod - Sat 1800.csv                                                                                 
-a----       12/11/2015   5:26 PM             98 NA - Data - SRV - Patching - Prod - Sat 2000.csv  

>gc 'T:\NA - Data - SRV - Patching - Dev - Sat 1800.csv'
#TYPE Selected.System.Management.Automation.PSCustomObject
"PCName"
"MyServer02"
"MyServer03"

Upvotes: 3

Related Questions