CODEWITHSUNDEEP
python-3.xxlrd
Robin
Robin

Reputation: 69

how to extract data into list of dicts from excel spreadsheet in python?

I am trying to parse an excel spreadsheet using xlrd python library.

enter image description here

Above picture is a sample data from my spreadsheet. I am trying to parse this data like this 'genders': [{'gender': 'male', 'country': 'USA'}, {'gender': 'female', 'country': 'canada'}] but couldnt quite get at it.

I have tried data = [[sheet.cell_value(r, c) for c in range(sheet.ncols)] for r in range(sheet.nrows)] but I dont see the data that I am looking for.

Could someone please help.

Upvotes: 0

Views: 186

Answers (1)

gilito
gilito

Reputation: 309

Your code is good for reading xls file. But you want to serialize your data. You specified how must output be, this is the procedure of serialize data, so... Here a quick and basic solution.

# Reading an excel file using Python
import xlrd

# Give the location of the file.
loc = ("file.xls")

# To open Workbook
wb = xlrd.open_workbook(loc)

# no comment about sheets, understant that you have only one sheet, set 0 ( first) 
sheet = wb.sheet_by_index(0)

# output is the serialized object
output = {'genders': []}

# we will iterate over rows, skipping first that is header ( 0+1 )
for i in range(0+1, sheet.nrows):
    # As first column is empty, we will assing to _
    _, gender, country = sheet.row_values(i)
    
    # filling serialized object
    output['genders'].append({'gender': gender,
                              'country': country})

print(output)

My output:

/home/gil/PycharmProjects/testing/venv/bin/python /home/gil/PycharmProjects/testing/soxlrd.py
{'genders': [{'gender': 'caca', 'country': 'bebe'}, {'gender': 'cece', 'country': 'dede'}, {'gender': 'ee', 'country': 'ff'}, {'gender': 'gg', 'country': 'hh'}]}

Process finished with exit code 0

Upvotes: 1

Related Questions