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

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!!

Datasets

Accepted answers

PickleRick

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.

All comments

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 Excel Alteryx Alteryx

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.

PickleRick

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.

sample.xlsx

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703