Trying to filter csv file based on content

Question

Trying to filter csv file based on content

Justin Doh 1,000

I have an existing ADF pipeline that is creating an error due to some bad (or not acceptable data) displayed on the csv file.

Basically, instead of having proper header on the first row with certain amount columns (with data), csv file displays data like "There is no data to report." on the first row.

Originally, pipeline reads the file name by expression such as bottom inside Pipeline expression builder:

@concat('XXX_Check_DetailPalm Beach Corp' ,formatDateTime(addDays(utcNow(),  pipeline().globalParameters.DifferenceOfDate),'yyyyMMdd'),'*','.csv')

This expression is inside "Copy data".

This original pipeline was working fine until csv file with some weird data shows up that pipeline started failing.

I asked Microsoft Co-pilot to give me some architecture of this logic, and it suggested using first "Get Metadata" to grab "child items", and then use "ForEach" to read each csv files, and then inside "ForEach", I have second "Get Metadata" using a parameter and @item().name, and then, it goes to "If Condition" and "Copy data".

The error happens at "Copy data" (inside If Condition) that it still takes a csv file that has a bad data even though there is an expression that indicates to grab a csv file that has specific column names inside a csv file.

I would like to know the architecture of this type of logic first and possibly go into more details like Expression later.

Thank you so much!

Pilladi Padma Sai Manisha 11,705 Reputation points Microsoft External Staff Moderator

2026-07-22T22:53:26.01+00:00
Hi @Justin Doh

Thank you for reaching out Microsoft Q&A!.

Based on your scenario, the overall pipeline design of using Get Metadata → ForEach → If Condition → Copy Data is a reasonable approach. However, the key limitation is that the Get Metadata activity can retrieve file-level metadata such as the file name, size, and last modified time, but it cannot inspect the contents of the CSV file. As a result, it cannot determine whether the first row contains the expected header or an unexpected message such as "There is no data to report."

If the validation is currently being performed only using the file name or metadata, the pipeline will still attempt to process the invalid file, causing the Copy Data activity to fail.

To validate the file contents before copying, you can use a Lookup activity to read the first row (or first line) of each CSV file and then use an If Condition activity to verify that it contains the expected header. If the validation succeeds, proceed with the Copy Data activity; otherwise, skip the file or move it to a separate location for further review.

Please note that the Copy Data activity does not support filtering files based on their contents. If you need to validate or filter data within the file at scale, consider using a Mapping Data Flow, Azure Databricks, or another custom processing step before the copy operation.

To better understand your scenario, could you please confirm the following?

Where are the CSV files stored (Azure Blob Storage or Azure Data Lake Storage Gen2)?

Is the invalid file always a single line containing "There is no data to report.", or are there other variations?

Are you using a DelimitedText dataset with First row as header enabled?

With this information, we can suggest the most suitable validation approach for your pipeline.
Justin Doh 1,000 Reputation points

2026-07-22T23:06:31.82+00:00
@Pilladi Padma Sai Manisha Thank you for getting back to me.

CSV files are stored in Azure File service

There is no other variations at this point besides some csv files have first row containing text - "There is no data to report".

Yes, I use DelimiteeText dataset with First row as header - checked.
Pilladi Padma Sai Manisha 11,705 Reputation points Microsoft External Staff Moderator

2026-07-24T22:07:00.0833333+00:00
Thank you for the additional information.

Based on what you've shared, the behavior is expected. Since the DelimitedText dataset is configured with First row as header, the Copy Data activity expects the first line of every CSV file to contain the column headers. When a file instead contains the text "There is no data to report." as its first line, ADF interprets that line as the header, resulting in a schema mismatch and causing the Copy activity to fail.

Because your files are stored in Azure Files, the recommended approach remains to validate the file contents before invoking the Copy Data activity. While Get Metadata can enumerate the files, it cannot inspect their contents. A common pattern is to use a Lookup activity to read the first row (or first line) of each file, followed by an If Condition to verify that the expected header is present. Only files that pass this validation should be sent to the Copy Data activity, while files containing "There is no data to report." can be skipped or moved to a separate folder for review.

To help us provide more specific guidance, could you also let us know whether the expected header is always identical across all valid CSV files? If possible, please share:

A sample of a valid CSV file (header and the first few rows).

A sample of an invalid CSV file that contains "There is no data to report.".

This will help us suggest the appropriate expression or pipeline logic to validate the file before processing.
Justin Doh 1,000 Reputation points

2026-07-25T01:21:02.6833333+00:00

@Pilladi Padma Sai Manisha

I was trying to upload csv files, but this site only allows txt files format.

So, I uploaded two csv files to Google drive:

https://drive.google.com/file/d/1dpBEfzUikq8hOIbOn-Am5JFltraOPdNz/view?usp=sharing

https://drive.google.com/file/d/1UdVV06xuCxak-NKIy86b7-cQId1U6Qh4/view?usp=sharing
Justin Doh 1,000 Reputation points

2026-07-27T21:45:04.44+00:00

@Pilladi Padma Sai Manisha

I would like to follow up on this.

So do we only need two/three parts ( Lookup Item --> If Condition (true: Copy data) )?

Thanks.
Pilladi Padma Sai Manisha 11,705 Reputation points Microsoft External Staff Moderator

2026-07-28T20:58:43.1133333+00:00
Hi @Justin Doh,

