how can I validate if there is any empty rows in excel file from blob storage using azure datafactory

CatalinaGarca-7814 0 Reputation points
2023-07-31T22:43:27.08+00:00

I have several excel files in blob storage and I need validate if there is any empty rows because I want copy the data to sql but without empty rows.

I need that all be dynamic, for example if I could specify a dynamic range inside the copy activity or something like that...

Thanks

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

2 answers

Sort by: Most helpful
  1. Nandan Hegde 34,171 Reputation points MVP
    2023-08-01T02:24:24.0766667+00:00

    Hey,

    you can use the below steps:

    1. use getmeta data activity to get the list of excel files
    2. iterate the files through for each activity
    3. within for each activity, use dataflow transformation to filter out the records with empty rows via filter transformation

    https://learn.microsoft.com/en-us/azure/data-factory/data-flow-filter

    1. copy the reminder of data into your required sink

    this is assuming there is only 1 sheet per excel file.


  2. KranthiPakala-MSFT 46,602 Reputation points Microsoft Employee
    2023-08-01T22:15:01.36+00:00

    @CatalinaGarca-7814 Thanks for using Microsoft Q&A forum and posting your query.

    I just tested with a sample excel file with empty rows in between actual data and ADF copy activity and mapping data flow are by default skipping the empty rows from source file and copying all the valid rows without empties to the sink data store.

    Here is how my source file data looks like:

    User's image

    Data Preview:
    User's image

    Sink data store after successful copy run:
    User's image

    Hope this helps. I don't see a reason for additional validation in this case unless if there is a need/requirement to check (Let me know if that is the use case). If I missed anything or if you are noticing a different behavior from your end, kindly share a sample file (with dummy) data so that I can test it using that data and can assist accordingly.


    Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.