Date column cannot be converted into date formats

[serhoshu1118]

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

[mborriero]

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.

[nick_ceneviva]

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.

[serhoshu1118]

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. 

[mborriero]

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

[serhoshu1118]

Here you go!

[nick_ceneviva · Accepted solution]

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.