Read a CSV or Excel file
Turn an arriving file into rows a workflow can work with, including awkward extracts with preamble, trailers or an unusual delimiter.
Most file-driven workflows start the same way: a file arrives, and a parse step turns it into a table the rest of the chain can read. Parse (Read) CSV and Parse (Read) XLSX are those steps, and their options exist because real extracts are rarely clean. A custodian file may open with three lines of preamble, close with a trailer, and use semicolons.
Add either step after a File received trigger or a Load File action, then set the options below in the side panel. For how the parsed table reaches the next step, see Action Inputs.
Parse (Read) CSV
| Option | What it does |
|---|---|
| Delimiter | The character separating fields. Defaults to a comma. Leave it empty to detect it per file |
| Skip Rows | Rows to ignore at the top of the file, before the header row |
| Skip Footer Rows | Rows to ignore at the bottom, for trailer lines that are not data |
The first row after any skipped rows is always treated as the header.
Letting Spark detect the delimiter
Leave Delimiter empty and each file is inspected on arrival. Spark tries a comma, a semicolon, a tab and a pipe, and keeps whichever one splits the header row into the most fields.
The header is the deciding row on purpose. Extracts often have ragged bodies: a 66-column header followed by cash lines that carry only a handful of fields. Judging the whole file would count that raggedness against the real delimiter and in favour of some incidental character that happens to appear consistently. The header is the one row guaranteed to be complete, so it is the one that votes.
Detection also parses the header properly rather than counting characters, so a quoted column name containing a separator, such as "Name, First";Age, does not vote for the character inside the quotes.
Set the delimiter explicitly when you know it and the files are consistent; that removes any doubt. Reach for detection when one workflow ingests files from several sources.
If a file cannot be read at all, the step fails with Unable to detect delimiter from CSV content. That usually means the header is empty, the file is not delimited text, or the rows you skipped did not include everything before the header. Open the run in Executions and check Skip Rows first.
Fixed-width files
A text file with columns defined by position rather than a separator uses Fixed Width mode instead, with a Column ranges editor that takes one start,end range per line. See Build a workflow.
Parse (Read) XLSX
| Option | What it does |
|---|---|
| Sheet Name | The sheet to load. Leave empty to load the first one |
| Header Row | Which row holds the column headers, counted from zero |
| Skip Rows | Rows to ignore at the top of the sheet, before the header row |
Excel files need no delimiter, so the equivalent question is which sheet and which row the headers are on. Naming the sheet is worth doing whenever the workbook has more than one, since "the first sheet" can change without warning when someone re-saves the file.
What comes next in the chain
A parse step outputs a Table, which is what the steps that follow expect:
- Map Data Fields to line the source columns up with your attributes.
- Filter Records to drop the rows you do not want.
- Save Record to write the result into a data object.