EDIdEv - Electronic Data Interchange Development

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.

ExcelMapper.zip

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 download a trial version of 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: