Search Shortcut cmd + k | ctrl + k
erpl_idoc
Downloads 887this week
GitHub stars 3
Extension descriptor (YAML)

Read & write SAP IDoc files (flat + IDoc-XML) as SQL in DuckDB — a community extension in the erpl family.

Maintainer(s): jrosskopf

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 []