Problem with input data format

[MK97pro]

Hi Community,

I am looking for solution connected with input data format.

As you can see below, my original format of the data looks like this:

but the data inside it is 

294182,21

260626,39

224459,65

the custom format in excel is: [$-en-US,1]# ##0 ;(# ##0 );-

Unfortunately, the Alteryx input data tool is not going inside of it and showing only value after formatting without part of data. 

Could you please help to clear such format in alteryx? Thanks in advance.

[jamielaird]

Hi @MK97pro 

When reading in from Excel Alteryx will bring in the underlying number without any cell formatting applied. Are you saying that you're seeing different behaviour? If so, could you post up an anonymized example of your workflow so we can investigate?

[MK97pro]

Hi @jamielaird 

please find attached excel file with such format which alteryx would import without thousands.

[jamielaird]

Hi @MK97pro ,

What you're seeing is the correct behaviour. Alteryx will bring in the underlying number and disregard any formatting applied in Excel.

If you'd like to display the values in the same way in Alteryx you can use the formula:

ToString([F1]/1000,0,1)

As shown in the example below and the attached workflow.

However please note that once you apply the conversion the value will no longer be recognized in Alteryx as a number, so you should only apply a transformation like this at the very end of a workflow before outputting the value.

[MK97pro]

Sorry,

you do not catch my request. In below comment I tried to explain the topic again.

[MK97pro]

@jamielaird 

sorry for interrupting you, but its not the solution of my problem. In my case it should be from 2,952 to 2951515 (ie. to the value from the excel file which is hidden due to custom format.

[jamielaird]

Hi @MK97pro ,

Gotcha. That's exactly what you should get by default, so back to my original question - are you seeing something different?

Here's how Alteryx will read in the values in your Excel file. As you can see Alteryx is reading in the underlying exact number disregarding any formatting applied in Excel: