Using Alteryx Python tool to turn off 1904 date setting in Excel

[mstockton]

Hi all -

I need to set up an Alteryx flow to iterate through all files in a folder and turn off the Excel 1904 date setting. I've written the below piece of Python code via the Python tool. The tool has one text input, which has a single field called "folder_path".

from ayx import Package
from ayx import Alteryx
Alteryx.installPackages(package="openpyxl", install_type="install --user")

def turn_off_1904_date_setting(folder_path):
    for filename in os.listdir(folder_path):
        if filename.endswith('.xlsx'):
            file_path = os.path.join(folder_path, filename)
            workbook = load_workbook(file_path)
            if workbook.epoch != 1900:
                workbook.epoch = 1900 # turn off 1904 date system
            workbook.save(file_path)

ayxDat = Alteryx.read('#1')
folder_path = ayxDat['folder_path'][0]
turn_off_1904_date_setting(folder_path)

However, I'm erroring out on the bolded line with a ValueError: 

ValueError                                Traceback (most recent call last)<ipython-input-2-f7b4c5c8587b> in <module>     13 ayxDat = Alteryx.read('#1')     14 folder_path = ayxDat['folder_path'][0]---> 15 turn_off_1904_date_setting(folder_path)<ipython-input-2-f7b4c5c8587b> in turn_off_1904_date_setting(folder_path)      8             workbook = load_workbook(file_path)      9             if workbook.epoch != 1900:---> 10                 workbook.epoch = 1900 # turn off 1904 date system     11             workbook.save(file_path)     12
~\AppData\Roaming\Python\Python38\site-packages\openpyxl\workbook\workbook.py in epoch(self, value)    129     def epoch(self, value):    130         if value not in (WINDOWS_EPOCH, MAC_EPOCH):--> 131             raise ValueError("The epoch must be either 1900 or 1904")    132         self._epoch = value 133

ValueError: The epoch must be either 1900 or 1904

As far as I can tell, the value error is simply saying that the epoch must be either 1900 or 1904. However, the line that's erroring out is set to "1900". Can anyone help me figure out how to resolve the value error?

Thank you!

[PhilipMannering · Accepted solution]

I assume you've got a `from openpyxl import load_workbook` statement going on there somewhere?

Try this,

import openpyxl
from openpyxl import load_workbook


def turn_off_1904_date_setting(folder_path):
    for filename in os.listdir(folder_path):
        if filename.endswith(".xlsx"):
            file_path = os.path.join(folder_path, filename)
            workbook = load_workbook(file_path)
            workbook.epoch = (
                openpyxl.utils.datetime.CALENDAR_WINDOWS_1900
            )  # turn off 1904 date system
            workbook.save(file_path)

[mstockton]

Oops good call out - I did have that in my script, just forgot to copy it in.

This worked! Thank you so much :)