Skip to main content

The XLRD python module for reading the spreadsheet documents - Introduction

xlrd module for python is an awesome module and I think it is a must for each and every pythonist to have a basic knowledge of this module. This module reads the data in the excel sheets or sometimes called as spreadsheets and thus the data can be used for computation(s) in our python scripts.

But a serious problem I have faced is, when I wanted to use this module it had a documentation which was not in the style of readthedocs. There was one xlrd readthedocs but at the time of writing this post it was empty. Eventhough I searched the internet(google) and have learnt on this module myself, I wanted to create and share my knowledge as a documentation for this module in the form of readthedocs not using the sphinx but manually writing each and everything. A harley davidson style.

Encoding used

This module(xlrd) uses unicode encoding. It is known that from Excel 97 onwards the data is stored in unicode format but the prior versions had some other encoding. But don't worry xlrd will read the data and convert it to unicode. If xlrd is not able to recognize the encoding then it will assume that the encoding is ASCII. If the encoding is not ASCII then it will raise a UnicodeDecodeError, in which case the user has to tell the encoding in which the excel sheet has been saved. We will see how you will supply the encoding format in upcoming sections.

Dates in Excel Spreadsheets

You might think that there are date objects in the excel sheets like in the same way we have python date objects. But in reality it has been found that there are no such data types. Dates are stored as float point numbers. Similar to the Unix time stamp where the value is counted from 00:00:00 Coordinated universal Time, January 1 1970, in excel sheets on windows the dates are stored from January 1 1970, 00:00:00 UTC, and on Macintosh the dates are stored from January 1, 1904, 00:00:00 UTC. Don't worry most of the times xlrd module will look after these things and you don't have to do any extra coding. We will see how to get the date values in excel sheets in further posts.
If you want to know more about the problems that the xlrd development team faced because of the excel sheets storing the data in float type format, you can have a look here: Dates in excel spreadsheets. I feel that it is worth looking at it because if you get any error in your program then you can Handle the errors easily.
All the remaining things are easy to understand, when you will be reading the docs, I will explain them with the code so that you can understand it very easily. Don't worry I assure you that if you can spend some time with this documentation you will gain a good knowledge of xlrd python module.

As always I have tried to explain all the things in this post in a way that it is easy to understand for everyone. In case if you didn't understand anything or have any doubt then comment in the comment box below and I will reply to your comment as soon as possible. You can also contact me from here: Contact me
Also please comment on how I can improve this post such that even the beginners will be able to understand this post easily. Tell me if I have made any typo or have made any mistake or if you want me to add anything which I have missed.
"Knowledge is gained by sharing the knowledge, not hoarding it."
Thank you, Have a nice day :)

Labels:

Popular posts from this blog

Making a quiz web app with python and flask

Edit : When you are creating a web app with h tml templates, then y ou will have to sa ve the html file in templates folder in the Current Wor ki ng Directory( CWD). If you save the file in the C W D directl y you will get a TemplateNotFound error. Thank you Udhay for pointing it out.   In this post we will create a quiz website using python . I will be using the flask framework . After reading this tutorial you will learn form submission , flask templates , python code in flask templates , shuffling the questions and options with the random module and few others.  Please note that this tutorial is not big as it seems to be. Some of the code has been rewritten to maintain consistency and also font size is somewhat big so that your eyes won't get stressed reading this tutorial. Also the content has not occupied the full width of the page. In this tutorial I am assuming that you are having a very basic understanding of the flask framework . Please refer the documentation
Read more

Problem 11 Project Euler Solution with python

Largest product in a grid In the 20×20 grid below, four numbers along a diagonal line have been marked in red. 08 02 22 97 38 15 00 40 00 75 04 05 07 78 52 12 50 77 91 08 49 49 99 40 17 81 18 57 60 87 17 40 98 43 69 48 04 56 62 00 81 49 31 73 55 79 14 29 93 71 40 67 53 88 30 03 49 13 36 65 52 70 95 23 04 60 11 42 69 24 68 56 01 32 56 71 37 02 36 91 22 31 16 71 51 67 63 89 41 92 36 54 22 40 40 28 66 33 13 80 24 47 32 60 99 03 45 02 44 75 33 53 78 36 84 20 35 17 12 50 32 98 81 28 64 23 67 10 26 38 40 67 59 54 70 66 18 38 64 70 67 26 20 68 02 62 12 20 95 63 94 39 63 08 40 91 66 49 94 21 24 55 58 05 66 73 99 26 97 17 78 78 96 83 14 88 34 89 63 72 21 36 23 09 75 00 76 44 20 45 35 14 00 61 33 97 34 31 33 95 78 17 53 28 22 75 31 67 15 94 03 80 04 62 16 14 09 53 56 92 16 39 05 42 96 35 31 47 55 58 88 24 00 17 54 24 36 29 85 57 86 56 00 48 35 71 89 07 05 44 44 37 44 60 21 58 51 54 17 58 19 80 81 68 05 94 47 69 28 73 92 13 86 52 17 77 04 89 55 40 04 52 08 83 97 35 99 16 07
Read more

Problem 60 Project Euler Solution with python

Prime pair sets The primes 3, 7, 109, and 673, are quite remarkable. By taking any two primes and concatenating them in any order the result will always be prime. For example, taking 7 and 109, both 7109 and 1097 are prime. The sum of these four primes, 792, represents the lowest sum for a set of four primes with this property. Find the lowest sum for a set of five primes for which any two primes concatenate to produce another prime. This problem is j u st a brute force problem. If you have come here because you don't know the limit upto which you will h ave to gener ate the prime numbers t hen go ahe ad and t r y with 10,000 . When I first start ed solving the problem I chose 1 million(beca use most of the problem s on project E uler have this limit ), but it took very long for the computer to fin d the solution. After searching on the internet then I found many people choosing 10, 000 so I have changed my in put f rom 1 million to 10000 and the output was f ast. He
Read more