X12 to Database: Batch Processing Example

The following example shows the To Translator code to parse an incoming message with the Vmd that ships with Iguana (other/example/270v3.vmd). Each transaction set is then parsed and the data is mapped from the transaction set into a new row of the table structure. All rows of the table (one per transaction set) are finally committed to the database with a single conn:merge()call.

conn = db.connect{<add DB connection info>}

function main(Data)
   local In, Name =x12.parse{vmd='example/270v3.vmd',data=Data}
   local OutTables=db.tables{vmd='example/270v3.vmd', name=Name}

   local TableRow = 1

   -- Iterate over functional groups
   for FGindex = 1, #In.InterchangeEnvelope.FunctionalGroup do
      print (In.InterchangeEnvelope.FunctionalGroup[FGindex])
      -- Iterate over transaction sets
      for TSindex = 1, #In.InterchangeEnvelope.FunctionalGroup[FGindex].TransactionSet do
         local TS = In.InterchangeEnvelope.FunctionalGroup[FGindex].TransactionSet[TSindex]
         processTS (TS, OutTables, TableRow)
         TableRow = TableRow + 1
      end
   end
   print (OutTables.tblL2000a)

   -- conn:merge { } 

   return true
end

function processTS (TS, OutTables, TableRow)
   local Table = OutTables.tblL2000a
   Table[TableRow].HLHierarchicalLevelCode = TS["Position 010"].Grp010ABCD[1].Loop2000A.HL[3]
   Table[TableRow].HLHierarchicalChildCode = TS["Position 010"].Grp010ABCD[1].Loop2000A.HL[4]
   -- Populate the rest of the columns in the table
   -- ...
end