topic Re: Excel Date Input into Alteryx incorrectly in Alteryx Designer Desktop Discussions

Excel Date Input into Alteryx incorrectly

Intern12 — Wed, 15 Jun 2022 19:36:49 GMT

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

Re: Excel Date Input into Alteryx incorrectly

Jay-RDC — Wed, 15 Jun 2022 20:23:40 GMT

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

Re: Excel Date Input into Alteryx incorrectly

davidskaife — Wed, 15 Jun 2022 20:25:59 GMT

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

Re: Excel Date Input into Alteryx incorrectly

Intern12 — Wed, 15 Jun 2022 20:26:39 GMT

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.

Re: Excel Date Input into Alteryx incorrectly

Intern12 — Wed, 15 Jun 2022 20:30:07 GMT

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!

Re: Excel Date Input into Alteryx incorrectly

Jay-RDC — Wed, 15 Jun 2022 20:36:38 GMT

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

Re: Excel Date Input into Alteryx incorrectly

Intern12 — Wed, 15 Jun 2022 20:40:51 GMT

@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.

Re: Excel Date Input into Alteryx incorrectly

davidskaife — Wed, 15 Jun 2022 20:46:48 GMT

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?

Re: Excel Date Input into Alteryx incorrectly

Intern12 — Wed, 15 Jun 2022 20:52:46 GMT

@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!

Re: Excel Date Input into Alteryx incorrectly

davidskaife — Wed, 15 Jun 2022 20:59:20 GMT

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?

Re: Excel Date Input into Alteryx incorrectly

Luke_C — Wed, 15 Jun 2022 21:29:35 GMT

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

Re: Excel Date Input into Alteryx incorrectly

Intern12 — Wed, 15 Jun 2022 21:30:07 GMT

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!

Re: Excel Date Input into Alteryx incorrectly

Intern12 — Wed, 15 Jun 2022 21:35:08 GMT

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.

Re: Excel Date Input into Alteryx incorrectly

Luke_C — Wed, 15 Jun 2022 21:37:49 GMT

@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.

Re: Excel Date Input into Alteryx incorrectly

Intern12 — Wed, 15 Jun 2022 21:41:58 GMT

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.

Re: Excel Date Input into Alteryx incorrectly

DataNath — Wed, 15 Jun 2022 21:46:19 GMT

@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:

Re: Excel Date Input into Alteryx incorrectly

Intern12 — Wed, 15 Jun 2022 21:51:12 GMT

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.

Re: Excel Date Input into Alteryx incorrectly

ddiesel — Thu, 16 Jun 2022 05:44:56 GMT

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

Re: Excel Date Input into Alteryx incorrectly

Intern12 — Thu, 16 Jun 2022 13:16:19 GMT

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!