1

Is there a workaround for using pandas ExcelWriter to append to a fsspec URL? I am working out of OneDrive and need to automatically append a master xlsx file with each new xlsx file that gets uploaded to the OneDrive folder (a new xlsx file get added to the OneDrive folder daily and need to create a master list without changing previous data) but the append function does not work with fsspec URLs and overwrites the master xlsx file.

This script runs on a trigger automatically and calls on any new .xlsx files that are in the OneDrive folder. The columns are exactly the same, but the rows vary and the file names are not consistent other than the .xlsx format so I do not think I can use the manipulate the start row or call a specific file name.

Is there a workaround for this? Essentially I want a master xlsx file that exists in OneDrive that will grow and update with each xlsx file export that gets uploaded to the OneDrive folder every day.

I tried...

with pd.ExcelWriter(
    "/Users/silby/OneDrive/test/dataTest.xlsx",
    mode='a',
    engine='openpyxl',
    if_sheet_exists='overlay'
) as writer:
    excel_merged.to_excel(writer)

and expected it to append the dataTest.xlsx file but it overwrites the existing data instead.

AlexK
  • 2,855
  • 9
  • 16
  • 27
rpsilberg
  • 11
  • 1
  • At first glance, this does not appear to have anything to do with fsspec, but internal logic to the excel writer engine. It would not surprise me if true append (only add bytes to a file) is not possible with excel files at all. Would CSV suffice? – mdurant Jan 24 '23 at 14:36
  • Thank you for the feedback. Yes, I already have a code working for a CSV format. The issue is limited functionality with large datasets on online platforms (ex: Google Workspace and limited spreadsheet size) so I have switched to Microsoft 365 Online and OneDrive to handle the larger file sizes, but still have the need to and extract data out of the "master" dataset for analysis, reports, and sharing with teammates without manually completing each step, which is much more easily accomplished with XLSX since OneDrive seems to limit functionality of other file types. – rpsilberg Jan 25 '23 at 13:42
  • My questions stemmed from some digging I did and found the following information: "mode{‘w’, ‘a’}, default ‘w’ File mode to use (write or append). Append does not work with fsspec URLs." The website: https://pandas.pydata.org/docs/reference/api/pandas.ExcelWriter.html – rpsilberg Jan 25 '23 at 13:44
  • That is correct, but your URL in this case is local, and onedrive is taking care of the syncing (not fsspec) – mdurant Jan 25 '23 at 14:47

0 Answers0