When does Alteryx take a date from excel and turn it into a number?

[JenM]

I have a strange error which is inconsistent, but is causing issues in a scheduled file.

I have an excel file which lists by date which file Alteryx needs to use for the rest of the workflow.

The date in excel appears as follows, and has format date

Date	file
23/07/2017	su070517
24/07/2017	mf260517

When the workflow ran this morning Alteryx showed this input as

Date	file
42939	su070517
42940	mf260517

When I ran it again the dates came through in the format 23/07/2017 - I didn't change anything in the workflow or anything in the excel!!

The result I want is that it comes through in 23/07/2017

I am using a text input to list all the tabs I want to pull through, followed by a dynamic input that reads each tab in the excel - the output of this is the bit that's inconsistent.

Are there any settings I can check in the dynamic input or settings for the workflow that could change how Alteryx pulls through a date in excel?

Thank you!

[MarqueeCrew]

Maybe you can define a template and use that template to assure that the data typing is the way that you want to see it for all dynamically input sheets.

Just a thought,

Mark

[JenM]

Hi Mark,

Do you mean define an excel template?

I'm not sure how that would help since running the workflow with the same worksheet (without changing data types or formats etc) produced 2 different results in Alteryx.

Sorry if I've miss understood.

[MarqueeCrew]

When I've seen "Excel" date issues before it generally comes from data that is in a date column that isn't a date.  Somewhere buried in a large row# comes a value that breaks the type and Alteryx was expecting a date.  

When things are inconsistent, that is a scary event (Halloween is coming).  Trick or treat?  Have you explored the worksheet to understand the contents?

[MarqueeCrew · Accepted solution]

@JenM,

Good to meet you and to take the mystery away from this event.  When running the workflow, we were able to recreate the issue by OPENING the workbook in Excel while we ran the dynamic input.  I was surprised that Alteryx allows for the read of the file while it is opened.  That's new to me.  We then got BAD date conversion results with an open workbook and GOOD conversion results with a closed workbook.

Lesson learned:  Run the workflow without having the excel file opened.

Cheers,

Mark

[Boneckrh19]

For anyone trying to troubleshoot this problem, it also changes the way currency/accounting fields are processed. My issue was with the currency data, so my workaround is to have a regex that replaces the "$" characters (which has to be put in as \$) and the "," characters with nothing so that those fields can be converted to doubles.

I wish there was a tip when you start a new workflow that lets you know the input will change if your file is being viewed in another program.