Import (1) Text File with (3) different Header Records (3)Different Tables

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Wayne L
    New Member
    • Sep 2007
    • 31
    #1

    Import (1) Text File with (3) different Header Records (3)Different Tables

    I am not sure on how I should approach this. I know a loop but I am a newbee on this..I have one file that contains 3 seperate header records that contain data under each header.
    (AA AA) (AA BB) (AA CC)
    What I want to do is loop thru the data under each header data. I am using ACCESS 2000 and have import Specs for each one.
    (AA AA)Data in (Table1) and
    (AA BB)DATA in (Table2) and
    (AA CC)Data in (Table3)
    The input file will have the header records in sequence.. So I just need to find AA AA all the data until it finds AA BB then AA CC.

    Thanx
    Wayne L
    Last edited by Wayne L; Nov 15 '10, 05:37 PM. Reason: wrong version of access
    Tags: None
    • TheSmileyCoder
      Recognized Expert Moderator Top Contributor
      • Dec 2009
      • 2322
      #2
      Its hard to grasp what your data looks like, but from what I see, I would suggest:

      Import the whole file to a table.
      Run a Insert Into query, selecting the fields you want, into table 1, and so on.

      If this for some reason does not work, im sure we can come up with a loop, but please present some sample data as accurately as possible.

        Comment

        • Wayne L
          New Member
          • Sep 2007
          • 31
          #3
          Below i have bolded and underlined what I want to key on with the loop..
          Currently I have set up three differenent tables to import them into and add some missing data and append to the correct format for export. The file has to stay in its current state with no changes except for the card colums. It is currently working in my current DB but would like to take this one file and import to their seperate tables instead of breaking them up into 3 seperate files before import. Thanks for your time looking at this..

          1LXF3441A OB MONTHLY FILLED ORDER/EXPENDITURE DIFFERENCE LISTING FOR 10-2010 PAGE 4407
          0 40540 4582AR 11 1804 60CC

          FY 11 APPN 1804 SBHD 60CC OB 4582A SX R C-UIC 40540 OFC CCG/SEGT 1
          0 DOCUMENT NO ACRN JOB ORDER TL NO ID BILL/VO NO UI COG STOCK NUMBER EXEC QTY AMT REMARKS
          ISS ACT FSC NIIN CODE

          V405400274PW11 AA 137LCA 006 EA FY-11 PWC CRA OBL 97978 66,000.00
          V405400274PW11 AA 137LCA D36 100 6022200 EA FY-11 PWC CRA EXP 0 1,753.96 MATCHED EXP
          .00 DIFFERENCE
          64,246.04 PT ORD EST

          V405400279R200 AA 1343CA 001 EA 4235015698792 OBL 1 3,347.86
          V405400279R200 AA 1343CA SMS 100 U0BL7 EA 4235015698792 EXP 1 3,955.33 MATCHED EXP
          607.47 DIFFERENCE

          V405400287B300 AA 134ECA 002 EA 4235015698792 OBL 2 6,695.72
          V405400287B300 AA 134ECA SMS 100 U0IIU EA 4235015698792 EXP 2 7,910.66 MATCHED EXP
          1,214.94 DIFFERENCE

          V405400287S002 AA 132ECA 002 SL 4020002460688 OBL 53 4,056.62
          V405400287S002 AA 132ECA NUA 100 U2347 SL 4020002460688 EXP 53 4,056.62 MATCHED EXP
          .00 DIFFERENCE

          V405400287S003 AA 132KCA 003 HD 5975004515001 OBL 180 797.40
          V405400287S003 AA 132KCA NUA 100 U2349 HD 5975004515001 EXP 180 797.40 MATCHED EXP
          .00 DIFFERENCE
          1LXF3441A OB MONTHLY FILLED ORDER/EXPENDITURE DIFFERENCE LISTING FOR 10-2010 PAGE 2114
          0 40540 4582AR 10 1804 60CC

          FY 10 APPN 1804 SBHD 60CC OB 4582A SX R C-UIC 40540 OFC CCG/SEGT 1
          0 DOCUMENT NO ACRN JOB ORDER TL NO ID BILL/VO NO UI COG STOCK NUMBER EXEC QTY AMT REMARKS
          ISS ACT FSC NIIN CODE

          V405400116S810 AA 0332B1 062 EA JON 332B PGI OBL 1 150.80
          V405400116S810 AA 0332B1 062 EA JON 332B PGI OBL 1 99,995.80
          V405400116S810 AA 0332B1 D05 100 0JFFF1I EA JON 332B PGI EXP 1 99,995.80 MATCHED EXP
          .00 DIFFERENCE
          150.80 PT ORD EST

          V405400125PC01 AA 0372F5 062 EA JON 372F CHAN OBL 1 1,439.89
          V405400125PC01 AA 0372F5 D05 100 0JFFF1I EA JON 372F CHAN EXP 1 1,439.89 MATCHED EXP
          .00 DIFFERENCE

          1LXF3441A OB MONTHLY FILLED ORDER/EXPENDITURE DIFFERENCE LISTING FOR 10-2010 PAGE 2114
          0 40540 4582AR 10 1804 60CC

          FY 09 APPN 1804 SBHD 60CC OB 4582A SX R C-UIC 40540 OFC CCG/SEGT 1
          0 DOCUMENT NO ACRN JOB ORDER TL NO ID BILL/VO NO UI COG STOCK NUMBER EXEC QTY AMT REMARKS
          ISS ACT FSC NIIN CODE

          V405400116S809 AA 0332B1 062 EA JON 332B PGI OBL 1 150.80
          V405400116S809 AA 0332B1 062 EA JON 332B PGI OBL 1 99,995.80
          V405400116S809 AA 0332B1 D05 100 0JFFF1I EA JON 332B PGI EXP 1 99,995.80 MATCHED EXP
          .00 DIFFERENCE
          150.80 PT ORD EST

          V405400125PC01 AA 0372F5 062 EA JON 372F CHAN OBL 1 1,439.89
          V405400125PC01 AA 0372F5 D05 100 0JFFF1I EA JON 372F CHAN EXP 1 1,439.89 MATCHED EXP
          .00 DIFFERENCE

          V405400130HZ01 AA 0373C5 062 EA JON 373C - HA OBL 97978 10,000.00
          V405400130HZ01 AA 0373C5 D36 100 6022400 EA JON 373C - HA EXP 0 1,414.23 MATCHED EXP
          .00 DIFFERENCE
          8,585.77 PT ORD EST

            Comment

            Previous template Next
            Working...