reading a protected excel sheet using copy data activity and handling NULL values ?

Question

Hi Team,

The input excel sheet has a table with 100 protected rows with data only in 20 rows and 80 rows are empty and protected . The destination table can not take in NULL values.

How can we make the copy command run only on the 20 rows, without bothering about the 80 empty protected rows.

Thanks,
Jas

Answer 1

Hi @Jaspreet Singh

Welcome to Microsoft Q&A forum and thanks for reaching out here.

If you want to copy only the cell/rows with data and exclude empty rows, then one option is to define the cell range in the dataset properties as explained in below screenshots which should help achieve your requirement. For example if you want to copy only first 20 rows then you can select a range like A1:C21 (i.e assuming you have 20 rows with data including 1 header column)

Data preview when cell range is not selected:

Data preview when cell range is selected to limited cells (Cells that has data only)

Here is the documentation related to this functionality in excel dataset: Excel dataset properties

Hope this info helps. Do let us know if you have further query.

----------

• Please don't forget to click on and upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators