fetch

How to read Excel files with Python (xlrd tutorial)



Sharing buttons:

today we will be doing a tutorial on how

to read data out of an excel file with

Python and in order to do this we're

going to rely on a package called Excel

Rd which we have to download and install

so let's go ahead and do that first so

if you open up the command prompt and

just do a pip install it install Excel

Rd I have this already installed so I

will pass that part so next let's open

up idle

I don't

this window a little bit

okay so first thing we do is import xlrd

okay next we're going to specify the

location of our excel file so for me

it's going to be this data xlsx file so

file location is equal to C Drive

data darks

next me to open up the workbook so

workbook seeking to xlrd open for

that's another location

okay

and now with the workbook we can open up

the different sheets in our spreadsheet

so let me just show you the structure of

the spreadsheet file so you have this

one excel file with two sheets that

we're gonna open up sheet one so let's

do that now

so we're basically gonna do sheet is

equal to workbook not sheet by

index and everything is zero based in

xlrd

so if we want to open up the the first

sheet we actually have to send in zero

as the index okay

okay we want to spell work right so

let's try that again

and we got our sheet so now we can start

reading the data from the sheet so if

you want to read the very first value

say name again

everything is zero based here so if you

want to read data from column one you

actually have to pass in zero for the

column index so it's going to be zero

one two three and the same thing applies

for rows so this is row zero zero one

two three okay

so let's read out the very first value

which is going to be sheet

that's so value

firstly passing the row zero and then we

pass the column also gone zero and we

get back name

that is indeed the very first first

value

so something that's very useful is if

you want to know how many rows and

columns of sheet has so I can account

all the rows and all the columns you can

actually do something like sheet that n

rows so that's number of rows for and

it's also the same thing for columns or

sheet cut and call and we get three

columns so what happens if we want to

read a specific row example we want to

read out all the values for say row zero

we can loop over all the values in that

row like so let's say for

call in range

she

Oh

we can do something like that

so

you could be Rho zero

and columns going to be the call

let's put this up

and

okay so we get named favorite DOB so we

looped over everything there I'm going

to do the same thing for let's say Bob

go to

so we just type in the index of that row

and we got all the information for that

row

now let's try reading in all the data in

that sheet we can actually do that with

list comprehension so you do something

like theta is equal to well gee this

could be 2 of comprehension is going on

but it's follow-up so I could do sheet

that cell value R see

seeking range she thought and cause

okay

then four are in range and ropes

so what we get back is going to be a

list of lists check the type of data the

list and if you want to read the value

the first value of the sheet you could

do something like the zero

zero again we get named if you want to

read

let's see Alice's date of birth you do

0 1 and then 0 1 2 so 1 2 let's try that

data 1

to

okay this is actually XLR DS way of

representing dates so we actually would

have to check what kind of values in

this field and then we can convert this

into the proper format so let's just do

that next so how would we check if a

field has a date well xlrd has a way to

check the type of cell value that you're

dealing with so we can try this again so

let's do

something like X D

not so I

okay

my bad supposed to be sheet

to give it

actually what I think I'm supposed to do

is something like this douchey that's so

type

one - okay so we get back this integer

and xlrd when you call cell type

you always get some kind of number and

that number actually represents

something and then in order to know what

that means you have to go through and

check the box so you want to go to this

site and this page and scroll down to

sub the sub class and then the type

number will tell you what kind of values

in that cell so we got cell pipe three

so three is a date and that is indeed a

date

okay let's make this more useful by

converting this into a Python date/time

so first let's extract the value from

that cell so we could do so I'm to sheet

that's so value and give you one okay so

now we want to convert this into a time

to pull some time pull two XLR d XL d

as

so then we pass in the excel pan

variable and date mode will be zero okay

so now we can take the time to pull and

pass it into a Python date/time method

and get back the date/time object

so let's import a time so let's do a

print a time that time

not my dad

get all the stuff out of our table

okay there you go so 1990

there yep

that is indeed our date okay well thank

you for watching this tutorial hope it

wasn't too messy if you have any

questions feel free to ask in the in the

comment section below and see you next

time