-2

i have 3 xlsx sheet in particular directory.I am combining into workbook.While combining,i need to exclude specified file.

Path="C:/JackDaniels/100Pipers/"
name="Panic"
writer=ExcelWriter(Path+name+"*.xlsx")#creating a workbook in name "name")
inp=glob.glob(Path+"*.xlsx")
inp=inp.remove(Path+name+"*.xlsx")#to remove ths file to avoid overwrite

# I have a code that will combine sheets

when i tried to run the above code am getting below error

list.remove(x):x not in list
Sparrow
  • 15
  • 6
  • try to print out `Path+Exc_file+*.xlsx` and you will propably see why it can't find `x` – Rabinzel Apr 19 '22 at 16:16
  • @Rabinzel-it priints "C:/JackDaniels/100Pipers/S_P.xlsx" – Sparrow Apr 19 '22 at 16:37
  • no it doesn't. I knew it and I just checked it myself. if you run the line `print(Path+Exc_file+*.xlsx)` the output is `C:/JackDaniels/100Pipers/S_P.xlsx*.xlsx`. The `"*.xlsx"` at the end is wrong. – Rabinzel Apr 19 '22 at 16:57
  • @Rabinzel..Thanks for pointing out...I removed that.But again am getiing the same error – Sparrow Apr 19 '22 at 17:15
  • then you need to find out why it can't find it. `print(int)` and see if `C:/JackDaniels/100Pipers/S_P.xlsx` is in that list and if not you need to find out why. I mean the error is really clear. – Rabinzel Apr 19 '22 at 17:28

1 Answers1

1

The question is really not clear and you should rephrase it. If you are trying to combine them in the sense that you want to append all the three sheets to a new empty sheet (for example all of your sheets have same columns) you should make a python file in the same directory as your excel worksheets:

import copy
import os
import pandas as pd

cwd = os.getcwd()

# list to store files
xlsx_files = []
exc_file = 'Exclude.xlsx'  # <-- The file name you want to exclude goes here.
out_file = 'Output.xlsx'   # <-- The output file name goes here.
# Iterate directory
for file in os.listdir(cwd):
    # check only Excel files
    if file.endswith('.xlsx'):
        xlsx_files.append(file)
        
print("All xlsx files:", xlsx_files)

df = pd.DataFrame()
aux_files_var = copy.deepcopy(xlsx_files)
for file in aux_files_var:
    print(file)
    if file == exc_file or file == out_file: continue  # <-- here you exclude the file and the output
    df = df.append(pd.read_excel(file), ignore_index=True)
    xlsx_files.remove(file)
print(f"""As you can see, only exc_file remains in xlsx_files.
Remaining xlsx files:{xlsx_files}""")

print(df)
df.to_excel(out_file)
Pietro D'Antuono
  • 352
  • 1
  • 11