How to stop ADF from formatting negative numeric string values in SQL table?

Question

I am new to ADF.

I have a copy data activity that uploads data from a csv file into a SQL Server table. The mapping for every column in the activity is string -> nvarchar. In the csv I have columns that contain values such as -100. Yet the copy data activity converts this string value into (100) in my SQL table. How do I stop this conversion?

The real goal would be to have ADF upload the string csv column into a money SQL table column. However on csv side the values in the string column are formatted as -, -100, 100 and the - (representing 0) can not be easily converted to money by ADF, or at least I am not aware of a way to do it?

EDIT: Ok so the columns are formatted as Number in the excel csv, so despite displaying -100 ADF is reading (100)? Is there an activity I can use to replace these parentheses with negative?

Answer 1

When you have the negative number (-100) represented in between parenthesis in Excel, and save the file as comma separated values, the would be as shown below:

id,name
(100.00),Ana
2.00 ,Topson
100.00 ,Ceb
20.00 ,Miracle

• To change it back to -100, you can do the following steps. First use the lookup activity on this data. The following is how the data would look:

• Now, using the set variable activity to replace the ( with - and ) with an empty space. The following is the dynamic content that I used:

@replace(replace(string(activity('Lookup1').output.value),'(','-'),')','')

• Now using OPENJSON, you can insert the data from this array of objects string directly into your table. The following is the query that I used:

INSERT INTO t1
SELECT * FROM OPENJSON('@{variables('tp')}')
WITH (id NVARCHAR(100),name NVARCHAR(100))

• The following is how the data reflects in the SQL server table. I have used azure SQL database for demonstration: