Excel to alteryx preserve date format without conversion.

[adriancontini08]

Hello,

I have an excel file with dates in the mm/dd/yyyy format and Alteryx automatically converts them into the standard yyyy-dd-mm format while importing. Is there any way to preserve the original mm/dd/yyyy format? Mainly I want to check if the user has entered the date in mm/dd/yyyy within the workflow using a regex tool, but the date conversion is not allowing me to do that.

[BS_THE_ANALYST]

@adriancontini08 I'd recommend this:

Use the Date Time tool.

This will allow you to convert the date back into your prior format. It will be a string data type now though, so consider that for downstream analyses.

[FrederikE]

Hey @adriancontini08,

If you select "First Row contains Data" then you will always read in Strings (as long as the column headers are strings). 

Then you can use a "dynamic rename" tool to get the column headers in a second step (option "Take Field Names from First Row of Data") again. 

All of your fields will end up being strings. 

[adriancontini08]

@BS_THE_ANALYST Thanks for your response. But that will defy the purpose. In the above we are forcefully converting the standard date format into mm/dd/yyyy after the input. But I want to make sure the excel input is in the mm/dd/yyyy. As in when the excel file is input into alteryx, it should show whatever the date format it was in excel whether it be yyyy-dd-mm or mm/dd/yyyy so that I can check the format using a regex tool within alteryx. Not sure if that makes sense.

[BS_THE_ANALYST]

@adriancontini08 I think a quick fix here is not to bring the file in as a .xlsx as this is bringing through the formatting aswell. Any chance you can bring it through as a .csv instead? This will remove the formatting on the column. It will bring the dates through exactly as they are entered.

[BS_THE_ANALYST]

@FrederikE  nice! love that solution. Great workaround.

[adriancontini08]

@FrederikE @Hey I tried this but even though the column header was a text format; it brought in the date as standard date format with the option of checking first row contains data

[FrederikE]

@adriancontini08,

That is interesting. In this case, it seems to be more of an excel-topic than Alteryx. I'd guess that Excel already saves it differently than it shows it to the user (excel likes to do such stuff).

Can you change it in Excel to a string instead of a date? 

[adriancontini08]

@FrederikE If i change it to string in excel, the date format changes. See example that I am using with string fields

[adriancontini08]

@BS_THE_ANALYST thanks. bringing it as csv works. I wish there was an option to bring it as an excel to preserve the other numeric fields