Adding data from different data frame to excel

Question

Currently what I want to do is take data I have from a data frame list and add them to an existing excel file as their own tabs.

To test this out, I have tried it with one data frame. There are no error but when I go to open the excel file it says it is corrupt. I proceed to recover the information but I rather not have to do that every time. I believe it would fail if I looped through my list to make this happen.

    import os,glob
    import pandas as pd
    from openpyxl import load_workbook
     
    master_file='combined_csv.xlsx'
    #set the directory
    os.chdir(r'C:\Users\test') 
    #set the type of file
    extension = 'csv' 
    #take all files with the csv extension into an array
    all_filenames = [i for i in glob.glob('*.{}'.format(extension))]
    col_to_keep=["Name",
                 "Area (ft)",
                 "Length (ft)",
                 "Center (ft)",
                 "ID",
                 "SyncID"]
        
    combine_csv = pd.concat([pd.read_csv(f, delimiter=';', usecols=col_to_keep) for f in all_filenames])
    combine_csv.to_excel(master_file, index=False,sheet_name='All')
    # Defining the path which excel needs to be created
    # There must be a pre-existing excel sheet which can be updated
    FilePath = r'C:\Users\test'
     
    # Generating workbook
    ExcelWorkbook = load_workbook(FilePath)
     
    # Generating the writer engine
    writer = pd.ExcelWriter(FilePath, engine = 'openpyxl')
     
    # Assigning the workbook to the writer engine
    writer.book = ExcelWorkbook
     
     
    # Creating first dataframe
    drip_file = pd.read_csv(all_filenames[0], delimiter = ';', usecols=col_to_keep)
    SimpleDataFrame1=pd.DataFrame(data=drip_file)
    print(SimpleDataFrame1)
     
     
    # Adding the DataFrames to the excel as a new sheet
    SimpleDataFrame1.to_excel(writer, sheet_name = 'Drip')
    
    writer.save()
    writer.close()

It seems like it runs fine with no errors but when I open the excel file I get the error shown below.

Does anyone see something wrong with the code that would cause excel to give me this error?

Thank you in advance

Rasmus · Accepted Answer · 2021-10-11 08:38:33Z

1

Your code knows its printing data to the same workbook, but to use writer you will also need to tell python what the sheet names are:

book = load_workbook(your_destination_file)
writer = pd.ExcelWriter(your_destination_file, engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)  # tells 
pandas/python what the sheet names are

Your_dataframe.to_excel(writer, sheet_name=DesiredSheetname)

writer.save()

Also, if you have pivots, pictures, external connections in the document they will be deleted and could be what is causing the corruption.

answered Oct 11, 2021 at 8:38

Rasmus

1369 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

rcheeks23 Over a year ago

I'm not fulling grasping what is being done here. I'm a little confused on why or how python will know the sheet name if it doesn't exist yet? I thought this line was what created the new sheet.? Your_dataframe.to_excel(writer, sheet_name=DesiredSheetname)

Rasmus Over a year ago

I see. In that case, chances are there's some content in your file that isn't compatible with pandas. What is the contents of your file besides the dataframes you are slotting in?

rcheeks23 Over a year ago

thank you for helping! i looked more into that line where it tells panda/python what the sheet names are and was able to get it to work by first creating the file with all the data concatenated into the first sheet and used that to make all the separate sheets that i needed. Thanks again!

Collectives™ on Stack Overflow

Adding data from different data frame to excel

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related