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

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:D: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++9999999 932234: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+15 3: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:E N'QTY+153:7'LIN+17++9999999957022:EN'QTY+153:2'LIN+18++9999999957473:EN'QTY+153:1'LIN+19++9999999957 688:EN'QTY+153:3'LIN+20++9999999957794:EN'QTY+153:2'LIN+21++9999999970496:EN'QTY+153:3'LOC+162+50239 49136774::9'LIN+22++21326806:EN'QTY+153:1'LIN+23++9999999923492:EN'QTY+153:2'LIN+24++9999999929029:E N'QTY+153:1'LIN+25++9999999932418:EN'QTY+153:2'LIN+26++9999999953253:EN'QTY+153:1'LIN+27++9999999953 260:EN'QTY+153:1'LIN+28++9999999953284:EN'QTY+153:1'LIN+29++9999999953307:EN'QTY+153:1'LIN+30++99999 99953338: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:E N'QTY+153:1'LIN+39++9999999957688:EN'QTY+153:2'LIN+40++9999999957695:EN'QTY+153:1'LIN+41++9999999957 749:EN'QTY+153:7'LIN+42++9999999957794:EN'QTY+153:10'LIN+43++9999999957879:EN'QTY+153:1'LIN+44++9999 999957985: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:E N'QTY+153:2'LIN+57++9999999953277:EN'QTY+153:2'LIN+58++9999999953284:EN'QTY+153:1'LIN+59++9999999953 888:EN'QTY+153:3'LIN+60++9999999954281:EN'QTY+153:1'LIN+61++9999999957015:EN'QTY+153:6'LIN+62++99999 99957022: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:E N'QTY+153:2'LIN+77++9999999953079:EN'QTY+153:6'LIN+78++9999999953147:EN'QTY+153:1'LIN+79++9999999953 154:EN'QTY+153:1'LIN+80++9999999953253:EN'QTY+153:1'LIN+81++9999999953260:EN'QTY+153:4'LIN+82++99999 99953277: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:E N'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'QT Y+153:6'LIN+101++9999999957886:EN'QTY+153:2'LIN+102++9999999957985:EN'QTY+153:5'LIN+103++99999999580 05: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++999999992996 8: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++999999995 3352:EN'QTY+153:1'LIN+120++9999999953390:EN'QTY+153:1'LIN+121++9999999953888:EN'QTY+153:3'LIN+122++9 999999956384:EN'QTY+153:5'LIN+123++9999999956391:EN'QTY+153:14'LIN+124++9999999956407:EN'QTY+153:2'L IN+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++999999995770 1: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++999999995 6384:EN'QTY+153:3'LIN+144++9999999956391:EN'QTY+153:1'LIN+145++9999999956407:EN'QTY+153:1'LIN+146++9 999999957015:EN'QTY+153:1'LIN+147++9999999957022:EN'QTY+153:4'LIN+148++9999999957749:EN'QTY+153:3'LI N+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'

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


--
regards,
Tom Ogilvy

