ERPL-IDoc Function Reference
Every function is self-documenting — run
SELECT * FROM duckdb_functions() WHERE function_name LIKE 'sap_idoc_%' to see a
description and an example for each.
Reading
| Function | What you get |
|---|---|
sap_idoc_read(path [, framing, lenient, encoding]) | generic long rows: document_key, docnum, segnum, segnam, psgnum, hlevel, mandt, sdata |
sap_idoc_read_control(path [, …]) | the control record — all 36 EDI_DC40 fields, typed (flat or XML) |
sap_idoc_read_segment(path, segnam, dict [, …]) | typed columns for one segment type, sliced from SDATA per the dictionary |
sap_idoc_read_fields(path, dict [, …]) | every field of every record in one call — long rows: document_key, segnum, psgnum, hlevel, segnam, field_pos, field_name, datatype, value |
sap_idoc_read_raw(path [, …]) | one row per physical record with exact bytes — the byte-exact writer source |
sap_idoc_read_xml(path) | generic long rows from an IDoc-XML file (self-describing; no dictionary) |
Every reader accepts a single path, a glob, or a LIST of paths, resolved through
DuckDB's virtual filesystem — so a whole directory works, including remote stores
(s3://…, http(s)://…, gs://…) once the matching extension is loaded
(INSTALL httpfs; LOAD httpfs;) and a CREATE SECRET is set for credentials:
SELECT * FROM sap_idoc_read(['a.idoc', 'b.idoc']); -- explicit list
SELECT filename, idoctyp
FROM sap_idoc_read_control('s3://bucket/idocs/*.idoc', filename => true);
Reader parameters
All readers accept:
framing='fixed'(default) |'lf'|'crlf'— auto-detected when omitted.lenient := true— salvage complete records from a truncated file.encoding='utf-8'(default) |'latin-1'.filename := true— add a source-file column (handy across a glob).
sap_idoc_read_fields also takes include_unknown := false to drop segments absent from
the dictionary (default keeps them as one row with the raw trimmed SDATA).
The readers are streaming and parallel: each file is parsed record-by-record in
constant memory (never fully buffered), and a glob/LIST is read with one thread per file.
Rows are therefore unordered across files (order within a file is preserved) — add
ORDER BY if you need a stable order, exactly as with read_csv/read_parquet.
Writing
COPY (<single BLOB/VARCHAR column of raw records>)
TO 'file.idoc' (FORMAT sap_idoc [, framing 'fixed'|'lf'|'crlf', validate true]);
Build the records with the pure encoders when composing from scratch:
| Encoder | Produces |
|---|---|
sap_idoc_encode_sdata(offsets, lengths, values) | a 1000-byte SDATA payload |
sap_idoc_encode_data_record(segnam, mandt, docnum, segnum, psgnum, hlevel, sdata) | a 1063-byte EDI_DD40 record |
sap_idoc_encode_control(values) | a 524-byte EDI_DC40 control record |
Converting (flat ⇄ XML)
| Function | Direction |
|---|---|
sap_idoc_to_xml(flat_path, dict) | flat → IDoc-XML text |
sap_idoc_xml_to_records(xml_path, dict) | IDoc-XML → flat records (for COPY … (FORMAT sap_idoc)) |
Dictionary tooling
| Function | Purpose |
|---|---|
sap_idoc_dict_offsets(src) | compute field offsets from lengths (author a dict from field order + width) |
sap_idoc_dict_validate(src) | list structural problems; empty = sound |
sap_idoc_dict_from_fields(fields, idoctyp, cimtyp, release) | normalize a raw IDOCTYPE_READ_COMPLETE field list to the dictionary schema |
See The segment dictionary for the dictionary schema and how to author or fetch one.