Problem formatting date

[hiva]

Hello!

I have a CSV file with the date formatted as "month/day/year". In Alteryx, the only way to make the conversion work was by using this formula

datetimeparse([date],"%y/%m/%d")

It doesn't make much sense, but it works. The problem is that when I open the CSV file and edit it on any field (and saving it on the same CSV format), Alteryx will no longer convert that date with that formula, and I need to use datetimeparse([date],"%m/%d/%y") instead (please note the order of the day, month, and year).

How can I make this work regardless the file gets edited or not?

Thanks!

[RolandSchubert]

Hi,

sounds very strange to me .. if the date field in the csv file is "month/day/year", the formula should be DateTimeParse([date], "%m/%d/%y") or DateTimeParse([date], "%m/%d/%Y") if year has four digits. Could you provide a sample csv file (before editing)?

Best regards

Roland

[fharper]

Keep in mind that all columns are "text" in a csv where for xlsx the excel driver makes assumptions based on the data in the file which can cause variations from one run to another based on the data.

If you read the csv and in a select or some other tool altered the data type but then changed the data on a subsequent run then you can hit issues as the data no longer is viewed or interpreted the same.  

Also...If you have an auto-field tool in the path it will potentially change the data type it chooses if the data changes.

I wrote my own macro to deal with all possible date formats I could think of...yy.mm.dd, yyyy.mm.dd, yyyy.mm, and so many more...wether the separator was "." or "/" or "-" or was mmm dd, yyyy or other textual date formats as well.  then use the macro to take the selected column and translate to my preferred format...I tend to always convert to yyyy-mm-dd as it is a natural sort format and is clearly understood and thus good for reporting as well.

I use regex-replace in a formula to do the actual conversion within a series of If clauses...

If         regex_Match ([date col], "pattern1")     then regex_replace([date col], "pattern1", "replace")

elseif  regex_Match ([date col], "pattern2")      then regex_replace([date col], "pattern2", "replace")

else if. ......and so on until you have covered each condition you want.

There can be variations on the theme as well....the one I originally made used a multi-field formula tool vs a regular formula tool and did the formula for any column with "date" in the field name.

You can use a recordID tool first and split the data and deselect all columns except the recordID and the columns you want to manipulate, feed into the macro and join the result by recordID.

There are many ways to do this and there is a macro in CReW that does some common patterns already but I think you need to tell it what to expect where my own interprets from the data itself so is self managing if you will.

Sorry I don't have a copy of Alteryx handy or I would give you copy of the macro...but this should be enough for you to build your own and you will learn more that way.

[danilang · Accepted solution]

Hi @hiva 

When you say "I open the CSV file and edit it on any field" are you opening it in Excel?  You probably are because , by default, Excel sets itself up as the default program to use to edit .csv files in Windows.  If you are opening it in Excel, Excel is probably changing the date time format when you save the back out again.  

Here's an example

This is a csv i created in TEXT editor. I used Notepad++, but you can use any text editor, like Notepad)

   

You can see that the date format is clearly "%Y/%m/%d, just like @RolandSchubert pointed out and the format you use in Alteryx

Here is the same file after I open it in Excel

You can see that in Excel has applied it's own date formatting rules and changed them to "%m/%d/%Y".  When I change one of the numbers to 3 and save the file, Excel saves all the dates in the new format.  

Here's the same file open again the text editor.

 

Even though I changed the number From 2 to 3, Excel has changed the format of all the dates to "%m/%d/%Y".  That's why you need to change formats in Alteryx after you edit the file

Excel is NOT a text editor and applies it's own rules when you open a file with it.  If you need to edit a .csv or any other text based file, use Notepad.

Dan 

[hiva]

Thanks for such detailed explanation.