Powershell - Export array to CSV in different columns

Question

I am trying to automate below API calls from a csv file.

http_uri
/ModuleName/api/12345/moverequest/MoveRequestQueue?batchSize=200
/ModuleName/api/Portal/[email protected]
/ModuleName/api/DeliveryDate/CommitEta?ref=H7J3M1EA4LF
/ModuleName/api/35345/moverequest/MoveRequestQueue?batchSize=500

The output should be like below in a csv file.

ScenarioName        Parameter   Value
MoveRequestQueue    batchSize   200
GetGarageLocations  email       [email protected]
CommitEta           ref         H7J3M1EA4LF
MoveRequestQueue    batchSize   500

I am using below code

$csv = Import-Csv C:\Powershell\Documents\Source.csv

$scenario   = @()

ForEach ($row in $csv){
$httpuri = $($row.http_uri)


#Iterating through CSV rows and segregate values
if ($httpuri -match "="){
    $equalarr = $httpuri -split '='
    if ($equalarr[0] -match "\?"){
        $questionarr = $equalarr[0] -split '\?'
        $scenarionamearr = $questionarr[0] -split '/'
        $totalelements = $scenarionamearr.Count
        $scenarioname = $scenarionamearr[$totalelements-1]

        $Scenario += $scenarioname
        $Scenario += $questionarr[1]
        $Scenario += $equalarr[1]

        }
    }


}

#Adding columns to csv
$columnName = '"Scenario","Parameter","Value"'
Add-Content -Path C:\Powershell\Documents\Output.csv -Value $columnName

#Writing values to CSV
$Scenario | foreach { Add-Content -Path C:\Powershell\Documents\Output.csv -Value $_ }

But Outout is generated like below

 Scenario                   Parameter   Value
 DequeueMoveRequestQueue        
 batchSize      
 200        
 GetCarrierLocations        
 email      
 x-qldanxqldanx

Since i am a newbie, searched a lot to solve this issue but couldn't succeed. Please throw some light on this.

Thanks in advance....

zdan · Accepted Answer · 2019-12-12 19:53:04Z

1

If you store your scenarios in structured objects you can use Powershell's built in Export-Csv command to generate your csv.

So, instead of

    $Scenario += $scenarioname
    $Scenario += $questionarr[1]
    $Scenario += $equalarr[1]

store an array of powershell objects:

    $Scenario += [PSCustomObject]@{
        "Scenario" = $scenarioname; 
        "Parameter" = $questionarr[1]; 
        "Value" = $equalarr[1];}

Then, when creating the csv file, just use Export-Csv:

$Scenario | Export-Csv -NoTypeInformation -Path C:\Powershell\Documents\Output.csv

answered Dec 12, 2019 at 19:53

zdan

29.6k7 gold badges62 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TheMadTechnician · Accepted Answer · 2019-12-12 19:58:51Z

So the issue is that you make an empty array, then add strings to it one at a time, which just makes it an array of strings. Then when you output it to the file it just adds each string to the file on its own line. What you want to do is create an array of objects, then use the Export-Csv cmdlet to output it to a CSV file.

Creating an array, and then adding things to it one at a time is not a good way to do it. PowerShell has to recreate the array each time you add something the way you're doing it. Better would be to have a pipeline that outputs what you want (objects, rather than strings), and capture them all at once creating the array one time. Or even better, just output them to the CSV file and not recollect them in general.

$CSV = Import-Csv C:\Powershell\Documents\Source.csv

$CSV.http_uri -replace '^.*/(.*)$','$1'|ForEach-Object{
    $Record = $_ -split '[=\?]'
    [PSCustomObject]@{
        ScenarioName = $Record[0]
        Parameter = $Record[1]
        Value = $Record[2]
    }
    } | Export-Csv -Path C:\Powershell\Documents\Output.csv -Append

Collectives™ on Stack Overflow

Powershell - Export array to CSV in different columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related