Thread: Import long string (modified Tom O snippet help!)
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
  
Posts: 27,285
Default Import long string (modified Tom O snippet help!)
This does the first part of the processing. However, it isn't as clean as
the other file. The lines that end up with a form like:

+14+5023949771634::9

and originate as 'LOC+14+5023949771634::9'

It is not clear what to do with them. they appear at the end of the LIN
group and would normally be QTY+17 if they followed the pattern.


The 17 type lines that have the format
EN'QTY+17:3

are found at the end of the LIN lines. So, not clear what to do with the
Number after the ":" (3 in the above example).

Anyway, run this macro against your file and see what I mean:

Sub testme3()

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:\SLSRPT2.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+,,")
l = Replace(l, ":EN'QTY+17:", ",")
l = Replace(l, "::9'QTY+17:", ",17,")
l = Replace(l, "::9'QTY+83:", ",83,")
l = Replace(l, "::9'QTY+198:", ",198,")
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 rng2 = Range(Cells(1, 2), _
Cells(Rows.Count, 2).End(xlUp))
For Each cell In rng2.SpecialCells(xlConstants)
cell.Offset(0, -1).Delete Shift:=xlShiftToLeft
Next
End Sub

--
Regards,
Tom Ogilvy



"Brian" wrote in message
...
Tom Ogilvy provided me with a fantastic snippet that imported in a similar
file. I have tried to modify the previous script to handle the new text
file layout however i have been recieving a runtime error 1004 amongst
others when i have been playing with this. I have put this basically back
to
orig snippet provided, with a couple of mods.

Please can you guys just have a look over the script see if you can see
the
glaring mistakes i have made whilst trying to modify Tom's script.
I have included some sample text at the end of the post .

---------------------------------------------------------

Sub GETINVRPT()

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

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

FNum = FreeFile

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

----------------------------------------------------------------------

Results i am after:

LOC | EAN | QTY17 | QTY198 | QTY83

0000000000000 | 0000000000000 | 0 | 0 | 0

from a table in the above format i have already created a pivot with
lookups
to make a very readable report,

Any help really appreciated.

Brian


--------------------------------------------------------------------------
-
Sample Text.

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


UNB+UNOA:3+5023949000004:14+5014838000001+060205:0 513+436+ETRADING+INVRPT'UN
H+23+INVRPT:D:96A:UN:EAN008'BGM+35+00000009+9'DTM+ 366:20060204:102'NAD+BY+50
23949000004::9'NAD+SU+5014838000001::9'LIN+1++2129 8776:EN'QTY+17:1'LOC+14+50
23949771634::9'QTY+198:0'LOC

+14+5023949771634::9'QTY+83:0'LOC+14+5023949771634 ::9'LIN+2++21326806:EN'QTY
+17:3'LOC+14+5023949057895::9'QTY+198:0'LOC+14+502 3949057895::9'QTY+83:0'LOC
+14+5023949057895::9'QTY+17:4'LOC+14+5023949136774 ::9'QTY+198:0'LOC+14+50239
49136774::9'QTY+83:0'LOC+14+

5023949136774::9'QTY+17:2'LOC+14+5023949182579::9' QTY+198:0'LOC+14+502394918
2579::9'QTY+83:0'LOC+14+5023949182579::9'QTY+17:5' LOC+14+5023949223920::9'QT
Y+198:0'LOC+14+5023949223920::9'QTY+83:0'LOC+14+50 23949223920::9'QTY+17:4'LO
C+14+5023949248730::9'QTY+19

8:0'LOC+14+5023949248730::9'QTY+83:0'LOC+14+502394 9248730::9'QTY+17:3'LOC+14
+5023949294535::9'QTY+198:0'LOC+14+5023949294535:: 9'QTY+83:0'LOC+14+50239492
94535::9'QTY+17:3'LOC+14+5023949319342::9'QTY+198: 0'LOC+14+5023949319342::9'
QTY+83:0'LOC+14+502394931934

2::9'QTY+17:4'LOC+14+5023949373414::9'QTY+198:0'LO C+14+5023949373414::9'QTY+
83:0'LOC+14+5023949373414::9'QTY+17:3'LOC+14+50239 49374976::9'QTY+198:0'LOC+
14+5023949374976::9'QTY+83:0'LOC+14+5023949374976: :9'QTY+17:1'LOC+14+5023949
414768::9'QTY+198:0'LOC+14+5

023949414768::9'QTY+83:0'LOC+14+5023949414768::9'Q TY+17:3'LOC+14+50239494239
33::9'QTY+198:0'LOC+14+5023949423933::9'QTY+83:0'L OC+14+5023949423933::9'QTY
+17:3'LOC+14+5023949584122::9'QTY+198:0'LOC+14+502 3949584122::9'QTY+83:0'LOC
+14+5023949584122::9'QTY+17:

