How to force Alteryx to input the column as text (sharepoint tool)

[kowan]

Hi:) 

I have excel which I want to use loaded via SharePoint tool. The thing is that Alteryx treats it as INT not Text, and cut the leading 0s. 

Here is the example of the input

in excel the column is set to text of course, and what Alteryx is doing: 

of course converting to string in the next steps do nothing as it was removed in 1st stage.. Note that number of 0s can vary, and also not all have 0s.. 

Thank you

Regards

Kornelia

[aatalai]

Have you saved it as a string in Excel?

[kowan]

Hi, do you mean column is treated as text in Excel? yes 

[aatalai]

Can you try adding an apostrophe "' ' " in the Excel to each row at the start of the field?

[kowan]

The problem is that it is not my file, and I don't see that people preparing this will do that:( Anyway I have added this up to first row which contains 00s and it doesn't work 

[Yoshiro_Fujimori]

I tried with SharePoint Input tool and a String field with all numbers was indeed converted to Int64 😮 

I am not sure if there is a fundamental solution on this.

As a workaround, if I am allowed to touch the input Excel file, I would add a column of the length of the number ( with Len() function ).

With that, we know the length of the original string, and then we can retrieve it with Formula tool;

    PadLeft(ToString([Data]), [Length], "0")

I know this is not elegant, but I hope this may be of some help. Good luck.

[kowan]

It is quite good solution but only when you can touch the file :( but the file is coming from somebody else and should be inputted automatically. But I will try to tell that there is an issue and say this potential solution

[KGT]

I don't really know the Sharepoint Input tool but of note here to help with troubleshooting. 

• The column is not set as text in Excel, because Excel does not use 1NF, so the cells are interpreted as text in Excel, but a column does not have a datatype. This is the reason that Alteryx will "figure out" the datatype.
• You just need a character somewhere in that column and it will be interpreted as string on import. Whether you can do that with the header, or the row above, I'm not sure.

This definitely is different, I'm normally cursing Excel for removing the leading zeros....

[kowan]

What I did for time being is indeed one record with XYZ - which in the flow I'm removing, to force the text. This is the id, so we want this 0s.. :) 

[apathetichell]

follow @Yoshiro_Fujimori 's suggestion. The issue is that Alteryx sees that every value in your excel column is a number. It then tries to fit the smallest possible datatype and since int64 is smaller than a similarly sized string ---> it opts for the int. An alternative fix ---> start your data import at 0 vs 1 --- so your headers become data. then use dynamic_rename to take column names from the first row. This assumes your column names are not also numbers.