Importing Long String - String Manipulation (INVRPT) (EDI EANCOM 96a)

[Brian]

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:0513+436+ETRADING+INVRPT'UNH+23+INVRPT:96A:UN:EAN008'BGM+35+00000009+9'DTM+366:20060204:102'NAD+BY+5023949000004::9'NAD+SU+5014838000001::9'LIN+1++21298776: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+5023949057895::9'QTY+198:0'LOC+14+5023949057895::9'QTY+83:0'LOC+14+5023949057895::9'QTY+17:4'LOC+14+5023949136774::9'QTY+198:0'LOC+14+5023949136774::9'QTY+83:0'LOC+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+5023949248730::9'QTY+17:3'LOC+14+5023949294535::9'QTY+198:0'LOC+14+5023949294535::9'QTY+83:0'LOC+14+5023949294535::9'QTY+17:3'LOC+14+5023949319342::9'QTY+198:0'LOC+14+5023949319342::9'QTY+83:0'LOC+14+502394931934
2::9'QTY+17:4'LOC+14+5023949373414::9'QTY+198:0'LOC+14+5023949373414::9'QTY+83:0'LOC+14+5023949373414::9'QTY+17:3'LOC+14+5023949374976::9'QTY+198:0'LOC+14+5023949374976::9'QTY+83:0'LOC+14+5023949374976::9'QTY+17:1'LOC+14+5023949414768::9'QTY+198:0'LOC+14+5
023949414768::9'QTY+83:0'LOC+14+5023949414768::9'QTY+17:3'LOC+14+5023949423933::9'QTY+198:0'LOC+14+5023949423933::9'QTY+83:0'LOC+14+5023949423933::9'QTY+17:3'LOC+14+5023949584122::9'QTY+198:0'LOC+14+5023949584122::9'QTY+83:0'LOC+14+5023949584122::9'QTY+17:
3'LOC+14+5023949670870::9'QTY+198:0'LOC+14+5023949670870::9'QTY+83:0'LOC+14+5023949670870::9'QTY+17:3'LOC+14+5023949692755::9'QTY+198:0'LOC+14+5023949692755::9'QTY+83:0'LOC+14+5023949692755::9'QTY+17:8'LOC+14+5023949771634::9'QTY+198:0'LOC+14+5023949771634
::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+5023949248730::9'QTY+198:0'LOC+14+5023949248730::9'QTY+83:0'LOC+14+5023949248730::9'QTY+17:2'LOC+14+5023949319342::9'QTY+198:0'LOC+14+5023949319342::9'QTY+83:0'LOC+14+5023949319342::9'LIN+4++21381416:EN'QTY+
17:1'LOC+14+5023949182579::9'QTY+198:0'LOC+14+5023949182579::9'QTY+83:0'LOC+14+5023949182579::9'LIN+5++21481499:EN'QTY+17:1'LOC+14+5023949057895::9'QTY+198:0'LOC+14+5023949057895::9'QTY+83:0'LOC+14+5023949057895::9'QTY+17:1'LOC+14+5023949373414::9'QTY+198:
0'LOC+14+5023949373414::9'QTY+83:0'LOC+14+5023949373414::9'LIN+6++21481505:EN'QTY+17:1'LOC+14+5023949057895::9'QTY+198:0'LOC+14+5023949057895::9'QTY+83:0'LOC+14+5023949057895::9'QTY+17:2'LOC+14+5023949771634::9'QTY+198:0'LOC+14+5023949771634::9'QTY+83:0'LO
C+14+5023949771634::9'LIN+7++21481512:EN'QTY+17:1'LOC+14+5023949057895::9'QTY+198:0'LOC+14+5023949057895::9'QTY+83:0'LOC+14+5023949057895::9'QTY+17:3'LOC+14+5023949145040::9'QTY+198:0'LOC+14+5023949145040::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+5023949832131::9'QTY+198:0'LOC+14+5023949832131::9'QTY+83:0'LOC+14+5023949832131::9'LIN+8++21481529:EN'QTY+17:2'LOC+14+5023949223920::9'QTY+198:0'LOC+14+5023949223920::9'QTY+83:0'LOC+14+5023949223920::9'QTY+17:1
'LOC+14+5023949374976::9'QTY+198:0'LOC+14+5023949374976::9'QTY+83:0'LOC+14+5023949374976::9'QTY+17:1'LOC+14+5023949832131::9'QTY+198:0'LOC+14+5023949832131::9'QTY+83:0'LOC+14+5023949832131::9'LIN+9++21493393:EN'QTY+17:1'LOC+14+5023949049625::9'QTY+198:0'LO
C+14+5023949049625::9'QTY+83:0'LOC+14+5023949049625::9'LIN+10++5014838064023:EN'QTY+17:5'LOC+14+5023949049625::9'QTY+198:0'LOC+14+5023949049625::9'QTY+83:0'LOC+14+5023949049625::9'QTY+17:5'LOC+14+5023949057895::9'QTY+198:0'LOC+14+5023949057895::9'QTY+83:0'
LOC+14+5023949057895::9'QTY+17:5'LOC+14+5023949136774::9'QTY+198:0'LOC+14+5023949136774::9'QTY+83:0'LOC+14+5023949136774::9'QTY+17:2'LOC+14+5023949145040::9'QTY+198:1'LOC+14+5023949145040::9'QTY+83:0'LOC+14+5023949145040::9'QTY+17:11'LOC+14+5023949182579::
9'QTY+198:0'LOC+14+5023949182579::9'QTY+83:0'LOC+14+5023949182579::9'QTY+17:2'LOC+14+5023949223920::9'QTY+198:0'LOC+14+5023949223920::9'QTY+83:0'LOC+14+5023949223920::9'QTY+17:5'LOC+14+5023949232199::9'QTY+198:0'LOC+14+5023949232199::9'QTY+83:0'LOC+14+5023
949232199::9'QTY+17:5'LOC+14+5023949248730::9'QTY+198:0'LOC+14+5023949248730::9'QTY+83:0'LOC+14+5023949248730::9'QTY+17:11'LOC+14+5023949294535::9'QTY+198:2'LOC+14+5023949294535::9'QTY+83:0'LOC+14+5023949294535::9'QTY+17:5'LOC+14+5023949319342::9'QTY+198:0
'LOC+14+5023949319342::9'QTY+83:0'LOC+14+5023949319342::9'QTY+17:4'LOC+14+5023949327619::9'QTY+198:0'LOC+14+5023949327619::9'QTY+83:0'LOC+14+5023949327619::9'QTY+17:5'LOC+14+5023949373414::9'QTY+198:0'LOC+14+5023949373414::9'QTY+83:0'LOC+14+5023949373414::
9'QTY+17:5'LOC+14+5023949374976::9'QTY+198:0'LOC+14+5023949374976::9'QTY+83:0'LOC+14+5023949374976::9'QTY+17:3'LOC+14+5023949414768::9'QTY+198:0'LOC+14+5023949414768::9'QTY+83:0'LOC+14+5023949414768::9'QTY+17:5'LOC+14+5023949423933::9'QTY+198:0'LOC+14+5023
949423933::9'QTY+83:0'LOC+14+5023949423933::9'QTY+17:4'LOC+14+5023949510183::9'QTY+198:0'LOC+14+5023949510183::9'QTY+83:0'LOC+14+5023949510183::9'QTY+17:5'LOC+14+5023949511753::9'QTY+198:0'LOC+14+5023949511753::9'QTY+83:0'LOC+14+5023949511753::9'QTY+17:3'L
OC+14+5023949584122::9'QTY+198:0'LOC+14+5023949584122::9'QTY+83:0'LOC+14+5023949584122::9'QTY+17:5'LOC+14+5023949597339::9'QTY+198:0'LOC+14+5023949597339::9'QTY+83:0'LOC+14+5023949597339::9'QTY+17:5'LOC+14+5023949670870::9'QTY+198:0'LOC+14+5023949670870::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+83:0'LOC+14+5023949771634::9'QTY+17:5'LOC+14+5023949832131::9'QTY+198:0'LOC+14+5023949832131::9'QTY+83:0'LOC+14+5023949832131::9'QTY+17:5'LOC+14+5023949833970::9'QTY+198:0'LOC+14+5023949833970::9'QTY+83:0'LO
C+14+5023949833970::9'QTY+17:6'LOC+14+5023949843304::9'QTY+198:0'LOC+14+5023949843304::9'QTY+83:0'LOC+14+5023949843304::9'QTY+17:1'LOC+14+5023949867056::9'QTY+198:2'LOC+14+5023949867056::9'QTY+83:0'LOC+14+5023949867056::9'QTY+17:5'LOC+14+5023949912859::9'Q
TY+198:0'LOC+14+5023949912859::9'QTY+83:0'LOC+14+5023949912859::9'QTY+17:4'LOC+14+5023949929392::9'QTY+198:0'LOC+14+5023949929392::9'QTY+83:0'LOC+14+5023949929392::9'QTY+17:5'LOC+14+5023949955601::9'QTY+198:0'LOC+14+5023949955601::9'QTY+83:0'LOC+14+5023949
955601::9'QTY+17:5'LOC+14+5023949962472::9'QTY+198:1'LOC+14+5023949962472::9'QTY+83:0'LOC+14+5023949962472::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+5023949182579::9'QTY+198:0'LOC+14+5023949182579::9'QTY+83:0'LOC+14+5023949182579::9'QTY+17:1'LOC+14+5023949223920::9'QTY+198:0'LOC+14+5023949223920::9'QTY+83:0'L
OC+14+5023949223920::9'QTY+17:3'LOC+14+5023949248730::9'QTY+198:0'LOC+14+5023949248730::9'QTY+83:0'LOC+14+5023949248730::9'QTY+17:3'LOC+14+5023949373414::9'QTY+198:0'LOC+14+5023949373414::9'QTY+83:0'LOC+14+5023949373414::9'QTY+17:1'LOC+14+5023949374976::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+198:0'LOC+14+5023949584122::9'QTY+83:0'LOC+14+5023949584122::9'QTY+17:1'LOC+14+5023949597339::9'QTY+198:0'LOC+14+5023949597339::9'QTY+83:0'LOC+14+5023949597339::9'QTY+17:4'LOC+14+5023949764661::9'QTY+198:0'LO
C+14+5023949764661::9'QTY+83:0'LOC+14+5023949764661::9'QTY+17:3'LOC+14+5023949771634::9'QTY+198:0'LOC+14+5023949771634::9'QTY+83:0'LOC+14+5023949771634::9'QTY+17:1'LOC+14+5023949833970::9'QTY+198:0'LOC+14+5023949833970::9'QTY+83:0'LOC+14+5023949833970::9'Q
TY+17:1'LOC+14+5023949867056::9'QTY+198:0'LOC+14+5023949867056::9'QTY+83:0'LOC+14+5023949867056::9'QTY+17:1'LOC+14+5023949929392::9'QTY+198:0'LOC+14+5023949929392::9'QTY+83:0'LOC+14+5023949929392::9'QTY+17:1'LOC+14+5023949962472::9'QTY+198:0'LOC+14+5023949
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+14+5023949182579::9'QTY+17:2'LOC+14+5023949670870::9'QTY+198:0'LOC+14+5023949670870::9'QTY+83:1'LOC+14+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++5014838370414:EN'QTY+17:2'LOC+14+5023949145040::9'QTY+198:0'LOC+14+5023949145040::9'QTY+83:0'LOC+14+5023949145040::9'QTY+17:1'LOC+14+5023949327619::9'QTY+198:0'L
OC+14+5023949327619::9'QTY+83:0'LOC+14+5023949327619::9'QTY+17:1'LOC+14+5023949584122::9'QTY+198:0'LOC+14+5023949584122::9'QTY+83:0'LOC+14+5023949584122::9'QTY+17:2'LOC+14+5023949670870::9'QTY+198:0'LOC+14+5023949670870::9'QTY+83:0'LOC+14+5023949670870::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+5023949319342::9'QTY+198:0'LOC+14+5023949319342::9'QTY+83:2'LOC+14+5023949319342::9'UNT+15237+23'UNZ+1+436'

 