"Brian" <it@user1.com> wrote in message
news:uB0es6%23KGHA.3052@TK2MSFTNGP09.phx.gbl...
> 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:D:96A:UN:EAN008'BGM+73E::9+00000014+9'DTM+356:20060129
-20060204:718'NAD+FR+9999999900004::9'NAD+SU+9999999900001::9'LOC+162+999999
9949625::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'QT
Y+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+1
1++9999999953253:EN'QTY+153:2'LIN+12++9999999953888:EN'QTY+153:3'LIN+13++999
9999954205:EN'QTY+153:2'LIN+14++9999999954250:EN'QTY+153:1'LIN+15++999999995
6407:EN'QTY+153:1'LIN+16++9999999957015:EN'QTY+153:7'LIN+17++9999999957022:E
N'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'QT
Y+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++99
99999953383:EN'QTY+153:-1'LIN+33++9999999953888:EN'QTY+153:7'LIN+34++9999999
956391: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'QT
Y+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++9
999999958821:EN'QTY+153:-1'LIN+46++9999999961616:EN'QTY+153:3'LIN+47++999999
9970199:EN'QTY+153:4'LIN+48++9999999970205:EN'QTY+153:2'LIN+49++999999997021
2:EN'QTY+153:5'LIN+50++9999999970649:EN'QTY+153:2'LOC+162+5023949145040::9'L
IN+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++99999
99938557:EN'QTY+153:1'LIN+56++9999999953253:EN'QTY+153:2'LIN+57++99999999532
77: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+15
3:6'LIN+62++9999999957022:EN'QTY+153:5'LIN+63++9999999957671:EN'QTY+153:2'LI
N+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++99
99999943353:EN'QTY+153:1'LIN+75++9999999951419:EN'QTY+153:1'LIN+76++99999999
52942: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+8
5++9999999953352:EN'QTY+153:2'LIN+86++9999999953888:EN'QTY+153:-3'LIN+87++99
99999954267:EN'QTY+153:3'LIN+88++9999999954328:EN'QTY+153:1'LIN+89++99999999
56384: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++9999
999957985:EN'QTY+153:5'LIN+103++9999999958005:EN'QTY+153:1'LIN+104++99999999
58821:EN'QTY+153:8'LIN+105++9999999960657:EN'QTY+153:-1'LIN+106++99999999616
16:EN'QTY+153:2'LIN+107++9999999970199:EN'QTY+153:6'LIN+108++9999999970205:E
N'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++9
999999938557:EN'QTY+153:1'LIN+115++9999999949560:EN'QTY+153:2'LIN+116++99999
99953154:EN'QTY+153:4'LIN+117++9999999953253:EN'QTY+153:3'LIN+118++999999995
3284: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'L
IN+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+5023
949232199::9'LIN+135++9999999923492:EN'QTY+153:1'LIN+136++9999999951419:EN'Q
TY+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'LI
N+143++9999999956384:EN'QTY+153:3'LIN+144++9999999956391:EN'QTY+153:1'LIN+14
5++9999999956407:EN'QTY+153:1'LIN+146++9999999957015:EN'QTY+153:1'LIN+147++9
999999957022:EN'QTY+153:4'LIN+148++9999999957749:EN'QTY+153:3'LIN+149++99999
99957909:EN'QTY+153:2'LIN+150++9999999957985:EN'QTY+153:2'LIN+151++999999997
0540:EN'QTY+153:2'UNT+1608+22'UNZ+1+435'
>
>

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" <twogilvy@msn.com> wrote in message
news:eP1s1F$KGHA.648@TK2MSFTNGP14.phx.gbl...
> 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
>
>
> --
> regards,
> Tom Ogilvy
>
> "Brian" <it@user1.com> wrote in message
> news:uB0es6%23KGHA.3052@TK2MSFTNGP09.phx.gbl...
>> 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:D:96A:UN:EAN008'BGM+73E::9+00000014+9'DTM+356:20060129
> -20060204:718'NAD+FR+9999999900004::9'NAD+SU+9999999900001::9'LOC+162+999999
> 9949625::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'QT
> Y+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+1
> 1++9999999953253:EN'QTY+153:2'LIN+12++9999999953888:EN'QTY+153:3'LIN+13++999
> 9999954205:EN'QTY+153:2'LIN+14++9999999954250:EN'QTY+153:1'LIN+15++999999995
> 6407:EN'QTY+153:1'LIN+16++9999999957015:EN'QTY+153:7'LIN+17++9999999957022:E
> N'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'QT
> Y+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++99
> 99999953383:EN'QTY+153:-1'LIN+33++9999999953888:EN'QTY+153:7'LIN+34++9999999
> 956391: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'QT
> Y+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++9
> 999999958821:EN'QTY+153:-1'LIN+46++9999999961616:EN'QTY+153:3'LIN+47++999999
> 9970199:EN'QTY+153:4'LIN+48++9999999970205:EN'QTY+153:2'LIN+49++999999997021
> 2:EN'QTY+153:5'LIN+50++9999999970649:EN'QTY+153:2'LOC+162+5023949145040::9'L
> IN+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++99999
> 99938557:EN'QTY+153:1'LIN+56++9999999953253:EN'QTY+153:2'LIN+57++99999999532
> 77: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+15
> 3:6'LIN+62++9999999957022:EN'QTY+153:5'LIN+63++9999999957671:EN'QTY+153:2'LI
> N+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++99
> 99999943353:EN'QTY+153:1'LIN+75++9999999951419:EN'QTY+153:1'LIN+76++99999999
> 52942: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+8
> 5++9999999953352:EN'QTY+153:2'LIN+86++9999999953888:EN'QTY+153:-3'LIN+87++99
> 99999954267:EN'QTY+153:3'LIN+88++9999999954328:EN'QTY+153:1'LIN+89++99999999
> 56384: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++9999
> 999957985:EN'QTY+153:5'LIN+103++9999999958005:EN'QTY+153:1'LIN+104++99999999
> 58821:EN'QTY+153:8'LIN+105++9999999960657:EN'QTY+153:-1'LIN+106++99999999616
> 16:EN'QTY+153:2'LIN+107++9999999970199:EN'QTY+153:6'LIN+108++9999999970205:E
> N'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++9
> 999999938557:EN'QTY+153:1'LIN+115++9999999949560:EN'QTY+153:2'LIN+116++99999
> 99953154:EN'QTY+153:4'LIN+117++9999999953253:EN'QTY+153:3'LIN+118++999999995
> 3284: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'L
> IN+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+5023
> 949232199::9'LIN+135++9999999923492:EN'QTY+153:1'LIN+136++9999999951419:EN'Q
> TY+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'LI
> N+143++9999999956384:EN'QTY+153:3'LIN+144++9999999956391:EN'QTY+153:1'LIN+14
> 5++9999999956407:EN'QTY+153:1'LIN+146++9999999957015:EN'QTY+153:1'LIN+147++9
> 999999957022:EN'QTY+153:4'LIN+148++9999999957749:EN'QTY+153:3'LIN+149++99999
> 99957909:EN'QTY+153:2'LIN+150++9999999957985:EN'QTY+153:2'LIN+151++999999997
> 0540:EN'QTY+153:2'UNT+1608+22'UNZ+1+435'
>>
>>
>
>

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:D").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

