Finance | Project Operations, Human Resources, ...

Data management extracts to Excel are saving numbers as text

(0) Share

Report

Posted on by ReganMitchell

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

...

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

GirishS 27,825 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
ReganMitchell 105 on at

Like (0)

Report

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

Was this reply helpful? Yes No
GirishS 27,825 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
ReganMitchell 105 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
ReganMitchell 105 on at

Like (0)

Report

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

Was this reply helpful? Yes No
GirishS 27,825 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
ReganMitchell 105 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Alireza Eshaghzadeh 14,637 Super User 2025 Season 2 on at

Like (0)

Report

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 .

Was this reply helpful? Yes No
ReganMitchell 105 on at

Like (0)

Report

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 .

[/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

Was this reply helpful? Yes No