CODEWITHSUNDEEP
powershellcsv
Matt
Matt

Reputation: 1155

PowerShell: Export each line of CSV to individual file

I have a tab-delimited CSV which needs to be broken apart into separate CSVs for each row, with the filename being [FirstName] [LastName].csv.

Additionally, I need to manipulate some data, specifically remove the dashes from the SSN.

Sample Content

FirstName  LastName   SSN
=========  =========  ======
Albert     Abernathy  111-11-1111
Billy      Barty      222-22-2222
Chris      Canton     333-33-3333

Desired output (Filename | CSV Content)

Albert Abernathy.csv | Albert     Abernathy  111111111
Billy Barty.csv      | Billy      Barty      222222222
Chris Canton.csv     | Chris      Canton     333333333

Here's what I have so far.

$csvFile = "C:\Test\List.csv"
$folderPath = Split-Path -Parent $csvFile
$csv = Import-Csv -Path $csvFile -Delimiter "`t" 
| Where-Object {$_.LastName -ne ""} ##Prevent importing blank lines
| Foreach-Object {$_.SSN -Replace "-","" } ##Remove dashes from SSN
foreach ($line in $csv)
{   $saveName = $line.FirstName + " " + $line.LastName + ".csv"
    Export-Csv -Delimiter "`t" -Path $folderPath\$saveName
}

So far, all of this runs, but it just pauses after the final clause and nothing happens. As I cobbled this together from Google searches, I'm certain my syntax needs work, so I'd be grateful if someone could point me in the right direction

--Update--

Credit to alroc for pointing me in the right direction, and mjolinor for providing an invaluable REPLACE syntax I could not find anywhere else. Here's my final script.

$csvFile = "C:\Test\List.csv"
$folderPath = Split-Path -Parent $csvFile

### filter out blank records and delete dashes from SSN ###
(Get-Content $csvFile | 
    Select-Object |
    Where-Object {$_.LastName -ne ""}) | #Rows with non-blank last name
    Foreach-Object {$_ -Replace '(\d{3})-(\d{2})-(\d{4})','$1$2$3'} |
    Set-Content $csvFile

### import csv data and export file for each row ###
$csv = Import-Csv -Path $csvFile -Delimiter "`t"
foreach ($row in $csv) {
    $outPath = Join-Path -Path $folderPath -ChildPath $($row.FirstName + " " + $row.LastName + ".csv");
    $row | Select-Object | 
        ConvertTo-Csv -NoTypeInformation -Delimiter "`t" | 
        Foreach-Object {$_.Replace('"','')} | #Delete quotes around each "cell"
        Out-File $outPath
    (Get-Content $outPath | 
        Select-Object -Skip 1) | #Delete header
        Set-Content $outPath
}

Upvotes: 0

Views: 6826

Answers (2)

mjolinor
mjolinor

Reputation: 68243

FWIW - Not tested. Kept as much of the original script as possible.

$csvFile = "C:\Test\List.csv"
$folderPath = Split-Path -Parent $csvFile.fullname

Import-Csv -Path $csvFile -Delimiter "`t" |
 Where-Object {$_.LastName -ne ""} |  ##Prevent importing blank lines
 Foreach-Object {
  $_.SSN = $_.SSN -Replace "-",""  ##Remove dashes from SSN
  $saveName = $_.FirstName + " " + $_.LastName + ".csv" 
  Export-Csv $_ -Delimiter "`t" -Path $folderPath\$saveName
  }

Upvotes: 0

alroc
alroc

Reputation: 28144

I'm not clear on what you mean by "it just pauses after the final clause and nothing happens" but it may have something to do with you piping import-csv to the rest of your process and attempting to assign the result to $csv. This works for me:

$csvFile = "C:\Test\List.csv"
$folderPath = Split-Path -Parent $csvFile
$csv = Import-Csv -Path $csvFile -Delimiter "`t" 
foreach ($record in $csv) {
    $OutFileName = join-path -Path $folderPath -ChildPath $($record.FirstName + " " + $record.LastName + ".csv");
    $record|select-object FirstName,LastName,@{name="SSN";expression={$_.SSN -replace "-",""}}|export-csv -NoTypeInformation -Path $OutFileName -Delimiter "`t";
}

Or if you want to do it without the intermediate variables, keeping it more pipeline-y:

$csvFile = "C:\Test\List.csv"
$folderPath = Split-Path -Parent $csvFile
Import-Csv -Path $csvFile -Delimiter "`t" | foreach {
    $OutFileName = join-path -Path $folderPath -ChildPath $($_.FirstName + " " + $_.LastName + "2.csv");
    $_|select-object FirstName,LastName,@{name="SSN";expression={$_.SSN -replace "-",""}}|export-csv -NoTypeInformation -Path $OutFileName -Delimiter "`t";
}

Upvotes: 1

Related Questions