Excel Date Input into Alteryx incorrectly

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

Excel Excel Alteryx Alteryx

Workflow

Accepted answers

ddiesel

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

All comments

Jay-RDC

Hello. Try to put a select tool after the input and change the data type to "date".

davidskaife

Hi @Intern12

Have you seen this article, seems to match your issue? https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Troubleshooting-Alteryx-Designer-Misinterprets-Dates-from-Excel/ta-p/617376

Intern12

Thank you for the quick response! Unfortunately, I have tried this (I attempted it again just to be sure), but I have not had any luck. It is still showing the same as before.

Intern12

Hey @DavidSkaife,

I have seen this article. I did a lot of researching before posting. The article says that the file is not an excel file and by changing it, the problem will be resolved. However, my file is an excel file. It is not a "Strict Open XML Spreadsheet" as is mentioned in the article.

Thanks!

Jay-RDC

May I know what's the format of the fields in excel?

Intern12

jayrdelacruz18

The format in excel is coming through as custom when I export it from my company's software. The format is yyyy-mm-dd. But again, I am not wanting to make any changes in excel. I am wanting to make the changes in Alteryx if possible. That way, when I (or someone else) re-run my report each month, I will not have to go into the excel document to make any changes. I can simply upload it to Alteryx and have everything done nicely.

davidskaife

Hi @Intern12

Tricky to know what you have/haven't already researched!

Is the file xlsx or xls? Could you share a sample dataset for us to help troubleshoot further?

Intern12

DavidSkaife

I understand that! Sadly, my report has some confidential information, and as I have seen in other posts, whenever I go into the excel document to delete the confidential information, the excel document seems to correct itself. When I tried to pull the New* excel document with only the date columns into Alteryx (none of the other confidential info), it came through as it should. That is why I am hoping to have the change occur in Alteryx by somehow manipulating the input tool.

The file is xlsx by the way.

Thank you!

davidskaife

Hi @Intern12

No problem, although removing the other fields and leaving just the dates works ok is interesting. You could try troubleshooting it by slowly adding the other fields to see if there is something odd going on?

With the file being xlsx rules out another theory

I assume something simple such as the AutoField tool, or performing a DateTime formatting formula doesn't have any affect?

Luke_C

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

Intern12

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!

Alteryx Dates.xlsx

Intern12

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.

Snip of Input.PNG

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

Hi @Intern12!

Example sequence of tools:

Example Input configuration:

Hope this helps!

Deb

Intern12

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!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

Aguisande 317