PowerShell splitting CSV into separate files

Question

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

So the sample data that you provided is not in CSV format, and is only 1 lines so there's not a good way to determine what you want grouped on. You should really update your question to clearly give sample data, and what you would expect that sample data to be output as. — TheMadTechnician
– TheMadTechnician, Commented Dec 11, 2015 at 23:13
Apologies, I was rushing! I did think up a custom object, with the properties being each individual where-object but I'm unsure of how that would perform. — user4317867
– user4317867, Commented Dec 11, 2015 at 23:45

FoxDeploy · Accepted Answer · 2015-12-11 22:25:45Z

3

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"

answered Dec 11, 2015 at 22:25

FoxDeploy

13.8k3 gold badges42 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user4317867 Over a year ago

Is there any peformance reasons for using Group-Object rather than my crude method of piping the Import-CSV into a Where-Object and filtering the results as desired?

FoxDeploy Over a year ago

Oh, there was nothing wrong with your approach, but I find using a purely cmdlet and pipeline based method to be more maintainable, and easier to understand later. I compared both approaches, and the Where-Object method (the one you were using) was 8.3 MS, while the approach I suggested took 11.4 MS. So, your method was actually faster!

user4317867 Over a year ago

I'm under 6 seconds for working 1220 lines, creating 22 text files. But the code looks horrible! Perhaps this is more a code golf type question?

Booga Roo Over a year ago

@user4317867 Probably not something really appropriate for code golf. Code length, code performance, code correctness, and code readability are not necessarily related. I'd put priority on correctness first, then readability a close second.

user4317867 Over a year ago

Just to revisit this, the Prod,Cert,Dev in the collection names are not important. I'm looking to collect ServerName, Reboot Day and Reboot Time. This will plug into additional PowerShell code to perform the server reboots along the Day/Time.

Collectives™ on Stack Overflow

PowerShell splitting CSV into separate files

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related