Parsing data into something usable.

**crownedzero** · 07-16-2009, 08:51 AM

I've been asked to *attempt* to import a text file and I don't even know where to begin.

Code:

 
9098459 STD  C199      09/15/200907/15/200909/15/2009 
 DIC2210                CHILD DIST FASH W/HARN STRP   PRODUCT LINE: IDIC     U/M: PAIR   WHSE: 000
                                D          1                3          0          0          0          0          3          0   *F
                                D          3                1          0          0          0          0          1          0   *F
                                                      --------   --------   --------   --------   --------   --------   --------
                              ITEM DIC2210 TOTAL:           4          0          0          0          0          4          0    F
 DIC2212                CHILD PINK FASH W/HARN STRP   PRODUCT LINE: IDIC     U/M: PAIR   WHSE: 000
                                D          12               1          0          0          0          0          1          0   *F
                                D          13               2          0          0          0          0          2          0   *F
                                D          2                1          0          0          0          0          1          0   *F
                                                      --------   --------   --------   --------   --------   --------   --------
                              ITEM DIC2212 TOTAL:           4          0          0          0          0          4          0    F
 DIC3200                YOUTH BLK FASH W/HARN STRP    PRODUCT LINE: IDIC     U/M: PAIR   WHSE: 000
                                D          4                2          0          0          0          0          2          0   *F
                                D          6                2          0          0          0          0          2          0   *F
                                                      --------   --------   --------   --------   --------   --------   --------
                              ITEM DIC3200 TOTAL:           4          0          0          0          0          4          0    F
 DIC3210                YOUTH DIST FASH W/HARN STRP   PRODUCT LINE: IDIC     U/M: PAIR   WHSE: 000
                                D          6                2          0          0          0          0          2          0   *F
 LC2403                 CHL  BRO CH/CAMO  COWBOY      PRODUCT LINE: DCC      U/M: PAIR   WHSE: 000
                                D          13               3          0          0          0          0          3          0   *F
                                                      --------   --------   --------   --------   --------   --------   --------
                             ORDER 9098459 TOTAL:          17          0          0          0          0         17          0    F
9098460 STD  C199      09/15/200907/15/200909/15/2009 
 DIC3210                YOUTH DIST FASH W/HARN STRP   PRODUCT LINE: IDIC     U/M: PAIR   WHSE: 000
                                D          4                1          0          0          0          0          1          0   *F
                                D          5                1          0          0          0          0          1          0   *F
                                                      --------   --------   --------   --------   --------   --------   --------
                              ITEM DIC3210 TOTAL:           2          0          0          0          0          2          0    F
 DIC2210                CHILD DIST FASH W/HARN STRP   PRODUCT LINE: IDIC     U/M: PAIR   WHSE: 000
                                D          13               2          0          0          0          0          2          0   *F
                                D          1                2          0          0          0          0          2          0   *F
                                D          3                2          0          0          0          0          2          0   *F
                                                      --------   --------   --------   --------   --------   --------   --------
                              ITEM DIC2210 TOTAL:           6          0          0          0          0          6          0    F
                                                      --------   --------   --------   --------   --------   --------   --------
                             ORDER 9098460 TOTAL:           8          0          0          0          0          8          0    F
9098461 STD  C199      09/15/200907/15/200909/15/2009 
 LC2103                 CHILD BLK/WH SNKE PRT         PRODUCT LINE: DCC      U/M: PAIR   WHSE: 000
                                D          1                1          0          0          0          0          1          0   *F
                                D          2                1          0          0          0          0          1          0   *F

Whereas the only information I need to glean from this are the order number, in thefirst instance in the upper left hand corner its 9098459, each style per order number i.e. DIC2210, DIC2212, and the size/item ordered info lines. D 1 3 0 etc.

**RuralGuy** · 07-16-2009, 05:36 PM

