This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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.