Powershell-script to export array contents to excel

Question

I am using the below script to export results to excel. However I am getting only 1 row when I am exporting it to excel.Please let me know how to acheive this. #script

$result = @()

$array = @("testA", "testB", "testC","testD")
$array2 = @("testA1", "testB2", "testC3","testD4")
$tbl = new-object psobject            
$tbl | add-member noteproperty VmName $array
$tbl | add-member noteproperty VMIP $array2
$result += $tbl 

$result|Export-Excel -Path "C:\Users\Empower\Desktop\OMS-Log\test.xlsx"

Console output:

VmName                                VMIP                            
------                                      ----                            
{testA, testB, testC, testD}     {testA1, testB2, testC3, testD4}

Exported result in Excel:

 VmName   VMIP
 testA    testA1

Required result in Excel

VmName  VMIP
testA   testA1
testB   testB2
testC   testB3
testD   testB4

Paweł Dyl · Accepted Answer · 2018-09-25 11:22:02Z

3

You are creating one object with two properties (each one is array). To export xlsx file you need four objects with two properties. It's sometimes called zipping:

$array = @("testA", "testB", "testC","testD")
$array2 = @("testA1", "testB2", "testC3","testD4")

$result = [System.Linq.Enumerable]::Zip($array,$array2,[Func[Object, Object, Object[]]]{
    [pscustomobject]@{VmName=$args[0];VMIP=$args[1]}
})

$result | Export-Excel -Path "C:\Users\Empower\Desktop\OMS-Log\test.xlsx"

Zip can also be expressed as:

$result = for ($i=0; $i -lt ([Math]::Min($array.Count, $array2.Count)); $i++) {
    [pscustomobject]@{VmName=$array[$i];VMIP=$array2[$i]}
}

answered Sep 25, 2018 at 11:22

Paweł Dyl

9,1831 gold badge13 silver badges28 bronze badges

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

1 Comment

pro99 Over a year ago

From the question we know that we require four object.Could you please answer me this..I am trying to export logs to excel..I have 1 array with 17 properties, since the number of logs keeps changing i dont know how many object it would require

Collectives™ on Stack Overflow

Powershell-script to export array contents to excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related