[Brian]

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]

My effort so far..... (don't work!)

Sub GETINVRPT()

Dim FName As String
Dim FNum As Long
Dim l As String
Dim l1 As Variant
Dim s As String
Dim sChr As String
Dim rng1 As Range, rng As Range
Dim cell As Range, iloc As Long

Columns("A:E").ClearContents
Columns(5).NumberFormat = _
"0000000000000"
FName = "C:\INVRPT.txt"

FNum = FreeFile

Open FName For Input As FNum
Line Input #FNum, s
s = Application.Clean(s)
s = Replace(s, Chr(9), "")
l = s
l = Replace(l, "LIN+", "LIN+,")
l = Replace(l, "LOC", "LIN+LOC")
l = Replace(l, ":EN'QTY+17:", ",")
l = Replace(l, "::9'QTY+198:", ",")
l = Replace(l, "::9'QTY+83:", ",")
l = Replace(l, "'", "")
' l = Replace(l, "+", ",")
l1 = Split(l, "LIN+")
Cells(1, 1).Resize(UBound(l1) - _
LBound(l1) + 1).Value = Application. _
Transpose(l1)
Close #FNum
Rows(1).Delete
Columns(1).Replace "++", ","
Columns(1).TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
FieldInfo:=Array( _
Array(1, 1), _
Array(2, 1), _
Array(3, 1), _
Array(4, 1), _
Array(5, 1), _
Array(6, 1))
Set rng1 = Cells(Rows.Count, 5).End(xlUp)
iloc = InStr(1, rng1, "UN", vbTextCompare)
rng1 = Left(rng1, iloc - 1)
Set rng = Columns(1).SpecialCells(xlConstants)
For Each cell In rng
iloc = InStr(1, cell, "+", vbTextCompare)
iloc = InStr(iloc + 1, cell, "+", vbTextCompare)
cell.Value = "'" & Mid(cell, iloc + 1, 13)
Next
Set rng = Columns(1).SpecialCells(xlBlanks)
rng.Formula = "=" & rng(1).Offset(-1, 0).Address(0, 0)
Set rng = Range(Cells(1, 1), _
Cells(Rows.Count, 1).End(xlUp))
rng.Formula = rng.Value
Set rng = Columns(2).SpecialCells(xlBlanks)
rng.EntireRow.Delete
Columns(2).Delete
Rows(1).Insert
Range("A1:E1").Value = _
Array("STOREID", "EAN", "QTY17", "QTY198", "QTY83")
Columns("A:E").AutoFit
Range("A1").CurrentRegion.Name = "Database"
End Sub

 

[Brian]

l = Replace(l, ":EN'QTY+17:", ",")
l = Replace(l, "::9'QTY+17:", ",")

Also noticed two variations on the QTY17