0

I have 3 workboooks with single sheets.I need to combine all workbooks into single workbook with 3 sheets.

I tried the below code :

from pandas import ExcelWriter

writer = ExcelWriter("Sample.xlsx")

for filename in glob.glob("*.xlsx"):
    df_excel = pd.read_excel(filename,engine='openpyxl')

    (_, f_name) = os.path.split(filename)
    (f_short_name, _) = os.path.splitext(f_name)

    df_excel.to_excel(writer, f_short_name, index=False)

writer.save()

i got an error like "File is not zip File"

Sparrow
  • 15
  • 6

1 Answers1

1

"Sample.xlsx" is created in the same directory as the input workbooks and before you look for all files with glob.glob("*.xlsx"). Therefore you try to read "Sample.xlsx" which is your writer. This isn't working.

Make sure to only iterate over the real input workbooks e.g. like that:

import pandas as pd
from pandas import ExcelWriter
import glob
import os

writer = ExcelWriter("Sample.xlsx")
input_workbooks = glob.glob("*.xlsx")
input_workbooks.remove("Sample.xlsx")

for filename in input_workbooks:
    df_excel = pd.read_excel(filename,engine='openpyxl')

    (_, f_name) = os.path.split(filename)
    (f_short_name, _) = os.path.splitext(f_name)

    df_excel.to_excel(writer, f_short_name, index=False)

writer.save()

Better would be to save the output workbook ("Sample.xlsx") to another directory to avoid confusion. Obviously, when you do that, you do can not remove it from the list any longer, so just delete the line: input_workbooks.remove("Sample.xlsx")

SebastianB
  • 95
  • 1
  • 8
  • If i need to do same code but with some specifi path ,it wasnt working. Path="C"/100pipers" File="News" writer = ExcelWriter(Path+File+".xlsx") input_workbooks = glob.glob(Path+"*.xlsx") input_workbooks.remove(Path+File+".xlsx") Geting an error"List.remove(x):x not in list.... – Sparrow Apr 19 '22 at 17:27
  • Yeah, when you specify a different path, you can not remove it from the list, because it isnt' there. So just delete the line. – SebastianB Apr 28 '22 at 11:59
  • I declare the same path.You can see writer was also in "Path" folder and am.going through the files in "Path" folder only – Sparrow May 04 '22 at 13:02