extracting data from reports

March 2, 2009 at 14:05:38
Specs: Windows XP, Pentium 4 3.00GHz
Ok, i've been trying to learn alot about vba and vbs along with batch stemming from work needed at my job. I am trying to tackle something that is far over my head but i'm going to try and figure it out anyway. Any help would be greatly appreciated getting me started.

I several reports like this but for instance, i have one report and in this report i have several reports separated by buyer names. I am needing to extract each report separately into their own word document. what i was thinking, and i think its logically possible but let me know if its not. Search the document for the name sequence and start from X lines above the name and copy the data from X lines above the next name then paste the reports in that manner into individual reports. If theres an easier or better way, i would love to hear it.


See More: extracting data from reports

Report •


#1
March 3, 2009 at 05:17:31
It depends on the format of the original report. If they are in text
format, they can be easily separated if you know how to identify
the separators.

Report •

#2
March 3, 2009 at 20:03:26
The reports are in .prn (print) file format. Is it possible to create a pool of names and if it hits one of those names copy from x lines above that to X lines before the next name it hits in that pool? The catch might be that the name is on each page of the report so I would have to have it copy to the next name. The other thing that might help is that the names are always in the same order but the pages are not always the same length. If they were the same number of pages i already have a script that will split a report into X number of pages. I appreciate any advise you could give.

Report •

#3
March 4, 2009 at 01:06:39
If the .prn files have no embedded printer formatting commands it
is relatively easy to break into separate files.

Report •

Related Solutions

#4
March 4, 2009 at 06:09:53
how would look about doing that then? any ideas?

Report •

#5
March 4, 2009 at 08:13:07
A prn file will almost surely have control chars. If you open it in a text editor it will likely look 'junked up'.


=====================================
If at first you don't succeed, you're about average.

M2


Report •

#6
March 4, 2009 at 08:38:42
The PRN files most definately do not come up in a txt editor as jumbled code. Now the formatting is out of sync meaning the positioning of the characters are not lined up correctly in notepad as compared to monarch persay. But even in notepad the text is all readable its just not printable.

I ran a macro on the prn files and it sets the lines in a printable format and saves the file in rtf format or can do it in .doc also if that helps.


Report •

#7
March 4, 2009 at 09:17:18
Can you paste in the first, let's say, 20 lines here?


=====================================
If at first you don't succeed, you're about average.

M2


Report •

#8
March 4, 2009 at 12:13:58
I hope this gives you an idea. I've replaced some information on the report to anonymize the report data. Also the formatting is incorrect on this but i hope it gives you an idea.

REPORT TITLE company name RUN
REPORT NO. reportname -
SYSTEM DATE-TIME 03/04/09 00.26.27
FORM NAME STD.
DELIVER REPORT TO city, LOCALRPTID______5000 LOCATION 00 div GENERIC

************************************************************************************************************************
REPORT NUMBER xxxxxx - 42 company name RUN DATE 03/03/09
RUN TIME 23:44:10
REVISED TRAFFIC ORDER MANIFEST - PREPAID - BACKHAUL PAGE 1 OF 1
P.O. XXXXXX BACKORDER NOT ALLOWED
SHIP TO DIV BUYER 001 T X name1 MUST CALL 72 HRS IN ADVANCE
SHIP FROM CONTACT CUSTOMER SERVICE 555-555-5555 FOR APPT
VENDOR 00000 vendor name SHIP 00/00/00
S/FROM 00000 US COLD STORAGE/vendor name
address PICK-UP 03/03/09
ARRIVAL 03/04/09
city state zip CARRIER TYPE(S): FRZ
555-555-5555

PICKUP ALLOWANCE: 6.0000 CWT


