community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Static data types with changing Excel input files

Highlighted
Alteryx Certified Partner

I'm working on a project where clients are sending in data based on an Excel template (it's blank with just the column names in place that the clients fill in the data). I've built out a process to validate the data in the columns that they enter. 

The issue I'm finding is that the data types for some columns change depending on the data that is entered (or in the case of some columns, what is NOT entered...which results in a column of all NULL values). However my validation process is built around the assumption that all of the columns are String data type. 

When I point to a new file, I'm finding the data types change (some go to Double) which "breaks" the validation process (at Joins where it requires String-to-String joins). 

The only way I've figured out how to "force" the data type as String is to use a "template" Excel spreadsheet with one row of data (all as "ABC" in the columns...which forces it to a String). I then Union this to the actual file and Union on position. Then I put a Sample tool in to skip the first record (but the data type has already been determined by that first line of data). 

It looks like this...

Static Data Types.png

 

This works, but you'd think there'd be a more efficient way to do this. 

Any thoughts from the Community?

Thanks,

Rod

ACE Emeritus
ACE Emeritus

Could you use a Multi-Field formula to force everything to String?  Or just a Select and manually click through and force everything to String?

 

Alteryx Certified Partner

@JohnJPS

Hmmm...I thought the Multi-Field Formula tool might do the trick, but the issue there is when it changes data types automatically, then the selection of what fields you want in the Current_Field variable will change as well (because a Text field now shows up in the Numeric field list...and you can't select from both). 

And with the Select tool, that's the issue...I don't find a way to "force" it to stay a String when a new input file is used...it always changes the data type and I'm trying to avoid the user to have to manually change the Select tool when they point to a new file.

Thanks for the reply though...

ACE Emeritus
ACE Emeritus

Check the bottom entry in the drop-down: you can list "All types of" fields, and then select any field of interest, regardless of type.

 

Alteryx Certified Partner

@JohnJPS

Always learning something new...never knew that option was there! Thanks!

Labels