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

[Brian]

I have a long text file , some of you may recognise it as a EDI EANCOM 96a
file...

Basically, this file is sent via EDI, but is not imported into our systems.

It is purely used to get sales figures

'LIN+1++9999999932234:EN'QTY+153:1 each line is prefixed with 'LIN+
'LOC+162+9999999949625::9 will give me the location

However the file is one long string, not CSV etc so i have to find a way to
get the imported file to split into rows at each 'LIN+

i have managed to get the text to import, but all in one cell, or split into
the world allocation of cells...

I would like to be able to get the file to dump into a sheet as follows

LOC 9999999949625

LIN STORE QTY 153 QTY 200 QTY 301
1 999999932234 1
2 9999999953277 1
3 9999999956407 1
4 9999999970212 2

Note i also recieve an Inventory File that has a similar format (QTY 200,
301 etc ), so i want to import that at some point to...
Once i get the idea on the best wat to split the file with one QTY i will
look at the bigger one...

As you can see from the text extract below of the file , its not fixed width
of very easy to split....

THIS DONT WORK...
________________________________________
Sub testme2()

Dim FName As String
Dim KeepTheNext As Boolean
Dim FNum As Long
Dim i As Long
Dim sLine As String

FName = "C:\SLSRPT.txt"

FNum = FreeFile

Open FName For Input As FNum
i = 1

KeepTheNext = False
Do While Not EOF(FNum)
Input #FNum, sLine
If InStr(1, sLine, "'LIN+", vbTextCompare) > 0 Then
KeepTheNext = True
Else
If KeepTheNext = True Then
KeepTheNext = False
Cells(i, 1).Value = sLine
' Exit Do
i = i + 1
End If
End If
Loop

End Sub


HELP!!!!!


Thanks for any advice in advance

Brian


________________________________________



SAMPLE OF TEXT....


