Importing data from a Print file.

**focusedonsound** · 12-23-2008, 12:12 AM

I have a bit of what I think is a unique issue and I have been browsing the interwebs for the past few days in search of an answer however I have come up with nothing. I am hoping that someone hear can shed some light.

My reports @ work come from a database however I only have access to them in 2 formats. 1. Printed on Green bar. 2. Printed to a .txt file.

I need the data import the data into excel but have yet to find a solution. As you will see the data is very nicely formatted however it contains no delimiters making this quite the challenge. Any help or insight anyone can provide is much appreciated.

Thanks
Focusedonsound

ITEM NUMBER: 97896BERL      (11658523)                                                  AS OF FRI, DEC 19, 2008,  8:25 AM <ITEMSALE>
 WAREHOUSE LOCAL
 STATUS: CANCELED          DESC:  JACKET MNS                                      PRODUCT CLASS:           CATEGORY:     0L00
  ALT #:           VEND MODEL #:                            PURCH PACK SIZE:   0                 PACK:       1     WEIGHT:    1.740

              NOMINAL LT: 3  NOMINAL RP:       4  CALC RP:       4  TOTAL HAND:       0  FUTURE ORDERS:       0

                                                                                   TOTAL      1 WK     2 WKS     3 WKS OVER 3WKS
                       LOC CAP:       0     LOC RESTOCK:         0
     TRUE BO:       0  PRIMARY:       0                           BACK ORDERS:         0         0         0         0         0
 INDIRECT BO:       0     BULK:       0                           RECENT SALE:         0         0         0         0         0


                                                   S A L E S    H I S T O R Y

     ****************** 08 ***************** ******************** 07 *************** ******************* 06 ****************
       ST #1   ST #2   ST #3 ST #4,5   TOTAL   ST #1   ST #2   ST #3 ST #4,5   TOTAL   ST #1   ST #2   ST #3 ST #4,5   TOTAL
  1        0       0       0       0       0       0       0       0       0       0       0       0       0       0       0
  2        0       0       0       0       0       0       0       0       0       0       0       0       0       0       0
  3        0       0       0       0       0       0       0       0       0       0       0       0       0       0       0
  4        0       0       0       0       0       0       0       0       0       0       0       0       0       0       0
  5        0       0       0       0       0       0       0       0       0       0       0       0       0       0       0
  6        0       0       0       0       0       0       0       0       0       0       0       0       0       0       0
  7        0       0       0       0       0       0       0       0       0       0       0       0       0       0       0
  8        0       0       0       0       0       0       0       0       0       0       0       0       0       0       0
  9        0       0       0       0       0       0       0       0       0       0       0       0       0       0       0
 10        0       0       0       0       0       0       0       0       0       0       0       0       0       0       0
 11        0       0       0       0       0       0       0       0       0       0       0       0       0       0       0
 12        0       0       0       0       0       0       0       0       0       0       0       0       0       0       0
 **        0       0       0       0       0       0       0       0       0       0       0       0       0       0       0


                                                 P U R C H A S E   H I S T O R Y

 VEND  MODEL #        ORDER FROM                     SALESMAN                     TERMS DAYS TERMS % FRGHT  PREPAID ON   FRGHT

 4634-88036BERL       XXXXXXXXXXXXXXXX                                        SEE GI                   FOB FACTORY
   AA**  ATT: CXXX PXXXXX EXT XXXXX                                 CI**  XXXXXXXXXXXXX ext. XXXXX
   GI**  CUSTOMER SERVICE FAX 
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

**JBeaucaire** · 12-23-2008, 12:39 AM

Layout a sample in Excel of what data you're trying to capture and what it should look like. Your sample above isn't very practical since you've stripped out all the data that might help identify what's actually needed here, so you need to mock up your final layout.

Use a text file with more data in it, and fill out your sample with the answers so we can see what goes where.

**focusedonsound** · 12-23-2008, 10:13 AM

JBeaucaire,

I have re-entered additional data. Obviously I would like to say that ALL this data is important to me. However the following data is most important.

