We are attempting to use filehelpers for some file interface projects specifically dealing with EDI. The sample definition below makes use of the EDI 858 Specification. Like most interface specs, each vendor has their own flavor. You can find a sample 858 EDI spec here
Our Vendor has their own flavor; this is the sample record definition we are currently using from before conversion:
H1_Record_type As String ' * 8 EDI858FF
H2_TRANS_DATE As String ' * 8 yyyyMMdd
H3_Trans_type As String ' * 2 00 = New, 01 = Cancel Carrier, 03 = Delete, 04 = Change
H4_Pay_type As String ' * 2 PP = Prepaid, CC = Collect, TP = Third Party (our default is PP)
H5_Load As String ' * 30 Authorization
H6_Load_Ready_date As String ' * 12 yyyyMMddHHmm
H7_Commodity As String ' * 10
H8_Customer_HQ_ID As String ' * 20 hard coded '0000000001'
H9_Customer As String
H10_Mill_ID As String ' * 20 Shipping Perdue Location or Destination Perdue Location
H11_Tender_Method As String ' * 10 blank for now
H12_Carrier_ID As String ' * 10 blank for now
H13_Weight As String ' * 7 estimated total load weight
H14_Weight_Qualifier As String ' * 2 blank for now
H15_Total_Miles As String ' * 7 zero for now
H16_Total_quantity As String ' * 7 blank for now
H17_Weight_Unit As String ' * 1 L = pounds, K = Kilograms, L=default
H18_REF As String ' * 3 REF
HR1_Ref_qualifier As String ' * 3 CO = Deliv Dest ref., P8 = Deliv Orig Ref.
HR2_Ref_value As String ' * 80
H19_END_REF As String ' * 7 END REF
H20_SPEC_INSTRUCTION As String ' * 16 SPEC INSTRUCTION
HS1_Qualifier As String ' * 3 1 = Credit Hold, 7T = Export Order, HM = Hazmat, L = Load ready for pickup
' PTS = Tarp, RO = Hot Load, TAR = Full Tarp, WTV = Weight Verification, DDN = Driver needs TWIC
' PR = Prohibited
H21_END_SPEC As String ' * 20 END SPEC INSTRUCTION
H22_NOTE As String ' * 4 NOTE
HN1_Note_Qualifier As String ' * 3 SPH = Special Handling, PRI = Load Priority, DEL = Transit Days
HN2_Note As String ' * 80
H23_END_NOTE As String ' * 8 END NOTE
H24_EQUIPMENT As String ' * 9 EQUIPMENT
H25_END As String ' * 13 END EQUIPMENT
H26_LOAD_CONTACT As String ' * 12 LOAD CONTACT
LC1_Contact_Name As String ' * 60
LC2_Contact_type As String ' * 2 EM= E-mail, FX = Fax, TE = Telephone
LC3_Contact_Data As String ' * 80
H27_END_LOAD_CONTACT As String ' * 16 END LOAD CONTACT
H28_STOP As String ' * 4 STOP There will always be 2 - CL and CU
S1_Stop_Number As String ' * 2
S2_Stop_Reason As String ' * 2 CL = Complete Load, CU = Complete Unload (one of each required for every load)
S3_LOCATION As String ' * 8 LOCATION
SL1_Location_ID As String ' * 20
SL2_Location_Name As String ' * 60
SL3_Location_Add1 As String ' * 55
SL4_Location_Add2 As String ' * 55
SL5_Location_City As String ' * 30
SL6_Location_State As String ' * 2
SL7_Location_Zip As String ' * 10 (use only 5 digits)
SL8_Location_Country As String ' * 3 USA, CAN, MEX
S4_END_LOCATION As String ' * 12 END LOCATION
S5_STOP_DATE As String ' * 9 STOP DATE
SD1_Date_Qualifier As String ' * 3 37 = No earlier than, 38 = No later than, 10 = Expected arrival time, 35 = Actual arrival
' 11 = Actual departure
SD2_Date_Time As String ' * 12 yyyyMMddHHmm
S6_END_STOP_DATE As String ' * 13 END STOP DATE
S7_STOP_REF As String ' * 8 STOP REF
SR1_Reference_Qualifier As String ' 3 72 = Transit Time, DK = Dock Number, OQ = Order Number
SR2_Reference_Value As String ' * 80
S8_END_STOP_REF As String ' * 12 END STOP REF
H29_END_STOP As String ' * 8 END STOP
H30_ORDER As String ' * 5 ORDER
O1_Order_Number As String ' * 80
H31_END_ORDER As String ' * 9 END ORDER
this is a sample message it would normally be in one long line:
EDI858FF~20140611~04~PP~1266010982~201406060700~CANOLA ML~0000000001~Business Name~RICHLAND~~~60000~~0~~L~REF~SA~Customer Name~END REF~STOP~01~CL~LOCATION~RICHLAND~~~~~~~~END LOCATION~STOP DATE~37~201406060000~END STOP DATE~STOP REF~OQ~5568~END STOP REF~END STOP~STOP~02~CU~LOCATION~261450~~~~~~~~END LOCATION~STOP DATE~37~201406060000~END STOP DATE~STOP REF~OQ~5568~END STOP REF~END STOP~ORDER~5568~END ORDER
I really think this may be too complex of a task for Filehelpers, but I wanted to put it out there to the community to see if you all could help.
As you can see the file is mostly tilde delimited, however certain fields in the definition also act as delimiters. For instance REF or STOP both contain additional information that could be many records deep. You could have multiple STOP definitions 1 to 999 location records. I am really really thinking that this is just too much for Filehelpers...
If you were to attempt this configuration for filehelpers, where would you start and how would you handle all the child fields?
FileHelpers is not the ideal tool for such a complex format. These are the reasons:
For (1), you don't mention whether you are importing or exporting EDI, or both. If you are only exporting EDI, error reporting would not be necessary.
For (2), you could work around the escaping problems by providing your own custom converters for the problem fields, but you might find every single field has a custom converter.
For (3), There are two ways of handling master/detail records with FileHelpers, but neither of them are likely to cover your requirements without some serious hacking
One way would be to use the MasterDetailEngine, but this only supports one detail type and only one level of nesting, so you would have to find workarounds for both of these.
Another way would be to use the to use the MultiRecordEngine. However, it would treat each row as an unrelated record and the hierarchy (that is, which S record belongs to which H record) would be hard to determine.