Thank you for sharing the screenshots of both the valid and invalid CSV files.

From the examples you've provided, the behavior is expected. The valid file begins with the expected column headers, whereas the invalid file contains only the text "There is no data to report." as its first line. Since your DelimitedText dataset is configured with First row as header, ADF interprets that text as the header row. When the Copy Data activity attempts to map the expected columns, it cannot find them, resulting in the failure.

Unfortunately, the Copy Data activity does not have a built-in capability to filter or skip files based on their contents. Likewise, the Get Metadata activity can enumerate files but cannot inspect the first line of a file.

To handle this scenario, you'll need to introduce a validation step before the Copy Data activity. A common approach is:

Use Get Metadata to enumerate the CSV files.

Iterate through the files using a ForEach activity.

Use a Lookup activity to read the first line (or first row) of each file.

Use an If Condition to verify that the first row contains the expected header (for example, that it starts with FacilityName or another known header column).

If the validation succeeds, continue with the Copy Data activity. Otherwise, skip the file or move it to a separate folder for review.

Based on the sample you shared, checking whether the first row starts with FacilityName should be sufficient to distinguish valid files from files containing "There is no data to report."

Please let us know if you'd like assistance building the Lookup + If Condition logic or the dynamic expressions required for this validation. We'll be happy to help.
Justin Doh 1,000 Reputation points

2026-07-28T22:15:48+00:00

@Pilladi Padma Sai Manisha Thank you for your help.

Can you check whether this is the architecture of pipeline you are implying?

This is inside ForEach:

Please let me know. Thanks!
Pilladi Padma Sai Manisha 11,705 Reputation points Microsoft External Staff Moderator

2026-07-29T21:20:35.54+00:00
Thank you for sharing the screenshots. It looks like you've successfully implemented the pipeline pattern of Get Metadata → ForEach → Lookup → If Condition → Copy Data, which is the right approach.

Since the pipeline is still attempting to execute the Copy Data activity for the invalid file, it appears that the issue may be with either:

The Lookup activity not returning the value you expect, or

The If Condition expression not correctly evaluating the Lookup output.

To help troubleshoot this further, could you please share the following?

The Lookup activity output (Output tab) for both:

a valid CSV file, and

the CSV file containing "There is no data to report.".

The complete expression configured in the If Condition activity.

The Lookup activity settings, specifically:

Whether First row only is enabled.

Whether the dataset used by the Lookup has First row as header enabled or disabled.

The Lookup output will determine how the first line is exposed (for example, as firstRow, value, or another structure), and the If Condition expression must reference that output correctly. Once we have those details, we can help identify why the invalid file is still passing the validation and suggest the appropriate expression.

Looking forward to your update.
Justin Doh 1,000 Reputation points

2026-07-29T22:57:51.7233333+00:00

@Pilladi Padma Sai Manisha Thank you for your help.

1, Sample Files

I shared these links for both csv files for sample on earlier thread on 7/24:

https://drive.google.com/file/d/1dpBEfzUikq8hOIbOn-Am5JFltraOPdNz/view?usp=sharing

https://drive.google.com/file/d/1UdVV06xuCxak-NKIy86b7-cQId1U6Qh4/view?usp=sharing

2.Expression Error

I am trying to come up with expression to read on If Condition.

Basically, I am trying to read the 1st column on 1st row of a csv file:

So, the logic is, " if the 1st column on the 1st row in the csv file is 'FacilityName', then.. " --> True.

I think my expression on the bottom screenshot is wrong.

3.Setting at "Lookup"

Also, the setting inside "Lookup" looks like this:

4.Two datasets

I have two datasets for the file.

1st one points to folder and 2nd one points to file.

1st one is used for "GetMetadata" and 2nd one is used for "Lookup".

Is that correct approach?

1st one:

2nd one:

Thank you.
Pilladi Padma Sai Manisha 11,705 Reputation points Microsoft External Staff Moderator

2026-08-04T21:22:25.06+00:00
Hi @Justin Doh,

Thank you for sharing the screenshots and the detailed configuration. They really help clarify your setup.

From what you've shared, your overall pipeline design looks good. Using one dataset for the folder (Get Metadata) and another parameterized dataset for the individual file (Lookup and Copy Data) is a common and valid approach.

The part that seems to be causing the issue is the If Condition expression.

Your current expression is:

@equals(activity('Lookup1').output.firstRow[0].name,'FacilityName')

With First row only enabled in the Lookup activity and First row as header enabled in the dataset, the output may not be structured the way the expression expects. As a result, the condition may not evaluate correctly.

To determine the correct expression, we'll need to see the actual output returned by the Lookup activity.

Could you please run the pipeline in Debug mode and share the Output (JSON) of the Lookup activity for both:

A valid CSV file.

An invalid CSV file that contains "There is no data to report."

You can find this by opening the pipeline run, selecting the Lookup activity, and navigating to the Output tab.

Once we can see the Lookup output, we'll be able to identify how the first row is being returned and suggest the appropriate expression for your If Condition.

I look forward to your update.

Justin Doh 1,000

@Pilladi Padma Sai Manisha

Thank you for your help!

1.Bottom is result of Lookup with a valid csv file:

