Preserving Percentage Format Challenges with ADF Copy Activity and SQL Table Loading

Question

Preserving Percentage Format Challenges with ADF Copy Activity and SQL Table Loading

Lavanya Bhajanthri 200

I have percentage values in an Excel file and aim to load them into a SQL table using Azure Data Factory copy activity. However, during the data load, the percentage values are being converted to decimals, even after modifying the datatype to varchar. What approach should I take to ensure the percentage values are loaded directly in the percentage format into the SQL table?

Subashri Vasudevan 11,231 Reputation points

2023-11-29T09:12:46.5866667+00:00

Hi, after modifying datatype in the table, did you refresh the schema in the data set pointing to SQL?

Please make sure, you are poiting to the right table in the right database, as sometimes we overlook the DB\table names.
Smaran Thoomu 24,750 Reputation points Microsoft External Staff Moderator

2023-12-01T07:18:31.4366667+00:00

@Lavanya Bhajanthri Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Accepted answer

0 additional answers

Your answer

Subashri Vasudevan 11,231 Reputation points

2023-11-29T09:12:46.5866667+00:00

Hi, after modifying datatype in the table, did you refresh the schema in the data set pointing to SQL?

Please make sure, you are poiting to the right table in the right database, as sometimes we overlook the DB\table names.
Smaran Thoomu 24,750 Reputation points Microsoft External Staff Moderator

2023-12-01T07:18:31.4366667+00:00

@Lavanya Bhajanthri Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

Smaran Thoomu 24,750 Microsoft External Staff Moderator

Hi @Lavanya Bhajanthri ,

Thank you for reaching out to us. I understand that you're facing challenges with preserving percentage format during data load from an Excel file to a SQL table using Azure Data Factory copy activity.

If the column type is set as Percentage in the source Excel, ADF will read the data as decimal. You can correct this either in the source Excel or after copying the data to the SQL table using a SQL script.

You can use the Script activity, next to copy activity and give the query as:

update <table_name>
set
<column_name>=<column_name>*100

User's image

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Smaran Thoomu 24,750 Reputation points Microsoft External Staff Moderator

2023-11-30T09:49:39.2066667+00:00

Lavanya Bhajanthri Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Lavanya Bhajanthri 200 Reputation points

2023-12-05T19:58:26.7433333+00:00

Hi smaran, thank you that works, sorry delaying

Share via

Preserving Percentage Format Challenges with ADF Copy Activity and SQL Table Loading

0 additional answers

Your answer