3'LOC+14+5023949670870::9'QTY+198:0'LOC+14+5023949 670870::9'QTY+83:0'LOC+14+
5023949670870::9'QTY+17:3'LOC+14+5023949692755::9' QTY+198:0'LOC+14+502394969
2755::9'QTY+83:0'LOC+14+5023949692755::9'QTY+17:8' LOC+14+5023949771634::9'QT
Y+198:0'LOC+14+5023949771634

::9'QTY+83:0'LOC+14+5023949771634::9'QTY+17:7'LOC+ 18+5023949825700::9'QTY+17
:4'LOC+14+5023949867056::9'QTY+198:0'LOC+14+502394 9867056::9'QTY+83:0'LOC+14
+5023949867056::9'QTY+17:5'LOC+14+5023949929392::9 'QTY+198:0'LOC+14+50239499
29392::9'QTY+83:0'LOC+14+502

3949929392::9'LIN+3++21348914:EN'QTY+17:1'LOC+14+5 023949248730::9'QTY+198:0'
LOC+14+5023949248730::9'QTY+83:0'LOC+14+5023949248 730::9'QTY+17:2'LOC+14+502
3949319342::9'QTY+198:0'LOC+14+5023949319342::9'QT Y+83:0'LOC+14+502394931934
2::9'LIN+4++21381416:EN'QTY+

17:1'LOC+14+5023949182579::9'QTY+198:0'LOC+14+5023 949182579::9'QTY+83:0'LOC+
14+5023949182579::9'LIN+5++21481499:EN'QTY+17:1'LO C+14+5023949057895::9'QTY+
198:0'LOC+14+5023949057895::9'QTY+83:0'LOC+14+5023 949057895::9'QTY+17:1'LOC+
14+5023949373414::9'QTY+198:

0'LOC+14+5023949373414::9'QTY+83:0'LOC+14+50239493 73414::9'LIN+6++21481505:E
N'QTY+17:1'LOC+14+5023949057895::9'QTY+198:0'LOC+1 4+5023949057895::9'QTY+83:
0'LOC+14+5023949057895::9'QTY+17:2'LOC+14+50239497 71634::9'QTY+198:0'LOC+14+
5023949771634::9'QTY+83:0'LO

C+14+5023949771634::9'LIN+7++21481512:EN'QTY+17:1' LOC+14+5023949057895::9'QT
Y+198:0'LOC+14+5023949057895::9'QTY+83:0'LOC+14+50 23949057895::9'QTY+17:3'LO
C+14+5023949145040::9'QTY+198:0'LOC+14+50239491450 40::9'QTY+83:0'LOC+14+5023
949145040::9'QTY+17:1'LOC+14

+5023949670870::9'QTY+198:0'LOC+14+5023949670870:: 9'QTY+83:0'LOC+14+50239496
70870::9'QTY+17:1'LOC+14+5023949701028::9'QTY+198: 0'LOC+14+5023949701028::9'
QTY+83:0'LOC+14+5023949701028::9'QTY+17:1'LOC+14+5 023949771634::9'QTY+198:0'
LOC+14+5023949771634::9'QTY+

83:0'LOC+14+5023949771634::9'QTY+17:1'LOC+14+50239 49832131::9'QTY+198:0'LOC+
14+5023949832131::9'QTY+83:0'LOC+14+5023949832131: :9'LIN+8++21481529:EN'QTY+
17:2'LOC+14+5023949223920::9'QTY+198:0'LOC+14+5023 949223920::9'QTY+83:0'LOC+
14+5023949223920::9'QTY+17:1

'LOC+14+5023949374976::9'QTY+198:0'LOC+14+50239493 74976::9'QTY+83:0'LOC+14+5
023949374976::9'QTY+17:1'LOC+14+5023949832131::9'Q TY+198:0'LOC+14+5023949832
131::9'QTY+83:0'LOC+14+5023949832131::9'LIN+9++214 93393:EN'QTY+17:1'LOC+14+5
023949049625::9'QTY+198:0'LO

C+14+5023949049625::9'QTY+83:0'LOC+14+502394904962 5::9'LIN+10++5014838064023
:EN'QTY+17:5'LOC+14+5023949049625::9'QTY+198:0'LOC +14+5023949049625::9'QTY+8
3:0'LOC+14+5023949049625::9'QTY+17:5'LOC+14+502394 9057895::9'QTY+198:0'LOC+1
4+5023949057895::9'QTY+83:0'

LOC+14+5023949057895::9'QTY+17:5'LOC+14+5023949136 774::9'QTY+198:0'LOC+14+50
23949136774::9'QTY+83:0'LOC+14+5023949136774::9'QT Y+17:2'LOC+14+502394914504
0::9'QTY+198:1'LOC+14+5023949145040::9'QTY+83:0'LO C+14+5023949145040::9'QTY+
17:11'LOC+14+5023949182579::