{

"firstRow": {

	"FacilityName": "XX XX Center LLC",

	"Department": "NURSING CERTIFIED",

	"Department Code": "203",

	"Employee First Name": "Abraham",

	"Employee Last Name": "Lincoln",

	"Position": "MBA",

	"Position Code": "10190",

	"Employee ID": "230687",

	"Hours": "16.73",

	"Dollars": "720.06",

	"Earning Code": "Doubletime",

	"Earning Group": "Earning",

	"Check Date": "7/10/2026",

	"Period End Date": "6/30/2026",

	"Check Type": "Regular Check",

	"Check Number": null

},

"effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime (West US)",

"billingReference": {

	"activityType": "PipelineActivity",

	"billableDuration": [

		{

			"meterType": "AzureIR",

			"duration": 0.016666666666666666,

			"unit": "Hours"

		}

	],

	"totalBillableDuration": [

		{

			"meterType": "AzureIR",

			"duration": 0.016666666666666666,

			"unit": "Hours"

		}

	]

},

"durationInQueue": {

	"integrationRuntimeQueue": 0

}

}

2.Bottom is result of the invalid CSV file that contains "There is no data to report.":

{

"effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime (West US)",

"billingReference": {

	"activityType": "PipelineActivity",

	"billableDuration": [

		{

			"meterType": "AzureIR",

			"duration": 0.016666666666666666,

			"unit": "Hours"

		}

	],

	"totalBillableDuration": [

		{

			"meterType": "AzureIR",

			"duration": 0.016666666666666666,

			"unit": "Hours"

		}

	]

},

"durationInQueue": {

	"integrationRuntimeQueue": 10

}

}

Thank you!

Pilladi Padma Sai Manisha 11,705 Reputation points Microsoft External Staff Moderator

2026-08-04T22:29:16.96+00:00
Thank you for sharing the Lookup outputs. They clearly show the root cause.

For the valid CSV, the Lookup activity successfully reads the file and returns a firstRow object containing the expected columns, such as FacilityName, Department, and so on.

For the invalid CSV, the Lookup activity does not return a firstRow object at all. This indicates that ADF is unable to parse the file because it does not contain a valid header row—only the text "There is no data to report."

Because of this, the current expression:

cannot work, as firstRow is not present for the invalid file.

At this point, the remaining question is how the If Condition behaves when referencing a property that is missing from the Lookup output. Before suggesting the exact expression, I'd like to confirm the following:

Is the pipeline failing at the If Condition activity itself, or does it evaluate successfully and still proceed to the Copy Data activity?

If it fails at the If Condition, could you please share the complete error message from that activity?

This will help determine the safest expression to check for the presence of firstRow before evaluating the header and ensure the invalid files are skipped without causing the pipeline to fail.

Looking forward to your update.
Justin Doh 1,000 Reputation points

2026-08-05T00:37:17.9933333+00:00

@Pilladi Padma Sai Manisha

Thanks for help again.

I noticed that, on both valid file and invalid file, this {} is what I see .

This {} is shown on both Input and Output.

I am not sure whether I answered your questions or not.

Bottom is Error details when I ran with a valid file:

Bottom is Error details when I ran with an invalid file:

Thank you!
Pilladi Padma Sai Manisha 11,705 Reputation points Microsoft External Staff Moderator

2026-08-05T22:22:58.6833333+00:00
Thank you for sharing the error details. This clarifies the issue.

The error confirms that the If Condition is failing because the invalid file does not contain a firstRow property in the Lookup output. Since the file only contains "There is no data to report.", the Lookup activity is unable to parse a valid row and therefore doesn't return firstRow.

As a result, this expression:

@equals(activity('Lookup1').output.firstRow[0].name,'FacilityName')

fails because it attempts to access a property that doesn't exist.

At this point, the challenge is not checking whether the value is "FacilityName", but first determining whether firstRow exists before trying to reference it.

Unfortunately, ADF pipeline expressions do not support safely accessing a missing property directly, so the current approach will always fail for files where firstRow is absent.

One option is to modify the validation so that the Lookup activity reads the first line as data instead of treating it as a header (for example, by using a dataset with First row as header disabled). This allows you to compare the first line against the expected header or detect the message "There is no data to report." before invoking the Copy Data activity.

Could you try configuring a separate Lookup dataset with First row as header disabled and let us know what the Lookup output looks like for both the valid and invalid files? That will help determine the most appropriate expression for the If Condition.

I look forward to your update.
Justin Doh 1,000 Reputation points

2026-08-05T22:59:31.8166667+00:00

@Pilladi Padma Sai Manisha Thank you for your help.

I disabled the "First row as header" on the Lookup dataset, and I ran an invalid and a valid csv file, and I got the same error message (on If Condition) as below:

And bottom is output on Lookup for a invalid file:

Bottom is output on Lookup for a valid file.

Thank you!
Pilladi Padma Sai Manisha 11,705 Reputation points Microsoft External Staff Moderator

2026-08-06T23:58:49.0466667+00:00
Thank you for testing that and for sharing the latest screenshots. This helps identify the issue.

The error is now different from the previous one and indicates that the firstRow object is being returned successfully. However, the expression is still treating it as an array.

From your Lookup output, we can see that firstRow has the following structure:

{ "firstRow": { "Prop_0": "FacilityName", "Prop_1": "Department", "Prop_2": "Department Code", ... } }

Since firstRow is an object, you should reference its properties by name instead of by index.

Could you try updating the If Condition expression to:

@equals(activity('Lookup1').output.firstRow.Prop_0, 'FacilityName')

If this evaluates to True, the file contains t expected header and you can proceed with the Copy Data activity. For the invalid file, Prop_0 should contain "There is no data to report.", causing the expression to evaluate to False, allowing you to skip the file.

Please let us know the results after testing this expression.
Justin Doh 1,000 Reputation points

2026-08-07T00:12:39.27+00:00

@Pilladi Padma Sai Manisha I tested, and it appears that it only worked (ingested data) when only valid file exists, but when both invalid file and valid file are there, it would not ingest data from the valid file. But, at least, ADF likes the new expression as it passes to ingest data.

How do we make it work so that it could ingest data when both invalid and valid files exist?

Thank you for your help!
Justin Doh 1,000 Reputation points

2026-08-11T17:20:10.61+00:00

@Pilladi Padma Sai Manisha I would like to follow up on my question on the top. Thank you.
Pilladi Padma Sai Manisha 11,705 Reputation points Microsoft External Staff Moderator

2026-08-11T20:32:20.83+00:00
Glad to hear the new expression is working when the valid file is present.

The remaining issue is likely that the Lookup activity is failing for the invalid file, so the If Condition never gets a chance to evaluate it. The expected logic is:

Valid file → Prop_0 = FacilityName → True → Copy Data runs.

Invalid file → Prop_0 = There is no data to report. → False → Copy Data is skipped.

Could you please check the Lookup activity status/output for the invalid file when both files are present? If the Lookup is failing, please share the error message.

Once we confirm that, we can adjust the validation so the valid file is still processed while the invalid file is skipped.
Justin Doh 1,000 Reputation points

2026-08-11T21:15:45.3833333+00:00

@Pilladi Padma Sai Manisha Thank you so much for your quick reply and your help.

I ran, and discovered that, for both valid and invalid file, Lookup showed "Succeeded".

Bottom is showing the output of invalid file:

How do we create a logic to skip the Copy activity?

I know we have a logic when Prop_0 is 'FacilityName' (shown on the bottom graphic).

As I indicated on previous post, challenge is when both valid and invalid files are present.

When only valid file exists, it works. Also, when only invalid file exists, it works by doing nothing.

Do I have to do anything on the "If Condition" when case is False? Currently, there is nothing.

Thank you.
Pilladi Padma Sai Manisha 11,705 Reputation points Microsoft External Staff Moderator

2026-08-13T21:22:29.1266667+00:00
Yes, you can leave the False branch empty. That is the expected configuration.

Your current expression is correct:

@equals(activity('Lookup1').output.firstRow.Prop_0, 'FacilityName')

The logic should work as follows:

Valid CSV: Prop_0 = FacilityName → True → Copy Data runs.

Invalid CSV: Prop_0 = There is no data to report. → False → Copy Data is skipped.

The ForEach then continues with the next file.

You do not need to add anything to the False branch unless you want to move/log the invalid files.

Since the Lookup is succeeding for both valid and invalid files, I recommend keeping the Copy Data activity only under the True branch and leaving the False branch empty.

One additional setting I would recommend for this scenario is to enable Sequential execution for the ForEach. This will process one file at a time and can make the file-by-file validation easier to manage:

ForEach → Settings → Sequential = On

So the final flow would be:

Get Metadata → ForEach (Sequential) → Lookup → If Condition → True: Copy Data / False: Skip

With this configuration, the invalid file should be skipped while the valid file continues to the Copy Data activity.
Justin Doh 1,000 Reputation points

2026-08-13T22:52:46.52+00:00

@Pilladi Padma Sai Manisha Thank you for your help.

I modified ADF by checking on "Sequential", but it still would not ingest data from the valid csv file (when both valid and invalid files are there).

When I place only the valid file, it ingests data.

Is it possible to have a meeting to take a look together possibly?

Thanks.
Justin Doh 1,000 Reputation points

2026-08-18T22:17:09.89+00:00

@Pilladi Padma Sai Manisha I would like to follow up. Thank you.
Pilladi Padma Sai Manisha 11,705 Reputation points Microsoft External Staff Moderator

2026-08-22T20:39:23.4766667+00:00

Thank you for testing the Sequential option and for the update.

Since the pipeline works when only the valid file is present, but does not ingest the valid file when both valid and invalid files are present, the issue appears to be related to how the files are being processed within the ForEach.

Your current validation logic is correct: the valid file should evaluate to True and proceed to Copy Data, while the invalid file should evaluate to False and be skipped. The False branch can remain empty.

I understand that reviewing the pipeline together would be helpful. We can continue troubleshooting this through the Q&A thread. Based on the configuration you've shared, the next area to review is the ForEach file iteration and the dataset parameter (@item().name) used by the Lookup and Copy Data activities, to ensure each iteration is independently using the current file.

Please share the latest pipeline configuration/run details if available, and we can continue from there.

1 answer

Your answer

Pilladi Padma Sai Manisha 11,705 Reputation points Microsoft External Staff Moderator

2026-07-22T22:53:26.01+00:00

Hi @Justin Doh

Thank you for reaching out Microsoft Q&A!.

Based on your scenario, the overall pipeline design of using Get Metadata → ForEach → If Condition → Copy Data is a reasonable approach. However, the key limitation is that the Get Metadata activity can retrieve file-level metadata such as the file name, size, and last modified time, but it cannot inspect the contents of the CSV file. As a result, it cannot determine whether the first row contains the expected header or an unexpected message such as "There is no data to report."

