Trying to filter csv file based on content

@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!

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?

1. The Lookup activity output (Output tab) for both:
   • a valid CSV file, and
     • the CSV file containing "There is no data to report.".
     1. The complete expression configured in the If Condition activity.
     2. 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.

@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.

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.

@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!

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.

@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!

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.

@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!

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.

@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!

@Pilladi Padma Sai Manisha I would like to follow up on my question on the top. Thank you.

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.

@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.

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.

@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.

@Pilladi Padma Sai Manisha I would like to follow up. Thank you.

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.

@Pilladi Padma Sai Manisha Thank you for your help.

I am trying to understand which configurations you want to review.
Let me share some screenshots here.

These might be duplicates from previous ones that I already posted:

Parameter:

I am not sure these are what you are looking for.

The dataset that I am using "isolvednew_260717_File_2nd" is pointing to a file, not a folder.

Why do we need to search into "Copy data"?

"Copy data" comes after which file/files are investigated whether it/they are valid with formatting or not, and isn't it (valid/invalid) where we are having an issue?

Please let me know whether these are enough.

Thank you!

Thank you for sharing the screenshots. Yes, these are helpful.

You are correct we don't need to troubleshoot Copy Data at this stage. It should only run after the file passes validation.

Your two-dataset approach is correct:

• Folder dataset → Get Metadata
• File dataset → Lookup and Copy Data
• The file parameter should receive @item().name from the ForEach.

The intended flow is:

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

The If Condition should be:

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

The False branch can remain empty.

Since the pipeline works with either file individually but not when both are present, the important point is to ensure that each ForEach iteration passes its own @item().name to the Lookup and Copy Data datasets.

@Pilladi Padma Sai Manisha Thank you for your help again.

The If condition's expression is same as before.

And, I tested again today, and it is same result. When both valid and invalid files are there, no data ingest is happening. But when I only have a valid, data get ingested.

Bottom is screenshots of ADF:

(1) When both invalid and valid files are there:

The strange thing is when I looked at which file it took for the areas in Orange Box, it takes an Invalid file.

(2) When only a valid file is there:

Output of Lookup1:

(3) When only an invalid file is there:

Output of Lookup1:

I hope these screenshots helped somewhat.

Please let me know if you have any idea.

Thank you so much!