CODEWITHSUNDEEP
powershellpowershell-2.0powershell-3.0powershell-remoting
Phoenix2529
Phoenix2529

Reputation: 147

Paste data from Excel and convert into an array?

I'm new powershell and want to take a list of servers from Excel, and literally paste into an input window. It should segregate it and process it one after other.

ie give the name of servers:

arvdel1
arvdel2
arvdel3
arvdel4

and it should take it as

$a=@(arvdel1,arvdel2,arvdel3,arvdel4)

How can I do this?

Upvotes: 1

Views: 3795

Answers (6)

Dwight Gappa
Dwight Gappa

Reputation: 11

You can use a required parameter (for Powershell versions less than 5.1 or 'Get-Clipboard' for version 5.1 or greater) that has type of String array [string[]]. It will prompt you for to enter the value of each array element until it detects a blank line that is entered.

I figured this out recently by looking at the input Parameters of a script. For years I was manipulating the text into the PowerShell single line array format with Excel's transpose and replacing 'Tab' characters in Notepad.

Just for reference PowerShell single line array format is ("1", "2", "3", "4", "5") 

...
param
(
    [parameter(Mandatory = $true)]
    [string[]]
    $listOfStrings
)
...

Example prompt to fill to a string array

Supply values for the following parameters:
listOfStrings[0]:

I created a list in Notepad (or Excel) of 1 through 5 and directly copied it into PowerShell window. Below is my sample output.

1
2
3
4
5

Supply values for the following parameters:
listOfStrings[0]: 1
listOfStrings[1]: 2
listOfStrings[2]: 3
listOfStrings[3]: 4
listOfStrings[4]: 5
listOfStrings[5]:
1
2
3
4
5

Upvotes: 1

harringg
harringg

Reputation: 3

I use this in scripts that require pasting a list of computer names:

$MultilineInput = Read-Host "Enter List of Computer Names"

$PCQueryList = @"
$MultilineInput
"@ -split '\r\n'

To confirm your pasted list are unique objects, run a $PCQueryList.count and $MultilineInput.count

arvdel1 arvdel2 arvdel3 arvdel4

$ $PCQueryList.count 4

$ $MultilineInput 1

Upvotes: 0

Phoenix2529
Phoenix2529

Reputation: 147

$cli= Read-Host("Enter the servers") $a=$cli.Replace("`n",",") $b=$a.split(",") foreach($i in $b) { write-host " hi" }

o/p:

Enter the servers: 1.1.1.1 2.2.2.2 3.3.3.3 hi hi hi

Upvotes: 1

Phoenix2529
Phoenix2529

Reputation: 147

I got the solution.My script will take input from clipboard in form of array and execute any command for each server.All i have to do is copy any number of servers from notepad and run the script Sample: $d = @(Get-Clipboard) foreach ($i in $d) { Write-Output "Hi" }

Upvotes: 1

user6811411
user6811411

Reputation:

It is unclear what you are after.

  • You simply can't paste an excel cell range into the powershell console prompt without getting a bunch of errors.
  • A PowerShell script could wait at an own prompt to accept input,
    • 1st problem here is that a single input (Read-Host) will be terminated by a cr/lf sequence what is what excel uses to separate adjacent ROWs. So you will need a loop to catch all pasted ROWs. 2nd problem is you don't know when all rows are processed.
    • When copying a range with multiple columns they are TAB separated

To process unknwon clipboard(text) content either with multiplte cols/rows I'd use (as suggested in the comments) Get-Clipboard and use the RegEx based split operator with OR to split by either a TAB or CR/LF

paste excel columns

> (Get-Clipboard) -split '\t|\r?\n'
arvdel1
arvdel2
arvdel3
arvdel4

paste excel rows

> (Get-Clipboard) -split '\t|\r?\n'
arvdel1
arvdel2
arvdel3
arvdel4

paste excel clumns and rows

> (Get-Clipboard) -split '\t|\r?\n'
arvdel1
arvdel2
arvdel3
arvdel4
arvdel2
C3
D3
E3
arvdel3
C4
D4
E4
arvdel4
C5
D5
E5

Instead of outputting to the console you can of course assign to a variable or directly append a ForEach-Object to process them.

Edit: an alternative reading the above excel file directly from PowerShell
using DFinke's ImportExcel module:

> import-excel .\Data.xlsx

ServersCol
----------
arvdel1
arvdel2
arvdel3
arvdel4

Upvotes: 1

Paweł Dyl
Paweł Dyl

Reputation: 9143

Here are the pieces you need:

try {
    $excel = New-Object -ComObject Excel.Application
    $workbook = $excel.Workbooks.Open('E:\Temp\Data.xlsx')
    $xlSheet = $excel.Sheets.Item('Sheet1')
    $a = @($xlSheet.Range("A1:A4").Value2)
    #prefered to use named range
    #$xlSheet.Range("Items").Value2
} finally {
    $workbook.Close()
    $excel.Quit()
    [Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
}

$a | sort | % {
  #process
  "Processing: $_"
}

Edit: To split string use -split:

'arvdel1,arvdel2,arvdel3,arvdel4' -split ','

Upvotes: 0

Related Questions