how can add column in html table?-CodePudding

I have excel file(csv).ı calculate dateline product licence. and ı creating table with html. ı need add column calculated licence day. but ı cant do it. my ecxcell file like this; . ı need 'product', 'bitis' and 'licence days(ı calculated with in foreach loop'.) But ı didnt do this. I need table with product,bitis and $licence. how can ı add $licence in table?

    $cInCsv = Import-Csv -Path $sInFile -Delimiter ";" -Encoding UTF7
$StartDate = Get-Date -UFormat "%d.%m.%Y"
$sDateFormat = "%d.%m.%Y"
$sInFile = "C:\Users\akilic\Desktop\tarihlerr.csv"

$header = @"
<style>
table {border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse;}
th {border-width: 1px; padding: 3px; border-style: solid; border-color: black; background-color: #6495ED;}
td {border-width: 1px; padding: 3px; border-style: solid; border-color: black;}

</style>
"@


#Import-Csv -Path $sInFile -Delimiter ";" -Encoding UTF7 | Select product,bitis,gün | ConvertTo-Html -Head $header | Out-File "C:/Users/akilic/Desktop/health.html" 


Foreach ($ThisUser in $cInCsv)
{    

    $EndDate = Get-Date ($ThisUser.'bitis')
    $day = NEW-TIMESPAN –Start $StartDate  –End $EndDate
    $licence = $day.Days
    
    if( $licence -lt 90 -and $licence -gt 0){
       
      $ThisUser | Select-Object product,bitis, @{Name='gun'; Expression={$licence}} | Export-Csv "C:\Users\akilic\Desktop\test.csv" -Append -Force
    
    }

    if($licence -lt 0){

        $pos = [Math]::Abs($licence) 
        Write-Host $ThisUser.product " lisansı $pos gün geçmiştir"
        $ThisUser | Select-Object product,bitis,@{Name='gun'; Expression={$pos}} | Export-Csv "C:\Users\akilic\Desktop\test.csv"  -Append -Force
        
    }
}

CodePudding user response：

You need to use datetime objects, not formatted strings.

Set $StartDate like this:

$StartDate = Get-Date  # a DateTime object, not a string

Then, inside the loop calculate the $EndDate from the 'bitis' column like this:

$EndDate = [datetime]::ParseExact($ThisUser.bitis, 'dd.MM.yyyy', $null)

Now you can create a TimeSpan using the DateTime objects $StartDate and $EndDate.

BTW. I would collect the data in a variable and afterwards save it all to CSV so you don't do so many disk writes appending to the file.

$result = foreach ($ThisUser in $cInCsv) {    
    $EndDate = [datetime]::ParseExact($ThisUser.bitis, 'dd.MM.yyyy', $null)
    $licence = (New-TimeSpan –Start $StartDate  –End $EndDate).Days

    if($licence -lt 90 -and $licence -gt 0) {
        # output the selected object to be collected in variable $result
        $ThisUser | Select-Object product,bitis, @{Name='gun'; Expression={$licence}}
    }
    elseif ($licence -lt 0){
        $pos = [Math]::Abs($licence) 
        Write-Host "$($ThisUser.product) lisansı $pos gün geçmiştir"
        $ThisUser | Select-Object product,bitis,@{Name='gun'; Expression={$pos}}
    }
}

# now write the result to csv file
$result | Export-Csv "C:\Users\akilic\Desktop\test.csv" -NoTypeInformation -Force

And/or create a HTML table from the data in $result

Oh yes, you need to swap these lines to read from the CSV file

$sInFile = "C:\Users\akilic\Desktop\tarihlerr.csv"
$cInCsv  = Import-Csv -Path $sInFile -Delimiter ";" -Encoding UTF7

CodePudding user response：

Exception calling "ParseExact" with "3" argument(s): "String was not recognized as a valid DateTime." I take this error. Can you help me?