How to convert multiple fields to "double" excluding texts

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

Incomplete data clean up.yxmd

Sample.xlsx

Workflow

Accepted answers

DawnDuong

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

All comments

binu_acs

@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

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