Creating an EDI Mapper in Excel
This is an example of how one can transform an Excel spreadsheet into a simple EDI mapper for generating and translating small EDI files.
Application
A practical application for this Excel spreadsheet EDI mapper would be at a small
"mom-&-pop" company that is unable to afford a comprehensive EDI system,
but wishing to do business with companies mandating EDI for all business transactions.
How it Works
Open the ExcelMapperX12.xls in MS Excel. If a security dialog
box appears, click on Enable Macros.
- To generate an EDI file
- Enter the SEF file in cell B,4. (This SEF file is required by the Framework EDI component to construct and validate your EDI file.)
- Enter an EDI filename output in cell C,4.
- Enter your data in column B.
- Enter the properties of the data element - of where the data should be assigned to - on their specified columns. (Basically at this stage, you are creating a map for your data so that it can be laid out in a format that conforms to your trading partner's EDI guideline.)
- Then click on the generate button.
- To translate an EDI file
- Click on the Translate worksheet.
- Enter the SEF filename in cell B,4. ( This SEF file is required by the Framework EDI component to parse and validate your EDI file.)
- Enter the EDI filename you wish to translate in cell C,4.
- Enter the properties of the data elements - of where the data should be obtained from - on their specified columns.
- Then click on the translate button. (The translate process will populate the cells in the Data Column as it reads the EDI file.)
During translation, the component will also validate the EDI file against the SEF file in order to create a 997 Functional Acknowledgment. The 997 Functional Acknowledgment string will get displayed after the translation is completed.
The Visual Basic Script
The VB script source code of the macros can be accessed by going to Tools, Macro, then Visual Basic Editor.
The macro source code for both the generation and translation uses the Framework EDI component, which can be downloaded for evaluation here.
VB script source code for generating an EDI file
Private Sub cmdGenerate_Click()
Dim oEdiDoc As Fredi.ediDocument
Dim oInterchange As Fredi.ediInterchange
Dim oGroup As Fredi.ediGroup
Dim oTransactionset As Fredi.ediTransactionSet
Dim oSegment As Fredi.ediDataSegment
Dim oWarning As Fredi.ediWarning
Dim oWarnings As Fredi.ediWarnings
Dim sSefFile As String
Dim sEdiFile As String
Dim sPath As String
Dim nRow As Integer
Dim nSegIdCol As Integer
Dim nElemPosCol As Integer
Dim nLoopSectionCol As Integer
Dim nAreaCol As Integer
Dim nDataCol As Integer
Dim nElem As Integer
Dim sHierString As String
Dim sSegmentID
Dim sLoopSection
Dim sErrMsg As String
Dim nErrCount As Integer
Cells(3, 6) = "Please wait gen..."
sPath = Application.ActiveWorkbook.Path & "\"
sSefFile = Cells(4, 2)
sEdiFile = Cells(4, 3)
Set oEdiDoc = New Fredi.ediDocument
oEdiDoc.CursorType = Cursor_ForwardWrite
'Load SEF files
oEdiDoc.LoadSchema sPath & sSefFile, 0
'specify terminators
oEdiDoc.SegmentTerminator = "~" & vbCrLf
oEdiDoc.ElementTerminator = "*"
oEdiDoc.CompositeTerminator = ">"
'spreadsheet rows and columns
nRow = 8
nSegIdCol = 4
nElemPosCol = 5
nLoopSectionCol = 6
nAreaCol = 7
nDataCol = 2
'Generating the ISA segment
Set oInterchange = oEdiDoc.CreateInterchange(Cells(30, nDataCol), Cells(31, nDataCol))
Set oSegment = oInterchange.GetDataSegmentHeader
For nElem = 1 To 16
oSegment.DataElementValue(nElem) = Cells(nRow, nDataCol)
nRow = nRow + 1
Next
'Generating the GS segment
Set oGroup = oInterchange.CreateGroup(Cells(31, nDataCol))
Set oSegment = oGroup.GetDataSegmentHeader
For nElem = 1 To 8
oSegment.DataElementValue(nElem) = Cells(nRow, nDataCol)
nRow = nRow + 1
Next
'Generating the ST segment
Set oTransactionset = oGroup.CreateTransactionSet(Cells(32, nDataCol))
Set oSegment = oTransactionset.GetDataSegmentHeader
For nElem = 1 To 2
oSegment.DataElementValue(nElem) = Cells(nRow, nDataCol)
nRow = nRow + 1
Next
'Generating all other segments in the Transaction Set
Do While Cells(nRow, nSegIdCol) <> ""
sSegmentID = Cells(nRow, nSegIdCol)
sLoopSection = Cells(nRow, nLoopSectionCol)
If sLoopSection <> "" Then
sHierString = sLoopSection & "\" & sSegmentID
Else
sHierString = sSegmentID
End If
'create data segment
Set oSegment = oTransactionset.CreateDataSegment(sHierString)
'populate elements with data
nElem = 1
Do While sSegmentID = Cells(nRow, nSegIdCol)
nElem = Cells(nRow, nElemPosCol)
oSegment.DataElementValue(nElem) = Cells(nRow, nDataCol)
nRow = nRow + 1
Loop
Loop
'save Edi file
oEdiDoc.Save sPath & sEdiFile
Cells(3, 6) = ""
'display EDI string
MsgBox oEdiDoc.GetEdiString, , "EDI Output"
'check for errors
Set oWarnings = oEdiDoc.GetWarnings
nErrCount = oWarnings.Count
If nErrCount > 0 Then
For i = 1 To nErrCount
Set oWarning = oWarnings.Warning(i)
sErrMsg = sErrMsg + oWarning.Description + " " + oWarning.SegmentLoopSection + "\" + oWarning.SegmentId + vbCrLf
Next
MsgBox sErrMsg, , "Errors found"
End If
End Sub
VB script source code for translating an EDI file
Private Sub cmdTranslate_Click()
Dim oEdiDoc As Fredi.ediDocument
Dim oSegment As Fredi.ediDataSegment
Dim oAck As Fredi.ediAcknowledgment
Dim sHierString As String
Dim sSegmentID
Dim sLoopSection
Dim nRow As Integer
Dim nSegIdCol As Integer
Dim nElemPosCol As Integer
Dim nLoopSectionCol As Integer
Dim nAreaCol As Integer
Dim nDataCol As Integer
Dim nElem As Integer
Dim sPath As String
On Error Resume Next
sPath = Application.ActiveWorkbook.Path & "\"
Cells(3, 6) = "Please wait translating..."
sSefFile = Cells(4, 2)
sEdiFile = Cells(4, 3)
Set oEdiDoc = New Fredi.ediDocument
'enable 997 acknowledgment
Set oAck = oEdiDoc.GetAcknowledgment
oAck.EnableFunctionalAcknowledgment = True
'Load SEF file
oEdiDoc.LoadSchema sPath & sSefFile, Schema_Standard_Exchange_Format
oEdiDoc.LoadSchema sPath & "997_4010.sef", Schema_Standard_Exchange_Format 'for generating 997
'Load EDI file
oEdiDoc.LoadEdi sPath & sEdiFile
'spreasheet rows and columns
nRow = 8
nSegIdCol = 4
nElemPosCol = 5
nLoopSectionCol = 6
nAreaCol = 7
nDataCol = 2
'get ISA segment
Set oSegment = oEdiDoc.FirstDataSegment
For nElem = 1 To 16
Cells(nRow, nDataCol) = oSegment.DataElementValue(nElem)
nRow = nRow + 1
Next
'get GS segment
Set oSegment = oSegment.GetDataSegmentByPos("\ISA\GS\GS")
For nElem = 1 To 8
Cells(nRow, nDataCol) = oSegment.DataElementValue(nElem)
nRow = nRow + 1
Next
'get ST segment
Set oSegment = oSegment.GetDataSegmentByPos("\ISA\GS\ST\ST")
For nElem = 1 To 2
Cells(nRow, nDataCol) = oSegment.DataElementValue(nElem)
nRow = nRow + 1
Next
'get all other segments
Do While Cells(nRow, nSegIdCol) <> ""
sSegmentID = Cells(nRow, nSegIdCol)
sLoopSection = Cells(nRow, nLoopSectionCol)
If sLoopSection <> "" Then
sHierString = sLoopSection & "\" & sSegmentID
Else
sHierString = sSegmentID
End If
'get data segment
Set oSegment = oSegment.GetDataSegmentByPos("\ISA\GS\ST\" & sHierString)
'get data from elements
If Not oSegment Is Nothing Then
nElem = 1
Do While sSegmentID = Cells(nRow, nSegIdCol)
nElem = Cells(nRow, nElemPosCol)
Cells(nRow, nDataCol) = oSegment.DataElementValue(nElem)
nRow = nRow + 1
Loop
Else
'instantiate oSegment object
Set oSegment = oEdiDoc.FirstDataSegment
nRow = nRow + 1
End If
Loop
Cells(3, 6) = ""
'display acknowledgment
MsgBox oAck.GetEdiString, , "997 Functional Acknowlledgment"
End Sub
Click here to evaluate the Framework EDI
The example programs provided in this article are for illustration only, and have no purpose other than to show software developers how to use the Framework EDI component in programming languages to process EDI files. If you have any questions, don't hesitate to contact us:
