Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Importing Fix Files

Discussion in 'Information Technology' started by todd, Jul 28, 2006.

  1. todd

    todd
    Expand Collapse
    Guest

    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.
     
  2. Loading...

    Similar Threads Forum Date
    Can Guru Sahib change our 'bhaag' (destiny/fortune) or we have to live with it ? is it fixed Sikh Sikhi Sikhism Oct 8, 2015
    Save Your Sanity for Less (Financial Fix Video) Business, Lifestyle & Leisure Jan 15, 2014
    Build Your Own Sikh Art Exhibit: Call for SPN Members to Fix a Problem! Sikh Youth Oct 17, 2013
    India Four Men Convicted in Rape Case That Transfixed India Breaking News Sep 10, 2013
    Opinion Human Evolution: No Easy Fix Breaking News Oct 5, 2011

  3. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    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.
    --
    Good Luck!

    Graham Mandeno [Access MVP]
    Auckland, New Zealand


    "todd" <tvanhees@talk21.com> wrote in message
    news:1149625855.133794.236170@y43g2000cwc.googlegroups.com...
    >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.
    >
     

Share This Page