import a column with alphanumeric into number formatted property

Hi , 

can you assist me in following , 

so i am loading a file with a column into a number formatted list property , in the csv file along with numbers some entries have text only while some entries have text along with number(as shown) , 



when i load this file in model , so all text lines are ignored , but somehow anaplan is taking only numbers from the lines where alphanumeric property is there 


after running the action for this list item , the property is 2411.2 , 

please guide as to how is this happening and what is workaround for this case


Best Answer

  • ryan_kohn
    Answer ✓

    Ideally, you would resolve this in an upstream system -- either in the data source or in your ETL layer.


    You can also build in some light transformation in Anaplan to account for a data set like this. In this case, instead of importing the value as a Number, you would import the value as Text, and convert the value into number using Anaplan formulas.




    Above is an example of how you could accomplish this. Note that the VALUE() function in Anaplan will return NaN for a non-numeric value, so you'll need to have additional logic to convert those NaN values to zeros using one of the techniques in this article.


  • This is an interesting one! I tried with a few other values below (importing the text value on the left into the number-formatted field on the right), with very intriguing results. The only one that failed was the one that didn't have spaces between the letters and the numbers. Otherwise, it appears that Anaplan will **** out any "whole" words (e.g. text completely surrounded by spaces), and then concatenate any remaining numbers. I am not as of yet able to find any documentation around this functionality, but I would also recommend submitting a case to [email protected]





    As to workarounds for your specific use case, what would be the desired functionality? For example, would you want the data in that row to import in some specific format (other than 2411.2), or would you want an error to be reported and not have the value imported at all?


  • Hi @ryan_kohn 


    Oh , thanks for the update , for the workaround yes it should ignore these values and should show 0 . Can you guide on this


    I would ask support on this