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 EDI message is just a small text file which can be send via email,http or ftp

Message Example:

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'

Here is the message format description:

EDIFACT File Description
UNA:+.?
UNB+UNOA:1+9377778760643:ZZ+9399700000001:ZZ+050322:1237+000000001Message header, with sender GLN, addressee GLN, date, time & interchange number
UNH+00000000000459+ORDERS:D:96A:UN:EAN008Message Beginning
BGM+220+10910220+92Purchase Order Number
DTM+137:20050322:102Order Date
DTM+2:20050322:102Requested Delivery Date
NAD+SU+1217::92JB Supplier Code
NAD+ST+304::92Ship to Store Code
NAD+IV+304::92Invoice to Store Code
LIN+1Item Line Number
PIA+5+711719347521:VN::91Product Identifier
QTY+21:10.0000:EAQuantity
PRI+AAA:40.0000::TUPrice Ex GST
UNS+SMessage trailer
CNT+2:1Control Total (total number of item lines)
UNT+14+00000000000459Message Trailer
UNZ+1+000000001Message End
Every data part is delimited by single quote and prefixed with some characters and they indicate which kind of data it is. For example LIN indicates line number

Processing EDI Data

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

Data reader.

  1. We can't use fixed width format because message length vary
  2. Better option to use delimited format but use wrong delimiter so entire line is read and passed to the next step
We can also use File System as reader connection type

Next step is to turn the data so every part will be in the 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 UNH part to go into UNH field, BGM into 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 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

For more technologies supported by our ETL Software see Advanced ETL Processor Versions and Visual Importer ETL Versions

Confused? Ask question on our ETL Forum

  • knowledgebase/processing_edifact_messages.txt
  • Last modified: 17/09/2018 09:43
  • by admin