need for some code to add in a counter??

walt1122

Active Member
Joined
Jun 6, 2002
Messages
318
Hello all, Have a need for some code to add in a counter?
Let me try to explain and maybe there is a better way to do it.

I have a tremendous amount of data, an average of 46,000 KB on each file in .RTF (Rich Text Format). I did find some code that helps me parse it into Excel by allowing me to set the maximum number of lines I can put on one workbook and then add a new workbook to continue the import filling into one workbook after another until all the data is imported. The way the original data is set up it is hard to work with, it isn't set up very well as you will see in the sample below. I have been able to use some simple formulas to extract the info on to one line so I can later do a sum-total for each person on the file. From the 60,000+ records on each worksheet I can get convert that into about 5,000 lines. However the problem, because the import stops at the predetermined counter number I quite often have a persons information on two workbooks. I would like to be able to pause the import at some point other than just the line counter alone and use the word "Northern Trust" or something so I don't pick up some participant information on the end of one file and conclude it on the top of another. I was thinking of a second counter in conjunction with the first so that the code does something like Count1 = "When row > 65,500 AND Count2 = "Northern Trust" > some count ( for example I did a countif and found 1,158 occurrences of "NORTHERN TRUST" on the file I have already imported. . I would eventually like to automate combining the workbooks together later and I could probably to try and fit all the data on one workbook. I'm thinking it is easier to do the sub totals now on 5,000 lines of data instead of trying to do it on 65,000 lines after I have combined them. Any help or direction on this effort would be greatly appreciated.
temp_5.txt
ABCDEFGHIJ
34\parNORTHERN TRUST
35\parBENEFIT PAYMENTSANNUAL STATEMENTOF PAYMENTS REPORT BYALPHA LASTNAMEDATE1/14/2005
36\parREPORT ID BPP425-ASP*HONEYWELL*PROGRAMID BPPB250R
37\parCLIENT 0018HONEYWELLPAGE19,705
38\parPLAN AB5FROM 1/01/2004 - 12/31/24
39\par
40\parPARTICIPANT NAMESSNPAYMT REF#
41\parHOME ADDRESSPART IDPAYMT DATEPAYMTTYPEW/H & DED
42\parPAYMENT/ADVICE ADDRESSRETIRE DATEFUNDING SOURCEAMOUNTTAXATIONAMOUNT
43\par______________________________________________________________________________________________________________________________________________________
44\parPublic, John Q.5/3/2004CHECK5146845
45\par123 any where123-45-6789BENEFIT999.83FEDERAL0NET AMOUNT789.37
46\parany town, any city zip123456789GROSS AMOUNT999.83GA STATE0
47\par10/1/2002BASMED210.46
48\parDIST CODE 7TOTAL210.46
49\par
50\parORDINC999.83TAXABLE999.83
51\parNONTAXABLE0
52\par______________________________________________________________________________________________________________________________________________________
53\parPublic, John Q.6/1/2004CHECK5691750
54\par123 any where123-45-6789BENEFIT999.83FEDERAL0NET AMOUNT789.37
55\parany town, any city zip123456789GROSS AMOUNT999.83GA STATE0
56\par10/1/2002BASMED210.46
57\parDIST CODE 7TOTAL210.46
58\par
59\parORDINC999.83TAXABLE999.83
60\parNONTAXABLE0
61\par______________________________________________________________________________________________________________________________________________________
62\parSmith, John7/1/2004CHECK6283632
63\par105 my street3456-78-9012BENEFIT999.83FEDERAL0NET AMOUNT789.37
64\parnew town NJ zip3456789012GROSS AMOUNT999.83GA STATE0
65\par10/1/2002BASMED210.46
66\parDIST CODE 7TOTAL210.46
67\par
68\parORDINC999.83TAXABLE999.83
69\parNONTAXABLE0
70\par______________________________________________________________________________________________________________________________________________________
71\parSmith, John8/2/2004CHECK6775662
72\par105 my street3456-78-9012BENEFIT999.83FEDERAL0NET AMOUNT789.37
73\parnew town NJ zip3456789012GROSS AMOUNT999.83GA STATE0
74\par10/1/2002BASMED210.46
75\parDIST CODE 7TOTAL210.46
76\par
77\parORDINC999.83TAXABLE999.83
78\parNONTAXABLE0
79\par______________________________________________________________________________________________________________________________________________________
temp_5
</span><span class="gensmall"></span></td></tr></table></td></tr><tr><td class="row1" width="150" align="left" valign="middle"><span class="nav">Back to top</span></td><td class="row1" width="100%" height="28" valign="bottom" nowrap="nowrap"><table cellspacing="0" cellpadding="0" border="0" height="18" width="18"><tr>
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sub importBig()
Dim ResultStr$, FileName$
Dim FileNum%, myCol%
Dim Counter As Double

