How to detect if a CSV has no records in ADF pipeline

Question

Hi,

I am quite new to ADF, so please bear with me.

We have a ADF pipeline that has a copy activity. The source is a Azure storage container called input and the destination is another container. The ADF pipeline needs to copy the files from the input container onto the target container. The pipeline has a trigger on 'when a new blob is created' so it gets triggered every time a new file is created on the source location.

All the files in the input container are CSVs and adhere to a schema.

Now it turns out some of the files have only the header row i.e. there are no records in the files other than the header row.

I would like to detect such files that have only header rows and not copy them across.

How can i achieve this in ADF please?

Thanks

Answer 1

Hey,

You can use lookup activity to check whether there are any rows in the blob file or not.

sample :

https://medium.com/azure-tutorials/how-to-use-the-data-factory-lookup-activity-to-read-data-from-storage-during-pipeline-run-12a9b53155d3

Answer 2

Hi @Biju Mathew ,

Welcome to Microsoft Q&A forum and thanks for using Azure services.

As I understand from the question, you want to know How to detect if a CSV has no records in ADF pipeline.

In addition to Nandan's reply to use Lookup Activity to check, below are the detailed steps:

1). First connect to the source dataset folder in Lookup Activity.

2). Add If Condition Activity and user Expression for Row Count check as : @greater(activity('Lookup1').output.count,0)

Here the "greater" function will compare the Lookup Activity count output to Zero and will take to True case if succeeds.

3). In the True Case, add Copy Activity to transfer files from Source to Target satisfying the criteria row count >0.

Here is the detailed demonstration video which covers the scenario stated above: How to Only Copy CSVs which Have Data | Skip Empty Files

Hope this helps.

If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.