CODEWITHSUNDEEP
excelpowershell
Starky
Starky

Reputation: 135

How to store a comma separated string from Excel workbook into a PowerShell variable

I have a list of values in multiple columns in the spreadsheet from an excel workbook. How do I extract those values and store them into a variable? The spreadsheet looks as follows:

   

  A          B          C

1  Col1    Col2    Col3

2  11        22        33

3  44        55        66

4  77        88        99

Here's the code I am using 

$file = "C:\demo.xlsx"

$sheetName = "Sheet1"

$objExcel = New-Object -ComObject Excel.Application

$workbook = $objExcel.Workbooks.Open($file)

$sheet = $workbook.Worksheets.Item($sheetName)

$objExcel.Visible=$false

$rowCount = ($sheet.UsedRange.Rows).count
   

for ($i=2; $i -le $rowCount-1; $i++)

{

     numberList += $sheet.Cells.Item($i,1).text -join "','"

     numberList += $sheet.Cells.Item($i,2).text -join "','"

     numberList += $sheet.Cells.Item($i,3).text -join "','"

}

$numberList

$objExcel.quit()

It's not concatenating values into single quotes & commas. Am I using -join at the wrong place?

Upvotes: 0

Views: 458

Answers (1)

Mathias R. Jessen
Mathias R. Jessen

Reputation: 174990

-join takes a collection of strings on the left-hand side, but in this expression:

$sheet.Cells.Item($i,1).text -join "','"

the left-hand side operand ($sheet.Cells.Item($i,1).text) is always going to be just a single string.

Wait with the quoting and concatenation until after you've collected all the cell values:

# enumerate all the cell values, store in $numberList
$numberList = for ($i=2; $i -le $rowCount-1; $i++)
{
    $sheet.Cells.Item($i,1).Text
    $sheet.Cells.Item($i,2).Text
    $sheet.Cells.Item($i,3).Text
}

# Now we quote them ...
$numberList = $numberList |ForEach-Object { "'${_}'" }

# ... and finally join them into one big string
$numberList -join ','

Upvotes: 1

Related Questions