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.
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.
@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".
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.
@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?