Warm tip: This article is reproduced from stackoverflow.com, please click
csv powershell

Cleaning up CSV Data Using Powershell

发布于 2020-03-27 10:24:29

I have a CSV that holds information on file paths. The full file path can be created if I merge the cells together and add "/" between them. Sometimes these cells can be empty or have multiple directories in them. Eg. Desktop/FolderA/FolderB

So for that I’ve got the script:

Import-Csv -Path 'test.csv'  | 
Select-Object @{Name='Path';Expression={$_.Folder1, "/" ,$_.Folder2, "/",$_.Folder3, "/",$_.Folder4, "/",$_.Folder5, "/", $_.Folder6, -join ','}}    | 
Export-Csv 'OutputTestFile.csv' -NoTypeInformation

This produces the output test file that looks a bit like:

HR Central / / / HR Central / Documents / ,

HR Central / Employee Services / / Employee Services - Internal / Employee Services / Procedures/a/b/c ,

HR / HR Business Partners / / Talent & Acquisition - Internal / HR Business Partners / ,

Which doesn't make for clean file paths because of the double slashes. Spaces are also an issue. I don’t even need to export it back to a CSV. I need to just be able to grab a list/array of file paths so I can create the file structure within an environment.

Ideally I'd like the file paths to look like this:

HR/HR Business Partners/Talent & Acquisition - Internal/HR Business Partners/,

HR Central/HR Central/Documents/,

Questioner
Pikapops
Viewed
84
JosefZ 2019-07-03 23:46

Another option:

foreach ( $csvLine in $(Import-Csv -Path 'test.csv')) {
    $rawPath = $csvLine | Select-Object @{
        Name='Path';
        Expression = { @( $_.Folder1, 
                          $_.Folder2, 
                          $_.Folder3, 
                          $_.Folder4, 
                          $_.Folder5, 
                          $_.Folder6) -join '/'
                    }
        }
    # remove multiple solidi
    $rawPath.Path.Split(
         '/', 
         [System.StringSplitOptions]::RemoveEmptyEntries) -join '/'
}