1. Item Number
2. Status
3. Total Hand
4. Back Orders (With the weeks layout)
5. Recent Sales (With the weeks layout)
4. All of the Sales History. This is an actual sales record what you are seeing is REAL!

As you can see. Being this is a printed report its one record after the next. So you will see in this example Item Number: 97896BLKL followed by 97896BLKM which then MAY or MAY not be followed by 97896BLKS it can be directly followed by any item. This example just so happens to show the next size.

  P U R C H A S E   H I S T O R Y

 VEND  MODEL #        ORDER FROM                     SALESMAN                     TERMS DAYS TERMS % FRGHT  PREPAID ON   FRGHT

 4634-88036BERXXL     XXXXX XXXX-XXXX XXXXX                                        SEE GI                   FOB FACTORY
   AA**  ATT: JOHN SMITH EXT 8337                                  CI**  (XXX) 555-1212 ext. 6813
   GI**  CUSTOMER SERVICE FAX 555-555-1212
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 DUE DATE   PO NUM      BAL DUE   VENDOR NAME                      QTY ORD   QTY REC   FIRST DAY   DEL   VENDOR COST  LAND COST  STAT

 ITEM NUMBER: 97896BLKL      (11018975)                                                  AS OF FRI, DEC 19, 2008,  8:25 AM <ITEMSALE>
 WAREHOUSE LOCAL
 STATUS: ACTIVE            DESC: XXXXXX JACKET MNS                                      PRODUCT CLASS:           CATEGORY:     0L00
  ALT #:           VEND MODEL #:                            PURCH PACK SIZE:   0                 PACK:       0     WEIGHT:    1.740

              NOMINAL LT: 3  NOMINAL RP:       0  CALC RP:       0  TOTAL HAND:      35  FUTURE ORDERS:       0

                                                                                   TOTAL      1 WK     2 WKS     3 WKS OVER 3WKS
                       LOC CAP:      12     LOC RESTOCK:         3
     TRUE BO:       0  PRIMARY:       5                           BACK ORDERS:         0         0         0         0         0
 INDIRECT BO:       0     BULK:      30                           RECENT SALE:       270        36        59        51        16


                                                   S A L E S    H I S T O R Y

     ****************** 08 ***************** ******************** 07 *************** ******************* 06 ****************
       ST #1   ST #2   ST #3 ST #4,5   TOTAL   ST #1   ST #2   ST #3 ST #4,5   TOTAL   ST #1   ST #2   ST #3 ST #4,5   TOTAL
  1        3       0       5      19      27       0       0       3       4       7       1       0       3       6      10
  2        1       0       3      10      14       0       0       0       1       1       0       0       0       0       0
  3        0       0       1       7       8       0       0       0       0       0       0       0       0       1       1
  4        0       0       2       2       4       0       0       0       0       0       0       0       0       0       0
  5        0       0       7       1       8       0       0       0       0       0       0       0       1       3       4
  6        0       0       1       1       2       0       0       1       0       1       0       0       3       3       6
  7        0       0       5       2       7       0       0       0       0       0       0       0      23       3      26
  8        0       0       5       4       9       2       0       0       3       5       0       0      12       4      16
  9        0       0       0       0       0       0       0       1       7       8       0       0       3       5       8
 10        1       0       1      14      16       0       0       0      12      12       0       0       7      22      29
 11        0       0      14      18      32       0       0      13      35      48       2       0      13       9      24
 12        2       0     103      36     141       3       0      21      60      84       2       0      24      30      56
 **        7       0     147     114     268       5       0      39     122     166       5       0      89      86     180


                                                 P U R C H A S E   H I S T O R Y

 VEND  MODEL #        ORDER FROM                     SALESMAN                     TERMS DAYS TERMS % FRGHT  PREPAID ON   FRGHT

 4634-A193-002-L      XXXX XXXX-XXXX XXXXX                                        SEE GI                   FOB FACTORY
   AA**  ATT: JOHN SMITH EXT 8337                                  CI**  (866) 715-3223 ext. 6813
   GI**  CUSTOMER SERVICE FAX 920-555-1212
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 DUE DATE   PO NUM      BAL DUE   VENDOR NAME                      QTY ORD   QTY REC   FIRST DAY   DEL   VENDOR COST  LAND COST  STAT

 12/18/08   637725           36   4634-XXXX XXXX-SOFT GOODS            36         0                 0    74.2500       74.2500  OPEN
 12/15/08   637724            0   4634-XXXX XXXX-SOFT GOODS            12        12   12/15/08      0    74.2500       74.2500  COMP
 11/12/08   670636            0   4634-XXXX XXXX-SOFT GOODS            72         0   12/31/49    999    74.2500       74.2500  COMP
 ********     /  /     GENSU      CLOSED BY GENSURE ON 10/15/08 AT 11:54.        0 RECEIVED.
 10/07/08   670635            0   4634-XXXX XXXX-SOFT GOODS            42        42   10/02/08     -5    74.2500       74.2500  COMP
 07/25/08   670634            0   4634-XXXX XXXX-SOFT GOODS            38        38   07/21/08     -4    74.2500       74.2500  COMP
 06/15/08   670549            0   4634-XXXX XXXX-SOFT GOODS            78        78   05/22/08    -24    74.2500       74.2500  COMP
 02/10/08   670099            0   4634-XXXX XXXX-SOFT GOODS            72        72   11/29/07    -73    74.2500       74.2500  COMP
 01/30/08   670103            0   4634-XXXX XXXX-SOFT GOODS            75        75   12/19/07    -42    74.2500       74.2500  COMP
 10/15/07   670098            0   4634-XXXX XXXX-SOFT GOODS           105       113   08/20/07    -56    74.2500       74.2500  COMP
 ITEM NUMBER: 97896BLKM      (11018967)                                                  AS OF FRI, DEC 19, 2008,  8:25 AM <ITEMSALE>
 WAREHOUSE LOCAL
 STATUS: ACTIVE            DESC: XXXXX JACKET MNS                                      PRODUCT CLASS:           CATEGORY:     0L00
  ALT #:           VEND MODEL #:                            PURCH PACK SIZE:   0                 PACK:       0     WEIGHT:    1.490

              NOMINAL LT: 3  NOMINAL RP:       0  CALC RP:       0  TOTAL HAND:      48  FUTURE ORDERS:       0

                                                                                   TOTAL      1 WK     2 WKS     3 WKS OVER 3WKS
                       LOC CAP:      12     LOC RESTOCK:         3
     TRUE BO:       0  PRIMARY:       5                           BACK ORDERS:         0         0         0         0         0
 INDIRECT BO:       0     BULK:      43                           RECENT SALE:       208        33        32        23        14


                                                   S A L E S    H I S T O R Y

     ****************** 08 ***************** ******************** 07 *************** ******************* 06 ****************
       ST #1   ST #2   ST #3 ST #4,5   TOTAL   ST #1   ST #2   ST #3 ST #4,5   TOTAL   ST #1   ST #2   ST #3 ST #4,5   TOTAL
  1        1       0       4      16      21       0       0       1       0       1       0       0       0       5       5
  2        1       0       1      13      15       0       0       0       0       0       0       0       0       1       1
  3        0       0       1       6       7       0       0       0       0       0       0       0       1       4       5
  4        0       0       0       1       1       0       0       0       0       0       0       0       1       0       1
  5        0       0       7       1       8       0       0       0       0       0       0       0       0       3       3
  6        0       0       4       4       8       0       0       0       0       0       0       0       3       1       4
  7        0       0       3       2       5       0       0       0       0       0       0       0      22       3      25
  8        0       0       5       3       8       0       0       0       0       0       0       0      14       2      16
  9        0       0       0       6       6       1       0       0       8       9       1       0       2       4       7
 10        1       0       6      10      17       1       0       3       7      11       1       0       6       8      15
 11        0       0      12      14      26       1       0       8      30      39       1       0       4      18      23
 12        0       2      61      22      85       3       0      26      40      69       0       0      10      13      23
 **        3       2     104      98     207       6       0      38      85     129       3       0      63      62     128