Unable to import .xlsx into Python: No such file or directory

Question

I'm trying to import data from HW3_Yld_Data.xlsx into Python. I made sure that the Excel file is in the same directory as the Python file. Here's what I wrote:

import pandas as pd

Z = pd.read_excel('HW3_Yld_Data.xlsx')

Here's the error I got:

In [2]: import pandas as pd
   ...: 
   ...: Z = pd.read_excel('HW3_Yld_Data.xlsx')
Traceback (most recent call last):

  File "<ipython-input-2-7237c05c79ba>", line 3, in <module>
    Z = pd.read_excel('HW3_Yld_Data.xlsx')

  File "/Users/Zhengnan/anaconda/lib/python2.7/site-packages/pandas/io/excel.py", line 151, in read_excel
return ExcelFile(io, engine=engine).parse(sheetname=sheetname, **kwds)

  File "/Users/Zhengnan/anaconda/lib/python2.7/site-packages/pandas/io/excel.py", line 188, in __init__
self.book = xlrd.open_workbook(io)

  File "/Users/Zhengnan/anaconda/lib/python2.7/site-packages/xlrd/__init__.py", line 394, in open_workbook
f = open(filename, "rb")

IOError: [Errno 2] No such file or directory: 'HW3_Yld_Data.xlsx'

What's mind-boggling is that it used to work fine. It appeared to stop working after I did a "conda update --all" yesterday.

BTW I'm using Spyder as IDE. Please help. Thank you.

Solution 1

Each process in the operating system has a current working directory. Any relative path is relative to the current working directory.

The current working directory is set to the directory from which you launched the process. This is very natural when using the command-line, but get be confusing for people only using GUIs.

You can retrieve it using os.getcwd(), and you can change it using os.chdir(). Of course, you can also change it before launching your script.

Solution 2

Instead of using the relative path, use the full path of your xlsx for a test. Your conda update may have changed your environment.

You can try something like this in order to test it:

import os
pre = os.path.dirname(os.path.realpath(__file__))
fname = 'HW3_Yld_Data.xlsx'
path = os.path.join(pre, fname)
Z = pd.read_excel(path)