Processing EDIFACT Messages

EDI provides a technical basis for commercial “conversations” between two entities, either internal or external. EDI constitutes the entire electronic data interchange paradigm, including the transmission, message flow, document format, and software used to interpret the documents. EDI standards describe the rigorous format of electronic documents.

In essence, an EDI message is just a small text file that can be sent via email, HTTP or FTP

UNH+199700001+INVOIC:D:93A:UN:EAN007'
BGM+380+424876'
DTM+137:199701111045:203'RFF+ON:334411'
RFF+AAK:23149'
NAD+BY+7080001000004::9++Hans Hansen AS+Storgata 1+TRONDHEIM++7005'
NAD+SU+7080000366767::9++Børsterud AS+Industriveien 1+OSLO++0580'
FII+RH+60731108042'
RFF+VA:FORETAKSREGISTERET NO123456789MVA'
CTA+AD+Lise Hansen'
NAD+IV+7080001000011::9++Hans Hansen Øst AS+Grenseveien 1+HEBEKK++1406'
NAD+DP+7080001000028::9++Hans Hansen Midt AS+Heggeveien 1+HEIMDAL++7080'
NAD+SF+7080000000065::9++Børsterud AS, Vareutlevering+Industriveien 1+OSLO++0580'
PAT+1'
DTM+7:19970110:102'
PAT+3'
DTM+13:19970630:102'
TOD+3++EXW'
LIN+1++7030432630011:EN'
PIA+1+7200018:SA::91'
IMD+FL++:::RULLESYSTEM'
IMD+C++TU'
QTY+47:100'
QTY+59:12'
ALI+++6'
MOA+66:25000,00'
MOA+203:22000,00'
PRI+AAB:250,00'
PRI+AAA:220,00'
PAC+++CT:KARTONG'
TAX+7+VAT+++:::23+S'
ALC+A++++PAD:::TILBUDSRABATT'
PCD+3:10'
MOA+8:2500,00'
ALC+A++++PDE:::PALLERABATT'
MOA+8:500,00'
RTE+1:5,00'
LIN+2++7030439770710:EN'
PIA+1+2844001:SA::91'
IMD+FL++:::BØRSTER'
IMD+C++TU'
QTY+47:200'
QTY+59:36'
ALI+++6'
MOA+66:2000000'
MOA+203:17900,00'
PRI+AAB:100,00'
PRI+AAA:89,50'
PAC+++CT:KARTONG'
ALC+A++++PAD:::TILBUDSRABATT'
PCD+3:10'MOA+8:2000,00'
ALC+A++++QD:::KVANTUMSRABATT'
QTY+1:50'
PCD+3:2,0'
MOA+8:100,00'
UNS+S'
CNT+2:2'
MOA+66:45000,00'
MOA+203:39900,00'
MOA+260:5100,00'
MOA+131:5100,00'
MOA+NET:39900,00'
MOA+125:39900,00'
MOA+150:9177,00'
MOA+86:49077,00'
MOA+129:39900,00'
TAX+7+VAT++39900,00+:::23+S'
MOA+176:9177,00'
ALC+A++++PAD:::TILBUDSRABATT'
MOA+8:4500,00'
ALC+A++++PDE:::PALLERABATT'
MOA+8:500,00'
ALC+A++++QD:::KVANTUMSRABATT'
MOA+8:100,00'
UNT+76+199700001'

In order to load data into the database, we need to transfer data into a more readable format

Data reader.

1. We can't use fixed-width format because message lengths vary
2. Better option to use delimited format but use the wrong delimiter so the entire line is read and passed to the next step

Next step is to turn the data so every part will be in a separate record.

UNH+199700001+INVOIC:D:93A:UN:EAN007'BGM+380+424876'DTM+137:199701111045:203' to

UNH+199700001+INVOIC:D:93A:UN:EAN007 [Record 1]
BGM+380+424876'DTM+137:199701111045:203 [Record 2]

Once data is rotated we want the UNH part to go into the UNH field, BGM into the BGM field and so on.
Since we have so many fields we will be using sub-transformation

The logic is as follows:

When field value starts with UNH+
We pass to the next step value after UNH+
if not we pass null
Keep value transformation allow us to fill in holes in the data

EG it converts:

Value1
nul
Value2
Null

into:

Value1
Value1
Value2
Value2

Next step is to get rid of empty values

And duplicate the data

The result of deduplication

Here we can convert our data even further if we wish using various transformations

Download sample