How to extract a few data elements from the following data

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I have loaded about 97000 records its in the format below: All I need
is the following form the data :

City Name ------------------------------> Only Need the Name
of the city in this case COULDER
TAXABLE AMT ------------------------------> Only need the first
amount in this case 267.40
TAX AMT -------------------------------> Need the first 4
amounts in this case 12.03 1.34 10.70 0.00


I have Access 2003 what I would like if possible is to read this
massive text file and just extract the above information Every
Record
is divided by the CITY data item. the data is as below:
thanks in advance

CITY: COULDER COUNTY: SAFIRE GEOCODE: 37-011-0822 STR: F7-011-0840 F
37-109
COMPANY-CD: 0010 DIVISION-CD: CUST-CD:
0010018222 TRANS-CD:
EXEMPT CERTIFICATE #:
INV: 9002220111 DATE: 08/13/20005 LINE: 00020 PROD-
CD: USER:
TAX RATE/TYPE----- .045000 S .005000
S .040000 S .000000 ZS
GROSS SALES------ 267.40 267.40
267.40 267.40
ZERO RATE AMT---- 0.00 0.00
0.00 267.40
TAXABLE AMT------ 267.40 267.40
267.40 0.00
TAX AMT---------- 12.03 1.34
10.70 0.00 24.07

Then The Next City Will come up and so on & on, what couould I do ? I
would appreciate it greatly if someone could do a macro on it.

thanks

Dave
 
This is a start that you can follow --
SELECT Mid([BigField],InStr([BigField],"CITY: ")+6,InStr([BigField],"
COUNTY: ")-(InStr([BigField],"CITY: "))-6) AS CITY,
Mid([BigField],InStr([BigField],"COUNTY: ")+8,InStr([BigField]," GEOCODE:
")-(InStr([BigField],"COUNTY: "))-8) AS COUNTY, ....
FROM YourTable;
 
Back
Top