9'QTY+198:0'LOC+14+5023949182579::9'QTY+83:0'LOC+1 4+5023949182579::9'QTY+17:
2'LOC+14+5023949223920::9'QTY+198:0'LOC+14+5023949 223920::9'QTY+83:0'LOC+14+
5023949223920::9'QTY+17:5'LOC+14+5023949232199::9' QTY+198:0'LOC+14+502394923
2199::9'QTY+83:0'LOC+14+5023

949232199::9'QTY+17:5'LOC+14+5023949248730::9'QTY+ 198:0'LOC+14+5023949248730
::9'QTY+83:0'LOC+14+5023949248730::9'QTY+17:11'LOC +14+5023949294535::9'QTY+1
98:2'LOC+14+5023949294535::9'QTY+83:0'LOC+14+50239 49294535::9'QTY+17:5'LOC+1
4+5023949319342::9'QTY+198:0

'LOC+14+5023949319342::9'QTY+83:0'LOC+14+502394931 9342::9'QTY+17:4'LOC+14+50
23949327619::9'QTY+198:0'LOC+14+5023949327619::9'Q TY+83:0'LOC+14+50239493276
19::9'QTY+17:5'LOC+14+5023949373414::9'QTY+198:0'L OC+14+5023949373414::9'QTY
+83:0'LOC+14+5023949373414::

9'QTY+17:5'LOC+14+5023949374976::9'QTY+198:0'LOC+1 4+5023949374976::9'QTY+83:
0'LOC+14+5023949374976::9'QTY+17:3'LOC+14+50239494 14768::9'QTY+198:0'LOC+14+
5023949414768::9'QTY+83:0'LOC+14+5023949414768::9' QTY+17:5'LOC+14+5023949423
933::9'QTY+198:0'LOC+14+5023

949423933::9'QTY+83:0'LOC+14+5023949423933::9'QTY+ 17:4'LOC+14+5023949510183:
:9'QTY+198:0'LOC+14+5023949510183::9'QTY+83:0'LOC+ 14+5023949510183::9'QTY+17
:5'LOC+14+5023949511753::9'QTY+198:0'LOC+14+502394 9511753::9'QTY+83:0'LOC+14
+5023949511753::9'QTY+17:3'L

OC+14+5023949584122::9'QTY+198:0'LOC+14+5023949584 122::9'QTY+83:0'LOC+14+502
3949584122::9'QTY+17:5'LOC+14+5023949597339::9'QTY +198:0'LOC+14+502394959733
9::9'QTY+83:0'LOC+14+5023949597339::9'QTY+17:5'LOC +14+5023949670870::9'QTY+1
98:0'LOC+14+5023949670870::9

'QTY+83:0'LOC+14+5023949670870::9'QTY+17:5'LOC+14+ 5023949692755::9'QTY+198:0
'LOC+14+5023949692755::9'QTY+83:0'LOC+14+502394969 2755::9'QTY+17:5'LOC+14+50
23949701028::9'QTY+198:0'LOC+14+5023949701028::9'Q TY+83:0'LOC+14+50239497010
28::9'QTY+17:6'LOC+14+502394

9771634::9'QTY+198:0'LOC+14+5023949771634::9'QTY+8 3:0'LOC+14+5023949771634::
9'QTY+17:5'LOC+14+5023949832131::9'QTY+198:0'LOC+1 4+5023949832131::9'QTY+83:
0'LOC+14+5023949832131::9'QTY+17:5'LOC+14+50239498 33970::9'QTY+198:0'LOC+14+
5023949833970::9'QTY+83:0'LO

C+14+5023949833970::9'QTY+17:6'LOC+14+502394984330 4::9'QTY+198:0'LOC+14+5023
949843304::9'QTY+83:0'LOC+14+5023949843304::9'QTY+ 17:1'LOC+14+5023949867056:
:9'QTY+198:2'LOC+14+5023949867056::9'QTY+83:0'LOC+ 14+5023949867056::9'QTY+17
:5'LOC+14+5023949912859::9'Q

TY+198:0'LOC+14+5023949912859::9'QTY+83:0'LOC+14+5 023949912859::9'QTY+17:4'L
OC+14+5023949929392::9'QTY+198:0'LOC+14+5023949929 392::9'QTY+83:0'LOC+14+502
3949929392::9'QTY+17:5'LOC+14+5023949955601::9'QTY +198:0'LOC+14+502394995560
1::9'QTY+83:0'LOC+14+5023949

