CODEWITHSUNDEEP
excelpowershellcsv
Nahshon paz
Nahshon paz

Reputation: 4603

powershell script, text placement in cells in csv file

So, to continue my lovely journey through powershell from here: Loop for two variables

I have a ps1 that runs a loop for a bunch of transactions and a bunch of nodes and sends them over to a csv file. 

$url = "https://someserver/trans="
$transactions = '1','2','3','4' #There are 4 transactions
$nodes = 'node1','node2','node3','node4','node5','node6' #There are 10 nodes

Remove-Item ATM.csv -Force

# So far so good
# Below is what I'd use as a function in bash. No sure what/how to do in PS:
#OUTPUT:
foreach($transaction in $transactions)
{
    foreach($node in $nodes)
    {

    "$transaction;$node" |out-file -Append ATM.csv
    curl -k -u user@pass $url$transaction$node | findstr "<value>" | out-file -Append ATM.csv
  }
}

Opening the file in excel, I end up with this output under column A:

   transaction1;node1 (in the first row, left-most cell)
   value1 (from the curl. It's actually a number and it sits in the row right under the first entry)

and so on and so forth for 2,3, and the rest. only the left most column (column A) gets populated.

What I'd like to get is a way to place the values in three columns, such that the csv will look like:

Column A    | Column B | Column C
transaction1| node1    | valueX
transaction2| node2    | valueY

and so on. The script or another will have to do this, the end user for this job who'll run the script will not open excel every day and start running macros, he needs the final csv ready from the script.

Whatever shall I do?

Upvotes: 1

Views: 481

Answers (2)

Olaf Reitz
Olaf Reitz

Reputation: 694

You are currently writing your output in two different lines. One solution could be to use the NoNewLine parameter in the Out-File:

"$transaction;$node" |out-file -Append ATM.csv -nonewline
curl -k -u user@pass $url$transaction$node | findstr "<value>" | out-file -Append ATM.csv

Personally I would create a Powershell Object and create the csv at the end:

$array = @()
foreach($node in $nodes) {
    $obj = New-Object psobject
    $obj | Add-Member -MemberType NoteProperty -Name 'Transaction' -Value $transaction
$obj | Add-Member -MemberType NoteProperty -Name 'Node' -Value $node
$obj | Add-Member -MemberType NoteProperty -Name 'Value' -Value (curl -k -u user@pass $url$transaction$node | findstr "<value>")
$array += $obj

}

Upvotes: 1

henrycarteruk
henrycarteruk

Reputation: 13227

Something like this will fix your issues, the only bit that's not included is selecting the value itself from Invoke-WebRequest (curl) as that will change depending on what's returned.

foreach($transaction in $transactions)
{
    foreach($node in $nodes)
    {
    $value = Invoke-WebRequest -Uri $url$transaction$node -UseBasicParsing | Select-Object -Expand Content

    Add-Content -Path ATM.csv -Value "$transaction,$node,$value"
    }
}

Upvotes: 1

Related Questions