Looking to load Excel values into a dictionary

Question

I'm trying to load an Excel file into a Python dictionary and have it follow a specific format.

Here is the Excel file:

Here is the dict I would like to output:

sheet1  = {'WLE-1-101':{'Max Height':8.19, 'Max Width':4.0, 'Laps':1},
           'WLE-1-102':{'Max Height':8.83, 'Max Width':4.0, 'Laps':2},
           'WLE-1-103':{'Max Height':9.47, 'Max Width':4.0, 'Laps':2}}

I have this code so far:

import xlrd
import pprint


loc = ('C:\example.xlsx')
wb = xlrd.open_workbook(loc)
sheet = wb.sheet_by_name('Sheet1')
sheet1 = {}

for i in range(sheet.nrows):
    if i >= 1:
        row = sheet.row_values(i)
        for cell in row:
            print(cell)

Which outputs this:

WLE-1-101
8.19
4.0
30.95
143.13
1
1.0


WLE-1-102
8.83
4.0
32.92
152.28
1
2.0


WLE-1-103
9.47
4.0
34.91
161.45
1
2.0


WLE-1-104
2.0
4.0
6.99
32.33
1
1.0


WLE-2-105
2.0
3.9
7.67
35.45
0
1.0


WLE-2-106
10.0
4.0
38.23
176.83
1
2.0


WLE-2-107
10.0
4.0
38.7
178.99
1
2.0


WLE-2-108
10.0
4.0
38.19
176.65
1
2.0


WLE-2-109
2.0
3.9
7.66
35.42
0
1.0


WLE-3-110
2.0
4.0
6.99
32.35
1
1.0


WLE-3-111
9.47
4.0
34.92
161.5
1
2.0


WLE-3-112
8.83
4.0
32.92
152.27
1
2.0


WLE-3-113
8.19
4.0
30.92
143.02
1
1.0

Can you provide the xlsx data as a csv or strip the example down to a few lines of data? [Images of code/data](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question) are really difficult to work with. I'd have to type this in by hand if I want to run it. Thanks. — ggorlen, Dec 31 '19 at 17:44

score 0 · Accepted Answer · answered Dec 31 '19 at 17:51

0

You can group your data like this:

for i in range(sheet.nrows):
    if i >= 1:
        wle, height, width, sq, weight, wide, laps = sheet.row_values(i)
        sheet1[wle] = {
          'Max Height': height,
          'Max Width': widht,
          'Laps': laps
        }

print(sheet1)

answered Dec 31 '19 at 17:51

marcos

4,473
1
10
24

That looks like a good approach, but it gives me a value error: – bmorton Dec 31 '19 at 21:07
wle,height,width,sq,weight,wide,laps = sheet.row_values(i) ValueError: too many values to unpack (expected 7) I tried .split(","), but got AttributeError: 'list' object has no attribute 'split' Any suggestions would be appreciated. – bmorton Dec 31 '19 at 21:39
The problem is with your data then, because if each row contains those values they should be able to be unpacked. `print(sheet.row_values(i))` to see if all rows have those values. @bmorton – marcos Dec 31 '19 at 22:08
Good tip! Changing the file format to xlsm made it work. Thanks! – bmorton Dec 31 '19 at 23:19

Looking to load Excel values into a dictionary

1 Answers1