ROUTE #:____________________ DRIVER: ____________________ MAINTAIN TEMP AT:____________________
TIME IN:____________________ TIME OUT:____________________ PALLETS IN: ____________________ PALLETS OUT:___________________
------------------------------------------------------------------------------------------------------------------------------------
PICK-UP NET ORIGINAL PICK-UP LABEL MANUFACTURER"S ORDER FRT OVRD
BALANCE RCVD ORDER CUBE WEIGHT DESCRIPTION PACK/SIZE PRODUCT CASE UPC NUMBER UNIT FLAG
------------------------------------------------------------------------------------------------------------------------------------
EL PASADO
30 30 18.8 336.60 CHICKEN, STRIP THIGH FJTA SESD 2/5LB 3332517 10807606000 110088 CASES
----- PATUXENT
70 70 40.7 770.00 CHICKEN, DARK & WHT DCD .5" 10LB 7336415 10805963000 462501 CASES
----- PATUXENT
30 30 38.3 870.00 CHICKEN, 8 PC MED BI SKN ON 2D 96/4.5OZ 7336670 10807604000 110086 CASES
----- PATUXENT
50 50 30.8 550.00 CHICKEN, TENDR BRDD JMB FRITR 2/5LB 8337172 10807583000 110070 CASES
----- PIERCE
20 20 24.3 640.00 CHICKEN, WING 1&2JT JMB IQF 6/5LB 8368904 63819769000 19769 CASES
----- PIERCE
40 40 70.8 1240.00 CHICKEN, 8 PC FRYER RAW IQF 28.75LB 8380099 63210027000 110027 CASES
----- PATUXENT
10 10 12.5 300.00 CHICKEN, THIGH BI SKON RAW IQF 96/4.66OZ 8456865 10821689000 025326 CASES
----- PATUXENT
45 45 29.3 585.00 CHICKEN, BRST 4 Z FIL BNLS 48/4OZ 8551764 10822929000 111059 CASES
----- PATUXENT
25 25 19.5 587.50 CHICKEN, DMSTK BI IQF FZN 96 96/3.66OZ 9456856 10821688000 025327 CASES
----- ----- ----- ------ -------
320 320 285.0 5879.10

TOTAL FREIGHT : 352.74 MARKET FREIGHT : 35.25 PICKUP ALLOWANCE : 352.74


REPORT NUMBER xxxxxx - 42 company name RUN DATE 03/03/09
RUN TIME 23:44:14
REVISED TRAFFIC ORDER MANIFEST - FREIGHT BILL - BACKHAUL PAGE 1 OF 1
P.O. 869871 P/U 1909 N. BECKLEY BACKORDER NOT ALLOWED
SHIP TO division BUYER 002 J X name2 city,state
SHIP FROM CONTACT person 555-555-5555 EXT.555
VENDOR 016630 vendodor name SHIP 00/00/00
S/FROM 00000 vendor name
address PICK-UP 03/02/09
P.O.BOX 00000 ARRIVAL 03/05/09
city state 000000000CARRIER TYPE(S): FRZ
555-555-5555

FREIGHT RATE: 10.5000 CWT
MARKET FREIGHT RATE: 10.5000 CWT

ROUTE #:____________________ DRIVER: ____________________ MAINTAIN TEMP AT:____________________
TIME IN:____________________ TIME OUT:____________________ PALLETS IN: ____________________ PALLETS OUT:___________________
------------------------------------------------------------------------------------------------------------------------------------
PICK-UP NET ORIGINAL PICK-UP LABEL MANUFACTURER"S ORDER FRT OVRD
BALANCE RCVD ORDER CUBE WEIGHT DESCRIPTION PACK/SIZE PRODUCT CASE UPC NUMBER UNIT FLAG
------------------------------------------------------------------------------------------------------------------------------------
LONE STAR
16 16 11.2 336.00 HUSH PUPPY, JLP FZN OBLONG RAW 4/5LB 1060607 00000000000 4227 CASES
----- LONE STAR
35 35 33.7 280.00 DANISH, RL CIN ICED FZN 6/9/2OZ 6085328 00000000000 4405 CASES
----- LONE STAR
72 72 50.6 1512.00 HUSH PUPPY, ONION FZN 4/5LB 9060591 00000000000 4213 CASES
----- ----- ----- ------ -------
123 123 95.6 2128.00

TOTAL FREIGHT : 223.44 MARKET FREIGHT : 223.44 PICKUP ALLOWANCE :


Report •

#9
March 4, 2009 at 17:10:47
It apppears to text (ASCII). Good. Now why backup n lines? Is it
for header information?

Report •

#10
March 4, 2009 at 18:54:27
I actually figured it would be easier to get the whole line rather than bits and pieces and to keep format

Report •


Ask Question