Import/Export .csv file

问题:

I have testDates.csv with 100 dates

date
1/10/17
6/10/18
9/10/42
...

I made a script that makes sample passwords based off these dates:

$file = Import-Csv -Path U:\Desktop\testDates.csv

foreach ($line in $file) {
    $fullDate = $line.date
    $year = Get-Date $fullDate -Format "yy"
    $currentDate = Get-Date $fullDate -Format "MM-dd"

    # Determining season
    if ($currentDate -ge (Get-Date 01-01) -and $currentDate -lt (Get-Date 03-01)) {
        $season = "Winter"
    } elseif ($currentDate -ge (Get-Date 03-01) -and $currentDate -le (Get-Date 05-31)) {
        $season = "Spring"
    } elseif ($currentDate -ge (Get-Date 06-01) -and $currentDate -le (Get-Date 08-31)) {
        $season = "Summer"
    } elseif ($currentDate -ge (Get-Date 09-01) -and $currentDate -le (Get-Date 11-30)) {
        $season = "Fall"
    } elseif ($currentDate -ge (Get-Date 12-01) -and $currentDate -le (Get-Date 12-31)) {
        $season = "Winter"
    } else {
        $season = "ClearyAnIntern"
    }

    # Creating password
    $SamplePassword = $season + $year
}

I want to export the created password back to the original .csv in a new column. So have a column "passwords" next to "dates" to see if my script works correctly. Example:

date    password
1/10/17 Winter17
6/10/18 Summer18
9/10/42 Fall42
...     ...

I know it involves Export-Csv but I don't know how to pipe it especially for each line. I've been looking at tons of different threads and the MS help center for how to use the different parameters such as -Append and so forth but everything I've seen and tried isn't exactly what I'm dealing with and to infer from those examples is outside the scope of my remedial knowledge at the moment. I'm beginning to believe this may involve nested foreach loops.

回答1:

Import-Csv C:\path\input.csv | ForEach-Object {
   $seasons = [Char[]]'wwsssmmmfffw' # seasons map
}{ # walk around CSV lines
   [PSCustomObject]@{
      Date = $_.date # first and second columns of future CSV
      Password = "$(switch ($seasons[([DateTime]$_.date).Month - 1]) {
         'w' {'Winter'} 's' {'Spring'} 'm' {'Summer'} 'f' {'Fall'}
      })$(Get-Date $_.date -Format yy)"
   }
} | Export-Csv C:\path\out.csv # export result

回答2:

Export-Csv expects a list of objects as input, so that's what you need to create first.

foreach ($line in $file) {
    ...
    [PSCustomObject]@{
        'date'     = $fullDate
        'password' = $SamplePassword
    }
}

Next, foreach loops can't write to the pipeline, so you either need to append to the output file inside the loop:

foreach ($line in $file) {
    ...
    [PSCustomObject]@{
        'date'     = $fullDate
        'password' = $SamplePassword
    } | Export-Csv 'output.csv' -NoType -Append
}

or collect the output in a variable and export it afterwards:

$list = foreach ($line in $file) {
    ...
    [PSCustomObject]@{
        'date'     = $fullDate
        'password' = $SamplePassword
    }
}

$list | Export-Csv 'output.csv' -NoType

Otherwise you need to replace the foreach loop with a ForEach-Object loop:

Import-Csv 'U:\Desktop\testDates.csv' | ForEach-Object {
    $fullDate = $_.date
    ...
    [PSCustomObject]@{
        'date'     = $fullDate
        'password' = $SamplePassword
    }
} | Export-Csv 'output.csv' -NoType

回答3:

You can use Calculated properties combined with a for loop to do so. The same is illustrated below -

#Importing csv dates file
$file = Import-Csv -Path U:\Desktop\testDates.csv

#Declaring the variable to store passwords
$SamplePassword = @()

#loop
foreach ($line in $file)
{
    #Variable declarations
    $fullDate = $line.date
    $year = get-date $fullDate -Format "yy"
    $currentDate = get-date $fullDate -Format "MM-dd"

    #Determining season
    if ($currentDate -ge (get-date 01-01) -and $currentDate -lt (get-date 03-01))
        {$season = "Winter"}
    elseif ($currentDate -ge (get-date 03-01) -and $currentDate -le (get-date 05-31))
        {$season = "Spring"}
    elseif ($currentDate -ge (get-date 06-01) -and $currentDate -le (get-date 08-31))
        {$season = "Summer"}
    elseif ($currentDate -ge (get-date 09-01) -and $currentDate -le (get-date 11-30))
        {$season = "Fall"}
    elseif ($currentDate -ge (get-date 12-01) -and $currentDate -le (get-date 12-31))
        {$season = "Winter"}
    else{
    $season = "ClearyAnIntern"
    }

    #Creating password
    $SamplePassword += $season + $year
}

After the above step, your $SamplePassword will contain all the passwords. Use calculated properties with a for loop as shown below -

#Declaring a new variable to store the results
$NewCsv = @()
$Originalcsv = Import-Csv -path U:\Desktop\testDates.csv
for($i = 0; $i -lt $Originalcsv.Length; $i++)
{
    $NewCsv += $Originalcsv[$i] | Select-Object *, @{Name='Password';Expression={$SamplePassword[$i]}}
}

$NewCsv | Export-Csv U:\Desktop\NewtestDatesWithPasswords.csv -NoTypeInformation

回答4:

I only guess the dates in your csv file have the format M/d/yy ? Importing from csv the variable type is always string, since my own locale deviates I have to define a different culture to convert.

Using a modified version of my function Get-Season from your other question.

## Q:\Test\2018\06\21\SO_50976425.ps1
## define function first
Function Get-Season([datetime]$Date){
    If (!$Date) {$Date = Get-Date} #If date was not provided, assume today.
    # The dates are obviously not exactly accurate and are best guesses
    $Spring = Get-Date -Day 20 -Month 03 -Year $Date.Year
    $Summer = Get-Date -Day 21 -Month 06 -Year $Date.Year
    $Autumn = Get-Date -Day 22 -Month 09 -Year $Date.Year
    $Winter = Get-Date -Day 21 -Month 12 -Year $Date.Year
    $Season = switch($Date) {
        {($_ -lt $Spring)}  {"Winter";Break}
        {($_ -lt $Summer)}  {"Spring";Break}
        {($_ -lt $Autumn)}  {"Summer";Break}
        {($_ -lt $Winter)}  {"Autumn";Break}
        {($_ -ge $Winter)}  {"Winter"}
    }
    "{0}{1}" -f $Season,$Date.ToString('yy')
}

#Importing csv dates file
$file = Import-Csv -Path '.\testDates.csv'

# get CultureInfo
$CIUS = New-Object System.Globalization.CultureInfo("en-US")

# process $file and create a new PSCustomObject
$DatePassw = foreach ($line in $file){
    $fullDate = [datetime]::ParseExact($line.date,"M/d/yy",$CIUS)
    [PsCustomObject]@{
        date     = $line.date
        password = (Get-Season $fulldate)
    }
}
$DatePassw | ft
$DatePassw | Export-Csv '.\testdatepassw.csv' -NoTypeInformation

Sample output:

> .\SO_50976425.ps1

date    password
----    --------
1/10/17 Winter17
6/10/18 Spring18
9/10/42 Summer42

> gc .\testdatepassw.csv
"date","password"
"1/10/17","Winter17"
"6/10/18","Spring18"
"9/10/42","Summer42"