function to fix awful FortiAnalyzer exports

convert-nonsensecsv.ps1

u<# Format-FortiAnalyzerReport
    Fixes terribad formatting from FortiAnalzyer CSV exports.

    Use:
    Format-FortiAnalyzerReport <UNC Path to CSV> -SaveCopy

    -FileName
        UNC path of the CSV to fix.

    -SaveCopy
        will save a new copy of the file with in a new sub-folder name '_formatted'

    -SuppressOutput
        If this switch is set, the function WILL NOT outpt the results
        to the pipeline.
#>

$env:OutputSubFolder = '_formatted'

function global:Format-FortiAnalyzerReport {
    param($FileName,[switch]$SaveCopy,[switch]$SuppressOutput)

    $csvImport = Get-Content $sourceFile 
    $resultsFormattedTable = @()

    # Create the objects
    ForEach ($row in $csvImport){
        $newObjectProps = [ordered]@{}
        ForEach ($cell in @($row -split ',')){
            $cellKeyVal = $cell.Trim('"') -split '='
            If ($cellKeyVal[0]){ $newObjectProps.add($cellKeyVal[0],$cellKeyVal[1]) }
        }
        If ($newObjectProps){ $resultsFormattedTable += [PSCustomObject]$newObjectProps }
    } 

    # Exports copy if -SaveCopy switch is used
    If ($SaveCopy){
        $originalFile = Get-Item $FileName
        $destinationFolder = Join-Path $originalFile.Directory $env:OutputSubFolder
        If (-not (Test-Path $destinationFolder)){ New-Item $destinationFolder -ItemType 'Directory' -Confirm:$false -Force > $null}
        $newFileName = $originalFile.BaseName + '.csv'
        $newFileUNC = Join-Path $destinationFolder $newFileName
        $null = $resultsFormattedTable |  Export-Csv $newFileUNC -NoTypeInformation -Force
    }

    # Outputs properly formatted objects to pipe if $SupressOutput is not used
    If (-not $SuppressOutput){ $resultsFormattedTable }
}

<# Example:
$sourceDir = '\\domain.local\public\fortianalyzerreports'

Get-ChildItem $sourceDir |
    Where {-not $_.psiscontainer} |
    ForEach {
        Format-FortiAnalyzerReport -FileName $_.FullName -SaveCopy -SuppressOutput
    }
#>

<# attempting to properly convert a CSV with formatting of "key=val" in each cell

#>


$sourceFile = 'C:\sample.csv'
$csvImport = Get-Content $sourceFile 


$resultsFormattedTable = @()

ForEach ($row in $csvImport){
    $newObjectProps = @{}
    ForEach ($cell in @($row -split ',')){
        $cellKeyVal = $cell.Trim('"') -split '='
        $newObjectProps[$cellKeyVal[0]] = $cellKeyVal[1]
    }
    $resultsFormattedTable += New-Object -TypeName PSObject -Property $newObjectProps
}

$resultsFormattedTable | Out-Csv $sourceFile -NoTypeInformation

<# attempting to properly convert a CSV with formatting of "key=val" in each cell

#>


$sourceFile = 'C:\sample.csv'

$csvImport = Get-Content $sourceFile

$resultsFormattedTable = @()

ForEach ($row in $csvImport){
    $newObjectProps = @{}
    ForEach ($cell in @($row -split ',')){
        $cellKeyVal = $cell.Trim('"') -split '='
        $newObjectProps.($cellKeyVal[0]) = $cellKeyVal[1]
        
    }
    $resultsFormattedTable += New-Object PSCustomObject -Property $newObjectProps 
} 
$resultsFormattedTable  | export-csv "$sourceFile.new.csv" -NoTypeInformation