I am trying to import Excel file in Python using Pandas. I am working in Spyder.
Below code is copied from a website and edited to suit my requirement.
Hi,
It is working now. Thanks. Did below changes to the code.
import pandas as MyPandas
MyExcel = MyPandas.read_excel (r’\Dummy\Dummy\Dummy\Dummy\ExcelPandasPythonExample.xls’)
print (MyExcel)
However facing one issue here, my excel file has 25 columns but I can see only two columns are imported in Python. First and last. There are three dots between first and last column of the imported excel. I can see [1338 rows x 25 columns] at the end of imported file. Also only few rows are visible from the imported file.
Can anyone please help me understand how do I import entire worksheet.
The first problem was caused by invalid file path - your excel file was in a different directory, hence Dummy\Dummy helped.
I assume you’re printing the pandas dataframe to the console. If that’s the case, then the “three dots” are a way to show that data is there but the output cannot fit into the console.
Once you load the excel file, try printing specific column instead.
@SachinBizboy
INSERT this line of code after importing pandas into your code
import pandas as pd
pd.set_option(“display.width”,None)
…your rest code and all the columns will be displayed
hope this was helpful
When you open a file with the file name , you are telling the open() function that your file is in the current working directory. This is called a relative path. If the user does not pass the full path to the file (on Unix type systems this means a path that starts with a slash), the path is interpreted relatively to the current working directory. The current working directory usually is the directory in which you started the program. A good start would be validating the input. In other words, you can make sure that the user has indeed typed a correct path for a real existing file, like this:
while not os.path.isfile(fileName):
fileName = input("Whoops! No such file! Please enter the name of the file you'd like to use.")
Another way to tell the python file open() function where your file is located is by using an absolute path, e.g.:
The problem is that the path is invalid, the directory as descripted, does not exist.
In addition to the options explained above. You can also try to convert your xls file into csv. Then you only apply the classic:
import pandas as pd
df=pd.read_csv(“ExcelPandasPythonExample”)
In Python, when you reference a file, it needs to exist. Otherwise, Python will return a FileNotFoundError: [Errno 2] No such file or directory .
The possible reasons for this error could be as follows.
Misspelled file name
The error will often occur due to misspelled filenames, so providing the correct file name would solve the issue.
Invalid file path or directory path
Sometimes you might give a wrong file path or directory path which does not exist. It usually happens even with the network path when it’s unreachable. So ensure that the file path is correct and if you are placing the file in the network path, make sure it’s reachable and accessible.
Using a relative path
If you use a relative path, the file would be searched in the current working directory** and not in the original path. So ensure you give an absolute path of the file to resolve the error.
List all the files in a specified directory
import os
for f in os.listdir("C:/Projects/Tryouts/etc"):
print(f)
Note that r"\Dummy" in Windows is relative to the drive or share of the current working directory. For example, if the current working directory is r"\\server\share\spam\eggs", then r"\Dummy" resolves to r"\\server\share\Dummy".
ntpath.isabs() (i.e. os.path.isabs() in Windows) returns true for a path such as r"\Dummy", which has a root directory but no drive or share. This misbehavior will probably never be fixed. pathlib.WindowsPath.is_absolute() implements the check correctly to require both a drive/share and a root directory, which is in agreement with C++ std::filesystem::path::is_absolute().
