How to validate CSV blank lines (before & after header) in Microsoft Fabric Data Pipelines?

Hi PAVAN_111 ,

This is a good “quality gate” use case, and you can do it fully inside Fabric with a pipeline plus a notebook, without Dataflow Gen2 or ADF, and without touching the original files.

The simplest pattern is: let the pipeline call a notebook that reads each CSV as plain text, checks the lines, and then writes a small log table saying “valid / invalid / reason” for each file. The files stay exactly where they are.

Inside the notebook you don’t use spark.read.csv for the check, because that will happily ignore blank lines. Instead, you read the file as text and apply your two rules manually. For example (PySpark in a Fabric notebook):

from datetime import datetime

folder = "/lakehouse/default/Files/your-folder" # or path passed in from pipeline

# List all CSV files under the folder
files_df = spark.read.format("binaryFile").load(folder + "/*.csv").select("path")
file_paths = [r.path for r in files_df.collect()]

results = []

for path in file_paths:

# Read file as text, line by line
lines = spark.read.text(path).rdd.map(lambda r: r.value).collect()

# Strip whitespace
stripped = [ (i, (line or "").strip()) for i, line in enumerate(lines) ]

# Find first non-blank line = header

header_idx = next((i for i, v in stripped if v != ""), None)
if header_idx is None:
results.append((path, False, "File is empty or only blank lines"))
continue

# Check for blank lines above header
if any(v == "" for i, v in stripped[:header_idx]):
results.append((path, False, "Blank line(s) before header"))
continue

# Check for blank lines after header
if any(v == "" for i, v in stripped[header_idx+1:]):
results.append((path, False, "Blank line(s) after header"))
else:
results.append((path, True, "Valid"))

# Write results into a small Delta table in the same Lakehouse
log_df = spark.createDataFrame(
[(p, ok, reason, datetime.utcnow()) for p, ok, reason in results],
["file_path", "is_valid", "reason", "checked_utc"]
)
log_df.write.mode("append").saveAsTable("csv_validation_log")

Your pipeline just has a notebook activity that passes the folder path (and maybe delimiter info) to this notebook. After it runs, you can query the csv_validation_log table to see exactly which files are valid or invalid and why, without moving or modifying the source files.

This stays 100% inside Fabric, uses only a pipeline and a notebook, and gives you a clear yes/no plus reason for every CSV in the folder.

– Gopi Krishna

Hi PAVAN_111,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to tayloramy & Ugk161610 for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.