Merge multiple excel files into single file

Python Help
1

import pandas as pd
import glob

location = ‘C:\Users\oliver.r.caluag\Desktop\sample merge\merged file\*.xlsx’
excel_files = glob.glob(location)

pd.set_option(‘display.max_rows’, 91)

pd.set_option(‘display.max_columns’,9)

df1 = pd.DataFrame()

for excel_file in excel_files:
df2 = pd.read_excel(excel_file)
df1 = pd.concat([df1, df2], ignore_index=True)

df1.fillna(value=“N/A”, inplace=True)
df1.to_excel(‘C:\Users\oliver.r.caluag\Desktop\sample merge\merged file\all_sleep_data.xlsx’)

print(df1)

Error received:
C:\Users\oliver.r.caluag\PycharmProjects\pythonProject\venv\Scripts\python.exe “C:/Users/oliver.r.caluag/Desktop/python excel/all_sleep_data.py”
Traceback (most recent call last):
File “C:\Users\oliver.r.caluag\Desktop\python excel\all_sleep_data.py”, line 15, in
df2 = pd.read_excel(excel_file)
File “C:\Users\oliver.r.caluag\PycharmProjects\pythonProject\venv\lib\site-packages\pandas\util_decorators.py”, line 311, in wrapper
return func(*args, **kwargs)
File “C:\Users\oliver.r.caluag\PycharmProjects\pythonProject\venv\lib\site-packages\pandas\io\excel_base.py”, line 364, in read_excel
io = ExcelFile(io, storage_options=storage_options, engine=engine)
File “C:\Users\oliver.r.caluag\PycharmProjects\pythonProject\venv\lib\site-packages\pandas\io\excel_base.py”, line 1233, in init
self._reader = self._engines[engine](self._io, storage_options=storage_options)
File “C:\Users\oliver.r.caluag\PycharmProjects\pythonProject\venv\lib\site-packages\pandas\io\excel_openpyxl.py”, line 521, in init
import_optional_dependency(“openpyxl”)
File “C:\Users\oliver.r.caluag\PycharmProjects\pythonProject\venv\lib\site-packages\pandas\compat_optional.py”, line 118, in import_optional_dependency
raise ImportError(msg) from None
ImportError: Missing optional dependency ‘openpyxl’. Use pip or conda to install openpyxl.

Process finished with exit code 1

Why I am still receiving these errors, I installed openpyxl using pip install openpyxl and tested it fine?

Test for openpyxl:
Python 3.9.5 (tags/v3.9.5:0a7dcbd, May 3 2021, 17:27:52) [MSC v.1928 64 bit (AMD64)] on win32
Type “help”, “copyright”, “credits” or “license” for more information.

import openpyxl

2

My guess is that you have Python installed twice, once with PyCharm and
once without. So the without version has openpyxl installed but the
PyCharm version does not.

Try this in PyCharm:

import openpyxl

and see what it does. If it fails, try this:

import sys
print(sys.path)
print(sys.executable)

then do the same thing outside of PyCharm.

3

Oh, another thought!

It looks like you are using a venv in PyCharm. (Why???)

Try deleting the venv and recreating it. Or running it without an venv.

(I don’t understand why so many people insist on telling beginners to
use a really advanced feature like virtual environments.)

4

i tried
import openpyxl
and it gave me the same error message

i tried this in pycharm
import sys
print(sys.path)
print(sys.executable)

and it gave me this:

C:\Users\oliver.r.caluag\PycharmProjects\pythonProject\venv\Scripts\python.exe C:/Users/oliver.r.caluag/PycharmProjects/pythonProject/venv/test.py
[‘C:\Users\oliver.r.caluag\PycharmProjects\pythonProject\venv’, ‘C:\Users\oliver.r.caluag\PycharmProjects\pythonProject’, ‘C:\Users\oliver.r.caluag\AppData\Local\Programs\Python\Python39\python39.zip’, ‘C:\Users\oliver.r.caluag\AppData\Local\Programs\Python\Python39\DLLs’, ‘C:\Users\oliver.r.caluag\AppData\Local\Programs\Python\Python39\lib’, ‘C:\Users\oliver.r.caluag\AppData\Local\Programs\Python\Python39’, ‘C:\Users\oliver.r.caluag\PycharmProjects\pythonProject\venv’, ‘C:\Users\oliver.r.caluag\PycharmProjects\pythonProject\venv\lib\site-packages’, ‘C:\Users\oliver.r.caluag\PycharmProjects\pythonProject\venv\lib\site-packages\win32’, ‘C:\Users\oliver.r.caluag\PycharmProjects\pythonProject\venv\lib\site-packages\win32\lib’, ‘C:\Users\oliver.r.caluag\PycharmProjects\pythonProject\venv\lib\site-packages\Pythonwin’]
C:\Users\oliver.r.caluag\PycharmProjects\pythonProject\venv\Scripts\python.exe

Thanks Steven, what do you mean deleting venv and recreating it? Or running it without an venv?
Is this the root lib? Sorry I am really a true beginner and dont understand much of this. So glad of your help.

Process finished with exit code 0