J
John
I have two text based reports that are 150+ pages. I need to "read" the data
off of the report and put it into a table. The first 14 rows of each page
are header stuff. I'll need to read some information off of the first header
(report name, job number, reprort dates) to verify that the file being
imported is the proper file, from that point forward, the header information
can be "dumped". I found a post from John Nurick back in 8/15/2005 with a
function to dump the first x rows from a text file, but it seems to be for a
1 page report, not mutiple. Each page is separated with a carrage return
character.
The data on the report is mutiple lines per record. IE: first row will have
cost code, cost code title, to date %, period %, etc. next row will have
quantity information, next row cost information, etc. There are "key" words
that repeate so I can use them as indicators as to where I am in the file.
The data is fixed width, so values are always in the same spot... The report
uses a trailing "-" for negitive valuse...
Here's a sample from the report:
++++++++++++++++
===============================================================================================================================================================================
: : JOB STATUS TO DATE :
JOB STATUS THIS MONTH : JOB UNIT COSTS :
WORKHOUR COST
:
:-------------------------------------:-----------------------------------:-----------------------------------:-----------------------------------
ACTIVITY : CURRENT : EARNED : : :
EARNED : : : : T-D : THIS : :
TO-DATE : THIS MONTH
CODE : BUDGET : BUDGET : ACTUAL : VARIANCE :
BUDGET : ACTUAL : VARIANCE : BUDGET : ACTUAL : MONTH
:BUDGET:------------:---------------
: : : : :
: : : : : ACTUAL : : ST
:TOTAL : ST : TOTA
===============================================================================================================================================================================
18221 F FLOOR SEALER 0.0 % COMP
0.0 % COMP 32.30
0.00 0.00
QUANTITY (SF ) 19,845.000 .000
.000
LABOR 6,396
0.322 0.000 0.000
HOURS 198
0.010 0.000 0.000 P-F 0.00
TO-DATE
P-F 0.00 MONTHLY
++++++++++++++++++++++++++
What's the best way to tackle this project? Can a text file be "read" by
access? Can I copy a "record" out of the report to a string variable and
then use Left, Right, Mid functions to deturmin what type of record it is,
then go to the next record?
off of the report and put it into a table. The first 14 rows of each page
are header stuff. I'll need to read some information off of the first header
(report name, job number, reprort dates) to verify that the file being
imported is the proper file, from that point forward, the header information
can be "dumped". I found a post from John Nurick back in 8/15/2005 with a
function to dump the first x rows from a text file, but it seems to be for a
1 page report, not mutiple. Each page is separated with a carrage return
character.
The data on the report is mutiple lines per record. IE: first row will have
cost code, cost code title, to date %, period %, etc. next row will have
quantity information, next row cost information, etc. There are "key" words
that repeate so I can use them as indicators as to where I am in the file.
The data is fixed width, so values are always in the same spot... The report
uses a trailing "-" for negitive valuse...
Here's a sample from the report:
++++++++++++++++
===============================================================================================================================================================================
: : JOB STATUS TO DATE :
JOB STATUS THIS MONTH : JOB UNIT COSTS :
WORKHOUR COST
:
:-------------------------------------:-----------------------------------:-----------------------------------:-----------------------------------
ACTIVITY : CURRENT : EARNED : : :
EARNED : : : : T-D : THIS : :
TO-DATE : THIS MONTH
CODE : BUDGET : BUDGET : ACTUAL : VARIANCE :
BUDGET : ACTUAL : VARIANCE : BUDGET : ACTUAL : MONTH
:BUDGET:------------:---------------
: : : : :
: : : : : ACTUAL : : ST
:TOTAL : ST : TOTA
===============================================================================================================================================================================
18221 F FLOOR SEALER 0.0 % COMP
0.0 % COMP 32.30
0.00 0.00
QUANTITY (SF ) 19,845.000 .000
.000
LABOR 6,396
0.322 0.000 0.000
HOURS 198
0.010 0.000 0.000 P-F 0.00
TO-DATE
P-F 0.00 MONTHLY
++++++++++++++++++++++++++
What's the best way to tackle this project? Can a text file be "read" by
access? Can I copy a "record" out of the report to a string variable and
then use Left, Right, Mid functions to deturmin what type of record it is,
then go to the next record?