469,347 Members | 3,452 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,347 developers. It's quick & easy.

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

31
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
Nov 15 '10 #1
2 1981
TheSmileyCoder
2,321 Expert Mod 2GB
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.
Nov 16 '10 #2
Wayne L
31
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
Nov 16 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Kevin Forbes | last post: by
4 posts views Thread by news | last post: by
2 posts views Thread by Raghavendra Mahuli | last post: by
6 posts views Thread by =?Utf-8?B?emFsZHk=?= | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.