If the validation is currently being performed only using the file name or metadata, the pipeline will still attempt to process the invalid file, causing the Copy Data activity to fail.

To validate the file contents before copying, you can use a Lookup activity to read the first row (or first line) of each CSV file and then use an If Condition activity to verify that it contains the expected header. If the validation succeeds, proceed with the Copy Data activity; otherwise, skip the file or move it to a separate location for further review.

Please note that the Copy Data activity does not support filtering files based on their contents. If you need to validate or filter data within the file at scale, consider using a Mapping Data Flow, Azure Databricks, or another custom processing step before the copy operation.

To better understand your scenario, could you please confirm the following?

Where are the CSV files stored (Azure Blob Storage or Azure Data Lake Storage Gen2)?

Is the invalid file always a single line containing "There is no data to report.", or are there other variations?

Are you using a DelimitedText dataset with First row as header enabled?

With this information, we can suggest the most suitable validation approach for your pipeline.
Justin Doh 1,000 Reputation points

2026-07-22T23:06:31.82+00:00

@Pilladi Padma Sai Manisha Thank you for getting back to me.

CSV files are stored in Azure File service

There is no other variations at this point besides some csv files have first row containing text - "There is no data to report".

Yes, I use DelimiteeText dataset with First row as header - checked.
Pilladi Padma Sai Manisha 11,705 Reputation points Microsoft External Staff Moderator

2026-07-24T22:07:00.0833333+00:00

Thank you for the additional information.

Based on what you've shared, the behavior is expected. Since the DelimitedText dataset is configured with First row as header, the Copy Data activity expects the first line of every CSV file to contain the column headers. When a file instead contains the text "There is no data to report." as its first line, ADF interprets that line as the header, resulting in a schema mismatch and causing the Copy activity to fail.

Because your files are stored in Azure Files, the recommended approach remains to validate the file contents before invoking the Copy Data activity. While Get Metadata can enumerate the files, it cannot inspect their contents. A common pattern is to use a Lookup activity to read the first row (or first line) of each file, followed by an If Condition to verify that the expected header is present. Only files that pass this validation should be sent to the Copy Data activity, while files containing "There is no data to report." can be skipped or moved to a separate folder for review.

To help us provide more specific guidance, could you also let us know whether the expected header is always identical across all valid CSV files? If possible, please share:

A sample of a valid CSV file (header and the first few rows).

A sample of an invalid CSV file that contains "There is no data to report.".

This will help us suggest the appropriate expression or pipeline logic to validate the file before processing.
Justin Doh 1,000 Reputation points

2026-07-25T01:21:02.6833333+00:00

@Pilladi Padma Sai Manisha

I was trying to upload csv files, but this site only allows txt files format.

So, I uploaded two csv files to Google drive:

https://drive.google.com/file/d/1dpBEfzUikq8hOIbOn-Am5JFltraOPdNz/view?usp=sharing

https://drive.google.com/file/d/1UdVV06xuCxak-NKIy86b7-cQId1U6Qh4/view?usp=sharing
Justin Doh 1,000 Reputation points

2026-07-27T21:45:04.44+00:00

@Pilladi Padma Sai Manisha

I would like to follow up on this.

So do we only need two/three parts ( Lookup Item --> If Condition (true: Copy data) )?

Thanks.
Pilladi Padma Sai Manisha 11,705 Reputation points Microsoft External Staff Moderator

2026-07-28T20:58:43.1133333+00:00

Hi @Justin Doh,

Thank you for sharing the screenshots of both the valid and invalid CSV files.

From the examples you've provided, the behavior is expected. The valid file begins with the expected column headers, whereas the invalid file contains only the text "There is no data to report." as its first line. Since your DelimitedText dataset is configured with First row as header, ADF interprets that text as the header row. When the Copy Data activity attempts to map the expected columns, it cannot find them, resulting in the failure.

Unfortunately, the Copy Data activity does not have a built-in capability to filter or skip files based on their contents. Likewise, the Get Metadata activity can enumerate files but cannot inspect the first line of a file.

To handle this scenario, you'll need to introduce a validation step before the Copy Data activity. A common approach is:

Use Get Metadata to enumerate the CSV files.

Iterate through the files using a ForEach activity.

Use a Lookup activity to read the first line (or first row) of each file.

Use an If Condition to verify that the first row contains the expected header (for example, that it starts with FacilityName or another known header column).

If the validation succeeds, continue with the Copy Data activity. Otherwise, skip the file or move it to a separate folder for review.

Based on the sample you shared, checking whether the first row starts with FacilityName should be sufficient to distinguish valid files from files containing "There is no data to report."

Please let us know if you'd like assistance building the Lookup + If Condition logic or the dynamic expressions required for this validation. We'll be happy to help.
Justin Doh 1,000 Reputation points

2026-07-28T22:15:48+00:00

@Pilladi Padma Sai Manisha Thank you for your help.

Can you check whether this is the architecture of pipeline you are implying?

This is inside ForEach:

Please let me know. Thanks!
Pilladi Padma Sai Manisha 11,705 Reputation points Microsoft External Staff Moderator

2026-07-29T21:20:35.54+00:00

Thank you for sharing the screenshots. It looks like you've successfully implemented the pipeline pattern of Get Metadata → ForEach → Lookup → If Condition → Copy Data, which is the right approach.

Since the pipeline is still attempting to execute the Copy Data activity for the invalid file, it appears that the issue may be with either:

The Lookup activity not returning the value you expect, or

The If Condition expression not correctly evaluating the Lookup output.

To help troubleshoot this further, could you please share the following?

The Lookup activity output (Output tab) for both:

a valid CSV file, and

the CSV file containing "There is no data to report.".

The complete expression configured in the If Condition activity.

The Lookup activity settings, specifically:

Whether First row only is enabled.

Whether the dataset used by the Lookup has First row as header enabled or disabled.

The Lookup output will determine how the first line is exposed (for example, as firstRow, value, or another structure), and the If Condition expression must reference that output correctly. Once we have those details, we can help identify why the invalid file is still passing the validation and suggest the appropriate expression.

Looking forward to your update.
Justin Doh 1,000 Reputation points

2026-07-29T22:57:51.7233333+00:00

@Pilladi Padma Sai Manisha Thank you for your help.

1, Sample Files

I shared these links for both csv files for sample on earlier thread on 7/24:

https://drive.google.com/file/d/1dpBEfzUikq8hOIbOn-Am5JFltraOPdNz/view?usp=sharing

https://drive.google.com/file/d/1UdVV06xuCxak-NKIy86b7-cQId1U6Qh4/view?usp=sharing

2.Expression Error

I am trying to come up with expression to read on If Condition.

Basically, I am trying to read the 1st column on 1st row of a csv file:

So, the logic is, " if the 1st column on the 1st row in the csv file is 'FacilityName', then.. " --> True.

I think my expression on the bottom screenshot is wrong.

3.Setting at "Lookup"

Also, the setting inside "Lookup" looks like this:

4.Two datasets

I have two datasets for the file.

1st one points to folder and 2nd one points to file.

1st one is used for "GetMetadata" and 2nd one is used for "Lookup".

Is that correct approach?

1st one:

2nd one:

Thank you.
Pilladi Padma Sai Manisha 11,705 Reputation points Microsoft External Staff Moderator

2026-08-04T21:22:25.06+00:00

Hi @Justin Doh,

Thank you for sharing the screenshots and the detailed configuration. They really help clarify your setup.

From what you've shared, your overall pipeline design looks good. Using one dataset for the folder (Get Metadata) and another parameterized dataset for the individual file (Lookup and Copy Data) is a common and valid approach.

The part that seems to be causing the issue is the If Condition expression.

Your current expression is:

@equals(activity('Lookup1').output.firstRow[0].name,'FacilityName')

With First row only enabled in the Lookup activity and First row as header enabled in the dataset, the output may not be structured the way the expression expects. As a result, the condition may not evaluate correctly.

To determine the correct expression, we'll need to see the actual output returned by the Lookup activity.

Could you please run the pipeline in Debug mode and share the Output (JSON) of the Lookup activity for both:

A valid CSV file.

An invalid CSV file that contains "There is no data to report."

You can find this by opening the pipeline run, selecting the Lookup activity, and navigating to the Output tab.

Once we can see the Lookup output, we'll be able to identify how the first row is being returned and suggest the appropriate expression for your If Condition.

I look forward to your update.
Justin Doh 1,000 Reputation points

2026-08-04T22:15:34.2733333+00:00

@Pilladi Padma Sai Manisha

Thank you for your help!

1.Bottom is result of Lookup with a valid csv file:

{

"firstRow": { "FacilityName": "XX XX Center LLC", "Department": "NURSING CERTIFIED", "Department Code": "203", "Employee First Name": "Abraham", "Employee Last Name": "Lincoln", "Position": "MBA", "Position Code": "10190", "Employee ID": "230687", "Hours": "16.73", "Dollars": "720.06", "Earning Code": "Doubletime", "Earning Group": "Earning", "Check Date": "7/10/2026", "Period End Date": "6/30/2026", "Check Type": "Regular Check", "Check Number": null }, "effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime (West US)", "billingReference": { "activityType": "PipelineActivity", "billableDuration": [ { "meterType": "AzureIR", "duration": 0.016666666666666666, "unit": "Hours" } ], "totalBillableDuration": [ { "meterType": "AzureIR", "duration": 0.016666666666666666, "unit": "Hours" } ] }, "durationInQueue": { "integrationRuntimeQueue": 0 }

}

2.Bottom is result of the invalid CSV file that contains "There is no data to report.":

{

"effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime (West US)", "billingReference": { "activityType": "PipelineActivity", "billableDuration": [ { "meterType": "AzureIR", "duration": 0.016666666666666666, "unit": "Hours" } ], "totalBillableDuration": [ { "meterType": "AzureIR", "duration": 0.016666666666666666, "unit": "Hours" } ] }, "durationInQueue": { "integrationRuntimeQueue": 10 }

}

Thank you!
Pilladi Padma Sai Manisha 11,705 Reputation points Microsoft External Staff Moderator

2026-08-04T22:29:16.96+00:00

Thank you for sharing the Lookup outputs. They clearly show the root cause.

For the valid CSV, the Lookup activity successfully reads the file and returns a firstRow object containing the expected columns, such as FacilityName, Department, and so on.

For the invalid CSV, the Lookup activity does not return a firstRow object at all. This indicates that ADF is unable to parse the file because it does not contain a valid header row—only the text "There is no data to report."

