Converting MS Excel into an EDI Mapper

EDI Tool for Developers

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 from http://www.edidev.com/ediregis.htm

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

Home

Evaluate Framework EDI

Source Code Examples

Purchase

Support

About EDIdEv LLC