Import of XLS missing Correct Date and Time

[Bobbins]

Good Afternoon,

My system generated report has a file format XLS which I am unable to change. Annoyingly its also formatting the date/time columns to just be time. (the date is still there). The XLS has 10 rows of garbage at the start which I chop off once I have imported. However....

If I try and import this into Alteryx, Alteryx loses the date and just gives me the time.

I believe this is due to the driver used. I have installed "Microsoft Access Database Engine 2010 Redistributable x86" (we can't install the 32bit version) and I am stuck on Alteryx  2020.4.5 for the foreseeable future.

I can't upload a file due to confidentiality, and if I try and edit it to hide the date, even saving it as a XLS (I am using O365), it seems to fix something and makes it work.

So in short, I have a XLS file being generated which I can not alter and i am stuck on Alteryx which i also can not change.

Any thoughts on how to fix this?

Thank you

[Bobbins]

Hi Deb,

Well, I gave it a try, this was the outcome! (Although i have tried to repeat it and it now just fails, either with "delimiter must be a single character" or  field errors, e.g. too many fields in record #5)

There is about 6 columns to the left that its just not pulling in.

[ddiesel]

@Bobbins 

Bummer! I was hoping that configuration would work for you.

Since you cannot upload your file, it's going to be tough for us to help troubleshoot it. Your best bet might be to open a support ticket  support@alteryx.com

When you find the solution, let us know here.

[Bobbins]

@ddiesel @Matt_D @ChrisTX @DataNath 

I have been able to eventually to sanitize the file enough and share it with the problems intact, please see attached

[Matt_D · Accepted solution]

@Bobbins Pick the range and it works. I'd suggest building a dynamic range based on column A and then reading the data in dynamically.

Let us know if you need any help with it.

Matt

[Bobbins]

Thanks @Matt_D  that works. Oddly the real version puts the dates into decimal but using this thread here I can grab thoose back out again https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-convert-the-Excel-numerical-dat...