Force data type of input data

[Timur_O]

It would be a handy feature if it were possible to choose a data type for an input tool to read the data in as. For example, if a dataset has multiple fields with different data types, it would be handy to be able to make the Input Tool read and output them all as a string, if needed. This would also make a handy tool, a sort of blanket data conversion to convert all fields to the specified type.

[tom_montpool]

I think what you're suggesting is a 'pre-Input' data type specification, and I'm not sure why that would be useful so I'd be interested to know that.

There are several ways to accomplish forcing data types 'post-Input' in bulk.

• The Select tool -- Options -- Change Field Type of Highlighted Fields
• The Select tool -- Options -- Save/Load -- Load Field Names & Types
• The MultiField Formula tool

There are probably more...

[crupley]

I have been searching for an answer similar to this as well. I am reading a table from a database, doing some work on it, and sedning it back to the database as a new table. The problem is that Alteryx is changing the data types. For example, I passed a Zipcode field as Varchar2(5) and Alteryx is calling it a String and giving me back a Char(6). When I try to match the new char(6) zipcode to our standard varchar2(5) zipcode fields, it will always fail due to the extra space at the end. Even trimming during the match does not work because they are simply different data types so I am forced to create a new column of the original data type and convert it there before matching. I know I can change the data type in the select tool, but if my table has a couple hundred columns this becomes extremely tedious.

Is there any way to assign a default data type for all fields, or maybe tell it to always use V_String instead of String? I just want Alteryx to return to me the same data type I gave it, or at least give me one I can use.

[RodL]

@crupley,

To your comment "I know I can change the data type in the select tool, but if my table has a couple hundred columns this becomes extremely tedious.", did you know that in the Select tool, you can select multiple fields at the same time and change the data type for all of them at once under the Options drop down?

[crupley]

I did not, that will certainly help. I would still like to have a "Default
String type" option similar to the "Default Distance Units" in the User
Settings.

[DataMonk]

Hi All. 

With regard to "why that would be useful" ... 

Being able to cast all the input fields to string for instance would prevent most data conversion issues and, specifically, an issue I am currently having where the source data has odd data for a "datetime" field and the dynamic input tool (or even the non-dynamic input tool) crashes out with "Error: Input Data (3): Internal Error: Invalid parameter detected in function (null). File: (null) Line: 0 Expression: (null)".  Strangely, the problem only occurs where the erroneous data is in a dataset with more than about two million rows.  If I write the input SQL to cast all the fields to char, the data comes across, warts and all, for me to deal with in due course.  Unfortunately, since I am using the Dynamic Input tool I will have to create the whole SQL statement for each table and then pass that to the tool instead of just changing the name of the input table.  Elegant solutions to this problem would be well received.

[basub]

I agree with last comment and not a fan of using too many tools since my workflow is getting cluttered with multi-step tools that I was able to accomplish in a single custom query in MS Access.

[ptopdjian]

Agreed. The challenge I'm having is that Alteryx is trying to force one of my columns to be a date field and is therefore taking 10x longer to process due to conversion errors. Little does Alteryx know that the first step after my input is a formula that fixes those conversion errors. It's not until I run the work flow a first time and cache the data that the workflow recognizes that a fix is coming. After data is caches all conversion errors are fixed and the work flow runs in 1/10th the amount of time. 

Any suggested fix that doesn't involve me rewriting the input SQL code?

[nidhipatel]

---

@Timur_O wrote:

It would be a handy feature if it were possible to choose a data type for an input tool to read the data in as. For example, if a dataset has multiple fields with different data types, it would be handy to be able to make the Input Tool read and output them all as a string, if needed. This would also make a handy tool, a sort of blanket data conversion to convert all fields to the specified type.

---

We do not have a provision for the forced data-type at Input. However, we do have a way around. Applying a forced data type at Input. 

For excel inputs, if you check the "Option 6: First row contains data" and then add a select tool. You'll see that the data-type for all the columns is "String"-type. 

You can rename and change the data-type of the columns per your requirement.

This fulfils our objective without any complex changes made to the workflow.

Cheers!

[hroderick-thr]

+1  The unknown columns at least need to default to Wstring. Unknown columns from Excel are frequently breaking

[GG_12003]

In my case I have Excel data externally authored and it arrives with String data (eg 'Pass') in a Custom Format date field.  On import to Alteryx the  data such as 'Pass' is simply showing as '00:00:00'.  No amount of Alteryx side fiddly will change this import behaviour to get the 'Pass' to show up.. 

Clearly Excel's data type controls are the real culprit. However, I need a way to tell Alteryx to ignore Excel's data type and just bring through the data as a string.  It's possible to solve this on the Excel side, but It's impractical to open the excel files individually and change the data format (or save as CSV).

, 

I thought the suggestion from @nidhipatel looked a good workaround.  I extracted the field names as row 1.  Changed data types in a select tool and used dynamic rename to pull the first row data into the Field names.  However it doesn't work for me, the actual data is still not imported and is represented by 00:00:00   rather than 'Pass'.