Simple functions to collect data from excel files using xlrd and python

excel_as_a_form_use_xlrd_to_read_data_from_excelIt’s no secret. Excel is abused and misused in so many different ways and the most rough abuse is disregarding Excel’s inherent 2D table structure and using it to collect data as an excel based form.

Recently had a task where I faced two hundred or so such forms, from where needed to pull the data. Turned to my favourite hammer, Python.

And with xlrd, this was as easy as cutting a cake. Here’s three functions that did the job.

GetFilenames collects all the filenames including path given a top level folder. Getdata collects the required data from the given row, col and writeOut dumps all the collected data into a csv file as a 2D table.

from os import walk
from os.path import join
def GetFilenames(mypath):
    return (join(dirpath,f) for (dirpath, dirnames, filenames) in walk(mypath) for f in filenames if f.endswith(('.xls','.xlsx')))

import xlrd
def getdata(fname,listx,listy):
Reads data from excel sheet

fname excel file name
listx=[2,4,6,8,10,12,14,17,19,21,27,29,2,6,8,10,4] # Rows index
listy=[1,1,1,1,1,1,1,1,1,1,1,1,4,4,4,4,4]          # Column index

from which data has to be extracted

returns dataList containing the extracted info from excel.

    dataList=[]   # initial an empty list
    book=xlrd.open_workbook(fname) # open workbook
    sheet=book.sheet_by_index(0) # Get first sheet
    for x,y in zip(listx,listy): # walk to all the row,col combination and collect data
        if sheet.cell_type(x,y) <> 3: # if its not a data data
        else:                         # if its a date data
          datetup =xlrd.xldate_as_tuple(sheet.cell_value(x,y),book.datemode)
    return dataList

import csv
def writeOut(fname,files):
    f = open(fname,"wb")
    for f in files:
    print fname,"written !!"    
    return None

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s