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

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!

Date Time

Preparation

Accepted answers

MarqueeCrew

@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

All comments

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

@JenM,

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

Cheers,

Mark

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703