Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    West Of Atlanta

    Unanswered: I need help, trying to import a flat file.... It gets better though..

    Found this forum yesterday, have been reading quite a bit. And doing lots of searches. I have made great progress but, am in need of help now.


    I am charged with importing a flat file into Access. In order to generate a report of the data.

    The file is fixed length. Each record is 1050 characters long. Each record could be 1 of 3 posibilities.

    They begin with a fixed length field that is 1 character in width. This defines the record type.

    There are two record types I am concerned with. They begin with a 1 or a 2.

    The last record on the file begins with a 9 and gives very different information that is uninportant to me. There is only one record on the file that begins with a 9, the very last record.

    Type 1 and Type 2 records have different fields (with different lenghts). But, they both total 1050 characters long.

    The type 1 records have many fixed length fields that have information I need the fields total 1050 charecters. (Customer information)

    The type 2 records consist of 1050 characters also. However the 1050 characters are made of 7 groups of fixed length fields that are 150 characters wide. (7 sets x 150 characters = 1050). (Account history)

    The type 1 records consist of contact information.
    The type 2 records consist of account history.


    What I have been able to acomplish.

    I used Ultra Edit 32. I inserted a ^P every 1050 characters. Saved this file.

    This let me have a file that I am able to import with Access.

    I then created TWO different import schemes (not sure of proper word)

    I do the first import, put the data in a table. I then sort by the first character. This lets me easily delete everything EXCEPT the type 1 records.

    I do the second import, put the data in another table. I then sort by the first character. This lets me easily delete everything EXCEPT the type 2 records.

    So now I have TWO tables.

    One containing TYPE 1 records the other contaiing TYPE 2 records.


    The simple solution is to do a query that is based on relationships in these two tables.

    Then generate the report based on the query.


    The problem....

    If there are more than 7 items in the account history. Then the flat file consist of as many TYPE 2 records as needed to include the history.

    So now in my two tables 1 have the customer information. BUT
    In table 2, I may have several Type 2 records that need to go with one Type 1 record. And I need all of this information on the final report.


    1Jim Peterson Main Street USA 11235 etc.
    211235 paid 45.00 charged 22.00 etc.
    211235 charged 22.00 paid 85.00 etc.
    1Bill Someone Elm Ave. 85642 etc.
    285642 charged 56.00 charged 20.00 etc.
    9two customers in list total balance = 456.00 Date 11/12/04

    So from the above information.

    Table 1 Has This
    1Jim Peterson Main Street USA 11235 etc.
    1Bill Someone Elm Ave. 85642 etc.

    Table 2 Has This
    211235 paid 45.00 charged 22.00 etc.
    211235 charged 22.00 paid 85.00 etc.
    285642 charged 56.00 charged 20.00 etc.

    Notice all the records in table 1 start with "1"
    Notice all the records in table 2 start with "2"

    Right after the "2" in the second table is an account number that I can use a a relatuionship with table 1.

    BUT AGAIN my trouble if you notice from the example above. I have TWO Type 2 Records that go with One record in Table 1.

    My first post.
    Be easy with me.
    Will gladly call anywhere int he US if this could be discussed on the phone.

    Thanks In Advance.

    God Bless,


  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    that's a nice one. here is a completely untested thrash at it typed off the top of my head with a beer in my other hand.

    (DAO code, but ADO equivalent exists - turn on the reference to Microsoft DAO 3.6 if you are using my DAO version in A revisions 2000 or later)

    you have a table called tblAllLines which holds ALL the imported records, the single field is called myLine

    you have a table tblLines1 with all the fields defined for "type 1" plus an autonumber field IDline1

    you have a table tblLines2 with a field thisLine1 (long integer), plus fields to hold whatever is coming out of "type 2" lines.

    dim dabs as dao.database
    dim rec1 as dao.recordset
    dim rec2 as dao.recordset
    dim rec0 as dao.recordset
    dim thisID as long
    dim loopz as integer

    docmd.runSQL "DELETE * FROM tblLines1;"
    docmd.runSQL "DELETE * FROM tblLines2;"

    set dabs = currentdb
    set rec0 = dabs.openrecordset("tblAllLines")
    set rec1 = dabs.openrecordset("tblLines1")
    set rec2 = dabs.openrecordset("tblLines2")

    with rec0

    do while not .EOF

    select case left$(!myline, 1) 'discover the line type

    case 1 'type 1 line
    'now you need a bunch of lines - one for each of your type 1 fields
    'all of them look like
    rec1!fieldname = mid$(!myline, X, Y)
    'the correct fieldname
    'X the starting char position (1,2,3 etc)
    'Y the length
    thisID = rec1!IDline1 'capture the ID

    case 2 'type 2 line
    'now you are in type 2 lines belonging to the previous type 1 line
    'you need to handle the 7 x 150 char fields. might as well import
    'them all and delete blanks later
    for loopz = 0 to 6
    rec2!thisLine1 = thisID
    rec2!myField = mid$(!myLine, 1 + 150 * loopz, 150)
    'or something similar if you are splitting the 150 into sub-fields
    'WORRIED: you said 7 * 150 but you forgot about the "2" at the beginning
    'or is there a 2 at the first of each * 150 ?? either way, the math is simple

    case 9 'end line
    'do nothing

    case else
    msgbox "unexpected line type"

    end select


    end with

    make yourself a form, add a button, stick the above code in it's _Click() event

    maybe it works! izy
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2004
    West Of Atlanta
    Thank you for your reply.

    Seeing that I understand about 10% of what your reply, I am thinking that this might be a bit more complicated than what I thought at my first assessment.

    However, I will learn it.

    First there is a "2" at the beginning of all of the records of the Type 2's.
    If there are 7 records in that 1050 set, then each one WILL begin with a "2".


    It would seem that you are directing me to just import it ONE time. Then run some programming against the ONE table to sort it out. I do not understand this.

    If not too much trouble, would you please clarify this point?

    Again, thank you for your assistance.


  4. #4
    Join Date
    Dec 2002
    Préverenges, Switzerland
    as far as i understood your post, you have lines:

    although there's a lot of blah blah in my suggestion, the algorithm is really very simple.

    start at the beginning

    if the line begins with "1" grab the data and keep a note of which line-1 this is

    if the line begins with "2", it's stuff that belongs to the previous line-1 so break it up into it's 150-char records (or subfields within the 150-char records) and save them one by one recording which line-1 they belong to

    stop at the end

    it should run pretty much as advertised: you will need to build a couple of tables to hold the results, and you need to modify (several copies of (if you are splitting into subfields)) two lines:

    a series of
    rec1!fieldname = mid$(!myline, X, Y)
    to split up the line-1 into subfields

    a series of
    rec2!myField = mid$(!myLine, X + 150 * loopz, Y)
    to split up the line-2 150-chars into subfields

    take the line-2 case:
    if you had fields of 1, 5, 10, 20, 5, 109 characters (=150), you would need
    rec2!myFieldA = mid$(!myLine, 1 + 150 * loopz, 1) 'start at 1, take 1
    rec2!myFieldB = mid$(!myLine, 2 + 150 * loopz, 5) 'start at 2, take 5
    rec2!myFieldC = mid$(!myLine, 7 + 150 * loopz, 10) 'start at 7, take 10
    etc until you have sucked the 150 chars dry
    the for/next loop handles the repeating 150-char blocks

    you shouldn't need to mess with the blah blah (unless i made a stupid typo - not unheard of, i confess), you only need some maths to count where your subfields start inside the 150-char block and stick the numbers in those two lines (..multi-copy lines)

    currently using SS 2008R2

  5. #5
    Join Date
    Nov 2004
    West Of Atlanta

    I have not been around programming in a LONG TIME! I have pretty much just been a user. I am starting to understand that what you are tellig me is refering to programming. If I go that rout it will be a great challange.

    I havd been reading over your code, and understaqnd what you are suggesting now.

    I will need to back up and educate myself a bit before moving forward with your suggestion.

    Even if you gave me "code" that I could paste and it run perfect! I would NOT know where to paste it to, how to execute it or know how to take advantage of it after I ran it.


    You have a great understanding of the layout of the original file. Your example you posted in your last reply was perfect.

    I currently have my situation "under control" for the time being. How I am doing it is a bit crude. The report is not as I desire but, it works (for now).

    So I will try to back up a bit and do some research, buy a book or two and see if I can learn a bit about basic programming within access. I am guessing it is programming or scripting. I will be at Boarders in a bit, (date night tonight).

    What I am doing to get by....

    Importing all Type 1's into Table A. (I actually import ALL into it then sort and delete based on if it has a 1 in the first field.)

    Import all Type 2's into Table B. (same tecnique as above.)

    I then run a query, settig up a relationship between the two tables. This query has EVERY FIELD from both tables in it.

    I then run a report from that query.

    -- The Problem --

    I end up with MANY duplicates in my report.

    So if there were three type 2's for one type 1. It would be in my report 3 times. This works for now.... But far from ideal...

    Thank you so much for your help.

    God Bless,
    Paul Bailey

  6. #6
    Join Date
    Nov 2004
    Kansas City, KS
    Have you tried to Group in a query your type 2's? Maybe run a Sum on the $ amounts? what it looks like you have is a one-to-many relationship. Best I can think of is to group the type 2's some how.

    Maybe this helps,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts