Excel to alteryx preserve date format without conversion.

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.

Date Time

Accepted answers

All comments

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.

datetime_help.yxmd

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

adriancontini08

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

testimport.xlsx

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

BS_THE_ANALYST

@adriancontini08 Sometimes I like to use the Autofield tool. It will automatically convert all the fields you select into their appropriate datatypes:

Can save time instead of manually changing them through the Select tool.

If the data doesn't covert into your expected datatype (after using the Autofield tool), this provides a nice insight that you have data in that column that needs to be dealt with.

adriancontini08

@BS_THE_ANALYST Thanks. I believe to convert to csv, i need to do save each tab separately since csv cant have multiple sheets? That is the main hassle actually.

apathetichell

Hi - what's your end goal here? Data validation? Getting the output into a different format for ingestion? This seems like a lot of steps - and at its route - you should know if the user hasn't entered a date in excel if you follow @FrederikE 's suggestion. If your data is showing up as dates - everything is a date in excel. If you need to look a specific date format in excel - I'd recommend doing that in Excel vs trying to get Alteryx to control and validates Excel's internal date formatting.

adriancontini08

@apathetichell Got it so basically any date in date format (mm/dd/yyyy) - converted using the datenow tool in excel, will be imported as date into Alteryx (yyyy-dd-mm)? But a date in a text format whether it be mm/dd/yyyy or whatsoever will be imported as text instead of date into Alteryx?

apathetichell

Here's the good rule of thumb -if the entire column of Excel is stored in Excel as date - it will be imported as date. It's a date. In Alteryx this will formatted as YYYY-MM-DD (aka standard date notation). If there is even one non-null field in the import range - Alteryx will view it as text and you'll have to filter/manipulate to get it in the format you want.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703