Separating Fields

[pkw25]

Hello

We will be receiving flat ASCII files from the Postal service which will contain information concerning who has paid and how much in post offices. We need to reconcile this info on our system ourselves.
Is their a way of seperating lines into fields at specific locations using Excel to make the file readable.
The following is an example of an output file with Header, Detail and Trailer

H99Smart Telecom 01030101
D02090440012388282260209001759450120024500103012802010000101
D02440195021640762260244040184800120011340103012802010000101
D02440195021642832260244041088050120048180103012802010000101
D02440195021643202260244040171780120003410103012802010000101
D02440195021643212260244035153170120018160103012802010000101
T00000550000107782

All I'm interested in formatting at the moment are the detail lines. The following is the first Detail line formated

D 0209 0440 01 238828 226 0209 00175945012 002450 010301 280201 0000101

Any help would appreciated on this matter.

Paraic Walsh

 

[xlbo]

It looks like your fields are fixed width so when importing into excel, just choose the "Fixed Width" option, set your field breaks where you want 'em, follow the wizard thru and you should have no trouble. If you record it, you should even be able to set up an import spec macro that does it all for you.
HTH
Geoff

 

[pkw25]

Thanks

I haven't use the record macro option before, It's quite useful. I have it working at a basic level.

Paraic Walsh

 

[SkipVought]

pkw25,
The Text to data wizard works great most of the time.

BUT...

when you are working with numeric digits as strings (like keeping the leading zeros), the wizard wimps out.

So...
here's some code to parse the detail lines, KEEPING the leading zeros...

Sub ParsePO()
    Dim rngData As Range, rngRow As Range, sType As String, sValue As String, iPos As Integer
    Dim iParser(1 To 11) As Integer, sRowValue As String, iCol As Integer, lRow As Long, iLen As Integer
    iParser(1) = 1
    iParser(2) = 5
    iParser(3) = 9
    iParser(4) = 11
    iParser(5) = 17
    iParser(6) = 20
    iParser(7) = 24
    iParser(8) = 35
    iParser(9) = 41
    iParser(10) = 47
    iParser(11) = 53
    Set rngData = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
    For Each rngRow In rngData
        sRowValue = rngRow.Value
        iCol = 1
        lRow = rngRow.Row
        sType = Left(rngRow, 1)
        Select Case sType
            Case "D"        'detail formatting
                iPos = 1
                For i = LBound(iParser, 1) To UBound(iParser, 1)
                    iLen = iParser(i) - iPos + 1
                    sValue = Mid(sRowValue, iPos, iLen)
                    Cells(lRow, iCol).Value = "'" & sValue
                    iCol = iCol + 1
                    iPos = iParser(i) + 1
                Next
                sValue = Mid(sRowValue, iPos, 7)
                Cells(lRow, iCol).Value = "'" & sValue
            Case "H"
            'here's where the header formatting would go
            Case "T"
            'here's where the trailer formatting would go
        End Select
    Next
End Sub

Hope this helps Skip,
metzgsk@voughtaircraft.com