--
Regards,
Tom Ogilvy


"Brian" <it@user1.com> wrote in message
news:%23x3lp3$KGHA.3396@TK2MSFTNGP10.phx.gbl...
> 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" <twogilvy@msn.com> wrote in message
> news:eP1s1F$KGHA.648@TK2MSFTNGP14.phx.gbl...
> > 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
> >
> >
> > --
> > regards,
> > Tom Ogilvy
> >
> > "Brian" <it@user1.com> wrote in message
> > news:uB0es6%23KGHA.3052@TK2MSFTNGP09.phx.gbl...
> >> 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:D:96A:UN:EAN008'BGM+73E::9+00000014+9'DTM+356:20060129
>
> -20060204:718'NAD+FR+9999999900004::9'NAD+SU+9999999900001::9'LOC+162+9999
99
> >
9949625::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'QT
> >
Y+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+1
> >
1++9999999953253:EN'QTY+153:2'LIN+12++9999999953888:EN'QTY+153:3'LIN+13++999
> >
9999954205:EN'QTY+153:2'LIN+14++9999999954250:EN'QTY+153:1'LIN+15++999999995
> >
6407:EN'QTY+153:1'LIN+16++9999999957015:EN'QTY+153:7'LIN+17++9999999957022:E
> >
N'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'QT
> >
Y+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++99
> >
99999953383:EN'QTY+153:-1'LIN+33++9999999953888:EN'QTY+153:7'LIN+34++9999999
> >
956391: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'QT
> >
Y+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++9
> >
999999958821:EN'QTY+153:-1'LIN+46++9999999961616:EN'QTY+153:3'LIN+47++999999
> >
9970199:EN'QTY+153:4'LIN+48++9999999970205:EN'QTY+153:2'LIN+49++999999997021
> >
2:EN'QTY+153:5'LIN+50++9999999970649:EN'QTY+153:2'LOC+162+5023949145040::9'L
> >
IN+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++99999
> >
99938557:EN'QTY+153:1'LIN+56++9999999953253:EN'QTY+153:2'LIN+57++99999999532
> >
77: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+15
> >
3:6'LIN+62++9999999957022:EN'QTY+153:5'LIN+63++9999999957671:EN'QTY+153:2'LI
> >
N+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++99
> >
99999943353:EN'QTY+153:1'LIN+75++9999999951419:EN'QTY+153:1'LIN+76++99999999
> >
52942: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+8
> >
5++9999999953352:EN'QTY+153:2'LIN+86++9999999953888:EN'QTY+153:-3'LIN+87++99
> >
99999954267:EN'QTY+153:3'LIN+88++9999999954328:EN'QTY+153:1'LIN+89++99999999
> >
56384: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++9999
> >
999957985:EN'QTY+153:5'LIN+103++9999999958005:EN'QTY+153:1'LIN+104++99999999
> >
58821:EN'QTY+153:8'LIN+105++9999999960657:EN'QTY+153:-1'LIN+106++99999999616
> >
16:EN'QTY+153:2'LIN+107++9999999970199:EN'QTY+153:6'LIN+108++9999999970205:E
> >
N'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++9
> >
999999938557:EN'QTY+153:1'LIN+115++9999999949560:EN'QTY+153:2'LIN+116++99999
> >
99953154:EN'QTY+153:4'LIN+117++9999999953253:EN'QTY+153:3'LIN+118++999999995
> >
3284: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'L
> >
IN+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+5023
> >
949232199::9'LIN+135++9999999923492:EN'QTY+153:1'LIN+136++9999999951419:EN'Q
> >
TY+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'LI
> >
N+143++9999999956384:EN'QTY+153:3'LIN+144++9999999956391:EN'QTY+153:1'LIN+14
> >
5++9999999956407:EN'QTY+153:1'LIN+146++9999999957015:EN'QTY+153:1'LIN+147++9
> >
999999957022:EN'QTY+153:4'LIN+148++9999999957749:EN'QTY+153:3'LIN+149++99999
> >
99957909:EN'QTY+153:2'LIN+150++9999999957985:EN'QTY+153:2'LIN+151++999999997
> > 0540:EN'QTY+153:2'UNT+1608+22'UNZ+1+435'
> >>
> >>
> >
> >
>
>

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" <twogilvy@msn.com> wrote in message
news:%23gri8JBLGHA.1076@TK2MSFTNGP10.phx.gbl...
> 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:D").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
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Brian" <it@user1.com> wrote in message
> news:%23x3lp3$KGHA.3396@TK2MSFTNGP10.phx.gbl...
>> 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" <twogilvy@msn.com> wrote in message
>> news:eP1s1F$KGHA.648@TK2MSFTNGP14.phx.gbl...
>> > 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
>> >
>> >
>> > --
>> > regards,
>> > Tom Ogilvy
>> >
>> > "Brian" <it@user1.com> wrote in message
>> > news:uB0es6%23KGHA.3052@TK2MSFTNGP09.phx.gbl...
>> >> 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:D:96A:UN:EAN008'BGM+73E::9+00000014+9'DTM+356:20060129
>>
>> -20060204:718'NAD+FR+9999999900004::9'NAD+SU+9999999900001::9'LOC+162+9999
> 99
>> >
> 9949625::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'QT
>> >
> Y+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+1
>> >
> 1++9999999953253:EN'QTY+153:2'LIN+12++9999999953888:EN'QTY+153:3'LIN+13++999
>> >
> 9999954205:EN'QTY+153:2'LIN+14++9999999954250:EN'QTY+153:1'LIN+15++999999995
>> >
> 6407:EN'QTY+153:1'LIN+16++9999999957015:EN'QTY+153:7'LIN+17++9999999957022:E
>> >
> N'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'QT
>> >
> Y+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++99
>> >
> 99999953383:EN'QTY+153:-1'LIN+33++9999999953888:EN'QTY+153:7'LIN+34++9999999
>> >
> 956391: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'QT
>> >
> Y+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++9
>> >
> 999999958821:EN'QTY+153:-1'LIN+46++9999999961616:EN'QTY+153:3'LIN+47++999999
>> >
> 9970199:EN'QTY+153:4'LIN+48++9999999970205:EN'QTY+153:2'LIN+49++999999997021
>> >
> 2:EN'QTY+153:5'LIN+50++9999999970649:EN'QTY+153:2'LOC+162+5023949145040::9'L
>> >
> IN+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++99999
>> >
> 99938557:EN'QTY+153:1'LIN+56++9999999953253:EN'QTY+153:2'LIN+57++99999999532
>> >
> 77: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+15
>> >
> 3:6'LIN+62++9999999957022:EN'QTY+153:5'LIN+63++9999999957671:EN'QTY+153:2'LI
>> >
> N+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++99
>> >
> 99999943353:EN'QTY+153:1'LIN+75++9999999951419:EN'QTY+153:1'LIN+76++99999999
>> >
> 52942: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+8
>> >
> 5++9999999953352:EN'QTY+153:2'LIN+86++9999999953888:EN'QTY+153:-3'LIN+87++99
>> >
> 99999954267:EN'QTY+153:3'LIN+88++9999999954328:EN'QTY+153:1'LIN+89++99999999
>> >
> 56384: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++9999
>> >
> 999957985:EN'QTY+153:5'LIN+103++9999999958005:EN'QTY+153:1'LIN+104++99999999
>> >
> 58821:EN'QTY+153:8'LIN+105++9999999960657:EN'QTY+153:-1'LIN+106++99999999616
>> >
> 16:EN'QTY+153:2'LIN+107++9999999970199:EN'QTY+153:6'LIN+108++9999999970205:E
>> >
> N'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++9
>> >
> 999999938557:EN'QTY+153:1'LIN+115++9999999949560:EN'QTY+153:2'LIN+116++99999
>> >
> 99953154:EN'QTY+153:4'LIN+117++9999999953253:EN'QTY+153:3'LIN+118++999999995
>> >
> 3284: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'L
>> >
> IN+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+5023
>> >
> 949232199::9'LIN+135++9999999923492:EN'QTY+153:1'LIN+136++9999999951419:EN'Q
>> >
> TY+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'LI
>> >
> N+143++9999999956384:EN'QTY+153:3'LIN+144++9999999956391:EN'QTY+153:1'LIN+14
>> >
> 5++9999999956407:EN'QTY+153:1'LIN+146++9999999957015:EN'QTY+153:1'LIN+147++9
>> >
> 999999957022:EN'QTY+153:4'LIN+148++9999999957749:EN'QTY+153:3'LIN+149++99999
>> >
> 99957909:EN'QTY+153:2'LIN+150++9999999957985:EN'QTY+153:2'LIN+151++999999997
>> > 0540:EN'QTY+153:2'UNT+1608+22'UNZ+1+435'
>> >>
>> >>
>> >
>> >
>>
>>
>
>

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:D").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.

