Static data types with changing Excel input files

[RodLight]

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

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

Any thoughts from the Community?

Thanks,

Rod

[JohnJPS]

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?

[RodLight]

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

[JohnJPS · Accepted solution]

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.

[RodLight]

@JohnJPS

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