955601::9'QTY+17:5'LOC+14+5023949962472::9'QTY+198 :1'LOC+14+5023949962472::9
'QTY+83:0'LOC+14+5023949962472::9'LIN+11++50148380 66317:EN'QTY+17:2'LOC+14+5
023949057895::9'QTY+198:0'LOC+14+5023949057895::9' QTY+83:0'LOC+14+5023949057
895::9'QTY+17:1'LOC+14+50239

49136774::9'QTY+198:0'LOC+14+5023949136774::9'QTY+ 83:0'LOC+14+5023949136774:
:9'QTY+17:1'LOC+14+5023949182579::9'QTY+198:0'LOC+ 14+5023949182579::9'QTY+83
:0'LOC+14+5023949182579::9'QTY+17:1'LOC+14+5023949 223920::9'QTY+198:0'LOC+14
+5023949223920::9'QTY+83:0'L

OC+14+5023949223920::9'QTY+17:3'LOC+14+50239492487 30::9'QTY+198:0'LOC+14+502
3949248730::9'QTY+83:0'LOC+14+5023949248730::9'QTY +17:3'LOC+14+5023949373414
::9'QTY+198:0'LOC+14+5023949373414::9'QTY+83:0'LOC +14+5023949373414::9'QTY+1
7:1'LOC+14+5023949374976::9'

QTY+198:0'LOC+14+5023949374976::9'QTY+83:0'LOC+14+ 5023949374976::9'QTY+17:1'
LOC+14+5023949423933::9'QTY+198:0'LOC+14+502394942 3933::9'QTY+83:0'LOC+14+50
23949423933::9'QTY+17:3'LOC+14+5023949511753::9'QT Y+198:0'LOC+14+50239495117
53::9'QTY+83:0'LOC+14+502394

9511753::9'QTY+17:2'LOC+14+5023949584122::9'QTY+19 8:0'LOC+14+5023949584122::
9'QTY+83:0'LOC+14+5023949584122::9'QTY+17:1'LOC+14 +5023949597339::9'QTY+198:
0'LOC+14+5023949597339::9'QTY+83:0'LOC+14+50239495 97339::9'QTY+17:4'LOC+14+5
023949764661::9'QTY+198:0'LO

C+14+5023949764661::9'QTY+83:0'LOC+14+502394976466 1::9'QTY+17:3'LOC+14+50239
49771634::9'QTY+198:0'LOC+14+5023949771634::9'QTY+ 83:0'LOC+14+5023949771634:
:9'QTY+17:1'LOC+14+5023949833970::9'QTY+198:0'LOC+ 14+5023949833970::9'QTY+83
:0'LOC+14+5023949833970::9'Q

TY+17:1'LOC+14+5023949867056::9'QTY+198:0'LOC+14+5 023949867056::9'QTY+83:0'L
OC+14+5023949867056::9'QTY+17:1'LOC+14+50239499293 92::9'QTY+198:0'LOC+14+502
3949929392::9'QTY+83:0'LOC+14+5023949929392::9'QTY +17:1'LOC+14+5023949962472
::9'QTY+198:0'LOC+14+5023949

962472::9'QTY+83:0'LOC+14+5023949962472::9'LIN+129 ++5014838370384:EN'QTY+17:
1'LOC+14+5023949182579::9'QTY+198:0'LOC+14+5023949 182579::9'QTY+83:0'LOC+14+
5023949182579::9'QTY+17:2'LOC+14+5023949670870::9' QTY+198:0'LOC+14+502394967
0870::9'QTY+83:1'LOC+14+5023949670870::9'QTY+17:2' LOC+1

4+5023949867056::9'QTY+198:0'LOC+14+5023949867056: :9'QTY+83:0'LOC+14+5023949
867056::9'LIN+130++5014838370414:EN'QTY+17:2'LOC+1 4+5023949145040::9'QTY+198
:0'LOC+14+5023949145040::9'QTY+83:0'LOC+14+5023949 145040::9'QTY+17:1'LOC+14+
5023949327619::9'QTY+198:0'L

OC+14+5023949327619::9'QTY+83:0'LOC+14+50239493276 19::9'QTY+17:1'LOC+14+5023
949584122::9'QTY+198:0'LOC+14+5023949584122::9'QTY +83:0'LOC+14+5023949584122
::9'QTY+17:2'LOC+14+5023949670870::9'QTY+198:0'LOC +14+5023949670870::9'QTY+8
3:0'LOC+14+5023949670870::9'

QTY+17:1'LOC+14+5023949929392::9'QTY+198:0'LOC+14+ 5023949929392::9'QTY+83:0'
LOC+14+5023949929392::9'LIN+131++5014838370438:EN' QTY+17:0'LOC+14+5023949319
342::9'QTY+198:0'LOC+14+5023949319342::9'QTY+83:2' LOC+14+5023949319342::9'UN
T+15237+23'UNZ+1+436'





Reply With QuoteReply With Quote