--
Regards,
Tom Ogilvy

"Brian" <it@user1.com> wrote in message
news:OQgcikLLGHA.2796@TK2MSFTNGP10.phx.gbl...
> 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" <twogilvy@msn.com> wrote in message
> news:%23gri8JBLGHA.1076@TK2MSFTNGP10.phx.gbl...
> > 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:D").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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Brian" <it@user1.com> wrote in message
> > news:%23x3lp3$KGHA.3396@TK2MSFTNGP10.phx.gbl...
> >> 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" <twogilvy@msn.com> wrote in message
> >> news:eP1s1F$KGHA.648@TK2MSFTNGP14.phx.gbl...
> >> > 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
> >> >
> >> >
> >> > --
> >> > regards,
> >> > Tom Ogilvy
> >> >
> >> > "Brian" <it@user1.com> wrote in message
> >> > news:uB0es6%23KGHA.3052@TK2MSFTNGP09.phx.gbl...
> >> >> 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:D:96A:UN:EAN008'BGM+73E::9+00000014+9'DTM+356:20060129
> >>
>
>> -20060204:718'NAD+FR+9999999900004::9'NAD+SU+9999999900001::9'LOC+162+999
9
> > 99
> >> >
> >
9949625::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'QT
> >> >
> >
Y+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+1
> >> >
> >
1++9999999953253:EN'QTY+153:2'LIN+12++9999999953888:EN'QTY+153:3'LIN+13++999
> >> >
> >
9999954205:EN'QTY+153:2'LIN+14++9999999954250:EN'QTY+153:1'LIN+15++999999995
> >> >
> >
6407:EN'QTY+153:1'LIN+16++9999999957015:EN'QTY+153:7'LIN+17++9999999957022:E
> >> >
> >
N'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'QT
> >> >
> >
Y+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++99
> >> >
> >
99999953383:EN'QTY+153:-1'LIN+33++9999999953888:EN'QTY+153:7'LIN+34++9999999
> >> >
> >
956391: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'QT
> >> >
> >
Y+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++9
> >> >
> >
999999958821:EN'QTY+153:-1'LIN+46++9999999961616:EN'QTY+153:3'LIN+47++999999
> >> >
> >
9970199:EN'QTY+153:4'LIN+48++9999999970205:EN'QTY+153:2'LIN+49++999999997021
> >> >
> >
2:EN'QTY+153:5'LIN+50++9999999970649:EN'QTY+153:2'LOC+162+5023949145040::9'L
> >> >
> >
IN+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++99999
> >> >
> >
99938557:EN'QTY+153:1'LIN+56++9999999953253:EN'QTY+153:2'LIN+57++99999999532
> >> >
> >
77: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+15
> >> >
> >
3:6'LIN+62++9999999957022:EN'QTY+153:5'LIN+63++9999999957671:EN'QTY+153:2'LI
> >> >
> >
N+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++99
> >> >
> >
99999943353:EN'QTY+153:1'LIN+75++9999999951419:EN'QTY+153:1'LIN+76++99999999
> >> >
> >
52942: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+8
> >> >
> >
5++9999999953352:EN'QTY+153:2'LIN+86++9999999953888:EN'QTY+153:-3'LIN+87++99
> >> >
> >
99999954267:EN'QTY+153:3'LIN+88++9999999954328:EN'QTY+153:1'LIN+89++99999999
> >> >
> >
56384: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++9999
> >> >
> >
999957985:EN'QTY+153:5'LIN+103++9999999958005:EN'QTY+153:1'LIN+104++99999999
> >> >
> >
58821:EN'QTY+153:8'LIN+105++9999999960657:EN'QTY+153:-1'LIN+106++99999999616
> >> >
> >
16:EN'QTY+153:2'LIN+107++9999999970199:EN'QTY+153:6'LIN+108++9999999970205:E
> >> >
> >
N'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++9
> >> >
> >
999999938557:EN'QTY+153:1'LIN+115++9999999949560:EN'QTY+153:2'LIN+116++99999
> >> >
> >
99953154:EN'QTY+153:4'LIN+117++9999999953253:EN'QTY+153:3'LIN+118++999999995
> >> >
> >
3284: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'L
> >> >
> >
IN+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+5023
> >> >
> >
949232199::9'LIN+135++9999999923492:EN'QTY+153:1'LIN+136++9999999951419:EN'Q
> >> >
> >
TY+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'LI
> >> >
> >
N+143++9999999956384:EN'QTY+153:3'LIN+144++9999999956391:EN'QTY+153:1'LIN+14
> >> >
> >
5++9999999956407:EN'QTY+153:1'LIN+146++9999999957015:EN'QTY+153:1'LIN+147++9
> >> >
> >
999999957022:EN'QTY+153:4'LIN+148++9999999957749:EN'QTY+153:3'LIN+149++99999
> >> >
> >
99957909:EN'QTY+153:2'LIN+150++9999999957985:EN'QTY+153:2'LIN+151++999999997
> >> > 0540:EN'QTY+153:2'UNT+1608+22'UNZ+1+435'
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

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


