CODEWITHSUNDEEP
powershell
Enock Prince
Enock Prince

Reputation: 141

Powershell - Export array to CSV in different columns

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....

Upvotes: 0

Views: 5885

Answers (2)

TheMadTechnician
TheMadTechnician

Reputation: 36277

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

Upvotes: 1

zdan
zdan

Reputation: 29450

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

Upvotes: 1

Related Questions