UNB+UNOA:3+999999990004:14+9999999900001+060205:0508+435+
+SLSRPT'UNH+22+SLSRPT:96A:UN:EAN008'BGM+73E::9+00000014+9'DTM+356:20060129-20060204:718'NAD+FR+9999999900004::9'NAD+SU+9999999900001::9'LOC+162+9999999949625::9'LIN+1++9999999932234:EN'QTY+153:1'LIN+2++9999999953277:EN'QTY+153:-1'LIN+3++9999999956407:EN'QTY+153:1'LIN+4++9999999970212:EN'QTY+153:2'LOC+162+5023949057895::9'LIN+5++21481505:EN'QTY+153:1'LIN+6++9999999923492:EN'QTY+153:-1'LIN+7++9999999929029:EN'QTY+153:2'LIN+8++9999999932418:EN'QTY+153:1'LIN+9++9999999937482:EN'QTY+153:1'LIN+10++9999999949560:EN'QTY+153:-1'LIN+11++9999999953253:EN'QTY+153:2'LIN+12++9999999953888:EN'QTY+153:3'LIN+13++9999999954205:EN'QTY+153:2'LIN+14++9999999954250:EN'QTY+153:1'LIN+15++9999999956407:EN'QTY+153:1'LIN+16++9999999957015:EN'QTY+153:7'LIN+17++9999999957022:EN'QTY+153:2'LIN+18++9999999957473:EN'QTY+153:1'LIN+19++9999999957688:EN'QTY+153:3'LIN+20++9999999957794:EN'QTY+153:2'LIN+21++9999999970496:EN'QTY+153:3'LOC+162+5023949136774::9'LIN+22++21326806:EN'QTY+153:1'LIN+23++9999999923492:EN'QTY+153:2'LIN+24++9999999929029:EN'QTY+153:1'LIN+25++9999999932418:EN'QTY+153:2'LIN+26++9999999953253:EN'QTY+153:1'LIN+27++9999999953260:EN'QTY+153:1'LIN+28++9999999953284:EN'QTY+153:1'LIN+29++9999999953307:EN'QTY+153:1'LIN+30++9999999953338:EN'QTY+153:3'LIN+31++9999999953345:EN'QTY+153:3'LIN+32++9999999953383:EN'QTY+153:-1'LIN+33++9999999953888:EN'QTY+153:7'LIN+34++9999999956391:EN'QTY+153:1'LIN+35++9999999956407:EN'QTY+153:1'LIN+36++9999999957015:EN'QTY+153:2'LIN+37++9999999957022:EN'QTY+153:1'LIN+38++9999999957183:EN'QTY+153:1'LIN+39++9999999957688:EN'QTY+153:2'LIN+40++9999999957695:EN'QTY+153:1'LIN+41++9999999957749:EN'QTY+153:7'LIN+42++9999999957794:EN'QTY+153:10'LIN+43++9999999957879:EN'QTY+153:1'LIN+44++9999999957985:EN'QTY+153:1'LIN+45++9999999958821:EN'QTY+153:-1'LIN+46++9999999961616:EN'QTY+153:3'LIN+47++9999999970199:EN'QTY+153:4'LIN+48++9999999970205:EN'QTY+153:2'LIN+49++9999999970212:EN'QTY+153:5'LIN+50++9999999970649:EN'QTY+153:2'LOC+162+5023949145040::9'LIN+51++5014838064023:EN'QTY+153:3'LIN+52++9999999923492:EN'QTY+153:1'LIN+53++9999999929029:EN'QTY+153:1'LIN+54++9999999932234:EN'QTY+153:3'LIN+55++9999999938557:EN'QTY+153:1'LIN+56++9999999953253:EN'QTY+153:2'LIN+57++9999999953277:EN'QTY+153:2'LIN+58++9999999953284:EN'QTY+153:1'LIN+59++9999999953888:EN'QTY+153:3'LIN+60++9999999954281:EN'QTY+153:1'LIN+61++9999999957015:EN'QTY+153:6'LIN+62++9999999957022:EN'QTY+153:5'LIN+63++9999999957671:EN'QTY+153:2'LIN+64++9999999957794:EN'QTY+153:-1'LIN+65++9999999958821:EN'QTY+153:2'LIN+66++9999999970205:EN'QTY+153:1'LOC+162+5023949182579::9'LIN+67++21326806:EN'QTY+153:1'LIN+68++9999999923492:EN'QTY+153:3'LIN+69++9999999929029:EN'QTY+153:1'LIN+70++9999999929951:EN'QTY+153:-1'LIN+71++9999999932234:EN'QTY+153:2'LIN+72++9999999937482:EN'QTY+153:2'LIN+73++9999999938557:EN'QTY+153:3'LIN+74++9999999943353:EN'QTY+153:1'LIN+75++9999999951419:EN'QTY+153:1'LIN+76++9999999952942:EN'QTY+153:2'LIN+77++9999999953079:EN'QTY+153:6'LIN+78++9999999953147:EN'QTY+153:1'LIN+79++9999999953154:EN'QTY+153:1'LIN+80++9999999953253:EN'QTY+153:1'LIN+81++9999999953260:EN'QTY+153:4'LIN+82++9999999953277:EN'QTY+153:1'LIN+83++9999999953307:EN'QTY+153:1'LIN+84++9999999953338:EN'QTY+153:2'LIN+85++9999999953352:EN'QTY+153:2'LIN+86++9999999953888:EN'QTY+153:-3'LIN+87++9999999954267:EN'QTY+153:3'LIN+88++9999999954328:EN'QTY+153:1'LIN+89++9999999956384:EN'QTY+153:5'LIN+90++9999999956391:EN'QTY+153:9'LIN+91++9999999956407:EN'QTY+153:3'LIN+92++9999999957015:EN'QTY+153:8'LIN+93++9999999957022:EN'QTY+153:4'LIN+94++9999999957251:EN'QTY+153:-2'LIN+95++9999999957473:EN'QTY+153:1'LIN+96++9999999957619:EN'QTY+153:1'LIN+97++9999999957657:EN'QTY+153:-2'LIN+98++9999999957695:EN'QTY+153:1'LIN+99++9999999957732:EN'QTY+153:1'LIN+100++9999999957749:EN'QTY+153:6'LIN+101++9999999957886:EN'QTY+153:2'LIN+102++9999999957985:EN'QTY+153:5'LIN+103++9999999958005:EN'QTY+153:1'LIN+104++9999999958821:EN'QTY+153:8'LIN+105++9999999960657:EN'QTY+153:-1'LIN+106++9999999961616:EN'QTY+153:2'LIN+107++9999999970199:EN'QTY+153:6'LIN+108++9999999970205:EN'QTY+153:5'LIN+109++9999999970557:EN'QTY+153:2'LOC+162+5023949223920::9'LIN+110++5014838064023:EN'QTY+153:3'LIN+111++9999999929029:EN'QTY+153:1'LIN+112++9999999929951:EN'QTY+153:2'LIN+113++9999999929968:EN'QTY+153:-1'LIN+114++9999999938557:EN'QTY+153:1'LIN+115++9999999949560:EN'QTY+153:2'LIN+116++9999999953154:EN'QTY+153:4'LIN+117++9999999953253:EN'QTY+153:3'LIN+118++9999999953284:EN'QTY+153:1'LIN+119++9999999953352:EN'QTY+153:1'LIN+120++9999999953390:EN'QTY+153:1'LIN+121++9999999953888:EN'QTY+153:3'LIN+122++9999999956384:EN'QTY+153:5'LIN+123++9999999956391:EN'QTY+153:14'LIN+124++9999999956407:EN'QTY+153:2'LIN+125++9999999957015:EN'QTY+153:3'LIN+126++9999999957183:EN'QTY+153:3'LIN+127++9999999957251:EN'QTY+153:2'LIN+128++9999999957671:EN'QTY+153:4'LIN+129++9999999957688:EN'QTY+153:2'LIN+130++9999999957701:EN'QTY+153:-1'LIN+131++9999999957794:EN'QTY+153:2'LIN+132++9999999957985:EN'QTY+153:1'LIN+133++9999999961616:EN'QTY+153:7'LIN+134++9999999970199:EN'QTY+153:1'LOC+162+5023949232199::9'LIN+135++9999999923492:EN'QTY+153:1'LIN+136++9999999951419:EN'QTY+153:2'LIN+137++9999999953147:EN'QTY+153:-1'LIN+138++9999999953253:EN'QTY+153:8'LIN+139++9999999953260:EN'QTY+153:3'LIN+140++9999999953277:EN'QTY+153:2'LIN+141++9999999953345:EN'QTY+153:1'LIN+142++9999999953888:EN'QTY+153:2'LIN+143++9999999956384:EN'QTY+153:3'LIN+144++9999999956391:EN'QTY+153:1'LIN+145++9999999956407:EN'QTY+153:1'LIN+146++9999999957015:EN'QTY+153:1'LIN+147++9999999957022:EN'QTY+153:4'LIN+148++9999999957749:EN'QTY+153:3'LIN+149++9999999957909:EN'QTY+153:2'LIN+150++9999999957985:EN'QTY+153:2'LIN+151++9999999970540:EN'QTY+153:2'UNT+1608+22'UNZ+1+435'

 

