In this post we will see how to read Excel files with extensions:ods and . fods with Python and Pandas. Pandas offers a read_excel() method to read Excel files as a DataFrame.
Many options are available - you can read any sheet, all sheets, first one or range of data. Pandas read and convert any data stored in ODS format to the popular DataFrame format.
Let's assume the next ODS file stored on our local machine which needs to be read as a DataFrame or any othder Python format:
Step 1: Install Pandas and odfpy
Before reading the ODS files with Python we need to install additional packages like: odfpy + Pandas (if not installed or upgrade to latest).
To install odfpy + Pandas use next commands:
pip install pandas
pip install odfpy
Step 2: Read the first sheet of Excel(ODS) files
Now we can read the first sheet of the worksheet by calling Pandas method read_excel():
import pandas as pd
pd.read_excel('~/Desktop/animals.ods', engine='odf')
Which will result in:
| Rank | Animal | Maximum speed | Class | Notes | |
|---|---|---|---|---|---|
| 0 | 1 | Peregrine falcon | 389 km/h (242 mph)108 m/s (354 ft/s)[2][6] | Flight-diving | The peregrine falcon is the fastest aerial ani... |
| 1 | 2 | Golden eagle | 240–320 km/h (150–200 mph)67–89 m/s (220–293 f... | Flight-diving | Assuming the maximum size at 1.02 m, its relat... |
| 2 | 3 | White-throated needletail swift | 169 km/h (105 mph)[8][9][10] | Flight | NaN |
| 3 | 4 | Eurasian hobby | 160 km/h (100 mph)[11] | Flight | Can sometimes outfly the swift |
| 4 | 5 | Mexican free-tailed bat | 160 km/h (100 mph)[12] | Flight | It has been claimed to have the fastest horizo... |
| 5 | 6 | Frigatebird | 153 km/h (95 mph) | Flight | The frigatebird's high speed is helped by its ... |
| 6 | 7 | Rock dove (pigeon) | 148.9 km/h (92.5 mph)[13] | Flight | Pigeons have been clocked flying 92.5 mph (148... |
| 7 | 8 | Spur-winged goose | 142 km/h (88 mph)[14] | Flight | NaN |
| 8 | 9 | Gyrfalcon | 128 km/h (80 mph)[citation needed] | Flight | NaN |
Step 3: Pandas read excel sheet by name
To work with multiple sheets read_excel method request the path the the file and the sheet name:
pd.read_excel("animals.ods", sheet_name="Sheet1")
If the sheet name is missed then the first sheet will be read.
Step 4: Pandas read excel range of data
If you like to read only a specific range of data read_excel has several useful parameters for this purpose like:
- skiprows - line numbers to skip. If you like to start from row 6 than use -
skiprows=6 - nrows - how many rows to read. If you like to start with fewer rows for a test
- index_col - which column to be used as a index
- usecols - which columns to be read only a specific range of columns:
pd.read_excel('/home/vanx/Desktop/animals.ods', usecols="A:C")
To find more examples on how to read and import Excel files to Pandas DataFrame and Python check: Notebook -
Read Excel ODS with Python Pandas
Step 5: Pandas read excel - XLRDError: Openoffice.org ODS file; not supported
If you face error during reading the ODS or any other excel file with Python and Pandas:
XLRDError: Openoffice.org ODS file; not supported
Then you can update Pandas to the latest possible version and this will resolve the problem.
Pandas can be upgraded by:
pip install --upgrade pandas
In some cases you can specify the reading engine for pd.read_excel as shown in the example:
pd.read_excel('animals.ods', engine='odf')