Creating powershell array

Question

I am a newby to powershell and am trying to automate a complex process. I need to copy and paste a 3 columns of data from one spreadsheet to a second one which will then be exported to an SAP transaction line by line. In my search I found the link below that discusses using $arr1 = @(0) * 20 to create the array. I have two questions.

The first question is what is the (0) referencing?

Also, using this formula how do I reference the workbook, sheet, and column that I need to use to create the array? Any help would be greatly appreciated.

PowerShell array initialization

The 1st question: find @( in help (Get-Help 'about_arrays' -ShowWindow as well as in Get-Help 'about_operators' -ShowWindow). You know that @( ) is an Array subexpression operator then... — JosefZ
– JosefZ, Commented Nov 13, 2018 at 20:24
If you're just working with CSV files, PowerShell has built in Import-CSV and Export-CSV cmdlets. If you're working with standard workbooks/worksheets, LazyWinAdmin and RamblingCookieMonster both have great articles about using the COM interface to access Excel files, and RCM's PSExcel custom module is great for instances where you don't have office installed, such as on a Windows Server instance — trebleCode
– trebleCode, Commented Nov 13, 2018 at 20:39
@trebleCode Do you have any links to the articles on LazyWinAdmin or RamblingCookieMonster? I forgot to add that this is an excel spreadsheet I am exporting from. — SHartman
– SHartman, Commented Nov 13, 2018 at 21:45
Sure. RCM's PSExcel module: ramblingcookiemonster.github.io/PSExcel-Intro & LWA's article: lazywinadmin.com/2014/03/… — trebleCode
– trebleCode, Commented Nov 14, 2018 at 16:35

Mike Twc · Accepted Answer · 2018-11-14 23:07:21Z

0

@() is an array literal (typically used to define empty or single element array) You can also use list object (System.Collection.ArrayList), might be more convenient since it has dynamic size.

Below is a quick and dirty example with COM object (reads data from range into array list)

$excel= new-object -com excel.application

$excel.Visible = $false

$wb = $excel.workbooks.open("$home\Documents\book1.xlsx")

$ws = $wb.Sheets.Item("Sheet1")

$data = New-Object System.Collections.ArrayList

foreach ($i in 1..20){

$data.Add(  (New-Object PSObject -Property @{A=$ws.Range("A$i").Value2; B=$ws.Range("B$i").Value2})  )  | Out-Null

}

Write-Output $data

$wb.Close()

answered Nov 14, 2018 at 23:07

Mike Twc

2,3752 gold badges17 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

Creating powershell array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related