Use Python's xlrd library to extract multiple columns or rows

Here's a quick demo of how to use Python to pull a designated range from a spreadsheet.

In this example I'm working with this spreadsheet:

Import the the xlrd library

>>> import xlrd

Set the location of the file you are analyzing: >>> loc = ("C:\FooFolder\Cities.xlsx")

Access the workbook:

>>> wb = xlrd.open_workbook(loc)

Designate the worksheet number you're reviewing:

>>> sheet = wb.sheet_by_index(0)

Get a column heading: >>> sheet.cell_value(0,0)

'City'

Get the total number of rows on the worksheet with data, and then print just the data in those rows from the first column

>>> for i in range(sheet.nrows): print(sheet.cell_value(i,0))

City New York Los Angeles Chicago Houston Philadelphia Phoenix Montreal Toronto Mexico City >>>

Varying the script this way, will get all of the column headings:

>>> for x in range(sheet.ncols): print(sheet.cell_value(0,x))

City State Country

Check out Geeks for Geeks for more excellent Python tips.

Count number of columns and rows in an Excel file

Here's a quick demo on how to count the number of rows and columns in an Excel file using Python.

First open the command prompt in the scripts folder for Python. Enter this command to get the xlrd library:

pip install xlrd

We'll analyze this Excel file:

Import the xlrd module >>> import xlrd

set the location of the file you want to analyze: >>> loc = ("C:\FooFolder\Cities.xlsx")

identify it was as an Excel workbook >>> wb = xlrd.open_workbook(loc)

select the sheet you want to review >>> sheet = wb.sheet_by_index(0)

we can read a particular cell's value: >>> sheet.cell_value(0,0) 'City'

Count the total number of rows: >>> print(sheet.nrows) 7

. . . and the total number of columns: >>> print(sheet.ncols) 1 >>>

Installing Pandas

Pandas is a data manipulation library for Python. Among other things it can be used to sort Excel spreadsheets.

Not that it will not be possible to install Pandas on Python 3.4 and lower. If you try to install pandas with these versions you will get an error:

Upgrade to the latest version (3.7.2.) to install pandas. Be sure to use the 32 bit or 64 bit version as appropriate. After it is installed, open command prompt in the scripts subfolder of the directory where Python was installed. Enter the command:

pip install pandas

Pandas will be installed. Now when using Python 3.7.2, you will be able to successfully import pandas: