STOREID EAN QTY17 QTY198 QTY83

sorry the message appeared to put the third QTY on the line below -
basically i need a line for each entry that i can pivot.

Brian




"Brian" wrote in message
...
I have a text file which i recieve that I wish to import into excel.

basically its an inventory report that will show me what productrs are at
which location. Also there are 3 qty values (stock, transit, backorder)
for each product.

basically i would like to get the following when i import -

(LOC)
STOREID EAN QTY17 QTY198
QTY83
0000000000000 0000000000000 0 0 0

I get a LIN+ then the EAN number followed by all the LOC and qtys.
I think if a LOC has a value in any of the Qtys it will even return Zero
for the other Qtys.

Tom Ogilvy provided me with a fantastic script for my last EDI file,
however that one only had one Qty so not sure how easy that would be to
modify, i have fiddled but i am afraid beyond my skills. I pivot the data
with lookups and produce a pretty readable report for the user (well, damn
easier than the text file below!)

Anyone interested look at the same titled message (less INVRPT), Note
these are EDI flat file exports so i would image would be of interest to
many users.

Please HELP!

Brian


Sample Text.

Note: I cut ou a massive chunk in the middle but kept the format...

UNB+UNOA:3+5023949000004:14+5014838000001+060205:0 513+436+ETRADING+INVRPT'UNH+23+INVRPT:D:96A:UN:EAN 008'BGM+35+00000009+9'DTM+366:20060204:102'NAD+BY+ 5023949000004::9'NAD+SU+5014838000001::9'LIN+1++21 298776:EN'QTY+17:1'LOC+14+5023949771634::9'QTY+198 :0'LOC
+14+5023949771634::9'QTY+83:0'LOC+14+5023949771634 ::9'LIN+2++21326806:EN'QTY+17:3'LOC+14+50239490578 95::9'QTY+198:0'LOC+14+5023949057895::9'QTY+83:0'L OC+14+5023949057895::9'QTY+17:4'LOC+14+50239491367 74::9'QTY+198:0'LOC+14+5023949136774::9'QTY+83:0'L OC+14+
5023949136774::9'QTY+17:2'LOC+14+5023949182579::9' QTY+198:0'LOC+14+5023949182579::9'QTY+83:0'LOC+14+ 5023949182579::9'QTY+17:5'LOC+14+5023949223920::9' QTY+198:0'LOC+14+5023949223920::9'QTY+83:0'LOC+14+ 5023949223920::9'QTY+17:4'LOC+14+5023949248730::9' QTY+19
8:0'LOC+14+5023949248730::9'QTY+83:0'LOC+14+502394 9248730::9'QTY+17:3'LOC+14+5023949294535::9'QTY+19 8:0'LOC+14+5023949294535::9'QTY+83:0'LOC+14+502394 9294535::9'QTY+17:3'LOC+14+5023949319342::9'QTY+19 8:0'LOC+14+5023949319342::9'QTY+83:0'LOC+14+502394 931934
2::9'QTY+17:4'LOC+14+5023949373414::9'QTY+198:0'LO C+14+5023949373414::9'QTY+83:0'LOC+14+502394937341 4::9'QTY+17:3'LOC+14+5023949374976::9'QTY+198:0'LO C+14+5023949374976::9'QTY+83:0'LOC+14+502394937497 6::9'QTY+17:1'LOC+14+5023949414768::9'QTY+198:0'LO C+14+5
023949414768::9'QTY+83:0'LOC+14+5023949414768::9'Q TY+17:3'LOC+14+5023949423933::9'QTY+198:0'LOC+14+5 023949423933::9'QTY+83:0'LOC+14+5023949423933::9'Q TY+17:3'LOC+14+5023949584122::9'QTY+198:0'LOC+14+5 023949584122::9'QTY+83:0'LOC+14+5023949584122::9'Q TY+17:
3'LOC+14+5023949670870::9'QTY+198:0'LOC+14+5023949 670870::9'QTY+83:0'LOC+14+5023949670870::9'QTY+17: 3'LOC+14+5023949692755::9'QTY+198:0'LOC+14+5023949 692755::9'QTY+83:0'LOC+14+5023949692755::9'QTY+17: 8'LOC+14+5023949771634::9'QTY+198:0'LOC+14+5023949 771634
::9'QTY+83:0'LOC+14+5023949771634::9'QTY+17:7'LOC+ 18+5023949825700::9'QTY+17:4'LOC+14+5023949867056: :9'QTY+198:0'LOC+14+5023949867056::9'QTY+83:0'LOC+ 14+5023949867056::9'QTY+17:5'LOC+14+5023949929392: :9'QTY+198:0'LOC+14+5023949929392::9'QTY+83:0'LOC+ 14+502
3949929392::9'LIN+3++21348914:EN'QTY+17:1'LOC+14+5 023949248730::9'QTY+198:0'LOC+14+5023949248730::9' QTY+83:0'LOC+14+5023949248730::9'QTY+17:2'LOC+14+5 023949319342::9'QTY+198:0'LOC+14+5023949319342::9' QTY+83:0'LOC+14+5023949319342::9'LIN+4++21381416:E N'QTY+
17:1'LOC+14+5023949182579::9'QTY+198:0'LOC+14+5023 949182579::9'QTY+83:0'LOC+14+5023949182579::9'LIN+ 5++21481499:EN'QTY+17:1'LOC+14+5023949057895::9'QT Y+198:0'LOC+14+5023949057895::9'QTY+83:0'LOC+14+50 23949057895::9'QTY+17:1'LOC+14+5023949373414::9'QT Y+198:
0'LOC+14+5023949373414::9'QTY+83:0'LOC+14+50239493 73414::9'LIN+6++21481505:EN'QTY+17:1'LOC+14+502394 9057895::9'QTY+198:0'LOC+14+5023949057895::9'QTY+8 3:0'LOC+14+5023949057895::9'QTY+17:2'LOC+14+502394 9771634::9'QTY+198:0'LOC+14+5023949771634::9'QTY+8 3:0'LO
C+14+5023949771634::9'LIN+7++21481512:EN'QTY+17:1' LOC+14+5023949057895::9'QTY+198:0'LOC+14+502394905 7895::9'QTY+83:0'LOC+14+5023949057895::9'QTY+17:3' LOC+14+5023949145040::9'QTY+198:0'LOC+14+502394914 5040::9'QTY+83:0'LOC+14+5023949145040::9'QTY+17:1' LOC+14
+5023949670870::9'QTY+198:0'LOC+14+5023949670870:: 9'QTY+83:0'LOC+14+5023949670870::9'QTY+17:1'LOC+14 +5023949701028::9'QTY+198:0'LOC+14+5023949701028:: 9'QTY+83:0'LOC+14+5023949701028::9'QTY+17:1'LOC+14 +5023949771634::9'QTY+198:0'LOC+14+5023949771634:: 9'QTY+
83:0'LOC+14+5023949771634::9'QTY+17:1'LOC+14+50239 49832131::9'QTY+198:0'LOC+14+5023949832131::9'QTY+ 83:0'LOC+14+5023949832131::9'LIN+8++21481529:EN'QT Y+17:2'LOC+14+5023949223920::9'QTY+198:0'LOC+14+50 23949223920::9'QTY+83:0'LOC+14+5023949223920::9'QT Y+17:1
'LOC+14+5023949374976::9'QTY+198:0'LOC+14+50239493 74976::9'QTY+83:0'LOC+14+5023949374976::9'QTY+17:1 'LOC+14+5023949832131::9'QTY+198:0'LOC+14+50239498 32131::9'QTY+83:0'LOC+14+5023949832131::9'LIN+9++2 1493393:EN'QTY+17:1'LOC+14+5023949049625::9'QTY+19 8:0'LO
C+14+5023949049625::9'QTY+83:0'LOC+14+502394904962 5::9'LIN+10++5014838064023:EN'QTY+17:5'LOC+14+5023 949049625::9'QTY+198:0'LOC+14+5023949049625::9'QTY +83:0'LOC+14+5023949049625::9'QTY+17:5'LOC+14+5023 949057895::9'QTY+198:0'LOC+14+5023949057895::9'QTY +83:0'
LOC+14+5023949057895::9'QTY+17:5'LOC+14+5023949136 774::9'QTY+198:0'LOC+14+5023949136774::9'QTY+83:0' LOC+14+5023949136774::9'QTY+17:2'LOC+14+5023949145 040::9'QTY+198:1'LOC+14+5023949145040::9'QTY+83:0' LOC+14+5023949145040::9'QTY+17:11'LOC+14+502394918 2579::
9'QTY+198:0'LOC+14+5023949182579::9'QTY+83:0'LOC+1 4+5023949182579::9'QTY+17:2'LOC+14+5023949223920:: 9'QTY+198:0'LOC+14+5023949223920::9'QTY+83:0'LOC+1 4+5023949223920::9'QTY+17:5'LOC+14+5023949232199:: 9'QTY+198:0'LOC+14+5023949232199::9'QTY+83:0'LOC+1 4+5023
949232199::9'QTY+17:5'LOC+14+5023949248730::9'QTY+ 198:0'LOC+14+5023949248730::9'QTY+83:0'LOC+14+5023 949248730::9'QTY+17:11'LOC+14+5023949294535::9'QTY +198:2'LOC+14+5023949294535::9'QTY+83:0'LOC+14+502 3949294535::9'QTY+17:5'LOC+14+5023949319342::9'QTY +198:0
'LOC+14+5023949319342::9'QTY+83:0'LOC+14+502394931 9342::9'QTY+17:4'LOC+14+5023949327619::9'QTY+198:0 'LOC+14+5023949327619::9'QTY+83:0'LOC+14+502394932 7619::9'QTY+17:5'LOC+14+5023949373414::9'QTY+198:0 'LOC+14+5023949373414::9'QTY+83:0'LOC+14+502394937 3414::
9'QTY+17:5'LOC+14+5023949374976::9'QTY+198:0'LOC+1 4+5023949374976::9'QTY+83:0'LOC+14+5023949374976:: 9'QTY+17:3'LOC+14+5023949414768::9'QTY+198:0'LOC+1 4+5023949414768::9'QTY+83:0'LOC+14+5023949414768:: 9'QTY+17:5'LOC+14+5023949423933::9'QTY+198:0'LOC+1 4+5023
949423933::9'QTY+83:0'LOC+14+5023949423933::9'QTY+ 17:4'LOC+14+5023949510183::9'QTY+198:0'LOC+14+5023 949510183::9'QTY+83:0'LOC+14+5023949510183::9'QTY+ 17:5'LOC+14+5023949511753::9'QTY+198:0'LOC+14+5023 949511753::9'QTY+83:0'LOC+14+5023949511753::9'QTY+ 17:3'L
OC+14+5023949584122::9'QTY+198:0'LOC+14+5023949584 122::9'QTY+83:0'LOC+14+5023949584122::9'QTY+17:5'L OC+14+5023949597339::9'QTY+198:0'LOC+14+5023949597 339::9'QTY+83:0'LOC+14+5023949597339::9'QTY+17:5'L OC+14+5023949670870::9'QTY+198:0'LOC+14+5023949670 870::9
'QTY+83:0'LOC+14+5023949670870::9'QTY+17:5'LOC+14+ 5023949692755::9'QTY+198:0'LOC+14+5023949692755::9 'QTY+83:0'LOC+14+5023949692755::9'QTY+17:5'LOC+14+ 5023949701028::9'QTY+198:0'LOC+14+5023949701028::9 'QTY+83:0'LOC+14+5023949701028::9'QTY+17:6'LOC+14+ 502394
9771634::9'QTY+198:0'LOC+14+5023949771634::9'QTY+8 3:0'LOC+14+5023949771634::9'QTY+17:5'LOC+14+502394 9832131::9'QTY+198:0'LOC+14+5023949832131::9'QTY+8 3:0'LOC+14+5023949832131::9'QTY+17:5'LOC+14+502394 9833970::9'QTY+198:0'LOC+14+5023949833970::9'QTY+8 3:0'LO
C+14+5023949833970::9'QTY+17:6'LOC+14+502394984330 4::9'QTY+198:0'LOC+14+5023949843304::9'QTY+83:0'LO C+14+5023949843304::9'QTY+17:1'LOC+14+502394986705 6::9'QTY+198:2'LOC+14+5023949867056::9'QTY+83:0'LO C+14+5023949867056::9'QTY+17:5'LOC+14+502394991285 9::9'Q
TY+198:0'LOC+14+5023949912859::9'QTY+83:0'LOC+14+5 023949912859::9'QTY+17:4'LOC+14+5023949929392::9'Q TY+198:0'LOC+14+5023949929392::9'QTY+83:0'LOC+14+5 023949929392::9'QTY+17:5'LOC+14+5023949955601::9'Q TY+198:0'LOC+14+5023949955601::9'QTY+83:0'LOC+14+5 023949
955601::9'QTY+17:5'LOC+14+5023949962472::9'QTY+198 :1'LOC+14+5023949962472::9'QTY+83:0'LOC+14+5023949 962472::9'LIN+11++5014838066317:EN'QTY+17:2'LOC+14 +5023949057895::9'QTY+198:0'LOC+14+5023949057895:: 9'QTY+83:0'LOC+14+5023949057895::9'QTY+17:1'LOC+14 +50239
49136774::9'QTY+198:0'LOC+14+5023949136774::9'QTY+ 83:0'LOC+14+5023949136774::9'QTY+17:1'LOC+14+50239 49182579::9'QTY+198:0'LOC+14+5023949182579::9'QTY+ 83:0'LOC+14+5023949182579::9'QTY+17:1'LOC+14+50239 49223920::9'QTY+198:0'LOC+14+5023949223920::9'QTY+ 83:0'L
OC+14+5023949223920::9'QTY+17:3'LOC+14+50239492487 30::9'QTY+198:0'LOC+14+5023949248730::9'QTY+83:0'L OC+14+5023949248730::9'QTY+17:3'LOC+14+50239493734 14::9'QTY+198:0'LOC+14+5023949373414::9'QTY+83:0'L OC+14+5023949373414::9'QTY+17:1'LOC+14+50239493749 76::9'
QTY+198:0'LOC+14+5023949374976::9'QTY+83:0'LOC+14+ 5023949374976::9'QTY+17:1'LOC+14+5023949423933::9' QTY+198:0'LOC+14+5023949423933::9'QTY+83:0'LOC+14+ 5023949423933::9'QTY+17:3'LOC+14+5023949511753::9' QTY+198:0'LOC+14+5023949511753::9'QTY+83:0'LOC+14+ 502394
9511753::9'QTY+17:2'LOC+14+5023949584122::9'QTY+19 8:0'LOC+14+5023949584122::9'QTY+83:0'LOC+14+502394 9584122::9'QTY+17:1'LOC+14+5023949597339::9'QTY+19 8:0'LOC+14+5023949597339::9'QTY+83:0'LOC+14+502394 9597339::9'QTY+17:4'LOC+14+5023949764661::9'QTY+19 8:0'LO
C+14+5023949764661::9'QTY+83:0'LOC+14+502394976466 1::9'QTY+17:3'LOC+14+5023949771634::9'QTY+198:0'LO C+14+5023949771634::9'QTY+83:0'LOC+14+502394977163 4::9'QTY+17:1'LOC+14+5023949833970::9'QTY+198:0'LO C+14+5023949833970::9'QTY+83:0'LOC+14+502394983397 0::9'Q
TY+17:1'LOC+14+5023949867056::9'QTY+198:0'LOC+14+5 023949867056::9'QTY+83:0'LOC+14+5023949867056::9'Q TY+17:1'LOC+14+5023949929392::9'QTY+198:0'LOC+14+5 023949929392::9'QTY+83:0'LOC+14+5023949929392::9'Q TY+17:1'LOC+14+5023949962472::9'QTY+198:0'LOC+14+5 023949
962472::9'QTY+83:0'LOC+14+5023949962472::9'LIN+129 ++5014838370384:EN'QTY+17:1'LOC+14+5023949182579:: 9'QTY+198:0'LOC+14+5023949182579::9'QTY+83:0'LOC+1 4+5023949182579::9'QTY+17:2'LOC+14+5023949670870:: 9'QTY+198:0'LOC+14+5023949670870::9'QTY+83:1'LOC+1 4+5023949670870::9'QTY+17:2'LOC+1
4+5023949867056::9'QTY+198:0'LOC+14+5023949867056: :9'QTY+83:0'LOC+14+5023949867056::9'LIN+130++50148 38370414:EN'QTY+17:2'LOC+14+5023949145040::9'QTY+1 98:0'LOC+14+5023949145040::9'QTY+83:0'LOC+14+50239 49145040::9'QTY+17:1'LOC+14+5023949327619::9'QTY+1 98:0'L
OC+14+5023949327619::9'QTY+83:0'LOC+14+50239493276 19::9'QTY+17:1'LOC+14+5023949584122::9'QTY+198:0'L OC+14+5023949584122::9'QTY+83:0'LOC+14+50239495841 22::9'QTY+17:2'LOC+14+5023949670870::9'QTY+198:0'L OC+14+5023949670870::9'QTY+83:0'LOC+14+50239496708 70::9'
QTY+17:1'LOC+14+5023949929392::9'QTY+198:0'LOC+14+ 5023949929392::9'QTY+83:0'LOC+14+5023949929392::9' LIN+131++5014838370438:EN'QTY+17:0'LOC+14+50239493 19342::9'QTY+198:0'LOC+14+5023949319342::9'QTY+83: 2'LOC+14+5023949319342::9'UNT+15237+23'UNZ+1+436'