Ingesting Excel and CSV files with Apache NiFi

Why Excel and CSV ingestion in Apache NiFi needs a plan

NiFi's ExcelReader and CSVReader turn spreadsheets and CSV exports into records that downstream processors can route, validate and load, but the reader only does part of the job. Three design choices decide whether a scheduled file drop stays reliable: how the schema is defined, which sheets are read and how bad rows are handled. Teams that leave these to defaults usually discover the consequences later, when a column is renamed, a second tab appears or a single malformed row stops a load.

This need is common when business teams send workbooks or CSV exports from finance, operations, sales or partner systems on a recurring basis, often by email, shared drive or SFTP. The files are rarely produced by a system with a stable contract. Headers move, dates arrive in mixed formats, and a person may save a workbook with a different sheet order than last month. A plan settles what the flow expects, what it does when that expectation fails and who is told, before the first production run.

This page covers how ExcelReader handles XLSX and older XLS files, including password-protected workbooks, how it reads multiple sheets, when to infer a schema versus supply an explicit one, and how to separate bad rows from good ones. Before you start, confirm with your file owners which formats and sheet layouts they will actually send, and whether any workbooks will be password protected and how those passwords will be supplied and rotated.

Reader options and when each fits

This table compares the reader settings that decide how spreadsheets and CSV exports become records, including schema source, sheet and header handling, workbook protection and bad-row behavior. Read each row as a decision: find the file type or source you receive, then check the setting that fits and what the component documentation says about it.

Setting	What it controls	When it fits	What to confirm
Schema inference	Reader guesses field names and types from the data	Exploratory loads or stable files where you can review the first inferred schema	Which rows drive inference and how mixed types in one column are typed
Explicit schema	Field names and types are fixed in the reader configuration	Recurring feeds where a changed column should stop the flow, not silently shift types	How the schema is supplied and how a mismatch is reported as a failure
Sheet selection	Which worksheets are read and how each sheet's rows become records	Workbooks with one data sheet, or sheets with the same layout processed one by one	Whether all sheets are read into one stream and how sheet names reach attributes
Header row and data start	Which row holds column names and where data begins	Exports with title blocks, notes or blank rows above the header	How empty rows and merged cells are handled before the header
Workbook password	Access to protected XLSX files through a supplied password	Partner or government workbooks delivered with a password from a secure channel	Where the password is stored, whether it is marked sensitive, and how a wrong password is reported
XLSX versus XLS	Format support for the file you receive	XLSX for current exports; XLS only when legacy sources cannot change	Whether your XLS files match the formats the ExcelReader documentation lists as supported
CSVReader delimiter and quoting	Separator, quote character and escape rules	Standard comma exports, or semicolon and tab files from regional or legacy systems	How embedded line breaks and quoted delimiters are parsed in your sample files
Bad-row handling	Whether a malformed row fails the whole FlowFile or only that record	Strict loads into databases; lenient loads when rejects are routed and reported	Which failure relationship receives partial errors and whether good records still pass

Handling XLSX, XLS and CSV files differently

Unprotected XLSX workbooks are the simplest case for the ExcelReader controller service. According to the ExcelReader documentation, the reader returns each row of each sheet as a record and can either infer a schema from the data or use an explicit schema that you supply. For recurring feeds, an explicit schema is usually the safer choice because inference depends on the rows present in a given file.

Record per row: Each row on each sheet becomes one record, so plan how header rows and blank rows are handled in the source template.
Schema source: Choose inferred or explicit schema deliberately, and record which one the flow uses in the design documentation.
No password needed: The flow does not need to supply a password for this file type, so no secured parameter is involved.
Sheet handling: Confirm with the file owner which sheets are data and which are notes or lookups, since the reader treats each sheet's rows as records.
Test with real exports: Run a sample from each sending team through the reader before go-live, because workbook layout differs between senders.

Before go-live, confirm with the sending team that workbooks follow one template and that column names and types stay stable across months.

Where bad rows come from and how to route them

Most failed ingestion traces back to a small set of predictable inputs: files whose shape changes, cells that do not match the declared type, and sheets that were never built for machine reading. Each card names one of these risks, the symptom it produces in the flow, and the schema or routing choice that keeps bad data out of the target.

Inconsistent column counts

