topic Re: Date column cannot be converted into date formats in Alteryx Designer Desktop Discussions

Date column cannot be converted into date formats

serhoshu1118 — Thu, 24 May 2018 04:00:54 GMT

Hi, I have an Excel input file with a date column that I can't seem to convert into date formats. I tried using the Select tool and the DateTime tool and both returned null values.

As seen here in the screen shot, the first three rows of the file are empty, so I had to use Select tool to skip first three rows, and then use Dynamic Rename to take field names from first row of data. Not sure if that affects the dates formatting. One solution is to manually remove the rows in the Excel file itself, but is there anyway there could be a better solution? Thanks!!

Re: Date column cannot be converted into date formats

mborriero — Wed, 26 Jul 2017 13:42:06 GMT

Hi @serhoshu1118, in the input tool, you have the possibility to select your starting line (4 in your case).

If that does not work, try to use the DateTime tool that you find in the Parse set.

I hope it helps.

Re: Date column cannot be converted into date formats

nick_ceneviva — Wed, 26 Jul 2017 13:44:23 GMT

You can configure the input tool to handle the empty rows. You can choose which row to start on (row 4 in your case) and then you won't need to filter out the blank rows or use the dynamic rename tool to give the column headers. Doing this seemed to pick up the date field as a date without any need for conversion.

Re: Date column cannot be converted into date formats

serhoshu1118 — Wed, 26 Jul 2017 14:09:34 GMT

Hi - I don't seem to have that option in my Input tool. Is that something in the newer version?

I also tried the DateTime tool and the Calendar Date returned all null values.

Re: Date column cannot be converted into date formats

mborriero — Wed, 26 Jul 2017 14:18:26 GMT

@serhoshu1118, can you send a version of your workflow or a screenshot of your input tool configuration?

Re: Date column cannot be converted into date formats

serhoshu1118 — Wed, 26 Jul 2017 19:03:35 GMT

Here you go!

Re: Date column cannot be converted into date formats

nick_ceneviva — Wed, 26 Jul 2017 19:14:52 GMT

It looks like it is the difference of using .xlsx (Excel 2007 +) vs .xls (Excel 97-2003) file types. Are you able to save the file as a .xlsx file? This would give you the option to say what row the data starts on.