Read & write SAP IDoc files (flat + IDoc-XML) as SQL in DuckDB — a community extension in the erpl family.
Maintainer(s):
jrosskopf
Compatibility Matrix
This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.
| Platform | 1.4.4 | 1.4.5 | 1.5.5 | 1.5.6 | 2.0.0 |
|---|---|---|---|---|---|
| Linux AMD64 | |||||
| Linux ARM64 | |||||
| macOS AMD64 | |||||
| macOS ARM64 | |||||
| Windows AMD64 | |||||
| Windows ARM64 | |||||
| WebAssembly |
Installing and Loading
INSTALL erpl_idoc FROM community;
LOAD erpl_idoc;
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| sap_idoc_dict_from_fields | scalar | Normalize an IDOCTYPE_READ_COMPLETE PT_FIELDS list into the SPEC B4 dictionary schema (the transform used internally by the sap_idoc_dictionary macro). Pure — it only reshapes data that erpl_rfc already returned; no RFC is performed here. | NULL | [SELECT UNNEST(sap_idoc_dict_from_fields([{SEGMENTTYP: 'E1MARAM', FIELD_POS: '1', FIELDNAME: 'MATNR', BYTE_FIRST: '0', EXTLEN: '18', DATATYPE: 'CHAR', ROLLNAME: 'MATNR', DESCRP: 'Material number'}], 'MATMAS05', '', '620')), SELECT UNNEST(sap_idoc_dict_from_fields(r.PT_FIELDS,'MATMAS05','','620')) FROM sap_rfc_invoke('IDOCTYPE_READ_COMPLETE', sap_idoc_params('MATMAS05')) r] |
| sap_idoc_dict_offsets | table | Compute a segment dictionary's field offsets from lengths only: the 0-based SDATA offset of each field is the cumulative width of the preceding fields in the same segment. Input needs segnam, field_pos, field_name, length (datatype optional). 'src' is a .csv/.parquet path, a table/view name, or a relation expression. | NULL | [SELECT * FROM sap_idoc_dict_offsets('my_fields.csv')] |
| sap_idoc_dict_validate | table | Validate a segment dictionary: returns one row per structural problem (offset<0/length<=0, field exceeds SDATA(1000), overlaps the previous field, duplicate field_pos). An empty result means the dictionary is structurally sound. | NULL | [SELECT * FROM sap_idoc_dict_validate('my.dict.parquet')] |
| sap_idoc_dictionary | table_macro | Fetch and normalise a segment dictionary (SPEC B4 schema: segnam, field_pos, field_name, offset, length, datatype) from a live SAP system over erpl_rfc. 'params' must be a struct carrying PI_IDOCTYP/PI_CIMTYP/PI_VERSION – build it with sap_idoc_params. Requires erpl_rfc to be loaded; on a SAP-less host use a persisted dictionary file instead. | NULL | [SELECT * FROM sap_idoc_dictionary(sap_idoc_params('FLIGHTBOOKING_CREATEFROMDAT01'));] |
| sap_idoc_encode_control | scalar | Compose a 524-byte EDI_DC40 control record (BLOB) from up to 36 field values given in EDI_DC40 order (tabnam, mandt, docnum, docrel, …, serial); missing/short values are space-padded. | NULL | [SELECT sap_idoc_encode_control(['EDI_DC40','001','0000000000000042','740'])] |
| sap_idoc_encode_data_record | scalar | Compose a 1063-byte EDI_DD40 data record (BLOB). Numeric header fields (docnum, segnum, psgnum, hlevel) are zero-padded to SAP widths; segnam/mandt/sdata are placed as-is. | NULL | [SELECT sap_idoc_encode_data_record('E1BPSBONEW','001',0,2,1,2, sap_idoc_encode_sdata([0,3], [3,4], ['LH','0400']))] |
| sap_idoc_encode_sdata | scalar | Compose a 1000-byte IDoc SDATA payload by placing each value at its (offset, length) — the parallel lists come from the segment dictionary. Values are space-padded/truncated to width. | NULL | [SELECT sap_idoc_encode_sdata([0,3], [3,4], ['LH','0400'])] |
| sap_idoc_params | macro | Build the IDOCTYPE_READ_COMPLETE import-parameter struct that sap_idoc_dictionary passes to sap_rfc_invoke. Separate from sap_idoc_dictionary because sap_rfc_invoke executes the RFC at BIND time, so its argument must already be constant there; calling this with literals folds to one. | NULL | [sap_idoc_params('FLIGHTBOOKING_CREATEFROMDAT01')] |
| sap_idoc_read | table | Read one or many SAP IDoc flat files as generic long rows: one row per data (EDI_DD40) record with document_key, docnum, segnum, segnam, psgnum, hlevel, mandt and the raw 1000-char SDATA. The path may be a single file, a glob ('dir/.idoc', 's3://bucket/idocs/.idoc') resolved over DuckDB's filesystem, or a LIST of paths. Framing is auto-detected; 'lenient' salvages truncated files, 'encoding' decodes non-UTF-8 SDATA, and 'filename := true' adds the source-file column. | NULL | [SELECT * FROM sap_idoc_read('flight.idoc'), SELECT filename, segnam FROM sap_idoc_read('corpus/*.idoc', filename=true), SELECT * FROM sap_idoc_read(['a.idoc', 'b.idoc'])] |
| sap_idoc_read_control | table | Read the typed control record(s) of one or many SAP IDoc flat (or XML) files — all 36 EDI_DC40 fields (tabnam, docnum, idoctyp, mestyp, sndprn, rcvprn, …) plus a document_key. One row per IDoc; accepts a single path, a glob, or a LIST of paths, and 'filename := true'. | NULL | [SELECT idoctyp, mestyp, sndprn FROM sap_idoc_read_control('flight.idoc'), SELECT filename, idoctyp FROM sap_idoc_read_control('corpus/*.idoc', filename=true)] |
| sap_idoc_read_fields | table | Decode ALL fields of every record in an IDoc using a segment dictionary — one call, long form: one row per (record, field) with document_key, segnum, psgnum, hlevel, segnam, field_pos, field_name, datatype, value. SDATA is sliced byte-correctly (same decoding as sap_idoc_read_segment). Accepts a single path, a glob, or a LIST of paths (+ 'filename := true'). Segments absent from the dictionary yield one row with NULL field_name/field_pos and the raw trimmed SDATA (set include_unknown => false to drop them). 'dict' is any dictionary source: a .csv/.parquet path, a table/view name, or a relation expression. | NULL | [SELECT segnam, field_name, value FROM sap_idoc_read_fields('flight.idoc', 'flight_dict.csv'), SELECT * FROM sap_idoc_read_fields('corpus/*.idoc', 'dict', filename=true) WHERE segnam = 'E1BPSBONEW'] |
| sap_idoc_read_raw | table | Read every physical record of one or many SAP IDoc flat files with exact bytes: document_key, record_index, record_type ('C' control / 'D' data) and raw_record (BLOB). Byte-exact source for the writer — COPY (…) TO … (FORMAT sap_idoc). Accepts a single path, a glob, or a LIST, and 'filename := true'. | NULL | [COPY (SELECT raw_record FROM sap_idoc_read_raw('f.idoc') ORDER BY record_index) TO 'g.idoc' (FORMAT sap_idoc)] |
| sap_idoc_read_segment | table | Typed read of one IDoc segment type: split each segment's SDATA into named, typed columns using a segment dictionary. Accepts a single path, a glob, or a LIST of paths (+ 'filename := true'). 'dict' is the dictionary source — a .csv/.parquet path, a table/view name, or a relation expression (SPEC B4 columns: segnam, field_pos, field_name, offset, length, datatype). | NULL | [SELECT airlineid, flightdate FROM sap_idoc_read_segment('flight.idoc', 'E1BPSBONEW', 'flight_dict.csv'), SELECT * FROM sap_idoc_read_segment('corpus/*.idoc', 'E1BPSBONEW', 'dict_view')] |
| sap_idoc_read_xml | table | Read an IDoc-XML file as generic long rows: document_key, seq, segnam, hlevel, field_name, value (control fields appear as segnam='EDI_DC40', hlevel 0). IDoc-XML is self-describing, so no dictionary is needed. | NULL | [SELECT * FROM sap_idoc_read_xml('flight.xml')] |
| sap_idoc_to_xml | table | Convert a flat IDoc file to IDoc-XML (returns one row with the XML text). The dictionary names each SDATA field. Inverse of sap_idoc_xml_to_records. | NULL | [SELECT xml FROM sap_idoc_to_xml('flight.idoc', 'flight_dict.csv')] |
| sap_idoc_version | scalar | Smoke/echo function proving erpl_idoc is loaded; returns 'erpl_idoc |
NULL | [SELECT sap_idoc_version('ok')] |
| sap_idoc_xml_to_records | table | Convert an IDoc-XML file to flat records (document_key, record_index, record_type, raw_record BLOB), recomputing SEGNUM/PSGNUM from the XML nesting and packing SDATA per the dictionary. Feed raw_record to COPY (FORMAT sap_idoc) to write a flat file. | NULL | [COPY (SELECT raw_record FROM sap_idoc_xml_to_records('flight.xml','flight_dict.csv') ORDER BY record_index) TO 'flight.idoc' (FORMAT sap_idoc)] |
Overloaded Functions
This extension does not add any function overloads.
Added Types
This extension does not add any types.
Added Settings
| name | description | input_type | scope | aliases |
|---|---|---|---|---|
| datazoo_banner | Show the DataZoo feedback banner when an extension is loaded in an interactive terminal (at most once a day per extension). | BOOLEAN | GLOBAL | [] |
| erpl_telemetry_enabled | Enable anonymous ERPL telemetry (opt-out); see https://erpl.io/telemetry. | BOOLEAN | GLOBAL | [] |
| erpl_telemetry_key | PostHog project key for ERPL telemetry. | VARCHAR | GLOBAL | [] |