Alteryx Input Tool Reading a String data type Column as Double

[surajmthomas]

I have a scenario where I have a dataset with 12 K Rows. One of the columns named "External_Customer_ID" has 10K rows as numeric (Example value 5678453) and 2K rows as String (Example values SAE-45372, AGHDSET, 54DFSGH2).

I am reading an .xlsb file using the INPUT TOOL. The data type assigned to the column "External_Customer_ID" is Double.

Therefore, 10K Rows are read properly, but 2K rows are showing up as NULL.

Is there a way to force Alteryx to read a Column using the INPUT TOOL as a String Datatype ?

(Using the SELECT TOOL to change the datatype After will not work because the 2K records are already read as Null and the application of the SELECT TOOL will work on the Null values which is not the expected behaviour)

The other alternative (kind of a dirty workaround) I found was to check the "First Row Contains Data" check box in the configuration of the INPUT TOOL, which will force the Column Headers in my excel to be treated as data and the data type invariably be assigned as String. I can convert the first row back to Headers using the DYNAMIC RENAME Tool under the Developer Tab. I can then use the SELECT TOOL to reassign the right data type. The only problem with this is that my Date Field gets affected and is read as 4362.

[atcodedog05 · Accepted solution]

Hi @surajmthomas 

You can use ToDate() function in formula tool to convert numeric date to Alteryx date.

Hope this helps 🙂

[danilang · Accepted solution]

Hi @surajmthomas 

I remember reading somewhere that the input tool only scans the first X thousand rows before determining the data type for the column.  Unfortunately I can't remember where, nor can I remember the the value of X.  

For your date issue, the number seems a little small.  Numeric dates in excel are referenced from 1899-12-30, so unless your dates are pre 1930, you should have 5 digits.  

Another little known fact. If you convert the date field to numeric and then use ToDate() on the number field, it will automagically convert the number to the proper date

Dan

[Qiu · Accepted solution]

@surajmthomas 

Maybe your "dirty" might the way.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/xslb-file-that-contains-data-but-alter...

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Alteryx-auto-reading-a-field-to-be-Num...

then conver the date data by ToDate().

[surajmthomas]

Thank You Everyone for your help

[surajmthomas]

Thank you @atcodedog05

[Qiu]

@surajmthomas 
Glad to help and thank you for the accept mark. 😁