Python Forum
Problem in saving .xlsm (excel) file using pandas dataframe in python
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Problem in saving .xlsm (excel) file using pandas dataframe in python
#1
I have two macro excel files(.xlsm file) with different information. I have written the python code that basically checks certain fields, and if that certain field is present then it saves in one folder, otherwise if that field is absent then it saves in another folder. I don't want any information removed from that excel file. I just wanted if that field is present then save the original file into that folder, otherwise save the original file in other folder. Code is not giving any error. But when I check the saved file, it is showing this error. Image Attached. Any help???

For testing, Input Files(.xlsm) are attached here

from pathlib import Path
import time
import parser
import argparse
import pandas as pd
import os
import warnings

warnings.filterwarnings("ignore")

parser = argparse.ArgumentParser(description="Process some integers.")

parser.add_argument("path", help="define the directory to folder/file")
parser.add_argument("--verbose", help="display processing information")

start = time.time()


def main(path_xlsm, verbose):
    if (".xlsm" in str(path_xlsm).lower()) and path_xlsm.is_file():
        xlsm_files = [Path(path_xlsm)]
    else:
        xlsm_files = list(Path(path_xlsm).glob("*.xlsm"))

    df = pd.DataFrame()
    
    for fn in xlsm_files:
        all_dfs = pd.read_excel(fn, sheet_name=None, header=None, engine="openpyxl")
        print(all_dfs)
        list_data = all_dfs.keys()
        all_dfs.pop("Lookups", None)
        all_dfs.pop("Instructions For Use", None)
        all_dfs.pop("Drop Down Boxes", None)
        all_dfs.pop("ResolutionLookups", None)
        
        for ws in list_data:  # Looping for excel sheet
            df1 = all_dfs[ws]
              
            if df1.iloc[3, 0] == "Client Representative" and df1.iloc[4, 1] == "DATE" and df1.iloc[4, 3] == "SHIFT":
                path_save = "C:\\Users\\ShantanuGupta\\Desktop\\Incoming\\Peel"
                df.to_excel(os.path.join(path_save, f"{fn.name}"), index=False)
            else:
                path_save = "C:\\Users\\ShantanuGupta\\Desktop\\Incoming\\Resolution"
                df.to_excel(os.path.join(path_save, f"{fn.name}"), index=False)
            
            
if __name__ == "__main__":
    start = time.time()
    args = parser.parse_args()
    path = Path(args.path)
    verbose = args.verbose
    main(path, verbose)  # Calling Main Function
    print("Processed time:", time.time() - start)  # Total Time 

Attached Files

.py   Incoming.py (Size: 1.84 KB / Downloads: 260)
Reply
#2
The attachment you provide, is your python script, not the excel files.
Reply
#3
(Aug-29-2021, 09:41 AM)shantanu97 Wrote: it is showing this error. Image Attached.
There is no image attached.

In you link with the .xlsm there is a problem with PEEL v2.0 UNLOCKED1.xlsm file.
When you ask people to test your code make it easier to do so,not use a path with your username in it.
C:\\Users\\ShantanuGupta\\Desktop\\Incoming\\Peel
import pandas as pd

fn = 'PEEL v2.0 UNLOCKED1.xlsm'
#fn = 'XtendTemplate_v125082021.xlsm' #Ok
df = pd.read_excel(fn, sheet_name=None, header=None, engine="openpyxl")
print(df)
Output:
{'Sheet1': Empty DataFrame Columns: [] Index: []}
If i open PEEL v2.0 UNLOCKED1.xlsm in excel an save it again then i get work.
Then i get both files to move to there folders(make my own path),
and there is no need to save if do nothing with files,just move them.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Python openyxl not updating Excel file MrBean12 1 249 Mar-03-2024, 12:16 AM
Last Post: MrBean12
  Copy Paste excel files based on the first letters of the file name Viento 2 348 Feb-07-2024, 12:24 PM
Last Post: Viento
  Python Alteryx QS-Passing pandas dataframe column inside SQL query where condition sanky1990 0 690 Dec-04-2023, 09:48 PM
Last Post: sanky1990
  Search Excel File with a list of values huzzug 4 1,147 Nov-03-2023, 05:35 PM
Last Post: huzzug
  Updating sharepoint excel file odd results cubangt 1 753 Nov-03-2023, 05:13 PM
Last Post: noisefloor
  Python and pandas: Aggregate lines form Excel sheet Glyxbringer 12 1,696 Oct-31-2023, 10:21 AM
Last Post: Pedroski55
  pandas : problem with conditional filling of a column Xigris 2 593 Jul-22-2023, 11:44 AM
Last Post: Xigris
  Question on pandas.dataframe merging two colums shomikc 4 783 Jun-29-2023, 11:30 AM
Last Post: snippsat
  Reading data from excel file –> process it >>then write to another excel output file Jennifer_Jone 0 1,046 Mar-14-2023, 07:59 PM
Last Post: Jennifer_Jone
  Save and Close Excel File avd88 0 2,840 Feb-20-2023, 07:19 PM
Last Post: avd88

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020