CODEWITHSUNDEEP
powershell
DDC
DDC

Reputation: 23

Powershell script returns only first value in Excel range

I'm getting some errors while trying to read Excel spreadsheet. I'm trying to read values in cells B2 to B15, but am only getting back B2.

Can someone help fix my code, so that all the correct lines can be written as output? Please and thanks...

Excel Table

a-n

PowerShell Code

$objExcel = New-Object -ComObject Excel.Application
$workbook = $objExcel.Workbooks.Open("C:\administrator\Book1.xlsx")
$sheet = $workbook.Worksheets.Item("Sheet1")
$rowMax = ($sheet.UsedRange.Rows).count

$i = 2;
$data = $sheet.Cells.Item($i,2).text 
$get = @{"$data"="value"}

foreach ($Key in ($get.GetEnumerator()))  {
    $i++;
    $Key.name
}

Output

I just get the first line of output, a, rather than the expected 14 lines from row 2-15, with the output being a-n.

Upvotes: 1

Views: 1486

Answers (1)

Maxime Franchot
Maxime Franchot

Reputation: 1033

A solution that would save you a lot of time is to export from Excel into a CSV file, and in powershell you'll have really easy access to the data, and you could manipulate it really easily too. It doesn't look like your data is very complex, so I highly recommend you do this. However, here's a solution to the way you have it now:

Here lies the first problem in your code, though. As you can see, the $i is defined in this line: 

$i = 2;

And in your for loop, you write:

foreach ($Key in ($get.GetEnumerator()))  {
    $i++;
    $Key.name
}

Note that this is mildly confusing. Usually, for-loops are either Iterator-Based (as your's is, using in), or, they are Range-Based, using the common range iterator $i. In your case, $i is an iterator but is used outside the loop to get data from your sheet. This should not be the case. Hence, the solution would be to put some of the lines outside your loop into your loop:

$i = 2;
$get = @{"$data"="value"}
foreach ($Key in ($get.GetEnumerator()))  {
    $data = $sheet.Cells.Item($i,2).text 
    $i++;
    $Key.name
}

Unfortunately, after having tried this out, I realised that $get.GetEnumerator is a dictionary with only one line! This should be obvious from the way it is defined right above, I guess we both missed it. That explains why your code only ran once, rather than running more times, but only returning the first line (which I would have expected from before).

The solution is that you should change the for loop to be range-based. You already defined the variable you need for this, as rowMax. Here's all your code in working order:

$objExcel = New-Object -ComObject Excel.Application
$workbook = $objExcel.Workbooks.Open("C:\administrator\Book1.xlsx")
$sheet = $workbook.Worksheets.Item("Sheet1")
$rowMax = ($sheet.UsedRange.Rows).count

$get = @{"$data"="value"}

for ($i = 2; $i -le $rowMax; $i++)  {
    $data = $sheet.Cells.Item($i + 1, 2).text 

    $get[$data] = $sheet.Cells.Item($i + 1, 1).text
}

In dictionary $get, $data is your key according to:

$get = @{"$data"="value"}

However, you then add another variable called "$key". Is this not the same as $data? Or when you wrote $data, did you actually mean $value? I recommend you stick use either $data or $key as the dictionary key, but not both.

EDIT: changed the code according to what we discussed in the comments.

Upvotes: 2

Related Questions