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

[ChrisTX]

Have you tried the option First Row Contains Data?

This should import all fields as string, likely limited to 255 characters.

Chris

[Bobbins]

This is already ticked @ChrisTX 

Anybody got any other ideas?

[DataNath]

Seems like a deeper issue/too much of a simple fix but does using a select tool and changing that field back to DateTime do anything? Or wrapping it in the ToDateTime() function? Also, is the field size long enough so Alteryx doesn’t do any weird truncating?

[Bobbins]

HI @DataNath ,

So the column is pulling itself in as a V_WString and has a size of 255. Not sure what the point of the legacy driver does either in this instance as there is no legacy for 97 Excel files.

I can manipulate the columns with everything, but it doesn't change anything.

Thanks

J

[ChrisTX]

@Bobbins 

Can you provide a few data examples for this: "I can manipulate the columns with everything, but it doesn't change anything."  ?

Show a few examples of how the data initially comes into the data stream, and which options you've tried to format the date/time columns to just be time.

[Bobbins]

Hi @ChrisTX ,

So the data exists in a '97 generated XLS format spreadsheet. An example of some rows are in this first snip. As you can see, columns C, D and E are formatted to show time only, but they actually contain the date too.

When i pull it into Alteryx I get the following:

As you can see, its just white space, there is no date to be seen.

This is what is shown in the input data tool window:

I have tried:

• Bring this into altreyx using the input data (*.xls), by name (name-of-file.xls) and by using a dynamic import.
• Cleaning the column, removing whitespace, linebreaks etc
• Using regex

The whole date value is just not present.

Thanks

[Matt_D]

Hi @Bobbins I cannot for the life of me replicate your Excel input. Could you share your example?

[Bobbins]

Hi @Matt_D ,

I wish i could, the file contains private information, and as soon as I open and resave the file, it solves it.

This kind of leads me down the path that the file is constructed in a way that is no longer accepted by current day engines, I mean 97 was built to be open by the access engine which is no  longer used. Excel itself can open it because for the time in opening, Microsoft will run the access engine as a virtual thing in the memory before essentially shutting it down.

The only solution I have found is to use something like AutoHotKeys to run a script which open and resaves the file in Excel for me, I can then process it as normal.

Its possible the 32 bit driver will work but I am stuck with the 64 bit so hey ho!

Thanks

[ddiesel]

Hi @Bobbins!

Have you tried inputting the file as a csv with no delimiters and then parsing the information you need?

In my experience, some of those old xls files are actually tab delimited files with an xls extensions that do not play nice with the Input tool. I have found this to be especially true if there are garbage rows at the top. 

Example Input configuration:

5) \0 indicates no delimiters

7) adjust the field length to prevent truncation

8) skip the garbage rows

Example parsing / clean-up:

Use the parse tool to split by delimiter (i.e. \t for tab) and other tools as needed to get everything in the right place.

If you plan to use this in a macro, it's a little bit fidgety because Alteryx will keep trying to change the 2) File Format back to excel, but there is a workaround for this.

Please let us know if this helps!

Thanks, 

Deb