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 |
2 Answers
Reset to default 0How 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!