Splitting a CSV file into multiple files based on column value

Question

I am new to PowerShell and need to split a CSV file into multiple files based on column value.

My source file looks like this

 ID   Name   TNumber
 123  Jo     123456
 123  Joe    789012
 124  Tom    896578
 124  Tom    403796

I read this thread which helped me with the grouping but I lack the understanding of how to split it into multiple files based on the ID column. Is that possible?

Answer 1

This is pretty old question. Stumbled upon a similar scenario where I had to create multiple CSV files from a single csv file based on value of a particular column in the file.

I created it like this because for me the column name on basis of which I had to split was not fixed & i had some 5-6 scenarios.

For me, the 2 values $InputFilePath & $SplitByColumnName were getting passed via a command line argument.

$InputFilePath = "C:\Test\SourceFile.csv"
$SplitByColumnName = "ColumnName" #Enter ColumnName here on basis of which you want to split.

$data = Import-Csv $InputFilePath | Select -ExpandProperty $SplitByColumnName -Unique

$a = $data | select 

ForEach ($i in $a)
{  
  $FinalFileNamePath = "C:\Test\" + $i + ".CSV" #This is where you would keep the splitted files.

  Import-Csv $InputFilePath | where {$_.$SplitByColumnName -eq $i } | Export-Csv $FinalFileNamePath -NoTypeInformation  
}

Answer 2

I came here looking for a quick answer to a fairly simple process but most of the answers seem a bit complicated with grouping or line manipulation.

The following works really well and I feel is much easier to understand:

$users = Import-Csv -Path "C:\example\path\users.csv" -Delimiter "`t"

foreach ($user in $users) {
    $user | Export-Csv -Path "C:\example\path\$($user.ID).csv" -Append -NoTypeInformation
}

For my particular case, we didn't want the particular column in the output file, so I used a Select. In your case this would look like:

$users = Import-Csv -Path "C:\example\path\users.csv" -Delimiter "`t"

foreach ($user in $users) {
    $user | Select Name, TNumber | Export-Csv -Path "C:\example\path\$($user.ID).csv"  -Delimiter "`t" -Append -NoTypeInformation
}

Answer 3

Group-Object by any column value, here we use the ID

$groups = Import-CSV e:\test.csv | Group-Object ID

$groups

Output

Count Name   Group
----- ----   -----
    2 123    {@{ID=123; Name=Jo; Tnumber=123456}, @{ID=123; Name=Joe;Tnumber=324233}}
    2 124    {@{ID=124; Name=Tom; Tnumber=5645645}, @{ID=124; Name=Tom; Tnumber=23423}}

Finally feed this to for loop

$groups | ForEach-Object {$_.Group | Export-Csv "$($_.Name).csv" -NoTypeInformation}

Answer 4

Sorry I misunderstood the question. Gravity, thanks for clarifying. I think some of the other answers might work but if they don't, you can try this. This is probably how I would do it. Note that I am assuming you have a tab delimited file, hence the -delimiter "t". If it is comma separated, just remove the -delimiter "t".

$users = import-csv users.csv -delimiter "`t"

# Loop through users.csv
foreach($user in $users)  
{
    # Put each field in a separate variable.
    $id = $user.ID;
    $name = $user.Name;
    $tnumber = $user.TNumber;

    # Write variables to host just to be sure the file is being read properly. This code can be commented out or removed after you are sure the file is being read.
    write-host $id;
    write-host $name;
    write-host $tnumber;
    write-host "";

    # If no file exists for a user with the ID contained in $id on this iteration, create it.
        if(!(Test-Path "$id.csv"))
        {
            out-file -filepath "$id.csv" -inputobject "ID`tName`tTNumber" -append;           
        }

    # Append record for the user with the ID contained in $id on this iteration to $id.csv
        out-file -filepath "$id.csv" -inputobject "$id`t$name`t$tnumber" -append;           
}

pause

Answer 5

If your file have tab delimiter you can do it :

$CurDir="C:\temp"

Import-Csv "$CurDir\test.csv" -Delimiter "`t" | Group ID | %{
    $ID="{0}.csv" -f $_.Name
    $_.Group | export-csv "$CurDir\$ID" -NoType
}

Answer 6

Maybe I'm complicating it but just in case I do the assumption that the Name column contains a middle name. Something like this:

ID     Name     TNumber
123    Jo       123456
123    Joe      789012
124    Tom      896578
124    Tom      403796
125    Jan W.   500300
125    Janny    700200

My solution to the problem looks like:

$csv = Get-Content .\input.txt # source CSV file
$cap = $csv[0] -split '\s+'    # caption of CSV
# replace spaces separating columns, group objects by ID
($csv[1..$csv.Length] -replace '(\d+)\s+(.*)\s+(\d+)', '$1,$2,$3' |
ConvertFrom-Csv -Delimiter ',' -Header $cap | Group-Object ID).ForEach{
  $_.Group | Export-Csv "$($_.Name).csv" # write result
}

Answer 7

You could find the unique list of IDs and then loop over them using Where-Object to filter out each ID into a separate file.

$csv = Import-CSV $Path
$IDs = $csv.ID | Select-Object -Unique
foreach ($ID in $IDs) {
    $csv | Where-Object {$_.ID -eq $ID} | Export-CSV "C:\example\path\$ID.csv"
}

Using Where-Object isn't particularly efficient because each time you are searching over the entire csv. You could use the where() method (requires PS4+) that has a built in split to replace the CSV object with a new object that has the previously filtered for values removed. This way each iteration is has less value to filter.

$csv = Import-CSV $Path
$IDs = $csv.ID | Select-Object -Unique
foreach ($ID in $IDs) {
    $newfile,$csv = $csv.where({$_.ID -eq $ID},'Split')
    $newfile | Export-CSV "C:\example\path\$ID.csv"
}

This assumes that source file that you showed was the formatted import of a comma delimited csv file. Otherwise use the -delimiter parameter of Import-CSV to set the correct limiter.

Answer 8

This should do the trick:

$fileContent = @(Get-Content -Path 'testfile.csv')

foreach( $line in $fileContent ) {
    $lineToken = ($line -replace '\s+', ' ').Trim() -split ' '
    if( $lineToken[0] -match '^[0-9]+$' ) {
        $line | Out-File -FilePath ($lineToken[0] + '.csv') -Append
    }
}

Answer 9

You wrote csv so I'm assuming the plain text version of your file looks like this:

ID,Name,TNumber 123,Jo,123456 123,Joe,789012 124,Tom,896578 124,Tom,403796

This is how I would do it:

#

$users = import-csv users.csv

foreach($user in $users)  
{

    $id = $user.ID;
    $name = $user.Name;
    $tnumber = $user.TNumber;

    out-file -filepath id.csv -inputobject $id -append;
    out-file -filepath name.csv -inputobject $name -append;
    out-file -filepath tnumber.csv -inputobject $tnumber -append;

}

#

Not that if you do not have a csv and it is a tab delimited file, you can add the following attribute to the first line:

-delimiter "`t"

Hope this helps.