Thread: Importing Long String - String Manipulation (EDI EANCOM 96a)
View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
  
Posts: 27,285
Default Importing Long String - String Manipulation (EDI EANCOM 96a)
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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:05 08+435+


+SLSRPT'UNH+22+SLSRPT:D:96A:UN:EAN008'BGM+73E::9+0 0000014+9'DTM+356:20060129

-20060204:718'NAD+FR+9999999900004::9'NAD+SU+999999 9900001::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++9999999 970212: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++9999999 932418:EN'QTY+153:1

'LIN+9++9999999937482:EN'QTY+153:1'LIN+10++9999999 949560:EN'QTY+153:-1'LIN+1

1++9999999953253:EN'QTY+153:2'LIN+12++999999995388 8:EN'QTY+153:3'LIN+13++999

9999954205:EN'QTY+153:2'LIN+14++9999999954250:EN'Q TY+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++9 999999970496:EN'QTY+153:3'

LOC+162+5023949136774::9'LIN+22++21326806:EN'QTY+1 53:1'LIN+23++9999999923492

:EN'QTY+153:2'LIN+24++9999999929029:EN'QTY+153:1'L IN+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++99999 99953307:EN'QTY+153:1'LIN+

30++9999999953338:EN'QTY+153:3'LIN+31++99999999533 45:EN'QTY+153:3'LIN+32++99

99999953383:EN'QTY+153:-1'LIN+33++9999999953888:EN'QTY+153:7'LIN+34++99999 99

956391:EN'QTY+153:1'LIN+35++9999999956407:EN'QTY+1 53:1'LIN+36++9999999957015

:EN'QTY+153:2'LIN+37++9999999957022:EN'QTY+153:1'L IN+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++99999 99957794:EN'QTY+153:10'LIN

+43++9999999957879:EN'QTY+153:1'LIN+44++9999999957 985:EN'QTY+153:1'LIN+45++9

999999958821:EN'QTY+153:-1'LIN+46++9999999961616:EN'QTY+153:3'LIN+47++99999 9

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++99999999 23492: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+6 1++9999999957015:EN'QTY+15

3:6'LIN+62++9999999957022:EN'QTY+153:5'LIN+63++999 9999957671: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++99999999385 57: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+15 3:6'LIN+78++9999999953147:

EN'QTY+153:1'LIN+79++9999999953154:EN'QTY+153:1'LI N+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++999999 9953338:EN'QTY+153:2'LIN+8

5++9999999953352:EN'QTY+153:2'LIN+86++999999995388 8: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+15 3:9'LIN+91++9999999956407:

EN'QTY+153:3'LIN+92++9999999957015:EN'QTY+153:8'LI N+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++99999 99957657:EN'QTY+153:-2'LIN

+98++9999999957695:EN'QTY+153:1'LIN+99++9999999957 732: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'Q TY+153:1'LIN+104++99999999

58821:EN'QTY+153:8'LIN+105++9999999960657:EN'QTY+1 53:-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'LO C+162+5023949223920::9'LIN

+110++5014838064023:EN'QTY+153:3'LIN+111++99999999 29029:EN'QTY+153:1'LIN+112

++9999999929951:EN'QTY+153:2'LIN+113++999999992996 8:EN'QTY+153:-1'LIN+114++9

999999938557:EN'QTY+153:1'LIN+115++9999999949560:E N'QTY+153:2'LIN+116++99999

99953154:EN'QTY+153:4'LIN+117++9999999953253:EN'QT Y+153:3'LIN+118++999999995

3284:EN'QTY+153:1'LIN+119++9999999953352:EN'QTY+15 3: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++99 99999957671:EN'QTY+153:4'L

IN+129++9999999957688:EN'QTY+153:2'LIN+130++999999 9957701:EN'QTY+153:-1'LIN+

131++9999999957794:EN'QTY+153:2'LIN+132++999999995 7985: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'L IN+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++999 9999953888:EN'QTY+153:2'LI

N+143++9999999956384:EN'QTY+153:3'LIN+144++9999999 956391:EN'QTY+153:1'LIN+14

5++9999999956407:EN'QTY+153:1'LIN+146++99999999570 15:EN'QTY+153:1'LIN+147++9

999999957022:EN'QTY+153:4'LIN+148++9999999957749:E N'QTY+153:3'LIN+149++99999

99957909:EN'QTY+153:2'LIN+150++9999999957985:EN'QT Y+153:2'LIN+151++999999997
0540:EN'QTY+153:2'UNT+1608+22'UNZ+1+435'








Reply With QuoteReply With Quote