Split big CSV file by comma delimiter - Powershell

Diana Persico 0
Viewed 72

This should be a very easy question but I couldn't find the answer anywhere.

I have a CSV file (exported from Excel) with just one column and about 2000 rows. I just need to split this file using Powershell in a way that for each cell (or each row: since I just have one column, it's the same) a unique file gets created. So the final output should be a set of 2000 CSV files.

This is a sample of what my input CSV file looks like (but actually there is no "value n", just a long transcript):

Column1,Column2
"1939 
value 1
",
"1939 Mc
value 2
",
"1941 
value 3
",
"1941 
value 4
",
"1941 
value 5

I would need as outputs a set of single files containing the text between quotation marks, either this way:

File 1:
1939 
value 1

File 2: 
1939 Mc
value 2

File 3:
1941 
value 3

File 4: 
1941 
value 4

File 5:
1941 
value 5

or this way (I don't care whether the quotations marks are kept in the output files or not):

File 1:
"1939 
value 1
"

File 2: 
"1939 Mc
value 2
"

File 3:
"1941 
value 3
"

File 4: 
"1941 
value 4
"

File 5:
"1941 
value 5
"

I've tried the following one (not mine, I found it on the web) but it doesn't return the split files.

 $InputFilePath = "C:\elisabetta.csv"
 $SplitByColumnName = "Column1" #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:\carlo\" + $i + ".CSV" #This is where you would keep the splitted files.
 
    Import-Csv $InputFilePath | where {$_.$SplitByColumnName -eq $i } | Export-Csv $FinalFileNamePath -NoTypeInformation
 }
powershell
1 Answers

I tried your sample csv file and was able to make it work with the following code. The code that you posted was splitting the files correctly but not writing the files into the directory.

$InputFilePath = "./testfile.csv"
$SplitByColumnName = "column1"
$data = Import-Csv $InputFilePath | Select -ExpandProperty $SplitByColumnName -Unique
$a = $data | select
$counter=1
                                                                                                                                                                                                                      ForEach ($i in $a)
 {
    $FinalFileNamePath = "$PSScriptRoot$($counter).csv"
    $counter++ 
 
    Import-Csv $InputFilePath | where {$_.$SplitByColumnName -eq $i } | Export-Csv $FinalFileNamePath -NoTypeInformation
 }

The output files will be named 1.csv,2.csv,...,n.csv where n is the number of rows.

You should receive the output files in the same directory as the input file but this can be changed.

Mayank Soni0
Related