Reading pipe-delimited, tab-delimited, .dat and fixed-width files in Orchestrate

Delimited files go through a CSV Format; fixed-width files go through lines()

Orchestrate has no separate parser for pipe, semicolon or tab files: you create a CSV Format whose Separator Character is that delimiter and read the document with asCSV using that format. Fixed-width files have no delimiter at all, so you read them as text, loop over lines(), and cut each field out by position with substring(start, end).

The file extension plays no part. asCSV() and asTEXT() both tell Orchestrate how to treat the document's content, so a .dat, .txt or extensionless file is read the same way as a .csv. Get the document from the integration event first, for example documents.name("payroll_extract.dat").get(), which returns the first event document with that filename; processing a CSV file in Orchestrate covers that part of the flow.

Set the delimiter in a CSV Format

In Orchestration Builder, click the gear icon above the canvas, choose Settings, then CSV Formats. The format has:

• Separator Character: enter | for a pipe file or ; for a semicolon file. The default is ,.
• Quote Character: default ".
• Escape Character: default \.
• Header Row: select it when the first line is column names.
• Columns: a name for each column and whether it is always required.

Pass the format to the second form of asCSV, which applies it to the document. Then rows() gives you an iterator of CsvRow values for a Loop, without the header row when the format defines one, and each row reads by name or position:

data.EachRecord.item.columnValue("Worker ID")
data.EachRecord.item.columnValueAtIndex(3)
data.EachRecord.item.numberColumnValueWithDefault("Hours", 0)

Many pipe-delimited extracts never quote their values. If such a file can contain a literal " or \ inside a value, the parser will read it as a quote or escape. Workday's docs do not describe that case, so the safe design is to set Quote Character and Escape Character to characters that cannot appear in the file.

Prove the format matches the file before processing anything: countColumns() on the parsed CSV should equal the number of fields in the file's layout, and countRows() should equal the record count the sender expects.

Why split() is the wrong tool for delimited lines

The obvious approach is to loop over lines and call split("|"). Workday's String reference documents the catch: adjacent separators are treated as one separator. Its own example turns "R:e::n:::o" into ["R", "e", "n", "o"].

In a delimited file, two separators in a row mean an empty field. With split, that field disappears and every column after it shifts one place to the left, so a blank middle name puts the hire date in the middle-name position. Nothing fails; the data is simply wrong. Parse the file with a CSV Format instead, and confirm it on a sample line that has an empty field: columnValueAtIndex for that position should return an empty value and the next column should hold the next field. When you really do need to parse one line at a time (see multi-record files below), fromLine in the CsvRow reference takes the line and a CSV format and builds a row value using that format.

Tab-delimited files

The CSV Formats page says to enter the separator character in the Separator Character field, and it does not say how a tab is entered there. Test it on a short sample file before building on it: parse the file with the format and check countColumns(). If it returns 1, the tab was not taken as the separator.

Header and trailer records

Bank, payroll and benefits files often wrap the detail lines in a header record and a trailer record, and the trailer usually carries a record count or a control total. The Text member functions handle this without a loop:

Each has a form that takes a lineEnding argument for files that use an unusual line terminator.

For a trailer such as T00000125, where positions 1 to 8 hold the detail count, a Create Values step named Parsed can build the detail text and both counts:

text:          documents.name("payroll_extract.dat").get().asTEXT().removeEmptyLines()
details:       data.Parsed.text.dropFirstAndLastLines(1, 1)
detailCount:   data.Parsed.details.lineCount()
trailerCount:  data.Parsed.text.lastLine().toString().substring(1, 9).trim().toNumber()

Then check detailCount against trailerCount. A Validate step only logs a message when its condition is false; Workday's reference says the orchestration does not stop when a validation fails. To stop the run, use Continue on Conditions with the assertion data.Parsed.detailCount == data.Parsed.trailerCount: when it evaluates to false, the orchestration stops and logs your message. A short file is a sending-system problem; loading part of it and reporting success is worse than loading nothing.

Files with several record types

When the file repeats a group (a header record starting H followed by its detail records starting D), batchIteratorByStartsWithAt("H") returns an iterator of text batches, a new batch created at each line that starts with H. An outer Loop takes one group per iteration, and an inner Loop over that batch's lines() handles its records. Inside the inner loop, branch on the record type with Branch on Conditions, for example data.EachLine.item.startsWith("D").

The ...After variants, such as batchIteratorByStartsWithAfter, create the batch after the matching line instead of at it, and the ByContains and ByEndsWith variants match on text anywhere in the line or at its end. Run one on a sample file and check the first batch's firstLine() before relying on which batch the marker line lands in, because the reference does not spell that out.

Read a fixed-width file by position

Write the layout down as zero-based start and end positions first. substring(start, end) counts from zero, includes the start position and excludes the end, so a field in columns 1 to 10 of the vendor's specification is substring(0, 10). Get this wrong by one and every field after it starts with the last character of the field before.

For a layout of worker ID (10 characters), last name (30) and pay rate (10):

workerId:  data.EachLine.item.substring(0, 10).trim()
lastName:  data.EachLine.item.substring(10, 40).trim()
payRate:   data.EachLine.item.substring(40, 50).trim().toNumber()

The Loop's Data Set is data.Parsed.details.lines(), which gives each line as a string.

trim() removes characters with code 32 or below from both ends, so it strips the padding spaces and a trailing carriage return on files with Windows line endings. toNumber() throws on anything that is not a valid number, so test the field with isNumber() in a Validate step before converting it.

The String reference does not say what substring does when the end position is past the end of a short line. Rather than depend on it, check data.EachLine.item.length() against the record length in the same Validate step, and log short lines with Log to File instead of parsing them.

For large files, keep the file as Text and loop over lines(). Workday's data-types page notes that Text values can be file-backed while String values are held in memory, and calling toString() on the whole document copies it into memory.

Write a fixed-width or delimited output file

For delimited output, a CSV Fold aggregation on the Loop with a CSV Format that has your separator builds the file from one row per iteration.

For fixed-width output, use a Text Fold aggregation with content type text/plain and a Line Separator, and build each line from padded fields. Read Workday's padding examples closely: "New York".leftPad(3, "*") returns "***New York", so the argument is the number of characters added, not the final width. To fill a 30-character field, truncate first and pad by the difference:

data.Row.lastName.truncate(30).rightPad(30 - data.Row.lastName.truncate(30).length())

For zero-filled numbers, leftPad(n, "0") follows the same rule. Check the result with length() in a Validate step on the first test run, so a layout error shows up as a failed validation rather than as a rejection from the receiving system. If you build each line in a Create Text Template instead, the text template guide covers the syntax.

Check the parse before you load anything

Run the orchestration once against a real sample file before you add the steps that write to Workday, and write each parsed record back out with Log to File or a CSV Fold. Compare that output with the source file for a handful of lines, especially lines with empty fields, the longest values, and the last record. When the columns line up and the counts match the trailer, enable the load. For the pieces that come after parsing, see sending records in groups with Batch Loop, and reading an Excel .xlsx file in Orchestrate when the same feed arrives as .xlsx.