importing an excel file to python

Question

I have a basic question about importing xlsx files to Python. I have checked many responses about the same topic, however I still cannot import my files to Python whatever I try. Here's my code and the error I receive:

import pandas as pd

import xlrd

file_location = 'C:\Users\cagdak\Desktop\python_self_learning\Coursera\sample_data.xlsx'
workbook = xlrd.open_workbook(file_location)

Error:

IOError: [Errno 2] No such file or directory: 'C:\\Users\\cagdak\\Desktop\\python_self_learning\\Coursera\\sample_data.xlsx'

Stimulant

Your problem is that the file is not found, not a problem of importing: verify that the file is at the path you think it's at. – Monteiro

Hint: Did you verify that the xlsx file does exist in the location ? – Coffeng

Yes, it does. I am copying the folder path here : C:\Users\cagdak\Desktop\python_self_learning\Coursera and the name of the excel file is : sample_data – Stimulant

Answer 1

With pandas it is possible to get directly a column of an Excel file. Here is the code.

import pandas
df = pandas.read_excel('sample.xls')

#print the column names
print df.columns

#get the values for a given column
values = df['column_name'].values

#get a data frame with selected columns
FORMAT = ['Col_1', 'Col_2', 'Col_3']
df_selected = df[FORMAT]

Kata

Thanks but I still receive "IOError: [Errno 2] No such file or directory " error although I am 100% sure the file location and file name is correct. I spent almost 3 hours to do this. – Stimulant

Also need to do pip install xlrd – Triggerhappy

Answer 2

You should use raw strings or escape your backslash instead, for example:

file_location = r'C:\Users\cagdak\Desktop\python_self_learning\Coursera\sample_data.xlsx'

or

file_location = 'C:\\Users\\cagdak\\Desktop\python_self_learning\\Coursera\\sample_data.xlsx'

Keeling

@CagdasKanar Do you have read access to the file? What happens if you run python -c "import os; print(os.stat(r'C:\Users\cagdak\Desktop\python_self_learning\Coursera\sample_data.xlsx'))" ? – Keeling

Should I run this on notebook or terminal? – Stimulant

In a terminal for that exact command. If you still get a "file not found" error, I suspect the file just isn't where you think it is, or you don't have access to that file for python to read it. – Keeling

Answer 3

go ahead and try this:

file_location = 'C:/Users/cagdak/Desktop/python_self_learning/Coursera/sample_data.xlsx'

Felisha

Answer 4

As pointed out above Pandas supports reading of Excel spreadsheets using its read_excel() method. However, it is dependent upon a number of external libraries depending on which version Excel/odf is being accessed. It defaults to selecting one automatically, though one can be specified using the engine parameter. Here's an excerpt from the docs:

"xlrd" supports old-style Excel files (.xls).
"openpyxl" supports newer Excel file formats.
"odf" supports OpenDocument file formats (.odf, .ods, .odt).
"pyxlsb" supports Binary Excel files.

If the required library is not already installed you'll see an error message suggesting library you need to install.

Smoothshaven