Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Suggested answer

Data management extracts to Excel are saving numbers as text

(0) ShareShare
ReportReport
Posted on by 105

Hey guys,

We have found issues with the way numbers are being  published - they are showing as text (e.g. column C below). Can you please advise how these can be exported as numbers?

Cheers,

Regan

pastedimage1676348663522v1.png

pastedimage1676348693421v2.png

...

  • ReganMitchell Profile Picture
    ReganMitchell 105 on at
    RE: Data management extracts to Excel are saving numbers as text
    [quote user="Alireza Eshaghzadeh"]

    Hi Regan,

    You need to change replace Dot to Comma in Excel file manually so it will indicate the values to number for this field.

    There is a setup for source format but it can just change the separator indicator in the excel file while it cannot change the format . 

    pastedimage1676549271603v1.png

    pastedimage1676549400439v2.png

    [/quote]

    Hey Alireza,

    Sorry, I'm not 100% sure what you're suggesting. We are based in Australia, therefore our numbers use periods/fullstops "." as our decimal point indicate, not comma's. Currently our Excel export parameter is set to the same as your screenshot. 

    Would you mind explaining to me again what you are suggesting I change to get this to work?

    Cheers,

    Regan

    pastedimage1676582465033v1.png

  • Suggested answer
    Alireza Eshaghzadeh Profile Picture
    Alireza Eshaghzadeh 13,564 Super User 2025 Season 1 on at
    RE: Data management extracts to Excel are saving numbers as text

    Hi Regan,

    You need to change replace Dot to Comma in Excel file manually so it will indicate the values to number for this field.

    There is a setup for source format but it can just change the separator indicator in the excel file while it cannot change the format . 

    pastedimage1676549271603v1.png

    pastedimage1676549400439v2.png

  • ReganMitchell Profile Picture
    ReganMitchell 105 on at
    RE: Data management extracts to Excel are saving numbers as text

    Thanks Girish - understood. Unfortunately those alternatives aren't suitable for us as we are utilising the automation function of the data entity to regularly extract. I will leave this ticket open in case someone has another alternative.

  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    RE: Data management extracts to Excel are saving numbers as text

    You misunderstood my suggestion - I asked you to create computed column or virtual field if you want $ symbol before amount value.

    You already said that you don't need $ symbol - So forget about computed column and virtual field.

    I too tried the same GeneralJournalAccountEntryEntity - I too had the same warning when I open the excel.

    There is no setup available to store the real field as a number field rather the text field.

    Also AccoutingCurrencyAmount field is Real field.

    Seems its common issue when you export the data using DMF and column contains real field values.

    This issue is not happening when you use export to excel option from table browser.

    One more option will be try using Open in excel functionality.

    Thanks,

    Girish S.

  • ReganMitchell Profile Picture
    ReganMitchell 105 on at
    RE: Data management extracts to Excel are saving numbers as text
    [quote user="GirishS"]

    If you require a $ symbol, then the field must be string. Because number field wont allows you to save values with $ symbol.

    I am not sure whether there is alternate field available which is of number type.

    One more workaround will be using the Computed column and virtual field on the data entity which requires a developer do it.

    You can ask a developer to try the suggestions.

    Thanks,

    Girish S.

    [/quote]

    Hey Girish,

    I have spoken to our DEV resource and they have confirmed that it is not a computed column - it is mapped to actual field in a table. Does this change your advice at all?

    Cheers,
    Regan

    pastedimage1676415435853v1.png

  • ReganMitchell Profile Picture
    ReganMitchell 105 on at
    RE: Data management extracts to Excel are saving numbers as text

    Sorry, I should clarify - I don't need the $ symbol I just need the amount in a number format for Pivot Queries.

    OK, I'll speak with DEV about options for "Computed column and virtual field on the data entity" - thanks for your advice.

  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    RE: Data management extracts to Excel are saving numbers as text

    If you require a $ symbol, then the field must be string. Because number field wont allows you to save values with $ symbol.

    I am not sure whether there is alternate field available which is of number type.

    One more workaround will be using the Computed column and virtual field on the data entity which requires a developer do it.

    You can ask a developer to try the suggestions.

    Thanks,

    Girish S.

  • ReganMitchell Profile Picture
    ReganMitchell 105 on at
    RE: Data management extracts to Excel are saving numbers as text
    [quote user="GirishS"]

    Hi Regan,

    Seems the field you are referring to is a string field. If its a string field, it will be stored as a text only even though it has only numbers.

    There is no setup available in d365 to avoid this. I think this is the standard behavior.

    Thanks,

    Girish S.

    [/quote]

    Hey Girish,

    Thanks for your response. Apologies, I'm not so technical - is there another value field that can be obtained from D365 F&O and added to this report that isn't a string field? We require the $ for each line but require it as a number for analysis rather than Text.

    Cheers,

    Regan

  • Suggested answer
    GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    RE: Data management extracts to Excel are saving numbers as text

    Hi Regan,

    Seems the field you are referring to is a string field. If its a string field, it will be stored as a text only even though it has only numbers.

    There is no setup available in d365 to avoid this. I think this is the standard behavior.

    Thanks,

    Girish S.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Congratulations to the January Top 10 leaders!

Check out the January community rock stars...

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,031 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,868 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans