Import of XLS missing Correct Date and Time

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

Help

Accepted answers

Matt_D

@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

All comments

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

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

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.

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

test1bc - Copy.xls

Matt_D

@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-date-to-date-in-Alteryx/td-p/11184

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703