Because of this, the current expression:

cannot work, as firstRow is not present for the invalid file.

At this point, the remaining question is how the If Condition behaves when referencing a property that is missing from the Lookup output. Before suggesting the exact expression, I'd like to confirm the following:

Is the pipeline failing at the If Condition activity itself, or does it evaluate successfully and still proceed to the Copy Data activity?

If it fails at the If Condition, could you please share the complete error message from that activity?

This will help determine the safest expression to check for the presence of firstRow before evaluating the header and ensure the invalid files are skipped without causing the pipeline to fail.

Looking forward to your update.
Justin Doh 1,000 Reputation points

2026-08-05T00:37:17.9933333+00:00

@Pilladi Padma Sai Manisha

Thanks for help again.

I noticed that, on both valid file and invalid file, this {} is what I see .

This {} is shown on both Input and Output.

I am not sure whether I answered your questions or not.

Bottom is Error details when I ran with a valid file:

Bottom is Error details when I ran with an invalid file:

Thank you!
Pilladi Padma Sai Manisha 11,705 Reputation points Microsoft External Staff Moderator

2026-08-05T22:22:58.6833333+00:00

Thank you for sharing the error details. This clarifies the issue.

The error confirms that the If Condition is failing because the invalid file does not contain a firstRow property in the Lookup output. Since the file only contains "There is no data to report.", the Lookup activity is unable to parse a valid row and therefore doesn't return firstRow.

As a result, this expression:

@equals(activity('Lookup1').output.firstRow[0].name,'FacilityName')

fails because it attempts to access a property that doesn't exist.

At this point, the challenge is not checking whether the value is "FacilityName", but first determining whether firstRow exists before trying to reference it.

Unfortunately, ADF pipeline expressions do not support safely accessing a missing property directly, so the current approach will always fail for files where firstRow is absent.

One option is to modify the validation so that the Lookup activity reads the first line as data instead of treating it as a header (for example, by using a dataset with First row as header disabled). This allows you to compare the first line against the expected header or detect the message "There is no data to report." before invoking the Copy Data activity.

Could you try configuring a separate Lookup dataset with First row as header disabled and let us know what the Lookup output looks like for both the valid and invalid files? That will help determine the most appropriate expression for the If Condition.

I look forward to your update.
Justin Doh 1,000 Reputation points

2026-08-05T22:59:31.8166667+00:00

@Pilladi Padma Sai Manisha Thank you for your help.

I disabled the "First row as header" on the Lookup dataset, and I ran an invalid and a valid csv file, and I got the same error message (on If Condition) as below:

And bottom is output on Lookup for a invalid file:

Bottom is output on Lookup for a valid file.

Thank you!
Pilladi Padma Sai Manisha 11,705 Reputation points Microsoft External Staff Moderator

2026-08-06T23:58:49.0466667+00:00

Thank you for testing that and for sharing the latest screenshots. This helps identify the issue.

The error is now different from the previous one and indicates that the firstRow object is being returned successfully. However, the expression is still treating it as an array.

From your Lookup output, we can see that firstRow has the following structure:

{ "firstRow": { "Prop_0": "FacilityName", "Prop_1": "Department", "Prop_2": "Department Code", ... } }

Since firstRow is an object, you should reference its properties by name instead of by index.

Could you try updating the If Condition expression to:

@equals(activity('Lookup1').output.firstRow.Prop_0, 'FacilityName')

If this evaluates to True, the file contains t expected header and you can proceed with the Copy Data activity. For the invalid file, Prop_0 should contain "There is no data to report.", causing the expression to evaluate to False, allowing you to skip the file.

Please let us know the results after testing this expression.
Justin Doh 1,000 Reputation points

2026-08-07T00:12:39.27+00:00

@Pilladi Padma Sai Manisha I tested, and it appears that it only worked (ingested data) when only valid file exists, but when both invalid file and valid file are there, it would not ingest data from the valid file. But, at least, ADF likes the new expression as it passes to ingest data.

How do we make it work so that it could ingest data when both invalid and valid files exist?

Thank you for your help!
Justin Doh 1,000 Reputation points

2026-08-11T17:20:10.61+00:00

@Pilladi Padma Sai Manisha I would like to follow up on my question on the top. Thank you.
Pilladi Padma Sai Manisha 11,705 Reputation points Microsoft External Staff Moderator

2026-08-11T20:32:20.83+00:00

Glad to hear the new expression is working when the valid file is present.

The remaining issue is likely that the Lookup activity is failing for the invalid file, so the If Condition never gets a chance to evaluate it. The expected logic is:

Valid file → Prop_0 = FacilityName → True → Copy Data runs.

Invalid file → Prop_0 = There is no data to report. → False → Copy Data is skipped.

Could you please check the Lookup activity status/output for the invalid file when both files are present? If the Lookup is failing, please share the error message.

Once we confirm that, we can adjust the validation so the valid file is still processed while the invalid file is skipped.
Justin Doh 1,000 Reputation points

2026-08-11T21:15:45.3833333+00:00

@Pilladi Padma Sai Manisha Thank you so much for your quick reply and your help.

I ran, and discovered that, for both valid and invalid file, Lookup showed "Succeeded".

Bottom is showing the output of invalid file:

How do we create a logic to skip the Copy activity?

I know we have a logic when Prop_0 is 'FacilityName' (shown on the bottom graphic).

