Python: Simulating CSV.DictReader with OpenPyXL

I have an Excel (.xlsx) file that I'm trying to parse, row by row. I have a header (first row) that has a bunch of column titles like School, First Name, Last Name, Email, etc.

When I loop through each row, I want to be able to say something like:

row['School']

and get back the value of the cell in the current row and the column with 'School' as its title.

I've looked through the OpenPyXL docs but can't seem to find anything terribly helpful.

Any suggestions?

Excel sheets are far more flexible than CSV files so it makes little sense to have something like DictReader.

Just create an auxiliary dictionary from the relevant column titles.

If you have columns like "School", "First Name", "Last Name", "EMail" you can create the dictionary like this.

keys = dict((value, idx) for (idx, value) in enumerate(values))
for row in ws.rows[1:]:
    school = row[keys['School'].value

python: Python: Simulating CSV.DictReader with OpenPyXL, I have an Excel (.xlsx) file that I'm trying to parse, row by row. I have a header (first row) that has a bunch of column titles like School, First Name  Description. A module that maps the information in each row in an openpyxl worksheet to a dict whose keys are given by the optional fieldnames parameter, similar to Python’s native csv.DictReader.

I'm not incredibly familiar with OpenPyXL, but as far as I can tell it doesn't have any kind of dict reader/iterator helper. However, it's fairly easy to iterate over the worksheet rows, as well as to create a dict from two lists of values.

def iter_worksheet(worksheet):
    # It's necessary to get a reference to the generator, as 
    # `worksheet.rows` returns a new iterator on each access.
    rows = worksheet.rows

    # Get the header values as keys and move the iterator to the next item
    keys = [c.value for c in next(rows)]
    for row in rows:
        values = [c.value for c in row]
        yield dict(zip(keys, values))

excel - Python: Simulating CSV.DictReader with OpenPyXL -, excel sheets far more flexible csv files makes little sense have dictreader. just create auxiliary dictionary relevant column titles. if have columns  How to create, read, update and search through Excel files using Python. csv.DictReader is explained more in the official Python I build this solution using Python 3 and openpyxl library.

I wrote DictReader based on openpyxl. Save the second listing to file 'excel.py' and use it as csv.DictReader. See usage example in the first listing.

with open('example01.xlsx', 'rb') as source_data:
    from excel import DictReader

    for row in DictReader(source_data, sheet_index=0):
        print(row)

excel.py:

__all__ = ['DictReader']

from openpyxl import load_workbook
from openpyxl.cell import Cell

Cell.__init__.__defaults__ = (None, None, '', None)   # Change the default value for the Cell from None to `` the same way as in csv.DictReader


class DictReader(object):
    def __init__(self, f, sheet_index,
                 fieldnames=None, restkey=None, restval=None):
        self._fieldnames = fieldnames   # list of keys for the dict
        self.restkey  = restkey         # key to catch long rows
        self.restval  = restval         # default value for short rows
        self.reader   = load_workbook(f, data_only=True).worksheets[sheet_index].iter_rows(values_only=True)
        self.line_num = 0

    def __iter__(self):
        return self

    @property
    def fieldnames(self):
        if self._fieldnames is None:
            try:
                self._fieldnames = next(self.reader)
                self.line_num += 1
            except StopIteration:
                pass

        return self._fieldnames

    @fieldnames.setter
    def fieldnames(self, value):
        self._fieldnames = value

    def __next__(self):
        if self.line_num == 0:
            # Used only for its side effect.
            self.fieldnames

        row = next(self.reader)
        self.line_num += 1

        # unlike the basic reader, we prefer not to return blanks,
        # because we will typically wind up with a dict full of None
        # values
        while row == ():
            row = next(self.reader)

        d = dict(zip(self.fieldnames, row))
        lf = len(self.fieldnames)
        lr = len(row)

        if lf < lr:
            d[self.restkey] = row[lf:]
        elif lf > lr:
            for key in self.fieldnames[lr:]:
                d[key] = self.restval

        return d

openpyxl-dictreader · PyPI, row in an openpyxl worksheet to a dict whose keys are given by the optional fieldnames parameter, similar to Python's native csv.DictReader. Python CSV DictReader The csv.DictReader class operates like a regular reader but maps the information read into a dictionary. The keys for the dictionary can be passed in with the fieldnames parameter or inferred from the first row of the CSV file.

python csv reader Code Example, Get code examples like "python csv reader" instantly right from your google search results with the Grepper Chrome Extension. The so-called CSV (Comma Separated Values) format is the most common import and export format for spreadsheets and databases. CSV format was used for many years prior to attempts to describe the format in a standardized way in RFC 4180.

Csv functions in python, Python comes with a module to parse csv files, the csv module. You can use the csv module's reader function or you can use the DictReader class. 3. For your final project, you'll read in simulated social media data from a file, compute With each way, we use one of these module: xlwt, xlsxwriter, openpyxl and pandas. The unicodecsv is a drop-in replacement for Python 2.7’s csv module which supports unicode strings without a hassle. Supported versions are python 2.7, 3.3, 3.4, 3.5, and pypy 2.4.0.

openpyxl convert CSV to EXCEL, create excel file in python using openpyxl Workbook() ws = wb.active f = open('​file.csv') reader Convert Excel file to CSV file using openpyxl With this method,  This article will show in detail how to work with Excel files and how to modify specific data with Python. First we will learn how to work with CSV files by reading, writing and updating them. Then we will take a look how to read files, filter them by sheets, search for rows/columns, and update cells of xlsx files. Let’s start with the simplest spreadsheet format: CSV. Part 1 — The CSV

Comments
  • have you tried using read_excel from pandas?
  • I also want to use such a convenient function. So far, I'm using ordereddict to help me solve the problem. If you find any way more convenient, please share with us.
  • Thanks for the reply; I'm a Python newbie so could you elaborate a bit more on your answer?
  • What is unclear? You can update the question in the light of the answer.