Excel Date Input into Alteryx incorrectly

[Intern12]

Good afternoon!

I have seen some similar posts, but nothing that actually solves my problem. I am working with a report that I pull from my companies software. I export the report from the software into excel. This report contains dates. My hope is to not enter into the excel document and change the format of the date in any way. When I don't change the format of the dates in excel, but I then import the excel file into Alteryx, the dates come out looking like "1905-07-10" although it should be 2018-07-30. This is happening directly after using the input tool in Alteryx. Therefore, it is simply not pulling the data correctly/not understanding that it is a date. 

Is there a way to solve this with only making changes in Alteryx? It doesn't seem that Alteryx is pulling this data in any form of pattern, so a formula would not solve the problem I believe. Some form of change on the input seems necessary, but I am not sure what that would look like.

Thank you!

                                   Excel                                                        Alteryx

ExcelAlteryx

[Luke_C]

@Intern12 Can you share the input tool configuration? Try checking 'First Row Contains Data'.

[Intern12]

Hey @DavidSkaife 

After trying to instead manipulate the problematic data in excel then importing to Alteryx to have it corrected, I tried manipulating the problematic data in Alteryx, then exporting to excel. So, I have attached some data that maybe you will be able to try out. Hopefully it doesn't correct for you when entering it into Alteryx. 

Thanks!

[Intern12]

Hey @Luke_C,

I have tried that as one other article suggested that. I had no luck. I attached an excel file in another comment of mine that hopefully you and others can try to play around with. Attached is the input as requested.

 

[Luke_C]

@Intern12 The data in that excel file shows all 1905 dates for me so I don't think I'll be able to reproduce the issue with it. 

[Intern12]

Ah, @ Luke_C , I now see that.

I am not sure how I can get a version of the data that is problematic without leaking any confidential information, sadly. 

[DataNath]

@Intern12it looks like Alteryx is somehow only reading the year part of the incoming field, as a number, and then trying to convert that to a date. I tested out just making a column of the years and when I wrap them in ToDate() it gives the output you're getting.

I can't see anything in your input configuration that would cause this to truncate or behave strangely so I'm tempted to question whether the custom data type that comes from your companies' system is causing the issue:

[Intern12]

Hello @ DataNath ,

That makes sense. Thanks for the clarification. I am assuming there isn't any way that anyone knows of to make changes in Alteryx to fix this problem. I am not too familiar with the software, but is there a way to set some sort of program within the input tool to have it read certain data a certain way?

It seems strange that it is only picking up the year for sure.

[ddiesel · Accepted solution]

Hi @Intern12!

I work with one type of file that is a tab delimited file with a .xls extension. The only thing that worked for me was to bring the file in with no delimiters and then manually parse it. Might be worth a try.

Example sequence of tools:

Example Input configuration:

Hope this helps!

Deb

[Intern12]

Hello @ddiesel!

I went into my companies software to see if I could export my file as a CSV, thanks to your comment (although not exactly the same)! I hadn't thought to do this. Turns out that was an option! After exporting as a CSV, then running it to Alteryx (mine worked with the default settings, so "," delimiter), everything seems to pull as it should!

Thank you all for your help!