ADF Copy Data Activity from CSV to SQL table incorrectly converts string values

Question

ADF Copy Data Activity from CSV to SQL table incorrectly converts string values

Azure_Noob 30

I have a csv file that I am trying to upload to a table on SQL Server. I am uploading all the data into SQL as a string. Columns that contain money values such as -100 are being converted to (100) despite having data type mapping in ADF being string -> nvarchar. How do I correct this so that the upload from ADF keeps -100 as -100 in SQL table?

Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2023-06-10T10:48:17.78+00:00

Hello @Azure_Noob,

Thanks for using MS Q&A portal and posting your query.

The issue is with the formatting of CSV file you are loading. If you open the file in Notepad++, if you see the values as (100) instead of -100, then for copy data activity also, it will be appearing as (100) and eventually it will be loaded as (100).

For you to cirumvent this issue, kindly format all your csv data as Text and save as csv again and retry running copy data activity.

Kindly let us know how it goes.

Thanks
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2023-06-12T14:07:55.12+00:00

Hi @Azure_Noob
Did you get a chance to check the below answer? Please let us know if it helped, or otherwise too,
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2023-06-13T02:36:34.95+00:00

Hi @Azure_Noob Just checking to see if the below answer helped you. If so, request you to accept the answer and rate. Otherwise please let us know for further assistance. Thanks
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2023-06-15T12:39:47.5333333+00:00

Thanks for your response and glad that your issue was resolved.

1 answer

Your answer

Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2023-06-10T10:48:17.78+00:00

Hello @Azure_Noob,

Thanks for using MS Q&A portal and posting your query.

The issue is with the formatting of CSV file you are loading. If you open the file in Notepad++, if you see the values as (100) instead of -100, then for copy data activity also, it will be appearing as (100) and eventually it will be loaded as (100).

For you to cirumvent this issue, kindly format all your csv data as Text and save as csv again and retry running copy data activity.

Kindly let us know how it goes.

Thanks
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2023-06-12T14:07:55.12+00:00

Hi @Azure_Noob
Did you get a chance to check the below answer? Please let us know if it helped, or otherwise too,
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2023-06-13T02:36:34.95+00:00

Hi @Azure_Noob Just checking to see if the below answer helped you. If so, request you to accept the answer and rate. Otherwise please let us know for further assistance. Thanks
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2023-06-15T12:39:47.5333333+00:00

Thanks for your response and glad that your issue was resolved.

Answer 1

Azure_Noob 30

Hi I was able to find a solution. I should have mentioned that I can not alter the source file, so your solution does not apply for me unfortunately. I imported the data as is and simply did nested replace functions to change the parenthesis to negative.

QuantumCache 20,676 Reputation points Moderator

2023-06-14T19:56:34.67+00:00

Hello @Azure_Noob,

Thanks for sharing more info and context on the solution.

Please feel free to post any further question if you have, we are happy to help!

Share via

ADF Copy Data Activity from CSV to SQL table incorrectly converts string values

1 answer

Your answer