Share via

xlsx file has more columns than sink in azure adf

Dinesh Prajapati 126 Reputation points
2023-03-01T14:30:27.44+00:00

I have one scenario where in my excel file has lot of columns, but in the sink azure postgres sql database, I have limited no of columns. What I want is the columns which are not there in the excel needs to be removed or discarded automatically while copy the data in copy activity. It was working for me previously, not sure what got changed, now I am getting error saying that the extra column from excel is not there in sink.
could you please help asap

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,196 questions
Accepted answer
  1. AnnuKumari-MSFT 32,161 Reputation points Microsoft Employee
    2023-03-03T08:01:16.94+00:00

    @Dinesh Prajapati ,

    Thankyou for using Microsoft Q&A platform and thanks for posting your query here.

    As I understand your query, you want the mapping to be handled automatically while copying multiple columns from excel to postgre sql having more columns in source and less in sink. Please let me know if that's not the correct understanding.

    Auto Mapping is only possible when all the columns from the source are available in the sink with the same column name .

    • By default, copy activity maps source data to sink by column names in case-sensitive manner.
    • If sink doesn't exist, for example, writing to file(s), the source field names will be persisted as sink names.
    • If the sink already exists, it must contain all columns being copied from the source.
    • Such default mapping supports flexible schemas and schema drift from source to sink from execution to execution - all the data returned by source data store can be copied to sink.

    Click here for more details on Default mapping in copy activity.

    In case, the column in sink are more than columns in source, you need to explicitly map the source and sink schema manually and remove all the unwanted columns. Click here for more details on Explicit mapping in copy activity.

    You can also define the mapping dynamically by providing the schema in json format. Checkout this video for getting idea on dynamic mapping : Dynamic Column mapping in Copy Activity in Azure Data Factory

    Hope it helps. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest