How to process a CSV file in Orchestrate for Integrations, from Document Retrieval to the output file

The path a CSV file takes through an Orchestrate integration

The file reaches the orchestration as a document on the integration event: the integration's business process runs a Service: Document Retrieval step before Service (Fire Integration), and the orchestration reads the file with the documents qualifier, for example documents.get().asCSV(). From there you give the file a CSV Format, loop over rows(), do the Workday work per row, and collect the results into an output document with an aggregation and a Store Document step.

Get the file onto the integration event

Workday's integration business process guidelines describe the standard inbound path as Initiation, then Service: Document Retrieval, then Service (Fire Integration). The retrieval step pulls the file from an external source such as an SFTP server, or routes a task to a person to upload it. Order matters: Document Retrieval has to come before Fire Integration, otherwise the orchestration starts with nothing to read.

An integration event carries its attached documents along with its launch parameters, messages and status, and the documents global functions work on those event documents. The useful ones for an inbound file:

Check the count before reading. If getDocumentCount() is 0, stop the run with a clear integration message instead of letting a later step fail on an empty value. Workday's troubleshooting table for an inbound integration that loaded nothing lists three causes: the retrieval step found no file, the file was empty, or the load failed. A count check plus a row check (below) separates the first two from the third.

Retrieve Document and Find Documents are a different mechanism. They read documents that a Store Document step saved earlier, by collection and entry ID or by reference ID. Use them to pick up your own intermediate files, not the file Document Retrieval brought in.

Workday's integration limits allow the Document Delivery and Retrieval services 5,000 integration documents per run, and the integration business process guidelines put the inbound document retrieval limit at 1 GB for XML and 300 MB for XLSX.

Parse it with a CSV Format, not with string splitting

Data.asCSV() treats the document as CSV with the default format. That works for a plain comma file, but as soon as you want columns by name, a header skipped automatically, or a different separator, create a CSV Format: gear icon above the canvas, Settings, CSV Formats. The fields are:

• Header Row: select it when the file's first line holds column names.
• Separator Character: default ,.
• Escape Character: default \.
• Quote Character: default ".
• Columns: one entry per column, each with a name and whether it is always required.

Then use the second form of asCSV, which takes a format and applies it. Two behaviours depend on that format:

• rows() leaves out the header row only when the format defines one. With the default format, the header line arrives as the first data row; start from the second row with rows(1), since row numbers are zero-indexed.
• columnValue("Cost Center ID") only works for a column that exists in the row's CSV schema, and throws if the name is not there. Without named columns, read by position with columnValueAtIndex(0).

Look at the escape character before go-live. It escapes separators inside fields, and the default is a backslash. If the sending system can put a backslash in a value, such as a file path, our recommendation is to set the escape to a character that never appears in the file; Workday's docs do not describe a specific failure here, but a stray escape character changes where a field ends.

Trailing blank lines produce empty rows. One of Workday's App Catalog sample apps removes them before parsing, with an expression of the form data.SourceFile.asTEXT().removeEmptyLines().asCSV().rows() (removeEmptyLines handles \n, \r and \r\n endings), and CsvRow.allColumnsAreEmpty() lets you skip any blank rows that remain.

Do not call toString() on the whole file to inspect it. Large Text and Data values are held in file-backed structures on disk, a String sits in memory, and toString() makes a copy. The runtime limits allow 200 MB of concurrent data in memory per orchestration, counting every in-scope value, against 2 GB on disk.

Loop over the rows and read each column

Add a Loop step and set its Data Set to the rows iterator, for example data.ParseFile.costCenters.rows(). Inside the loop, data.<LoopName>.item is the current CsvRow and data.<LoopName>.itemNumber is its position, which you want in every log line.

The typed readers convert as they read:

data.EachRow.item.columnValue("Cost Center ID")
data.EachRow.item.numberColumnValueWithDefault("Headcount Budget", 0)
data.EachRow.item.booleanColumnValue("Active")

numberColumnValue and booleanColumnValue throw when the value cannot be converted, and the WithDefault forms only cover a missing value, not a bad one. So a single malformed row can end the loop. Two steps keep one bad row from stopping the file:

1. A Validate step at the top of the loop body, with one check per rule (required fields present, numeric fields numeric with isNumeric()). Validate does not stop processing on its own; branch on its allValid output and log its failedValidations.
2. A local error handler on a group of the row's steps, set to Conditional Outputs. It runs its own steps and outputs isSuccess instead of rethrowing, so the loop moves on to the next row. Workday does not allow Conditional Outputs on the last step of an orchestration or group, so keep a step after the handled group, such as the Log to File that records the row result. In the handler, log the row number and processingError.message(); Workday says that message is for logging only, so do not branch on its text.

The Loop's Filter field narrows which items enter the loop, and Sort By orders them, which helps when the target needs parents loaded before children.

Then do the work for the row: a Send Workday API Request with a SOAP body built in Create Text Template, a REST call, or a lookup. When the target accepts many records per call, group rows with a Batch Loop instead of calling once per row. To combine the file with a Workday report on a shared key, see the Join Loop guide.

Write the output file

Add an aggregation to the Loop (three-dots menu, Add Aggregation) and choose a strategy per output:

• CSV Fold builds one CSV document from a row per iteration. Define the row as a String List expression, such as the input ID, a status and the Workday response's ID, or select a CSV Format from the Format list.
• Count counts the iterations it aggregates. Every strategy has a Condition, so one Count with no condition and one conditioned on isSuccess give you the total and the successes.
• Text Fold with content type text/csv builds the file line by line when you need full control over each line, as for a fixed-width layout.

Pass the CSV Fold output to Store Document. In an Integration System orchestration the stored document is associated with the launch event by default but not attached; set Attach document to integration event to true so it appears on the event, and select Deliver from associated delivery services if the integration's delivery configuration should send it on.

For a per-row audit trail, Log to File writes a CSV log with your own CSV Format or the default Timestamp, Severity, Message and Details columns. If an unhandled step fails, Orchestrate stores and attaches every named log automatically, so the log survives the failure.

If the receiving system needs a character set other than UTF-8, convert as the last step with convertCharsetEncoding. Workday notes that data in another character set is transmittable but no longer processable, so keep all processing in UTF-8 and convert only what you send.

Prove the run did what the file asked

A Success status means the orchestration finished, not that every row landed. Compare three numbers: the unconditioned Count (every row the loop saw), the Count of rows that succeeded, and the rows in your error log. The last two should add up to the first. Then open the integration event and check that the output and log files are attached, and read a sample of target records back in Workday (the cost centers, workers or assignments the file changed) to confirm the values, not just the existence of a response. For reading failed step messages, the Orchestrate errors guide covers where they appear.

Pipe-delimited, tab-delimited and fixed-width files follow the same path with a different parsing step; reading delimited and fixed-width files covers those.