As I indicated on previous post, challenge is when both valid and invalid files are present.

When only valid file exists, it works. Also, when only invalid file exists, it works by doing nothing.

Do I have to do anything on the "If Condition" when case is False? Currently, there is nothing.

Thank you.
Pilladi Padma Sai Manisha 11,705 Reputation points Microsoft External Staff Moderator

2026-08-13T21:22:29.1266667+00:00

Yes, you can leave the False branch empty. That is the expected configuration.

Your current expression is correct:

@equals(activity('Lookup1').output.firstRow.Prop_0, 'FacilityName')

The logic should work as follows:

Valid CSV: Prop_0 = FacilityName → True → Copy Data runs.

Invalid CSV: Prop_0 = There is no data to report. → False → Copy Data is skipped.

The ForEach then continues with the next file.

You do not need to add anything to the False branch unless you want to move/log the invalid files.

Since the Lookup is succeeding for both valid and invalid files, I recommend keeping the Copy Data activity only under the True branch and leaving the False branch empty.

One additional setting I would recommend for this scenario is to enable Sequential execution for the ForEach. This will process one file at a time and can make the file-by-file validation easier to manage:

ForEach → Settings → Sequential = On

So the final flow would be:

Get Metadata → ForEach (Sequential) → Lookup → If Condition → True: Copy Data / False: Skip

With this configuration, the invalid file should be skipped while the valid file continues to the Copy Data activity.
Justin Doh 1,000 Reputation points

2026-08-13T22:52:46.52+00:00

@Pilladi Padma Sai Manisha Thank you for your help.

I modified ADF by checking on "Sequential", but it still would not ingest data from the valid csv file (when both valid and invalid files are there).

When I place only the valid file, it ingests data.

Is it possible to have a meeting to take a look together possibly?

Thanks.
Justin Doh 1,000 Reputation points

2026-08-18T22:17:09.89+00:00

@Pilladi Padma Sai Manisha I would like to follow up. Thank you.
Pilladi Padma Sai Manisha 11,705 Reputation points Microsoft External Staff Moderator

2026-08-22T20:39:23.4766667+00:00

Thank you for testing the Sequential option and for the update.

Since the pipeline works when only the valid file is present, but does not ingest the valid file when both valid and invalid files are present, the issue appears to be related to how the files are being processed within the ForEach.

Your current validation logic is correct: the valid file should evaluate to True and proceed to Copy Data, while the invalid file should evaluate to False and be skipped. The False branch can remain empty.

I understand that reviewing the pipeline together would be helpful. We can continue troubleshooting this through the Q&A thread. Based on the configuration you've shared, the next area to review is the ForEach file iteration and the dataset parameter (@item().name) used by the Lookup and Copy Data activities, to ensure each iteration is independently using the current file.

Please share the latest pipeline configuration/run details if available, and we can continue from there.

Answer 1

Justin Doh hi & thx for sharing urs issue here at Q&A portal,

Get Metadata can list files and inspect properties such as name, size, or modification time, but it doesn’t validate CSV headers or inspect the file contents. That’s why the bad file still reaches Copy Activity.

A better pipeline structure is

Get Metadata (childItems) > Filter by filename > ForEach > Lookup first row > If Condition > Copy Data

Inside the loop, use a parameterized CSV dataset pointing to @item().name. Configure the Lookup activity with firstRowOnly = true, but set First row as header to false. This makes ADF return the physical first line as values such as Prop_0, Prop_1, etc., rather than interpreting "There is no data to report." as a column name. Lookup supports returning only the first row of a delimited-text file.

The If Condition can then check either for the rejection message

@not(

contains(

string(activity('Lookup_First_Row').output.firstRow),

'There is no data to report'

)

Or, preferably, validate the expected header values explicitly:

@and(

equals(activity('Lookup_First_Row').output.firstRow.Prop_0, 'ExpectedColumn1'),

equals(activity('Lookup_First_Row').output.firstRow.Prop_1, 'ExpectedColumn2'),

equals(activity('Lookup_First_Row').output.firstRow.Prop_2, 'ExpectedColumn3')

)

Run Copy Data only through the True branch. In the False branch, move the file to a rejected folder, log its filename, or simply skip it.

Keep the Copy Activity’s actual source dataset configured with First row as header = true. Use a separate dataset for the Lookup validation with First row as header = false. Otherwise ADF may interpret the bad message as a header before your condition can validate it.

rgds,

Alex

&

If my answer was helpful pls mark it and additional thx if u follow me at Q&A portal

and at my blog https://ctrlaltdel.blog/

Justin Doh 1,000 Reputation points

2026-07-23T20:35:02.9733333+00:00

@Alex Burlachenko Thank you so much for your help.

I am stuck from the beginning 1st and 2nd points.

When you mentioned, Get Metadata (childItems), do I use the dataset that points to folder or file?

I have Get Metadata1 created for a folder level that looks like bottom and does not have parameter.

Or should I use Get Metadata2 that points to file (shown on the bottom three screenshots) and has parameter (fileName)?

Also, what is "Filter by filename"? Are we talking about Filter (shown below)?

I guess I will get feedbacks on these first and move one by one slow.

Thank you.
Justin Doh 1,000 Reputation points

2026-07-27T21:58:43.9033333+00:00

@Alex Burlachenko I would like to follow up. Also, do we need "Get Metadata" activity? How do we inspect the content of file?