How to convert multiple fields to "double" excluding texts

[Guccio-Gasparrini]

Hello community,

I creating a workflow that cleans up files. I am having some trouble converting the text columns to doubles while excluding columns that should remain as text.

After eliminating the useless header from files, I use auto field to convert columns to their appropriate data type. This doesn't always work because some numbers have a lot of decimals. To convert these into doubles, I use the multi-field formula and select all the texts, change output type to double and use the formula "tonumber([_CurrentField_],1,1)".

This does the trick except one problem: the text column that should remain a text becomes null.

I plan to run this workflow for thousands of different files with different column names, so I can't manually exclude the text files, is there a formula that excludes texts from getting converted into null?

I attached the workflow and the sample file.

Please let me know the changes I need to do to keep the columns with letters as string and successfully convert all the columns with numbers into doubles

[binuacs]

@Guccio-Gasparrini I think Alteryx doesn't support multiple data type for a single column. If you want to keep both numeric vales and text you should keep the data type as String

[Guccio-Gasparrini]

@binuacs I believe you misunderstood what I was saying. I do not intend to have multiple data type for a single column. What I intend to do is convert all the columns with numbers into doubles and all the columns with letters into string.

[DawnDuong]

Hi @Guccio-Gasparrini 

the reason why using simple multifield tool does not work is because it does the conversion for all fields without discrimination.

Is there a rule in the column name to identify if a column should be a text or a numerical field? Or ask it another way, when you check the outcome which rules / criteria do you use to determine if a field should be categorised as text or numeric?

if there is you can build in the rule using the “FieldName” parameter of the Multifield tool to only convert those that meet the criteria.

If such a rule exists then you can avoid having to look into the content of each column - which is possible but cumbersome and computationally ”expensive” and most methods (including the Autofield tool) relies on the first N rows to classify the data type to numeric or text.

dawn 

[Guccio-Gasparrini]

Hi @DawnDuong 

>Is there a rule in the column name to identify if a column should be a text or a numerical field?

The only way to identify if a column should be a text or a numerical field is that when it is converted to a double, a text would come out as null. You can see this in the workflow I posted.

Is there perhaps a formula I can add in the multi field feature that makes an exception if the result is null?

[DawnDuong]

Hi @Guccio-Gasparrini 

i see, so the type needs to be determined based on the content itself. I am unable to access internet on laptop so i shared the pix of a workflow that may do the trick for your use case.

you can see the initial text inputs are Changed to proper double/text at the output.

Hope this helps.

Dawn 

[Guccio-Gasparrini]

@DawnDuong 

I deselected the "change output to" and I used your formula

"if !tonumber([_currentfield_])=0
then tonumber([_currentfield_])
elseif ([_currentfield_])="0" then 0
else [_currentfield_]
endif"

However this does not change the string field that needs to be converted.

Perhaps you can share the workflow you generated?

I'll share mine with your modifications. Let me know if I am still doing anything wrong.

[DawnDuong · Accepted solution]

I am unable to view the workflow at the moment.

did you have an auto field at the end?

this is a kind of “augmented” autofield process 

the change only happens after the autofield tool 

dawn

[Guccio-Gasparrini]

@DawnDuong 

I applied it at the autofield before the formula and it didn't work. I now have applied it after the formula and now it works! Thank you very much