Wrong Date coming in Alteryx when using Input Data Tool from an Excel File (.xlsx)

[Rajat_BNYM]

I am trying to input an excel file in Alteryx Designer and date field is getting messed up when imported into Alteryx, using the Input Data Tool.

See the screenshots for reference.

Have also attached the erroneous file with dates in it.

Thanks.

[Jay-RDC]

Make sure to check the incoming data if it reads it as Date. You can use a select tool to verify. You can also use a date time tool to convert it to date time format.

[DataNath]

@Rajat_BNYM could also try saving the file as a CSV before bringing it in as a .csv and applying a DateTimeParse() in the Multi-Field formula to handle the 3 fields at once:

[Sebastiaandb]

Jep, @DataNath is right :-)!

See also as reference: (1) Solved: Excel Date Input into Alteryx incorrectly - Page 2 - Alteryx Community

[Rajat_BNYM]

@jay-RDC Yes, Alteryx reads them as Date. The fields are also in the Date Format in the excel.

[Rajat_BNYM]

@DataNath and @Sebastiaandb 

The thing is, I am trying to automate the process and do not have the option to change the file format. Is there any way to do this without haveing to do any manual intervention in the raw excel?

[DavidSkaife · Accepted solution]

@Rajat_BNYM 

Cracked it, kinda....credit to this post on the community - https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Date-returning-different-number/m-p/38...

Your file is actually a Strict Open XML Spreadsheet which also shares a .xlsx extension. 

if you re-save it as a standard Excel Workbook you should find the import works correctly

[danilang]

Hi @Rajat_BNYM 

Since it looks like the error is tied to the older file format(good catch @DavidSkaife), you can modify your workflow to run a vbs to open the file and save it as modern format before reading it into Alteryx.  See this post for info on calling a vbs script from within the Run Command tool.  You'll need to use

objExcel.ActiveWorkbook.SaveAs Filepath, FileFormat=51

See here for a list of the allowable FileFormat options 

Dan 

[Rajat_BNYM]

Thanks @DavidSkaife @danilang for your solutions.

We had no idea that this was a 'Strict Open XML Spreadsheet'.

Thanks @Sebastiaandb @DataNath @Jay-RDC for your solutions.