Thread: Import long string (modified Tom O snippet help!)
View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Brian Brian is offline
external usenet poster
  
Posts: 17
Default Import long string (modified Tom O snippet help!)
Tom,

Looking at LIN+1

'LIN+1++21298776:EN'QTY+17:1'LOC+14+5023949771634: :9'QTY+198:0'LOC+14+5023949771634::9'QTY+83:0'LOC+ 14+5023949771634::9

The first Qty+17 assigned to the LOC which comes immediately after....
bloody stupid format for a file!

So when i imported the file with the line commented out i was left with a
entry at the end with no value...


"Tom Ogilvy" wrote in message
...
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