We have an ADF pipeline to transform an Excel file to CSV.

The Excel file has numeric column containing decimal data.

Example:

Data
15000000
23219284.250000

We have an ADF pipeline to transform an Excel file to CSV.

The Excel file has numeric column containing decimal data.

Example:

Data
15000000
23219284.250000

We tried using double data type for this column in Data Flow source.

However, it outputs them as:

CSV Output using Double
1.5E7
2.321928425E7

We also tried using Decimal (18,6) for this column but it outputs trailing zeros:

CSV Output using Decimal (18,6)
15000000.000000
23219284.250000

How can I change the CSV output such a way that it removes the trailing zeros and does not use exponential notation (without explicitly using Decimal(18,2) data type)?

Expected CSV Output
15000000
23219284.25

• azure-data-factory

Share Improve this question Follow edited Mar 10 at 13:40 developer asked Mar 10 at 12:07 developerdeveloper 1,62744 gold badges3838 silver badges8686 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

How can I change the CSV output such a way that it removes the trailing zeros and does not use exponential notation (without explicitly using Decimal(18,2) data type)?

Follow the below steps to get the expected output:

Step1: Please try using the following expression to achieve the expected results. replace(toString(toDecimal(Data)),'.00','')

Step2: I have used the same sample data that you provided.

Step3: Use the following expression in the derived column as required. replace(toString(toDecimal(Data)),'.00','')

Step4: Output is as expected as per your requirement.

This should get you exactly what you need and no need for an entire Data Flow. This is a simple ADF Copy activity.

1. Test File

2. ADF COPY SOURCE

3. ADF SINK

4. ADF MAPPING (IMPORTANT)

5. Output (Note: If you don't expand the column, it will visually show the 1.5E+08, I will show what I mean below)

Example of it not expanded on the column and visual of it in VS Code (not Excel Desktop):

Cheers!