[Tom Ogilvy]

Here is a start. You would have to tell me what to do with the first line
and what the LOC lines are.

Sub testme2()

Dim FName As String
Dim FNum As Long
Dim l As String
Dim l1 As Variant

FName = "C:\SLSRPT.txt"

FNum = FreeFile

Open FName For Input As FNum
Line Input #FNum, l
l1 = Split(l, "LIN+")
Cells(1, 1).Resize(UBound(l1) - _
LBound(l1) + 1).Value = Application. _
Transpose(l1)
Close #FNum
End Sub

 

[Brian]

Thanks Tom, thats fantastic!

The first line I would basically delete (the information will not be used at
this time)..
Basically the LOC is a separator for the StoreID followed by all the LIN
orders until the next LOC.

I would like to get it so i get the

(STOREID)
Column1
9999999988888

(LINE) (PRODUCTID) (QTY)
Column2 Column3 Column4
1 9999999912345 1
2 9999999923456 2
3 9999999934567 1
4 9999999954321 3

column 1 would be the line, column 2 the ProductID and column 3 the Qty

It would be good if i could separate the lines by LOC so i get


Brian

 

[Tom Ogilvy]

Sub testme2()

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").ClearContents
Columns(3).NumberFormat = _
"0000000000000"
FName = "C:\SLSRPT.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+153:", ",")
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))
Set rng1 = Cells(Rows.Count, 4).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
End Sub

 

[Brian]

That worked perfectly!,. however, what i thought i wanted wasnt quite
right...

If I wanted to get into into a format like:

STOREID EAN QTY153

5023949049625 5014838132234 1
5023949049625 5014838153277 -1
5023949049625 5014838156407 1
5023949049625 5014838170212 2


So i only ended up with 3 columns (ditching the LIN no).
I would also want to automatically add the headings at A1:C1

For a pretty readabl;e report I decided to pivot the data, which works very
nicely for the user, only I had to copy the STOREID along side every EAN
entry.

I was hoping that looking through an example formula would make it much
clearer exactly how it works.... I have been fiddling, but not been
sucessful as yet.

Any chance you could add some comments to your snippet which might help
me...

Thanks for all your help, i have actually got the string into a readable
pivot, just like to automate that last bit, before i look at the next part
of these reports!

Brian

 

[Tom Ogilvy]

Sub testme2()

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").ClearContents
Columns(3).NumberFormat = _
"0000000000000"
FName = "C:\SLSRPT.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+153:", ",")
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))
Set rng1 = Cells(Rows.Count, 4).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:C1").Value = _
Array("STOREID", "EAN", "QTY153")
Columns("A:C").AutoFit
Range("A1").CurrentRegion.Name = "Database"
End Sub

should produce what you describe.

 

[Brian]

Thank you so much Tom, your help is greatly appreciated.
I now have the text imported and a Pivot table which displays the
information to the user, also have lookups to make those code numbers
display as actually useful products and sites....

I have posted another very similar problem for the inventry file i am being
sent...
If you take a look at that one i would be eternally grateful!

I am learning from your example but i am afraid I find these arrays beyond
my vba skills.

Thanks again for your assistance.

Brian