FileName = InputBox("Please enter the Text File's name, e.g. test.txt, that you want to import!")
If FileName = "" Then End

FileNum = FreeFile()
Open FileName For Input As #FileNum
Application.ScreenUpdating = False

'Create A New WorkBook With One Worksheet In It
Workbooks.Add template:=xlWorksheet
Counter = 1

Do While Seek(FileNum) <= LOF(FileNum)
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & FileName

Line Input #FileNum, ResultStr
If Left(ResultStr, 1) = "=" Then
ActiveCell.Value = "'" & ResultStr
Else
ActiveCell.Value = ResultStr
End If

If ActiveCell.Row = 65535 Then
myCol = myCol + 1
ActiveCell.Offset(1, myCol).Select
Else
ActiveCell.Offset(1, myCol).Select
End If

Counter = Counter + 1
Loop
Close
Application.StatusBar = False
End Sub
 
Upvote 0
Walt

Is it always NORTHERN TRUST that indicates a new set of records?

If it is then something like this, untested, code might work.
Code:
Sub LargeFileImport()
Dim Datafile As Variant
Dim ResultStr As String
Dim g As String
Dim FileNum As Integer
Dim Counter As Variant
Dim CounterMax As Variant
Dim sPath As String
Dim FileCounter As Integer
Dim FileName As String
    
    'Output File
    FileCounter = 1
    sPath = "C:\testing\temp_" & FileCounter & ".txt"
    'Number of Records in Each Text File
    CounterMax = 64500
    'Call Auto_Open
    'Ask User for File's Name
    Application.DefaultFilePath = ThisWorkbook.Path
    
    Datafile = Application.GetOpenFilename(Title:="Need to locate the Northern Trust Benefit Payment.TXT file. Try Pension Accounting\200X DB Plans... ")
    If Datafile = False Then
        MsgBox "Please help me find the location of the Northern Trust Benefit Payment.TXT file"
        Datafile = Application.GetOpenFilename(Title:="Please locate the Northern Trust Benefit Payment.TXT file")
        If Datafile = False Then
            Exit Sub
        End If
    End If
    ' Opens the data file and imports just the relevant columns
    'FileName = "C:\Account Analysis - (180 Char)_030106.txt"
    'Check for no entry
    FileName = Datafile
    'Get Next Available File Handle Number
    FileNum = FreeFile()
    'Open Text File For Input
    Open FileName For Input As #FileNum
        'Get Next Available File Handle Number
        g = FreeFile()
        'Open Text File For Output
        Open sPath For Output As #g
        'Turn Screen Updating Off
        'Application.ScreenUpdating = False
        'Set The Counter to 1
        Counter = 1
    
        'Loop Until the End Of File Is Reached
        
        Do While Seek(FileNum) <= LOF(FileNum)
            'Display Importing Row Number On Status Bar
            Application.StatusBar = "Importing Row " & _
            Counter & " of text file " & FileName
            'Store One Line Of Text From File To Variable
            Line Input #FileNum, ResultStr
            If Left(ResultStr, 14) <> "NORTHERN TRUST" Then
                'Output One Line Of Text From Variable To File
                Print #g, ResultStr
            Else
    
                Close #g
                'Open a New Ouput File
                FileCounter = FileCounter + 1
                sPath = "C:\testing\temp_" & FileCounter & ".txt"
                g = FreeFile()
                Open sPath For Output As #g
            End If
        'Increment the Counter By 1
        Counter = Counter + 1
        'Start Again At Top Of 'Do While' Statement
        Loop
        'Close The Open Text File
        Close #g
    Close #FileNum
    'Remove Message From Status Bar
    Application.StatusBar = False
    MsgBox "Great, that's Completed! Next step is to pull in the .TXT and convert it to .XLS Just say OK"

End Sub
 
