I'm using os and pandas to modify multiple excel spreadsheets by iterating through a folder but ~$ keeps getting added to the file name

Question

My initial code is here:

import pandas as pd
import os
directory_in_str = input('\n\nEnter the name of the folder you would like to use. If there are spaces, replace with underscores: ')
directory_in_str.strip()
directory = os.fsencode(directory_in_str)
user = input('\nEnter your first initial and last name as one word (ex: username): ')
user.strip()

path1 = '/Users/'
path2 = '/Desktop/DataScience/'
dspath = path1 + user + path2
slash = '/'

for file in os.listdir(directory):
     filename = os.fsdecode(file)
     if filename.endswith(".xls") or filename.endswith(".xlsx"): 
        print(directory)
        pathname = dspath + directory_in_str + slash + filename
        print(filename)
        #Global = pd.read_excel(pathname, sheet_name=0)
        Stats = pd.read_excel(pathname, sheet_name=1)
        listorder = ['1', '2', '3']
        Stats = Stats.reindex(columns=listorder)
        Stats.to_excel(filename, sheet_name='Statistics', index=False)
        continue
     else:
        continue

I've included the filename print statement to insure that the correct path is being used. However, the print statement happens twice. These are the statements printed.

b'testrearrange'
Testname.xlsx
b'testrearrange'
~$Testname.xlsx

Why are the two characters '~$' added? The error originates from the line

Stats = pd.read_excel(pathname, sheet_name=1)

with the error

ValueError: File is not a recognized excel file

Does anyone know how to fix this?

Answer 1

I think the files starting with "~$# are temporary excel files that are created when you open the file in excel. One option is to close the file, in which case the temporary file is deleted. Other option is to change the logic by which you list the files to be read so that it ignores files that start with ~. I like to use glob for this

from glob import glob

path = "C:/Users/Wolf/[!~]*.xls*"
files = glob(path)

for file in files:
    print("Do your thing here")