"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:OpTukwMLGHA.2668@tk2msftngp13.phx.gbl...
> 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:D").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.
>
> --
> Regards,
> Tom Ogilvy
>
> "Brian" <it@user1.com> wrote in message
> news:OQgcikLLGHA.2796@TK2MSFTNGP10.phx.gbl...
>> 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" <twogilvy@msn.com> wrote in message
>> news:%23gri8JBLGHA.1076@TK2MSFTNGP10.phx.gbl...
>> > 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:D").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
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> > "Brian" <it@user1.com> wrote in message
>> > news:%23x3lp3$KGHA.3396@TK2MSFTNGP10.phx.gbl...
>> >> 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" <twogilvy@msn.com> wrote in message
>> >> news:eP1s1F$KGHA.648@TK2MSFTNGP14.phx.gbl...
>> >> > 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
>> >> >
>> >> >
>> >> > --
>> >> > regards,
>> >> > Tom Ogilvy
>> >> >
>> >> > "Brian" <it@user1.com> wrote in message
>> >> > news:uB0es6%23KGHA.3052@TK2MSFTNGP09.phx.gbl...
>> >> >> 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:D:96A:UN:EAN008'BGM+73E::9+00000014+9'DTM+356:20060129
>> >>
>>
>>> -20060204:718'NAD+FR+9999999900004::9'NAD+SU+9999999900001::9'LOC+162+999
> 9
>> > 99
>> >> >
>> >
> 9949625::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'QT
>> >> >
>> >
> Y+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+1
>> >> >
>> >
> 1++9999999953253:EN'QTY+153:2'LIN+12++9999999953888:EN'QTY+153:3'LIN+13++999
>> >> >
>> >
> 9999954205:EN'QTY+153:2'LIN+14++9999999954250:EN'QTY+153:1'LIN+15++999999995
>> >> >
>> >
> 6407:EN'QTY+153:1'LIN+16++9999999957015:EN'QTY+153:7'LIN+17++9999999957022:E
>> >> >
>> >
> N'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'QT
>> >> >
>> >
> Y+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++99
>> >> >
>> >
> 99999953383:EN'QTY+153:-1'LIN+33++9999999953888:EN'QTY+153:7'LIN+34++9999999
>> >> >
>> >
> 956391: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'QT
>> >> >
>> >
> Y+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++9
>> >> >
>> >
> 999999958821:EN'QTY+153:-1'LIN+46++9999999961616:EN'QTY+153:3'LIN+47++999999
>> >> >
>> >
> 9970199:EN'QTY+153:4'LIN+48++9999999970205:EN'QTY+153:2'LIN+49++999999997021
>> >> >
>> >
> 2:EN'QTY+153:5'LIN+50++9999999970649:EN'QTY+153:2'LOC+162+5023949145040::9'L
>> >> >
>> >
> IN+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++99999
>> >> >
>> >
> 99938557:EN'QTY+153:1'LIN+56++9999999953253:EN'QTY+153:2'LIN+57++99999999532
>> >> >
>> >
> 77: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+15
>> >> >
>> >
> 3:6'LIN+62++9999999957022:EN'QTY+153:5'LIN+63++9999999957671:EN'QTY+153:2'LI
>> >> >
>> >
> N+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++99
>> >> >
>> >
> 99999943353:EN'QTY+153:1'LIN+75++9999999951419:EN'QTY+153:1'LIN+76++99999999
>> >> >
>> >
> 52942: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+8
>> >> >
>> >
> 5++9999999953352:EN'QTY+153:2'LIN+86++9999999953888:EN'QTY+153:-3'LIN+87++99
>> >> >
>> >
> 99999954267:EN'QTY+153:3'LIN+88++9999999954328:EN'QTY+153:1'LIN+89++99999999
>> >> >
>> >
> 56384: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++9999
>> >> >
>> >
> 999957985:EN'QTY+153:5'LIN+103++9999999958005:EN'QTY+153:1'LIN+104++99999999
>> >> >
>> >
> 58821:EN'QTY+153:8'LIN+105++9999999960657:EN'QTY+153:-1'LIN+106++99999999616
>> >> >
>> >
> 16:EN'QTY+153:2'LIN+107++9999999970199:EN'QTY+153:6'LIN+108++9999999970205:E
>> >> >
>> >
> N'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++9
>> >> >
>> >
> 999999938557:EN'QTY+153:1'LIN+115++9999999949560:EN'QTY+153:2'LIN+116++99999
>> >> >
>> >
> 99953154:EN'QTY+153:4'LIN+117++9999999953253:EN'QTY+153:3'LIN+118++999999995
>> >> >
>> >
> 3284: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'L
>> >> >
>> >
> IN+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+5023
>> >> >
>> >
> 949232199::9'LIN+135++9999999923492:EN'QTY+153:1'LIN+136++9999999951419:EN'Q
>> >> >
>> >
> TY+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'LI
>> >> >
>> >
> N+143++9999999956384:EN'QTY+153:3'LIN+144++9999999956391:EN'QTY+153:1'LIN+14
>> >> >
>> >
> 5++9999999956407:EN'QTY+153:1'LIN+146++9999999957015:EN'QTY+153:1'LIN+147++9
>> >> >
>> >
> 999999957022:EN'QTY+153:4'LIN+148++9999999957749:EN'QTY+153:3'LIN+149++99999
>> >> >
>> >
> 99957909:EN'QTY+153:2'LIN+150++9999999957985:EN'QTY+153:2'LIN+151++999999997
>> >> > 0540:EN'QTY+153:2'UNT+1608+22'UNZ+1+435'
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>