0

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

zdan
  • 28,667
  • 7
  • 60
  • 71
SHartman
  • 13
  • 2
  • 1
    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 Nov 13 '18 at 20:24
  • 1
    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 Nov 13 '18 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 Nov 13 '18 at 21:45
  • Sure. RCM's PSExcel module: http://ramblingcookiemonster.github.io/PSExcel-Intro/ & LWA's article: https://lazywinadmin.com/2014/03/powershell-read-excel-file-using-com.html – trebleCode Nov 14 '18 at 16:35

1 Answers1

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()
Mike Twc
  • 2,230
  • 2
  • 14
  • 19