Massive nasty huge cluttered mess of a flat file

[joe789]

Hi Folks,

I am, unfortunately, working on a massive nasty huge cluttered mess of a flat file for a local government agencies compliance with new billing standards. Any help or suggestions would be greatly appreciated. After analyzing the nebulous and arduous task that lies ahead of me, I only come up with one way of attempting to step thru this.

The first step would be to somehow place carriage returns in specific places in the flat file. The flat file is just one continuous endless stream of about 29 different types of records. I realized that of these 29 different types of records, all running together in one flat file, there exists a unique identifier to each type of record; for instance,

ISA*
GS*
ST*
BPR*
REF*EV*
etc etc etc

and so on and so forth. Is there an easy way thru some easy code that I can run, where if one of the above listed 29 special cancatenation of characters indicating a different record type can have a carrage return applied to it before those special cancatenation of characters are reached in the huge mess of a flat file.

This would help me, as the first step, to figure out how to load this stuff ... sigh.

Thank you very much,

Joe

 

[jdraw]

I have attached some vba code that simulates your scenario and offers an approach to solution.

'---------------------------------------------------------------------------------------
' Procedure : MessFixer
' Author    : Jack
' Date      : 03/03/2011
' Purpose   : To take a long unformatted text string (file) and create a series of
'text strings by
'  finding markers within the unformatted text, and
'  inserting Cr Lf before each marker, then
'  outputting the "formatted strings" to a text file.
'
'In this trial a string is used to represent the incoming "Mess"
'and the final output is written to the immediate window via Debug.Print
'
'The incoming unformatted string is called Mess
'Mess includes the embedded markers
'Markers  are ISA* BPR* GS* ST* REF* EV* in this sample
'a vbCrLf constant is prepended to each found Marker in a new string called FixedMess
'---------------------------------------------------------------------------------------
'
Sub MessFixer()
Dim CRLf As String
   On Error GoTo MessFixer_Error

CRLf = vbCrLf  'This equates to VB's  vbCrLf constant
Dim Mess As String
Dim FixedMess As String

'simulate the long unformatted string with embedded markers
'Markers  are ISA* BPR* GS* ST* REF* EV*

Mess = "ISA*m,nas,cmans,cjdslofISA*jertprefi[dckBPR*merf[plr[kgpgk54GS*abcdefST*1234567BPR*This is a testREF*Not ApplicableREF*REF*EV*This is the last record in the test dataset."

FixedMess = Replace(Mess, "ISA*", CRLf & "ISA*")
FixedMess = Replace(FixedMess, "BPR*", CRLf & "BPR*")
FixedMess = Replace(FixedMess, "GS*", CRLf & "GS*")
FixedMess = Replace(FixedMess, "ST*", CRLf & "ST*")
FixedMess = Replace(FixedMess, "REF*", CRLf & "REF*")
FixedMess = Replace(FixedMess, "EV*", CRLf & "EV*")
Debug.Print FixedMess

   On Error GoTo 0
   Exit Sub

MessFixer_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure MessFixer of Module AWF_Related"
End Sub

It could be adjusted to read a text file and to write the final records to a new file.
It could be adjusted to work with all record type markers.


Output from TestData

ISA*m,nas,cmans,cjdslof
ISA*jertprefi[dck
BPR*merf[plr[kgpgk54
GS*abcdef
ST*1234567
BPR*This is a test
REF*Not Applicable
REF*
REF*
EV*This is the last record in the test dataset.

Hope it's useful.