Date column cannot be converted into date formats

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

capture 3.PNG

Date Time

Preparation

Accepted answers

nick_ceneviva

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.

All comments

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.

Capture2.JPG

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703