1

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
 }
Santiago Squarzon
  • 41,465
  • 5
  • 14
  • 37
Diana Persico
  • 11
  • 2
  • CSV means comma-**separated**. If you only have one column, nothing is separated and what you really have is a text file. – Joel Coehoorn Sep 14 '22 at 19:01
  • Well CSV files, as the name implies, are comma separated. Each cell in a row is separated by a comma, and each row is separated by a new line. The titles of the columns (if there are titles) are always the first row. Posts that may be of interest: [powershell -split('') specify a new line](https://stackoverflow.com/a/39252621/10601203), [Import-Csv](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/import-csv?view=powershell-7.2). – Jesse Sep 14 '22 at 19:04
  • @JoelCoehoorn I still have 2000 rows which are comma separated – Diana Persico Sep 14 '22 at 19:08
  • 4
    Are you able to post a sanitized sample of the csv to see what you're referring to, along with your attempts at getting the results you're after? Preferably a before and after. – Abraham Zinala Sep 14 '22 at 19:19
  • @AbrahamZinala just did it, but the fact is there are no results since this one that I tried it doesn't work. – Diana Persico Sep 14 '22 at 20:01
  • 1
    Can you properly format your code. Also, please post the sample of the csv in plain text so we may be able to copy and work with it ourselves. What are the expected results? – Abraham Zinala Sep 14 '22 at 20:23
  • 2
    Provided Csv only has one column populated with a multiline string and Column2 is empty. Is that exactly how your Csv looks? – Santiago Squarzon Sep 14 '22 at 21:09
  • @SantiagoSquarzon yes! – Diana Persico Sep 15 '22 at 16:31

1 Answers1

0

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 Soni
  • 111
  • 4