By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,611 Members | 1,464 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,611 IT Pros & Developers. It's quick & easy.

Import from .txt to Access 2003

P: 1
Hello,
I am trying to import a fixed width text file with possible different widths based on a value in one field. I have the data .txt file and a spec file which describes the fixed width of one group of characters based on a value in a different field. I have pasted an example below. I thought a script would best handle this but cannot find any data describing how. Also, there are character sat the end of the rows that are "unpacked" and i cannot seem to find anything that will unpack these for me.

Text file
FL01 999STSFLD DTSE200712030702IGP9MAYER JERA* J  
FL01 999STSFLD DTSE200712030704IEP9WOODRUFF TIM* A T  -<
FL01 999STSFLD DTSE200712031157IEP1 M 56 34 34  *

Spec file
02 SEARCH-DETAIL-ACTIVITY-REC.
05 PSDAC-PLANT-SEARCH-PARAM.
10 PSDAC-STATE-COUNTY.
20 PSDAC-STATE-CODE PIC X(2).
20 PSDAC-COUNTY-CODE PIC X(2).
10 PSDAC-ACCOUNT-NUMBER PIC X(10).
10 PSDAC-TITLE-COMPANY-CODE PIC X(3).
10 PSDAC-BRANCH-ID PIC X(3).
10 PSDAC-ORDER-NUMBER PIC X(15).
10 PSDAC-USER-ID PIC X(4).
10 PSDAC-SEARCH-DATE.
20 PSDAC-SEARCH-DATE-CC PIC 9(02).
20 PSDAC-SEARCH-DATE-YY PIC 9(02).
20 PSDAC-SEARCH-DATE-MM PIC 9(02).
20 PSDAC-SEARCH-DATE-DD PIC 9(02).
10 PSDAC-SEARCH-REQUEST-TIME.
20 PSDAC-SEARCH-REQUEST-HOUR PIC 9(2).
20 PSDAC-SEARCH-REQUEST-MINUTE PIC 9(2).
10 PSDAC-TYPE-OF-SEARCH PIC X.
88 PSDAC-ORDER-SEARCH VALUE 'O'.
88 PSDAC-DATEDOWN VALUE 'D'.
88 PSDAC-INVESTIG-SEARCH VALUE 'I'.
10 PSDAC-ENTERED-GENERATED-IND PIC X.
88 PSDAC-PARM-WAS-ENTERED VALUE 'E'.
88 PSDAC-PARM-WAS-GENERATED VALUE 'G'.
10 PSDAC-APPLICATION-TYPE PIC X.
88 PSDAC-PLANT-PARM VALUE 'P'.
88 PSDAC-TAX-PARM VALUE 'T'.
10 PSDAC-SEARCH-PARAMETER.
20 PSDAC-PARAMETER-TYPE PIC X.
88 PSDAC-SUBDIVIDED-PARM VALUE '1'.
88 PSDAC-SECTIONAL-PARM VALUE '2'.
88 PSDAC-ARB-APN-PARM VALUE '3'.
88 PSDAC-MINING-CLAIM-PARM VALUE '4'.
88 PSDAC-RANCHO-PARM VALUE '5'.
88 PSDAC-ABSTRACT-PARM VALUE '6'.
88 PSDAC-INSTRUMENT-PARM VALUE 'I'.
88 PSDAC-INST-ALIAS-PARM VALUE 'J'.
88 PSDAC-INST-BOTH-PARM VALUE 'K'.
88 PSDAC-REFERENCE-INST-PARM VALUE '7'.
88 PSDAC-REFERENCE-ALIAS-PARM VALUE '8'.
88 PSDAC-NAME-PARM VALUE '9'.
88 PSDAC-REFERENCE-BOTH-PARM VALUE 'A'.
88 PSDAC-CASE-PARM VALUE 'C'.
20 PSDAC-PARAMETER-DETAIL PIC X(100).
20 PSDAC-SUB-DETAIL
REDEFINES PSDAC-PARAMETER-DETAIL.
30 PSDAC-SUB-PLAT PIC X(12).
30 PSDAC-SUB-BLOCK PIC X(6).
30 PSDAC-SUB-LOT-N-SUBLOTS.
35 PSDAC-SUB-LOT PIC X(8).
35 PSDAC-SUB-HI-LOT PIC X(8).
35 PSDAC-SUB-SUB-LOT PIC X(6).
35 PSDAC-SUB-HI-SUB-LOT PIC X(6).
30 PSDAC-SUB-SPACES PIC X(54).
20 PSDAC-SECTIONAL-DETAIL
REDEFINES PSDAC-PARAMETER-DETAIL.
30 PSDAC-SEC-TWP-DIR PIC X.
30 PSDAC-SEC-RNG-DIR PIC X.
30 PSDAC-SEC-TWP.
35 PSDAC-SEC-TWP-NBR PIC X(3).
35 PSDAC-SEC-TWP-HALF PIC X.
30 PSDAC-SEC-RNG.
35 PSDAC-SEC-RNG-NBR PIC X(3).
35 PSDAC-SEC-RNG-HALF PIC X.
30 PSDAC-SEC-SECTION PIC X(3).
30 PSDAC-SEC-QUARTER PIC X(6).
30 PSDAC-JUNK1 REDEFINES PSDAC-SEC-QUARTER.
35 PSDAC-SEC-GOVT-LOT-IND PIC X(3).
35 PSDAC-SEC-GOVT-LOT PIC X(3).
30 PSDAC-SEC-ARB PIC X(9).
30 PSDAC-SEC-SPACES PIC X(72).
20 PSDAC-ARB-DETAIL
REDEFINES PSDAC-PARAMETER-DETAIL.
30 PSDAC-ARB PIC X(50).
30 PSDAC-HI-ARB PIC X(50).
20 PSDAC-MINING-CLAIM-DETAIL
REDEFINES PSDAC-PARAMETER-DETAIL.
30 PSDAC-CLAIM-NAME PIC X(25).
30 PSDAC-DISTRICT PIC X(18).
30 PSDAC-MINE-SPACES PIC X(57).
20 PSDAC-ABSTRACT-DETAIL
REDEFINES PSDAC-PARAMETER-DETAIL.
30 PSDAC-ABSTRACT-ID PIC X(05).
30 PSDAC-LOW-ACRES PIC S9999V99999 COMP-3.
30 PSDAC-HIGH-ACRES PIC S9999V99999 COMP-3.
30 PSDAC-ABSTRACT-SPACES PIC X(85).
20 PSDAC-REF-INST-DETAIL
REDEFINES PSDAC-PARAMETER-DETAIL.
30 PSDAC-REF-ORIG-INST PIC X(13).
30 PSDAC-REF-SPACES PIC X(87).
20 PSDAC-INSTRUMENT-DETAIL
REDEFINES PSDAC-PARAMETER-DETAIL.
30 PSDAC-INSTRUMENT-NUM PIC X(13).
30 PSDAC-INST-SPACES PIC X(87).
20 PSDAC-CASE-DETAIL
REDEFINES PSDAC-PARAMETER-DETAIL.
30 PSDAC-CASE-NUM PIC X(18).
30 PSDAC-CASE-SPACES PIC X(82).
20 PSDAC-NAME-DETAIL
REDEFINES PSDAC-PARAMETER-DETAIL.
30 PSDAC-NAME-LAST PIC X(25).
30 PSDAC-NAME-FIRST PIC X(12).
30 PSDAC-NAME-MI PIC X(1).
30 PSDAC-NAME-SPOUSE.
35 PSDAC-NAME-SPOUSE-FIRST PIC X(12).
35 PSDAC-NAME-SPOUSE-MI PIC X(1).
30 PSDAC-NAME-SPACES PIC X(49).
20 PSDAC-PARTY-ROLE.
30 PSDAC-PARTY.
35 PSDAC-PARTY-LAST PIC X(25).
35 PSDAC-PARTY-FIRST PIC X(12).
35 PSDAC-PARTY-MI PIC X.
30 PSDAC-ROLE PIC X(6).
20 PSDAC-SOC-SEC-NBRS REDEFINES
PSDAC-PARTY-ROLE.
30 PSDAC-NAME-SOC-SEC-NBR PIC X(9).
30 PSDAC-NAME-SPOUSE-SSN PIC X(9).
30 PSDAC-SOC-SEC-SPACES PIC X(26).
10 PSDAC-NAME-SEARCH-IND PIC X.
88 PSDAC-GI-NAME-SEARCH VALUE 'G'.
88 PSDAC-TORTEE-NAME-SEARCH VALUE 'T'.
10 PSDAC-PARAMETER-COUNT PIC S9(9) COMP-3.
10 PSDAC-EIBTASKN PIC S9(7) COMP-3.
10 FILLER PIC X(46).
Jan 24 '08 #1
Share this Question
Share on Google+
1 Reply


nico5038
Expert 2.5K+
P: 3,072
This will be a daunting effort :-)

For some background info you can check:
http://www.3480-3590-data-conversion...le-import.html

Personally I woldn't try to create a script for this. The Redefines and the Computational fields aren't really easy to interpret.
Best to use a COBOL application to print these records and process the result :-)

Nic;o)
Jan 28 '08 #2

Post your reply

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