Powershell script to break up list into multiple arrays

To do this, there are a number of options. You can read in everything from the Excel file in one large array and split that afterwards in smaller chunks or you can add the Excel file values in separate arrays while reading.
The code below does just that.

In any case, it is up to you when you would like to actually send the data.

1. process each array immediately (send it to a SAP business objects query) while reading from Excel
2. add it to a Hashtable so you keep all arrays together in memory
3. store it on disk for later use

In the code below, I choose the second option to read in the data in a number of arrays and keep these in memory in a hashTable.
The advantage is that you do not need to interrupt the reading of the Excel data like with option 1. and there is no need to create and re-read 'in-between' files as with option 3.

$source = Join-Path -Path $ENV:UserProfile -ChildPath "Documents\Test\DataSD.xls"
$maxArraySize = 2000

$Excel = New-Object -ComObject Excel.Application
# It would speed up things considerably if you set $Excel.Visible = $false
$WorkBook = $Excel.Workbooks.Open($source)
$WorkSheet = $WorkBook.WorkSheets.Item(1)
$WorkSheet.Activate()

# Create a Hashtable object to store each array under its own key
# I don't know if you need to keep the order of things later, 
# but it maybe best to use an '[ordered]' hash here.
# If you are using PowerShell version below 3.0. you need to create it using
# $hash = New-Object System.Collections.Specialized.OrderedDictionary
$hash = [ordered]@{}

# Create an ArrayList for better performance
$list = New-Object System.Collections.ArrayList
# Initiate a counter to use as Key in the Hashtable
$arrayCount = 0
# and maybe a counter for the total number of items to process?
$totalCount = 0

# Start reading the Excel data. Begin at row $row
$row = 2
do {
    $list.Clear()
    # Add the values of column 1 to the arraylist, but keep track of the maximum size
    while ($WorkSheet.Cells.Item($row, 1).Value() -ne $null -and $list.Count -lt $maxArraySize) {
        [void]$list.Add($WorkSheet.Cells.Item($row, 1).Value())
        $row++
    }
    if ($list.Count) {
        # Store this array in the Hashtable using the $arrayCount as Key. 
        $hash.Add($arrayCount.ToString(), $list.ToArray())
        # Increment the $arrayCount variable for the next iteration
        $arrayCount++
        # Update the total items counter
        $totalCount += $list.Count
    }
} while ($list.Count)

# You're done reading Excel data, so close it and release Com objects from memory
$Excel.Close()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($WorkSheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($WorkBook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

# At this point you should have all arrays stored in the hash to process
Write-Host "Processing $($hash.Count) arrays with a total of $totalCount items"

foreach ($key in $hash.Keys) {
    # Send each array to a SAP business objects query separately
    # The array itself is at $hash.$key or use $hash[$key]
}