Excel data incorrectly read as "00:00:00" instead of "ABC"

[Varshaa]

Alteryx is reading string datatype correctly as string. However, the actual text seems to be reflecting incorrectly as "00:00:00". Any thoughts around why please?

I tried looking at other discussions but they seem to be about numbers being read as string, and numbers being read with extra decimal places.

Thank you!!

[meeravijayan2011]

Hi @Varshaa 

The '00:00:00' represents time in Alteryx. Could you please share a screenshot? 

[Varshaa]

Hi @meeravijayan2011 
Sorry the screenshots didn't upload earlier.

Here's whats happening:

Data in ExcelAlteryx

To provide more background, this was actually a .xlsb file which I cannot import in Alteryx as I do not have Access 2010 drivers yet. So I ended up converting the file to .xlsx and then imported to Alteryx using Input tool.

The 00:00:00 you see is what the data looks like when imported. It still is a string but is definitely not what the excel has.

[meeravijayan2011]

Hi @Varshaa 

Could you please let me know which version of Alteryx you are using. If the alteryx version is 2018 or above as far as i know alteryx accepts .xlsb files. 

There is a beautiful macro developed by alteryx experts which you can find: https://community.alteryx.com/t5/Engine-Works-Blog/The-Ultimate-Alteryx-Holiday-gift-of-2015-Read-AL...

[danilang]

Hi @Varshaa 

This is a strange case.  Can you share a minimum version of your .xlsx.  Include just enough data to reproduce the problem

Thanks

Dan 

[PickleRick · Accepted solution]

I've had this problem before and the way we got around it was to go back into the excel and change the data type there to string before inputting into Alteryx as it seemed like Alteryx was reading it in as a time first before realising it should be a string, hence losing the data.

[Varshaa]

@PickleRick  You're right, the datatype in my excel is strangely Date! I never noticed. Will need to change this for future files. Is there no other workaround other than manually changing datatype of that field in all my files? I have over 50 files to work with 😞

@meeravijayan2011 thank you for sharing the macro! it works for .xlsx but unable to find something for xlsb. I'm using Alteryx version 2019, strangely still doesn't read xlsb.

@danilang attached sample file with just 1 line item and headers. My headers begin at line 3, data at 4 so I imported from 3 unsing Input tool. There are several other sheets in the excel, this data is in Sheet3 (in my sample there's just 1 sheet for you) so I input that specific sheet. I have a folder with about 25 such files, my goal is to consolidate data from all Sheet 3's by filtering "Region".

Problems:

1. What I have are xlsb files whereas all that I'm able to find are stuff to do with xlsx or xls.

2. I have a macro to input data from a specific sheet from multiple files which works for xlsx. but due to this datatype issue, data read by alteryx isn't whats in my excel so I cannot leverage my macro at all, even if I converted all my xlsb files to xlsx.

[PickleRick]

@Varshaa ah I'm not sure as I only had one column to change, but will let you know if we figure something out! Although it does sound like a problem that is happening behind the scenes so I'm unsure if we'd be able to work around it?

[Varshaa]

@PickleRick Understood, thank you!

[aliboukhobza31]

An alternative solution, if the excel file is too large to open and edit, you can use the Text input to manually edit your input file.