Incorrect decimal placement importing from csv file

CommunityMember115752
CommunityMember115752
edited July 8 in Modeling
ImportAsNum.png

Hi, I've noticed strange behaviour when importing into a number formatted cell in Anaplan from a csv file.

My csv file is directly exported from a Workday report, and the csv file contains 2 decimal places for some entries, but some entries are rounded to 0.dp where the two d.ps are ".00".

Anaplan is incorrectly putting the decimal point on the entries with 2 d.p.s. I attach an example below.

the workaround i'm using is to import as a text formatted cell (where it imports correctly) and then use VALUE() to convert to a number. But this seems like an inefficient workaround (duplicates the Amount column unnecessarily).

Is there a better solution?

Tagged:

Best Answer

  • Dikshant
    Dikshant
    Answer ✓

    This is the csv file

    image.png

    Tried loading it in number format line item in list property

    image.png
    image.png
    image.png

    Let me know what am I missing ? Or is this the expected result ? If yes, then we need to look at the file, it must be a delimiter issue.

    image.png

Answers

  • ahemantthere
    ahemantthere
    edited July 8

    Have you changed the format of the AmountasNum line item to Decimal with 2 significant places?

    image.png

    You can try this and import again. The amounts which have no decimal places ex. "76" will be imported as 76.00, and the ones which do have decimal places will be imported as ex. "89.95".

  • PulkitChawla
    PulkitChawla

    @david_rickard - When you are importing your .csv file, make sure to set the decimal point as dot. In your case, you have kept it as comma that's why you are not getting the correct values.

    So, simply go to import data source and update it's decimal point to dot and you should be good then.

  • CommunityMember115752
    CommunityMember115752

    Unfortunately neither of these are solutions as i've already applied those settings:

    image.png

  • Dikshant
    Dikshant
    edited July 8

    Is it possible to share a screenshot of the number format column in your csv ? Not an actual number but an example to understand better ?

  • CommunityMember115752
    CommunityMember115752

    Thanks @PulkitChawla. Changing the Decimal seperator to Dot worked. As I was opening the csv file in Excel I didn't really appreciate the commas where present in the csv file itself. Thanks for your solution.

  • PulkitChawla
    PulkitChawla

    @david_rickard - Glad to hear that.

Categories