Hi all, I have an input file in Excel (attached 'Test file with Empty column.xlsx') with 2 columns, ID and Status. Status column is empty. Excel input file: ID Status1 2 3 Problem Statement: When I read this file in Alteryx, Status column is assigned the data type Double but I would like it to be of String data type. Tried 3 methods to change the data type from Double to String: * Formula tool - Using the Expression to replace Status column: ToString([Status], 0) * Multi-Field Formula tool - Changing output type of Status column to V_String with the Expression: IIF(IsNull([_CurrentField_]),'',[_CurrentField_]) * Select tool - Changing the data type to WString When reading the file after applying each transformation as specified above, the Status column is still assigned Double data type. 2 workarounds/solutions to change the data type from Double to String: * Change settings in Input tool and use Dynamic Rename tool* Step 1 - Connect the file to an Input tool and check 'First Row Contains Data' * Step 2 - Use Dynamic Rename tool with Rename Mode: 'Take Field Names from First Row of Data' * Multi-Field Formula tool* Step 1 - Changing output type of Status column to V_String with the Expression: IIF(IsNull([_CurrentField_]),' ',[_CurrentField_]) and write to a file * Step 2 - Read the above Excel as input and use a Data Cleansing tool to remove leading and trailing whitespaces and overwrite the Excel file Questions: * Is there a better way to convert the data type of empty column from Double to String keeping other columns intact? * How does Alteryx assign the data type for empty/null columns? (Note: I tested with other files and it does not depend on the data type of the first column.) Test file with Empty column.xlsx

Alteryx auto assigning empty column Double data type

Hi all,

I have an input file in Excel (attached 'Test file with Empty column.xlsx') with 2 columns, ID and Status. Status column is empty.

Excel input file:

ID	Status
1
2
3

Problem Statement: When I read this file in Alteryx, Status column is assigned the data type Double but I would like it to be of String data type.

Tried 3 methods to change the data type from Double to String:

Formula tool - Using the Expression to replace Status column: ToString([Status], 0)
Multi-Field Formula tool - Changing output type of Status column to V_String with the Expression: IIF(IsNull([_CurrentField_]),'',[_CurrentField_])
Select tool - Changing the data type to WString

When reading the file after applying each transformation as specified above, the Status column is still assigned Double data type.

2 workarounds/solutions to change the data type from Double to String:

Change settings in Input tool and use Dynamic Rename tool
- Step 1 - Connect the file to an Input tool and check 'First Row Contains Data'
- Step 2 - Use Dynamic Rename tool with Rename Mode: 'Take Field Names from First Row of Data'
Multi-Field Formula tool
- Step 1 - Changing output type of Status column to V_String with the Expression: IIF(IsNull([_CurrentField_]),' ',[_CurrentField_]) and write to a file
- Step 2 - Read the above Excel as input and use a Data Cleansing tool to remove leading and trailing whitespaces and overwrite the Excel file

Questions:

Is there a better way to convert the data type of empty column from Double to String keeping other columns intact?
How does Alteryx assign the data type for empty/null columns? (Note: I tested with other files and it does not depend on the data type of the first column.)

Test file with Empty column.xlsx

Input

Accepted answers

All comments

davidskaife

Hi @Aastha

In regards to your first question, changing the field type to WString does work in the Select Tool, unless i'm missing something?

flying008

Hi,@Aastha

******

Q1: Is there a better way to convert the data type of empty column from Double to String keeping other columns intact?
A1: Yes, there is a smart resolution of Dynamic Metadata for you, it can automatic convert empty columns type to V_String and keep other columns nature.

Tips: If your non-empty columns have null row, you must use Data-Cleansing tool to remove null rows first.

******

Q1: How does Alteryx assign the data type for empty/null columns? (Note: I tested with other files and it does not depend on the data type of the first column.)
A1: This is a very interesting topic, but personally I found that there is no pattern, the empty column type may be Double or Bool or other.

录制_2022_07_21_15_07_29_209.gif

Emmanuel_G

Hi @Aastha ,

With select tool, you can edit the type easily ( Take a look at screenshoot below ).

However, keep in mind that some conversions will not work, such as converting strings to numeric type (Int, double or fixed decimal).

Doing such a conversion will allow you to change type but your column values will become null so you will lose data.

Aastha

@Emmanuel_G , @flying008 and @davidskaife Thanks for your response. I originally had output a file from Alteryx with a blank column of datatype “String”. But when I read the same file back into Alteryx, that column was read in as “Double”.

This creates a problem because when I am building an analytic app and the interface tool is not picking up the column that I want with “String” datatype due to it being “Double”.

So the 2 solutions I mentioned in my post did not work and should not be considered as a solution. I am still looking for a way to solve the problem above.

BransonJamesFarley

Ever find a solution for this?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703