Read excel file in python using pandas

Question

I am trying to read excel file in pycharm using pandas. I installed the package successfully. My issue is that I am trying to use file location in addition to its name I tried many thing as follow:

import pandas as pd
fileLocation = "C:\\Users\\GTS\\Desktop\\Network Interdiction Problem\\Manuscript\\Interdiction_Data.xlsx"
fileName = 'Interdiction_Data.xlsx'
data = pd.read_excel('fileLocation'+'fileName')

However I keep receiving the following error

Traceback (most recent call last):
  File "C:/Users/GTS/PycharmProjects/Reliability1/Reliability1.py", line 4, in <module>
    data = pd.read_excel('fileLocation'+'fileName')
  File "C:\Users\GTS\PycharmProjects\Reliability1\venv\lib\site-packages\pandas\io\excel\_base.py", line 304, in read_excel
    io = ExcelFile(io, engine=engine)
  File "C:\Users\GTS\PycharmProjects\Reliability1\venv\lib\site-packages\pandas\io\excel\_base.py", line 824, in __init__
    self._reader = self._engines[engine](self._io)
  File "C:\Users\GTS\PycharmProjects\Reliability1\venv\lib\site-packages\pandas\io\excel\_xlrd.py", line 21, in __init__
    super().__init__(filepath_or_buffer)
  File "C:\Users\GTS\PycharmProjects\Reliability1\venv\lib\site-packages\pandas\io\excel\_base.py", line 353, in __init__
    self.book = self.load_workbook(filepath_or_buffer)
  File "C:\Users\GTS\PycharmProjects\Reliability1\venv\lib\site-packages\pandas\io\excel\_xlrd.py", line 36, in load_workbook
    return open_workbook(filepath_or_buffer)
  File "C:\Users\GTS\PycharmProjects\Reliability1\venv\lib\site-packages\xlrd\__init__.py", line 111, in open_workbook
    with open(filename, "rb") as f:
FileNotFoundError: [Errno 2] No such file or directory: 'fileLocationfileName'

Any Idea?

Thanks in advance

Answer 1

Your fileLocation variable includes the name of the file. reading fileLocation + fileName is essentially reading

C:\\Users\\GTS\\Desktop\\Network Interdiction Problem\\Manuscript\\Interdiction_Data.xlsxInterdiction_Data.xlsx

Another issue is that you have quotation marks around your variable names when calling pd.read_excel() meaning that you are passing a string to the function.

Try:

data = pd.read_excel(fileLocation)

Answer 2

I'm seeing some issues in your code... First, let's start with the pd.read_excel() documentation: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html

It says there if the file is saved in your current working directory, just pass the file name to your function.

import pandas as pd
fileName = 'Interdiction_Data.xlsx'
data = pd.read_excel(fileName) # notice that when we call an object, we didn't use quotation marks.

However, if your file is not in your current directory, just pass the path to the file or as you called "filelocation".

import pandas as pd
fileLocation = "C:\\Users\\GTS\\Desktop\\Network Interdiction Problem\\Manuscript\\Interdiction_Data.xlsx"
data = pd.read_excel(fileLocation) # notice that when we call an object, we didn't use quotation marks.

Tip: To avoid some bugs or issues with file paths or directory names, try not to use space so separate the words...

I hope you solve your problem.

Answer 3

openpyxl is a Python library to read/write Excel 2010 xlsx/xlsm/xltx/xltm files.

Install it like this

pip install openpyxl

Example code

df = pd.read_excel('File_name.xlsx', engine = 'openpyxl')