I’m working on a program that uses a schedule stored in an Excel file to turn something on and off. The program runs in the background and checks the Excel data every minute. The file is stored on OneDrive, which is mapped to a local disk on the computer where the program runs.
I can access the file using pandas without any issues:
data = pd.read_excel(r"C:UsersRyszardOneDrive - Politechnika WarszawskaRezerwacje systemu HILHIL - schedule.xlsx", sheet_name=f'{month}')
However, I’m facing a problem with synchronization. When I manually open the file before my script runs, it updates properly. But if I try to access it directly through the script, it doesn’t seem to refresh the data. Meaning that pandas funtion read_excel seems to not triggering the OneDrive synchronization, as manually opening the file does.
Is there a way to force a file update before accessing it with pandas? Do you have any ideas how to deal with it? I thought about using a PowerShell command, but I haven’t found anything that works. I’m not using the OneDrive API and don’t want to, as it seems unnecessary and overcomplicated for this case.
1
Based on this question and answer from the Superuser site, you can shutdown the OneDrive process and then restart the background task to force a resync.
To do this in Python, we need to find the OneDrive.exe file, then send two commands, then wait and finally load the file.
import subprocess
import winreg
import pandas as pd
from time import sleep
# pull the location of your OneDrive.exe file from the registry
key = winreg.OpenKey(
winreg.HKEY_CURRENT_USER,
r'SoftwareMicrosoftOneDrive',
0,
winreg.KEY_READ
)
onedrive_exe, _ = winreg.QueryValueEx(key, 'OneDriveTrigger')
key.Close()
# turn off OneDrive then restart it as a background task.
# Use Popen to avoid hanging since OneDrive is a continuous process
subprocess.run([onedrive_exe, '/shutdown'])
subprocess.Popen([onedrive_exe, '/background'])
# wait a short period, then load the dataframe
sleep(5)
data = pd.read_excel(r"C:UsersRyszardOneDrive - Politechnika WarszawskaRezerwacje systemu HILHIL - schedule.xlsx", sheet_name=f'{month}')