Upvote 0
Hi Norie, I hear and obey, Yes I think I will try your code. I have the code from Joe Was running in the background but it is "looking" at each line?? and is taking a little bit of time to get through all the data. Probably doing a good job but I have couple of dozen, if not more, of these files that will need converting and i'm just trying to set up a process that will take the manual work out of it. These files are huge only becaue of the way they are configured, all the spaces and header junk take up a tremendous amount of space. That is one of the many reasons I'm trying to modify them so they can be worked with. I take in 180,000 + rows that once they are reconfigured easily fit on one TAB. I do have to be very careful. This process and the macros will eventually be given to the external auditors who will do the reconfiguring so they can review the data. In theory I am not allowed to "touch" the files for fear I could do an "ENRON" or "World.com" on the pension money.

Send myself tons of checks $ and then cover it up.

thanks to you and Joe Was for all the help. I will play around with the code in a a little while. I'm having so much fun trying to learn and understand that I'm neglecting my work.


Walt
 
Upvote 0
Walt

All the code posted so far, including the original code, is going through each line of the file.:)
 
Upvote 0
Hi Norie, it didn't look like the counter was working at all?

Line Input #FileNum, ResultStr
If Left(ResultStr, 14) <> "NORTHERN TRUST" Then
'Output One Line Of Text From Variable To File
Print #g, ResultStr



it just ran till the end didn't stop for anything.

So I checked and i came up with this

Line Input #FileNum, ResultStr
If Left(ResultStr, 19) <> "\par NORTHERN TRUST" Then
'Output One Line Of Text From Variable To File
Print #g, ResultStr

cause the " \par NORTHERN TRUST" is what is on the file from Northern in the .RTF format. And I did get a reaction, albiet a bad one. Soon as the code sees the "\par NORTHERN TRUST" it stops dead and starts up a new file. I now have 193 text files with a couple of lines in them. Whoops!! gfood thing it was a small .RTF file

So any thoughts on how to get it to work??? count till 65000 then loop until "\par NORTHERN TRUST" just quessing I haven't a clue!

thanks

Walt
 
Upvote 0
Walt

The code I posted has nothing really to do with a counter.

What it should do, and remember it's untested, is go through the data file
and every time it comes across the text NORTHERN TRUST it should create a new file.

I actually thought the \par part was some fault with the HTML maker.
 
Upvote 0
Hi Norie, yes the the "Northern Trust" part has the \par in front of it. Plus the problem is it does repeat every time there is a new page. So of course it will create a new file every time it sees the "\par Northern Trust". So is there any way we can have the counter count to some perdetermined number like 65,000 and then have the code look for the "\par Northern Trust" or have an IF statement say IF(and(counter = 65,000,"\par Northern Trust"?? and then start a new file??

thanks

Walt
 
Upvote 0
I did not have a file large enough to test my code with, but it looks like it should work, not it posts to the Ready bar at the bottom of the screen where it is at [as a counter] as it runs. I made the code just move over a column when it gets to the bottom of the sheet if you need more columns between data it is an easy fix!

myCol = myCol + 1

Just change the "1" to the number of columns you would like to skip between imports.
 
Upvote 0
Hi Joe Was, I'm getting an error #1004 "Application-defined or object-defined error" at the second occurance of line ActiveCell.Offset(1, myCol).Select

The code does run and the Application.StatusBar shows that it goes to the 65536 then that is when the error occurs. The Sheet it creates shows that the data has be moved and the sheet is completely full down to the bottom 66536.


If ActiveCell.Row = 65535 Then
myCol = myCol + 1
ActiveCell.Offset(1, myCol).Select
Else
ActiveCell.Offset(1, myCol).Select
End If

I tried to use 65000 same effect.

I changed
Line Input #FileNum, ResultStr
If Left(ResultStr, 1) = "=" Then
ActiveCell.Value = "'" & ResultStr
Else
ActiveCell.Value = ResultStr
End If

to

Line Input #FileNum, ResultStr
If Left(ResultStr, 19) = "\par NORTHERN TRUST" Then
ActiveCell.Value = "'" & ResultStr
Else
ActiveCell.Value = ResultStr
End If

same effect.

Looks like it doesn't like the
ActiveCell.Offset(1, myCol).Select

any thoughts on why and my second question is what will trigger it to stop at the end of a record not at the counter total of what ever we say but at the break between data so I get all of the peoples data on one page and not on two.

Still don't get that??

thanks for your continued effort.

Walt
 
Upvote 0

Forum statistics

Threads
1,215,518
Messages
6,125,291
Members
449,218
Latest member
Excel Master

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top