help writing code 3

[amal1973]

Hello..
Every month I download a file that contains department numbers and six columns of services that department incurred during that month. They are Service rate one, Service rate two, Service rate three…. and so on. The actual data in the columns are the kind of service and its cost (ALL TOGATHER)

I would like to write code that I could run on that file to make separate columns for the charges


GL RC NBR DEPR AMT SERVICE RATE 1 SERVICE RATE2
0840475 0 LAN ACC $102
0840475 0 PC PRT MAINT $8
0840829 0 MNFRM PRT $38 NTWK ACC $57
0840829 0 LAN ACC $102
0840829 0 PC PRT MAINT $8
0840829 0 LAN ACC $102
0840829 0 PC PRT MAINT $8
0840829 0 LAN ACC $102
0840829 0 PC PRT MAINT $8
0840830 0 LAN ACC $102
0840831 0 DSKTP SPRT $15 PC MAINT $11

That’s what I am looking for
GlRCNBR LAN ACC PC PRT MAINT ENTR SW NTWK
0840831 102 8 0 57
Appreciate any help

 

[MichaelRed]

The sample data and the description appear to suggest a simple parse routine. The sample results confuse and confound the previous 'suggestion'. I do not see the rows (records) in the input which transpose to become the output.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[amal1973]

Thanks for your response
I have seven columns, Department Number, Service Rate 1, Service Rate 2 and so on..
The date under the Service rate has the description of the service and the cost all together (LAN ACC $102)
I can’t query or do anything with this data, even If I wanted to know what king of services every department had that month?? Can’t know with the current format.

I know that I have to create separate column for every service by name. Like a column for (LAN ACC) and a column for (PC PRT MAINT) and what comes inside these columns is the cost of that service.
Please tell me if I am making sense.

Thank you


That’s what I am looking for
[GlRCNBR] [DEPR] [LAN ACC] [PC PRT MAINT] [ENTR SW]
0840831 0 102 8 0

 

[MichaelRed]

My confusion:
[GlRCNBR] [DEPR] [LAN ACC] [PC PRT MAINT] [ENTR SW]
0840831 0 102 8 0
^^^^^^Match^^^^^^ --------------No Match-----------
<>

[GlRCNBR] [DEPR] [DSKTP SPRT) PC MAINT
0840831 0 $15 $11

so what am i not understanding?
MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[amal1973]

Michael.. I think you have it write . i have all of these sevices. they are not separated.

CNTLR $9
COF APP SRVR $39.50
COF CLNT SRVR $215
COF PC $2.30
COF PRT $ 2.30
CRT $16
DSKTP SPRT $15
ENTR SW $24
IMG ACC $35
LAN ACC $102
MNFRM PRT $38
NTWK ACC $57
PC MAINT $11
PC PRT MAINT $8

They are all scattered in the Service rate columns from one to six.

Is there a way that I could show you a file with sample data? This will make it much easier to understand how the data are presented. I really thank you for your patience and help.

 

[MichaelRed]

Hmmmmmmmmmmmmm,

All the service rateN cols appear to have the service seperates from the rate by the &quot; $&quot;, so just load this and translate to two(2) cols by the seperator. The appearance is that the info is badly de-normalized, so why go 1/2 way? make multiple records from any input w/ multiple service rates. Pull back together with group or aggregate. Unless &quot;[DEPR]&quot; has somne non-zero values, just dump it. Worst case table has only:


[GlRCNBR][tab][DEPR][tab][ServiceCode][tab][ServiceChg]

Cols, with multi rows where formerly there were (up to) 7 &quot;ServiceCodes w/ RateCOls&quot; supporting ~~ 14 different service codes?

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[amal1973]

Michael.. thanks but honestly!! i did not understand what you mean!! i still dont know how to make this file workable..

 

[MichaelRed]

GlGrp G_Nbr Srvc Chrg
0840475 0 LAN ACC $102.00
0840475 0 PC PRT MAINT $8.00
0840829 0 MNFRM PRT $38.00
0840829 0 NTWK ACC $57.00
0840829 0 LAN ACC $102.00
0840829 0 PC PRT MAINT $8.00
0840829 0 LAN ACC $102.00
0840829 0 PC PRT MAINT $8.00
0840829 0 LAN ACC $102.00
0840829 0 PC PRT MAINT $8.00
0840830 0 LAN ACC $102.00
0840831 0 DSKTP SPRT $15.00
0840831 0 PC MAINT $1.00

Public Function basParsGl()

    'Michael Red    6/11/02
    'Parse text file into Gl Table

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    
    Dim FilIn As Integer
    Dim SrvcStrt As Integer
    Dim ChrgStrt As Integer
    Dim ChrgEnd As Integer
    Dim MyLin As String
    Dim SrvcRate As Variant

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblGlChrg", dbOpenDynaset)

    FilIn = FreeFile
    Open "C:\MsAccess\TextParse\TxtIn.Txt" For Input As FilIn

    Do While Not EOF(FilIn)

        Line Input #FilIn, MyLin
        Idx = 0

        'SrvcRate = basSplit(Mid(MyLin, 18), "$")
        SrvcStrt = 18
        ChrgStrt = InStr(SrvcStrt, MyLin, "$")
        ChrgEnd = InStr(ChrgStrt, MyLin, " ")
        If (ChrgEnd = 0) Then
            ChrgEnd = Len(MyLin)
        End If
        Do While ChrgStrt <> 0

            With rst
                .AddNew
                    !GlGrp = Trim(Left(MyLin, 10))
                    !G_Nbr = Trim(Mid(MyLin, 11, 7))
                    !Srvc = Trim(Mid(MyLin, SrvcStrt, ChrgStrt - SrvcStrt))
                    !Chrg = Trim(Mid(MyLin, ChrgStrt, ChrgEnd - ChrgStrt))
                .Update
            End With

            SrvcStrt = ChrgEnd
            ChrgStrt = InStr(SrvcStrt, MyLin, &quot;$&quot;)
            If (ChrgStrt = 0) Then
                Exit Do
            End If
            ChrgEnd = InStr(ChrgStrt, MyLin, &quot; &quot;)
            If (ChrgEnd = 0) Then
                ChrgEnd = Len(MyLin)
            End If
        Loop

    Loop

    Close #FilIn
    Set rst = Nothing
    Set dbs = Nothing

End Function

from your 'sample', the above code produces the above above &quot;table&quot;.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[amal1973]

I cant thank you enought.. now i can work with my file....
thanks a million

 

[KyleS]

Very nice MichaelRed! Kyle

 

[rccline]

This code is breaking at
ChrgStrt = InStr(...)

What am I missing?

Thank you.

Robert




Do While Not EOF(FilIn)

Line Input #FilIn, MyLin
Idx = 0

'SrvcRate = basSplit(Mid(MyLin, 18), &quot;$&quot
SrvcStrt = 18
ChrgStrt = InStr(SrvcStrt, MyLin, &quot;$&quot
ChrgEnd = InStr(ChrgStrt, MyLin, &quot; &quot
If (ChrgEnd = 0) Then
ChrgEnd = Len(MyLin)
End If
Do While ChrgStrt <> 0

 

[KyleS]

What error is it giving you? Kyle

 

[rccline]

The code ran one more step:

'SrvcRate = basSplit(Mid(MyLin, 18), &quot;$&quot
SrvcStrt = 18
ChrgStrt = InStr(SrvcStrt, MyLin, &quot;$&quot
--> ChrgEnd = InStr(ChrgStrt, MyLin, &quot; &quot

The code stopped at &quot;ChrgEnd&quot; With the error:
Runtime Error 5. Invalid proceedure, call or argument.

Robert