Creating CSV file from powershell, column issue with empty cells

Question

My issue is that I need to create CSV file which will print values in separate columns, next to each other. For now I have separate columns, but with empty cells.

For example: Column B has values starting from cell 1 until cell 8. Column C will start printing values starting from cell 9, but should start from cell 1. It contains values under values of column B, but I need them next to each other.

I have such code

$allsites = @(
'newone'
'new'
'one'
)

# Set the path to save the CSV file
$csvFilePath = "C:\PATH"


$parameters = @(

)


# Create an empty array to store the parameter values
# $parameters = @()

foreach ($site in $allsites) {

    $xmlFilePath = "C:\PATH"
    $xmlContent = [xml](Get-Content -Path $xmlFilePath)

    $allparameters = @(

        'parameter1'
        'Parameter2'
        'Parameter3'
        'Parameter4'

    )



    foreach ($parameter in $allparameters) {
        $parameterNode = $xmlContent.SelectSingleNode("//add[@key='$parameter']")
        if ($parameterNode -eq $null) {
            $parameterValue = "Parameter not found or commented out."
        } else {
            $parameterValue = $parameterNode.getAttribute("value")
        }

        Write-Host $parameterValue

        $paramObj = [PSCustomObject]@{
            A = ''
            B = ''
            C = ''
            D = ''
        }

        if ($site -eq "newone") {
            $paramObj.B = $parameterValue
        }
        elseif ($site -eq "new") {
            $paramObj.C = $parameterValue
        }
        elseif ($site -eq "one") {
            $paramObj.D = $parameterValue
        }

        $parameters += $paramObj
    }





    # Export the parameter values to the CSV file
    if ($parameters.Count -gt 0) {
        $parameters | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | Out-File -FilePath $csvFilePath -Encoding UTF8 -Append
        # Clear the $parameters array for the next iteration
        
        $parameters = @()
    }

    timeout /t 20
}

Write-Host "CSV file created successfully at $csvFilePath"

timeout /t 10

Here is a piece of output

"A","B","C","D"
"","JIRA","",""
"","false","",""
"","1200","",""
"","0","",""
"","true","",""
"","Parameter not found or commented out.","",""
"A","B","C","D"
"","","JIRA",""
"","","false",""
"","","1200",""
"","","0",""
"","","yesyoucan",""
"","","Parameter not found or commented out.",""
"A","B","C","D"
"","","","JIRA"
"","","","false"
"","","","1200"
"","","","0"
"","","","dzialaj"
"","","","Parameter not found or commented out."

What I need is:

print values in separate columns next to each other, without empty cells. Each column should start from cell 1.

Can you help? I can't find solution. I think it should be something with this line

Clear the $parameters array for the next iteration $parameters = @() It should contain only parameters for A, B, C without any empty values.

I tried add-member and tried to write some conditions to not add empty values without luck.

Does this answer your question: [Is there a PowerShell equivalent of `paste` (i.e., horizontal file concatenation)?](https://stackoverflow.com/q/68070226/1701026) — iRon, Jun 01 '23 at 10:24
`$paramObj.A |FullJoin $paramObj.B |FullJoin $paramObj.C |FullJoin $paramObj.D -Name A,B,C,D` see also: [Is there a way to transform horizontally formatted CSV output to vertical when converting XML to CSV in PowerShell?](https://stackoverflow.com/a/76330651/1701026) — iRon, Jun 01 '23 at 11:40

score 0 · Answer 1 · answered Jun 01 '23 at 09:10

0

This is pretty straight-forward to fix - simply change the default property values to be empty strings instead of $null!

$paramObj = [PSCustomObject]@{
    A = ''
    B = ''
    C = ''
    D = ''
}

answered Jun 01 '23 at 09:10

Mathias R. Jessen

157,619
12
148
206

the result is the same as before column B starting from cell 1 and column C starting from cell 9 – Mario7 Jun 01 '23 at 09:16
@Mario7 Can you provide a better example of the input you're using and output you're seeing? Hard to tell whether it's right or wrong without seeing the input and the resulting headers in the output – Mathias R. Jessen Jun 01 '23 at 09:18
Perhaps I read your post wrong - are you trying "flip" the table, eg. turn the columns into rows? – Mathias R. Jessen Jun 01 '23 at 09:19
Hi, I have edited my code in description. I added full code and output of it. I need to have values column next to column. For now it's B column cells 1-9 are filled. Then C column should be filled from cell 1-9 but it is filled from 10-18.. so there are empty cells which I don't need – Mario7 Jun 01 '23 at 09:27
@Mario7 can you run it again without the `Select-Object -Skip 1` command? So we can see the resulting headers? – Mathias R. Jessen Jun 01 '23 at 09:45
Yes, I added modified code and output hmm. It looks like something with loop, it shouldn't print empty columns. Only this with values A,B,C or D. not all of them.the empty columns are being printed because I'm appending the $parameters array to the CSV file on each iteration, even when some columns are empty. To fix this issue I would need some condition to check if any of the columns (B, C, D) have values before exporting to the CSV file. but I don't know how to edit this code corretly. Can you help? – Mario7 Jun 01 '23 at 10:12

Creating CSV file from powershell, column issue with empty cells

1 Answers1