Importing Fix Files

[todd]

I am attempting to import a FIX (Financial Information exchange ) file
into an access database using the import wizard but this does not work
because the wizard doesn't differentiate what is at the the start of
the file and what is at the end of the file. I currently save the Fix
file as a text file as access doesn't understand what a fix file and
try and import this.

For background information is basically a text file with varying
amounts of data per line but has a start and ending for each
line.Sample excerpt from file below :

8=FIX.4.1,9=0081,35=A,98=0,108=60,95=11,96=easylogging,52=20051202-06:37:45,49=UknWrkSta,56=,34=1,10=077,
8=FIX.4.1,9=0216,35=EASYMSG,5156=0,5164=0,5163=0.00,5142=System,5060=,5063=06:37:45,5062=20051202,5112=06:37:45,5061=SYSTEM,5020=STARTUP,5021=,5141=2.0,5075=TCL23,5140=UnsetUser,5058=369582,52=20051202-06:37:45,49=UknWrkSta,56=,34=2,10=151,

You will note the start of each line is always with 8=FIX.4.1, and the
end is always 10= ( a value that changes constantly. The length of each
line may vary dependant on the amount of data contained within each
line.

So in summary i need to Open the File in this example the file is
called 2902MyOr.Fix and loop through where each line starts with 8= and
populate each row with the data - leaving fields blank if their is no
data in that field for that row. The row only ends when the last piece
of data is identified with the value 10= and then everything is
inserted into the row before starting on the next piece of data to
interpret.

Any help on the import procedure for this would be appreciated.

 

[Graham Mandeno]

Hi Todd

This is certainly possible, but you will probably have to open and read the
text file line by line using VBA code and pick each line apart to populate
the fields in the new record.

I'm not clear about what the number to the left of every "=" is. Is it a
field name or what? It looks more like an attribute code, which suggests
that you will be writing multiple records (one per data element) linked to a
header record (one per line).

Also, when you say "each line", do you mean that the lines are delimited by
line separators (CR or CR+LF)?

All these factors affect how you would go about it.

In any case, you would open the file for input:

Dim sFile as string, hFile as integer
sFile = "C:\Some path\2902MyOr.fix"
hFile = FreeFile
Open sFile for input as #hFile

Then you can read it line by line:

Dim sLine as string
Do until EOF(hFile)
Line Input #hFile, sLine
' do something with the line
Loop
Close #hFile

For each line, you can split it into data elements at the comma separators
(assuming there are no commas anywhere else in the data):

Dim aElements as Variant, i as integer
aElements =Split( sLine, ",")
For i = 0 to UBound( aElements )
' do something with aElement(i)
Next i

For each element, you can use Split again to separate the left and right
side of the "=" and do whatever is appropriate with the two parts.