Two issues about values in Excel and Alteryx

Question

Hello everybody!
I'm facing these 2 issues for a long time. Any ideas to help me, please?

1) "Actual Rec Start Date" is a field in from a monthly Excel file of mine (file attached). On A1 cell, there's the field copied from the March. And on A2, the field from April. They're exactly the same, but when I compare both on Excel, I get FALSE. It's the same on Alteryx, once they're not recognized as same fields.

2) The second issue is that on the same monthly Excel file of mine, I have a field that can have strings on the majority of rows in on month, and on the other can have mostly numbers. So, Alteryx can reads this field as String or Double. Is there any way to force this field to always be String type, regardless the values on the initial rows? I've tried using "First Rows Contains Data", but it's leading to other issues on other fields.

Thanks a lot and in advance!

ALTERYX.xlsx

jpscoralick · Answer

Thanks a lot, @RobertOdera and @SPetrie!

SPetrie · Answer

For your first issue, its hard to see, but there are different types of spaces between Rec  and Start in the first and second row.

I did a substitute of spaces with | and its easier to see. Thats why it shows as false, because they technically are different.

If you are wanting to fix that in Alteryx, you can use a formula to substitute the 160 Non-breaking space with an actual space (code 32)

Replace([ColumnName],CharFromInt(160)," ")

In Excel the substitute would look like =SUBSTITUTE(A1,CHAR(160)," ")

For the string issue.

Another method is using the Forced selection in a select tool on the field that is already recognized as a string.

RobertOdera · Answer

Hi @jpscoralick

Kindly consider the below (and yes, I show the same as you do when I investigate #1 in Alteryx)

1. Add a Select Tool after the Input Tool, so you can evaluate the Field type and size

2. Add a Multi-Field Tool after the select tool (so you can update multiple columns at the same time) and set it to type = VW_String

3. Add a Multi-Field Tool before the output tool to make sure all fields output as Strings

I hope you find this helpful - cheers!

For_jpscoralick.yxzp