This can be done but you need to be real specific. *Exactly* what is needed form the DIC2210 section? The two?? lines that begin with the D or do you need the just the total? Will there be 9 fields in each line or will there be more or maybe less. This is obviously a report so it can be treated like fixed length fields.

**RuralGuy** · 07-16-2009, 05:54 PM

BTW, it would helpful if you upload a sample as a .txt file.

**crownedzero** · 07-16-2009, 07:48 PM

Well, to put it as easy as I can without confusing myself I need:

The order number, the item number and the pair information i.e.
D 1 3 0 0 --> to the end of that line.

I'll try and upload both the txt file and an example of what I need from it tomorrow. Thanks again

**crownedzero** · 07-17-2009, 08:57 AM

Here's a test file Ruralguy.

**crownedzero** · 07-17-2009, 09:15 AM

Optimally it would come out like this.

**crownedzero** · 07-17-2009, 09:16 AM

... for real this time.

**RuralGuy** · 07-17-2009, 09:55 AM

There are additional fields on the report:
D 4 2 0 0 0 0 2 0 *F
Are you not interested in those?
9098459 DIC3200 D 4 2 4
Actually I do not see where you got the last 4 in the line I posted.

**crownedzero** · 07-17-2009, 10:23 AM

If you look at the text there is a column labeled short, the total short is comprised of the item number size 1 shortage, item number size 2, etc. If you look at the per order item number they are summed up under the hashed line(in most cases.)

**crownedzero** · 07-17-2009, 10:25 AM

For this particular file I only need the number short of each item per order. As to say for order XYZ I'm short 2 pairs of DIC2210 Size 2D, and 2 pairs of DIC2210 size 3D. And the final column there is to say I am short 4 pairs of DIC2210 for order XYZ.

**RuralGuy** · 07-17-2009, 10:43 AM

You will have to bear with me since I do not know your products or this report.

As to say for order XYZ I'm short 2 pairs of DIC2210 Size 2D, and 2 pairs of DIC2210 size 3D.

I'm sorry but I do not see order XYZ, nor do I see size 2D or 3D. Where should I be looking?

**crownedzero** · 07-17-2009, 11:32 AM

Originally Posted by RuralGuy

There are additional fields on the report:
D 4 2 0 0 0 0 2 0 *F
Are you not interested in those?
9098459 DIC3200 D 4 2 4
Actually I do not see where you got the last 4 in the line I posted.

9098459 is the order number. The "D" to the right of the ItemNum is the width. The 4 following that is the size. In the test data the order number is on the far left. Everything under that number up to the next order number falls under one order(they are also numerical if that helps) The width and the size are split directly under the product description.

Code:

 CHILD DIST FASH W/HARN STRP

which is directly to the right of the ItemNum. I found this pretty confusing as well.

**RuralGuy** · 07-17-2009, 12:10 PM

Everything you describe to me needs to be in relation to the TestData sample and *not* the results you created.

**crownedzero** · 07-17-2009, 12:46 PM

K, it's all there spread out quite a bit.

Order number in the test data is the 7 digit number on the far left starting 909****.

The item number is somewhat in a header row, but everything to the right of it can be discarded.

The following lines here contain the width, size, and shortage but they are somewhat spread out. The first two values indented under the item number line are width and size.

The itemnumber shortage is the second to last "column", not including the "F" at the end.

So it kind of breaks down like this for the first item:

Code:

9098459(OrderNumber)
    DIC2210(ItemNum)
                                         D(width) 3(size)                                       1(1D shortage)
                                         D(width) 1(size)                                       3(3D shortage)
                                                                                                4(Total short)

Everything else is erroneous data.

**RuralGuy** · 07-17-2009, 04:23 PM

It looks like you want a line for each size but the total short for all of the sizes in that item on each line. Why not the short for just that size on that line?

Parsing data into something usable.

Thread Tools

Parsing data into something usable.

Similar Threads

parsing data in access (coding?)

Find data, load data, and save as a new record

Unmatched data entered with data in table

Posting Permissions