Python – Cannot read an Excel file in pandas

jupyter-notebookpandaspython

I am using Juypter which I launch from the same directory which contains the notebook and Excel file. I use the following commands:

import pandas as pd
!ls      # Returns the contents of the working directory.  Use "dir" in a Windows.

Which returns:

Book1.xlsx
common_excel_tasks.ipynb

I then use the following commands:

excel_file = 'Book1.xlsx'
df1 = pd.read_excel(excel_file)

But I get the following error:

---------------------------------------------------------------------------
ImportError                               Traceback (most recent call last)
<ipython-input-3-28585effb1ed> in <module>
      1 excel_file = 'Book1.xlsx'
----> 2 df1 = pd.read_excel(excel_file)

/usr/local/lib/python3.7/site-packages/pandas/util/_decorators.py in wrapper(*args, **kwargs)
    206                 else:
    207                     kwargs[new_arg_name] = new_arg_value
--> 208             return func(*args, **kwargs)
    209 
    210         return wrapper

/usr/local/lib/python3.7/site-packages/pandas/io/excel/_base.py in read_excel(io, sheet_name, header, names, index_col, usecols, squeeze, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, verbose, parse_dates, date_parser, thousands, comment, skip_footer, skipfooter, convert_float, mangle_dupe_cols, **kwds)
    308 
    309     if not isinstance(io, ExcelFile):
--> 310         io = ExcelFile(io, engine=engine)
    311     elif engine and engine != io.engine:
    312         raise ValueError(

/usr/local/lib/python3.7/site-packages/pandas/io/excel/_base.py in __init__(self, io, engine)
    817         self._io = _stringify_path(io)
    818 
--> 819         self._reader = self._engines[engine](self._io)
    820 
    821     def __fspath__(self):

/usr/local/lib/python3.7/site-packages/pandas/io/excel/_xlrd.py in __init__(self, filepath_or_buffer)
     18         """
     19         err_msg = "Install xlrd >= 1.0.0 for Excel support"
---> 20         import_optional_dependency("xlrd", extra=err_msg)
     21         super().__init__(filepath_or_buffer)
     22 

/usr/local/lib/python3.7/site-packages/pandas/compat/_optional.py in import_optional_dependency(name, extra, raise_on_missing, on_version)
     91     except ImportError:
     92         if raise_on_missing:
---> 93             raise ImportError(message.format(name=name, extra=extra)) from None
     94         else:
     95             return None

ImportError: Missing optional dependency 'xlrd'. Install xlrd >= 1.0.0 for Excel support Use pip or conda to install xlrd.

Best Solution

You should install xlrd dependence.

pip install xlrd should do the trick for you.

Hope this helps!

Related Solutions

Python – How to check whether a file exists without exceptions

If the reason you're checking is so you can do something like if file_exists: open_it(), it's safer to use a try around the attempt to open it. Checking and then opening risks the file being deleted or moved or something between when you check and when you try to open it.

If you're not planning to open the file immediately, you can use os.path.isfile

Return True if path is an existing regular file. This follows symbolic links, so both islink() and isfile() can be true for the same path.

import os.path
os.path.isfile(fname)

if you need to be sure it's a file.

Starting with Python 3.4, the pathlib module offers an object-oriented approach (backported to pathlib2 in Python 2.7):

from pathlib import Path

my_file = Path("/path/to/file")
if my_file.is_file():
    # file exists

To check a directory, do:

if my_file.is_dir():
    # directory exists

To check whether a Path object exists independently of whether is it a file or directory, use exists():

if my_file.exists():
    # path exists

You can also use resolve(strict=True) in a try block:

try:
    my_abs_path = my_file.resolve(strict=True)
except FileNotFoundError:
    # doesn't exist
else:
    # exists

Python – How to copy a file in Python

shutil has many methods you can use. One of which is:

from shutil import copyfile
copyfile(src, dst)

# 2nd option
copy(src, dst)  # dst can be a folder; use copy2() to preserve timestamp

Copy the contents of the file named src to a file named dst. Both src and dst need to be the entire filename of the files, including path.
The destination location must be writable; otherwise, an IOError exception will be raised.
If dst already exists, it will be replaced.
Special files such as character or block devices and pipes cannot be copied with this function.
With copy, src and dst are path names given as strs.

Another shutil method to look at is shutil.copy2(). It's similar but preserves more metadata (e.g. time stamps).

If you use os.path operations, use copy rather than copyfile. copyfile will only accept strings.

Best Solution

Related Solutions

Python – How to check whether a file exists without exceptions

Python – How to copy a file in Python

Related Question