How to load excels sheet with different schema into cosmos DB using ADF

Sivanesan S 21

I am trying to load some excel files with same sheet name but with different schemas from Azure blob storage to Azure CosmosDB using ADF. I have created the pipeline using wildcard source path. But while trying to preview the data its throwing an error saying that load excel with different schema is not supported now
Can anyone suggest how to proceed further.

HimanshuSinha-msft 19,386 Reputation points Microsoft Employee

2022-03-16T19:09:46.273+00:00

Hello @Sivanesan S ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .
Thanks
Himanshu
Sivanesan S 21 Reputation points

2022-03-17T02:48:49.07+00:00

Hi @HimanshuSinha-msft ,

Thanks for reaching out. Sorry I wasn't able to get back as I got side tracked with other items. Before using for each activity I wanted to import each file manually and check. But while previewing the data all excels are getting pulled with a blank row at the top. And in one excels its just pulling only the blank row not the data row. can you help with why this is happening.

Thanks
Sivanesan
HimanshuSinha-msft 19,386 Reputation points Microsoft Employee

2022-03-23T19:30:06.2+00:00

Hello @Sivanesan S ,
Thanks for the patience , unfortunately wthout the looking at the data , helping will be a bit difficult . If you are OK , please share the excel here ( please please remove actualy data and only share dummy data ) or you can also email me the excel at azcommunity@microsoft.com with the below details .
Subject : Attn Himanshu
Please let me know once you have done the same.
Thanks
Himanshu
HimanshuSinha-msft 19,386 Reputation points Microsoft Employee

2022-03-24T18:53:22.127+00:00

Hello @Sivanesan S ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .
Thanks
Himanshu
Sivanesan S 21 Reputation points

2022-03-25T06:09:25.157+00:00

Hi @HimanshuSinha-msft ,
Thanks for getting back on this.. I guess the issue is happening because of the formatting issue in the excels. Earlier I was trying to copy the data using data flow activity during when I got NULL records inserted into the table. When I tried to copy the same data using copy activity the data got inserted without any NULLs which seems to be weird.

Thanks
Sivanesan

Accepted answer

Samy Abdul 3,366 Reputation points

2022-03-10T13:35:25.393+00:00

Hi @Sivanesan S , Generally, we use Lookup/ GetMetaData activity depending upon the requirement to read through the source and pass the output to ForEach activity which iterate through one by one and subsequently passed to copy activity to copy the data in to the sink.

Below links further helps you to understand it. Thanks

https://azurelib.com/foreach-activity-in-azure-data-factory-with-practical-example/

https://www.youtube.com/watch?v=KuWYuHlUwD0
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

1 additional answer

Samy Abdul 3,366 Reputation points

2022-03-10T13:22:34.93+00:00

Hi @Sivanesan S , the issue on similar lines addressed here:

https://stackoverflow.com/questions/71315791/read-excel-files-with-different-schema-is-not-supported-now-in-dataflow-adf
Please sign in to rate this answer.
Sivanesan S 21 Reputation points

2022-03-10T13:25:30.917+00:00

Hi @Samy Abdul .. Thanks for responding.. Can you guide me to use for each activity as I am new to Azure and ADF
Sign in to comment

Share via

How to load excels sheet with different schema into cosmos DB using ADF

1 additional answer