A CSV row with more or fewer delimiters than the header is the most common reason a parser rejects a file. Unquoted commas inside a free-text field often cause this, and so do exports that append a trailing column for some rows only. With CSVReader, decide whether the schema is fixed and route rows that fail parsing to a separate failure path instead of letting one bad line stop the whole file. Check how the reader handles short and long rows in your test set before you go live.

Type mismatches against an explicit schema

When you supply an explicit schema, a value such as N/A, a text date in a numeric column, or a currency symbol can fail conversion. Inferred schemas hide these problems until a downstream load fails. Decide per field whether a bad value rejects the record or is kept as a string for review, and document that choice with the schema version. Use explicit schemas for production feeds and inference only while profiling a new source.

Merged and blank header rows

Spreadsheets built for people often place a title, a merged banner or a blank line above the real header. ExcelReader reads these as data, so column names become shifted or empty and the schema no longer matches the file. Agree a fixed header row with the sender, or skip leading rows before parsing. Treat a changed header as a schema change to route for review, not as a record to load.

Multiple sheets with different layouts

A workbook can hold several sheets, and each may use a different column order, a summary block or a separate header. ExcelReader returns rows from each sheet as records, so a summary sheet can contaminate a data sheet unless you select the intended sheets. Map each sheet name to its own schema and route unknown sheet names to a review queue. Confirm the sheet selection options in the component documentation for your NiFi version.

Encoding and delimiter drift

A file that was UTF-8 last month may arrive as Windows-1252 or with semicolons instead of commas after a locale change in the source system. The symptom is often garbled accented characters or a single wide column that fails every row. Check the encoding, delimiter and quote character on arrival, compare them with the expected profile, and route mismatches before parsing. Ask senders to commit to the format in writing and version it.

Setting up ExcelReader and CSVReader in a flow

This sequence takes a spreadsheet or CSV source from an undefined input to a flow that parses records with a known schema, handles sheets and headers deliberately, and sends bad rows to a reject path that you have tested. Each step ends with a check that tells you whether to move on.

Define the target schema or choose inference
Before touching NiFi, the data owner and the integration engineer agree on the columns, data types, required fields and date formats the target expects, and write them down as an Avro schema or a field list. If the source changes often or is too wide to model by hand, choose schema inference for the first build and plan to freeze the inferred schema later. Done when the team can point to one written schema, or to a documented decision to infer, and the owner has signed off on the field names.
Add the reader controller services
In the NiFi canvas, open the controller services for the process group and add an ExcelReader for workbooks and a CSVReader for text exports. Set the schema access strategy to the explicit schema or to schema inference, set the header line and delimiter for the CSV reader, and set the sheet and password properties for the Excel reader if they apply. Enable both services before any processor uses them. Done when both services show as enabled and their validation panels report no errors.
Configure the record-based processor
Place the processor that consumes the records, such as ConvertRecord, QueryRecord or PutDatabaseRecord, and set its record reader to the matching service. Set the record writer to the format the next step needs. Confirm in the processor properties that the reader and writer are the ones you enabled, not defaults from another flow. Done when the processor validates and an incoming test FlowFile produces output records in the writer format.
Set sheet and header handling
Decide which sheets to read and whether the first row is a header. For workbooks with several sheets, confirm how the reader handles each sheet and whether you need one flow path per sheet, and record the sheet names in your design. For CSV exports, confirm the header row index, the encoding and the quote and escape characters with a sample from the sender, because mismatches here are a common cause of shifted columns. Done when a sample from each sheet and each export parses with the expected column count and names.
Test with a sample workbook and a deliberately bad file
Run a known-good workbook and CSV through the flow, then run a second set that includes a missing required value, a text value in a numeric column, an extra column, a row with the wrong delimiter count and, if your process uses them, a password-protected workbook with the correct and incorrect password. Compare record counts and field values with the source. Done when the good files produce the expected record count and the bad file produces failures you can explain row by row.
Route invalid records to a reject path
Connect the failure and parse-failure relationships of the record processors to a separate path, and place a ValidateRecord or similar check ahead of the target to catch type and business-rule violations that the reader accepts. Write rejected records with their original row context to a quarantine location and attach an error attribute so the sender can be told what to fix. Done when the deliberately bad rows from the previous step appear on the reject path, the good rows continue to the target, and no record is counted in both places.
Confirm end-to-end counts and reject reporting
Run the complete flow on the full set of sample files and reconcile source row counts against accepted and rejected counts, using provenance to trace a sample of rows through each path. Confirm the reject location is monitored, and record the reader settings, schema version and sample files in your change record so the build can be repeated. Done when the reconciliation balances for every sample file and the team agrees which settings are approved for the first production source.

Schema inference or an explicit schema: which one to choose

For records that feed a database or a validated target, use an explicit schema as the default and treat inference as a discovery tool. An inferred schema describes the file you happened to receive, not the file the target expects. When the ExcelReader or CSVReader infers types from a sample, a column that held only whole numbers last month can become a decimal, a text field full of digits can become a number, and a new column appears without anyone deciding it should. Each of these changes passes through the reader silently and reaches the writer, where it may fail late, truncate a value or create an unplanned column. An explicit schema, supplied through the reader's schema access strategy with a schema text or a schema registry, fixes the field names, types and order before any record is read.

The main benefit of an explicit schema is that drift becomes visible at the point where it happens. If a sender adds, removes or renames a column, the record fails to match the schema and is routed to a failure relationship with an error attribute, instead of flowing into the target with a changed shape. Pair the reader with a validation step such as ValidateRecord so that mismatches are counted and reported back to the sender. Explicit schemas also make the cost of change visible to the people who own the target: a new column requires a deliberate schema update, a test run and a sign-off, rather than a quiet change in production.

Inference still has a place. Use it when you are profiling an unfamiliar workbook or export, and capture the inferred result to write the explicit schema. Confirm with your own test files how the readers behave on your sheets, including blank header cells, merged cells and date formats, because the exact inference rules and their options are documented in the NiFi component reference for each reader. Keep the schema under version control with the flow, so that a change to it is reviewed like any other change to the pipeline.

Security and operational considerations for spreadsheet intake

Spreadsheets and CSV exports arrive from people and systems you do not fully control, so intake needs controls on who can deliver files, how workbook passwords are held, and what happens to files that fail. The cards below cover those controls and the evidence you need to keep afterward.

Workbook passwords as sensitive values

Passwords for protected workbooks belong in a sensitive parameter or an external secrets source, not in processor properties or flow definitions that are exported to a registry. Confirm that the ExcelReader password property is backed by a sensitive value, that the value is excluded from flow exports, and that rotation does not require editing flow code. Ask who can read parameter contexts, since that permission controls who can recover the password.

Restricting who can deliver files

Decide which accounts, service identities and network paths may drop files into each source directory, bucket or SFTP folder that NiFi reads. Use separate folders per sender so a compromised account cannot write into another feed. Confirm that NiFi reads only from locations it needs, and that write permissions on those locations are reviewed when senders change.

Treating spreadsheets as untrusted input

A workbook can contain macros, external links and formulas that execute or fetch data when opened in Excel. NiFi's reader parses cell values, so keep macro-enabled files out of the intake path where your policy allows, and check that formula text is stored as data rather than evaluated downstream. Ask whether the target database or BI tool re-evaluates formula strings, because that is where injection usually occurs.

Provenance and logging for each file

Record the file name, sender, checksum, size, sheet names and row counts for every file, either as FlowFile attributes or in your log platform. NiFi provenance shows where each FlowFile went, which supports investigation of a disputed load. Confirm how long provenance is kept and where it is stored, because the retention window sets how far back you can prove what arrived.

Retention of rejected files

Rejected workbooks and CSV files should move to a quarantine location with a defined retention period, access restricted to the data owners who must review them, and a record of who released or deleted each file. Keep rejected originals long enough for senders to correct them. Agree the retention period with your data protection and records owners before go-live, since rejected files may still contain personal or confidential data.

TLS and authentication for the cluster

Access to the NiFi UI, the flow and the sensitive parameters depends on how users authenticate and how traffic is encrypted. Review the authentication providers and TLS settings in the NiFi administration guide before connecting any intake flow to production data. Confirm that the UI is not bound beyond the loopback interface until security is configured.

Questions teams ask before building Excel and CSV ingestion

What does an Excel and CSV ingestion build cost, and what drives the effort?

The cost depends mostly on how many file types, sheets and schemas the flow must handle, and on how clean the source files are. Effort is spent on profiling sample files, writing record readers and schemas, designing reject handling, testing against real exports and documenting the flow. Most engagements are fixed-scope, so the written scope sets the price. Ask for a quote that separates one-time build work from recurring items such as support, schema changes and retainers, and ask which items are priced per source, per schema or per flow. DeployOpen does not publish a price list, so the quote follows from the scope you agree in writing.

