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

Lavanya Bhajanthri 200 Reputation points
2023-11-29T05:56:31.4766667+00:00

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?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,177 questions
{count} votes

Accepted answer
  1. Smaran Thoomu 12,615 Reputation points Microsoft Vendor
    2023-11-29T12:30:16.04+00:00

    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.


0 additional answers

Sort by: Most helpful