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(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 | NULL | NULL | |
| 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', …])] |
| 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, my_sdata)] |
| 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 | NULL | NULL | |
| sap_idoc_read | table | NULL | NULL | |
| sap_idoc_read_control | table | NULL | NULL | |
| sap_idoc_read_fields | table | NULL | NULL | |
| sap_idoc_read_raw | table | NULL | NULL | |
| sap_idoc_read_segment | table | NULL | NULL | |
| 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 |
|---|---|---|---|---|
| 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 | [] |