How long does a typical Excel and CSV ingestion build take?

Duration rises with file variety and schema count. A single CSV export with a stable header usually takes less time than a set of workbooks with several sheets, merged header rows, mixed date formats and per-vendor layouts. Time also depends on how quickly your team confirms expected values and reject owners, and on how many environments the flow must pass through before production. Ask for a timeline that lists each source as its own line item, so you can see which sources drive the schedule. DeployOpen states timelines in the written scope rather than in general terms.

What must our team provide before the build starts?

Your team needs to supply representative sample files, the expected schema for each source, and a named owner for rejected rows. Good samples include the ugliest real exports you receive, such as files with extra title rows, blank columns, mixed date formats and trailing totals. For each source, the schema owner should confirm field names, types, required fields and allowed values. The reject owner decides who reviews bad rows, how long they are kept and how senders are told about errors. Without these three inputs, the flow tends to pass malformed data quietly or fail without anyone responding.

Can our internal engineers build Excel and CSV ingestion themselves?

Yes, if your team already runs Apache NiFi and has someone comfortable with record readers, controller services and flow testing. Many teams start internally with a single CSV source and bring in a partner for the harder parts, such as password handling, multi-sheet workbooks or a large number of vendor layouts. Check three things before deciding: whether you have the skills to write and test explicit schemas, whether someone owns flow changes after go-live, and whether you can run the flow in the environments where the data lives. DeployOpen can plan the build, implement it or support your team on a fixed scope.

How are passwords for protected workbooks handled securely?

Passwords for protected XLSX and XLS files should never be stored in flow definitions, exported templates or Git-versioned flows in clear text. NiFi's ExcelReader can read password-protected and unprotected workbooks, so the question is where the password comes from and who can read it. Plan to store it as a sensitive value in a parameter context or an external secret source, restrict who can view or change it, and log access without logging the value itself. Confirm with your security team how rotation works when a vendor changes the workbook password. Hardening guidance for sensitive values and authorization is covered in the NiFi security material linked from the administration guide.

What happens when a vendor changes the layout of a file?

A layout change usually shows up as rows that fail schema checks, columns that shift position, or new sheets that the flow does not expect. The flow should route these records to a failure or reject path rather than writing them to the target, and an alert should tell the reject owner that a source changed. Fixing it means reviewing the new layout with the sender, updating the explicit schema or reader settings, testing against recent files, and promoting the change through your normal change process. Ask in advance how the vendor notifies you of changes and who approves a schema update, because that agreement matters more than the first build.

Should we infer schemas or define them explicitly for spreadsheets?

For recurring feeds, define the schema explicitly and use inference only during discovery. Schema inference reads a sample and guesses types, which can turn a leading-zero identifier into a number or a date column into text when a later file differs. ExcelReader can infer a schema or use one you provide, so the choice affects how often the flow surprises you. Explicit schemas make validation strict and predictable, and they give reviewers a document to approve. Inference still helps when you first explore an unknown export, and the inferred result can become the starting point for the explicit schema.

How DeployOpen scopes and delivers spreadsheet ingestion in NiFi

A fixed-scope engagement for Excel and CSV ingestion starts with a written scope that names the inputs, the outputs and the failure rules before any flow is built. DeployOpen asks for sample files from each sending party, the target schema for each destination, and a decision on what happens to rejected rows. For workbooks, the scope records which sheets matter, whether any files are password-protected and how the password is supplied, and whether the team expects XLSX, legacy XLS or both. For CSV exports, it records the delimiter, character encoding, header behavior and the quoting rules each source uses. These answers decide whether the flow relies on schema inference or an explicit schema in the ExcelReader and CSVReader controller services.

Delivery runs inside the client's own infrastructure, in the environments the client names, usually a development instance and a production cluster kept separate. DeployOpen tests the flow with real files from the senders, plus deliberately malformed copies: a missing header, a shifted column, a text value in a numeric field, a hidden sheet, and a file opened and saved by a spreadsheet application. Each test is checked against the reject route and the provenance events, so the team can see which rows were routed to failure and why. Documentation covers the flow, the schema files, the reject handling steps and the runbook for a sender who changes a template. Ongoing support, schema updates and testing against new sender files can be added as an optional retainer once the fixed-scope work is accepted.