need for some code to add in a counter??

[walt1122]

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
	A	B	C	D	E	F	G	H	I	J
34	\par	NORTHERN TRUST
35	\par	BENEFIT PAYMENTS	ANNUAL STATEMENT	OF PAYMENTS REPO	RT BY	ALPHA LAST	NAME		DATE	1/14/2005
36	\par	REPORT ID BPP425-ASP		*HONEYWELL*					PROGRAM	ID BPPB250R
37	\par	CLIENT 0018		HONEYWELL					PAGE	19,705
38	\par	PLAN AB5	FRO	M 1/01/2004 - 12	/31/2	4
39	\par
40	\par	PARTICIPANT NAME	SSN				PAYMT REF#
41	\par	HOME ADDRESS	PART ID	PAYMT DATE	PAYMT	TYPE	W/H & DED
42	\par	PAYMENT/ADVICE ADDRESS	RETIRE DATE	FUNDING SOURCE	AMOUN	T	TAXATION	AMOUNT
43	\par	___________________________________	_____________________________	_________________	_____	____________	______________	___________	_______________	____________
44	\par	Public, John Q.		5/3/2004	CHECK		5146845
45	\par	123 any where	123-45-6789	BENEFIT		999.83	FEDERAL	0	NET AMOUNT	789.37
46	\par	any town, any city zip	123456789	GROSS AMOUNT		999.83	GA STATE	0
47	\par		10/1/2002				BASMED	210.46
48	\par		DIST CODE 7				TOTAL	210.46
49	\par
50	\par						ORDINC	999.83	TAXABLE	999.83
51	\par								NONTAXABLE	0
52	\par	___________________________________	_____________________________	_________________	_____	____________	______________	___________	_______________	____________
53	\par	Public, John Q.		6/1/2004	CHECK		5691750
54	\par	123 any where	123-45-6789	BENEFIT		999.83	FEDERAL	0	NET AMOUNT	789.37
55	\par	any town, any city zip	123456789	GROSS AMOUNT		999.83	GA STATE	0
56	\par		10/1/2002				BASMED	210.46
57	\par		DIST CODE 7				TOTAL	210.46
58	\par
59	\par						ORDINC	999.83	TAXABLE	999.83
60	\par								NONTAXABLE	0
61	\par	___________________________________	_____________________________	_________________	_____	____________	______________	___________	_______________	____________
62	\par	Smith, John		7/1/2004	CHECK		6283632
63	\par	105 my street	3456-78-9012	BENEFIT		999.83	FEDERAL	0	NET AMOUNT	789.37
64	\par	new town NJ zip	3456789012	GROSS AMOUNT		999.83	GA STATE	0
65	\par		10/1/2002				BASMED	210.46
66	\par		DIST CODE 7				TOTAL	210.46
67	\par
68	\par						ORDINC	999.83	TAXABLE	999.83
69	\par								NONTAXABLE	0
70	\par	___________________________________	_____________________________	_________________	_____	____________	______________	___________	_______________	____________
71	\par	Smith, John		8/2/2004	CHECK		6775662
72	\par	105 my street	3456-78-9012	BENEFIT		999.83	FEDERAL	0	NET AMOUNT	789.37
73	\par	new town NJ zip	3456789012	GROSS AMOUNT		999.83	GA STATE	0
74	\par		10/1/2002				BASMED	210.46
75	\par		DIST CODE 7				TOTAL	210.46
76	\par
77	\par						ORDINC	999.83	TAXABLE	999.83
78	\par								NONTAXABLE	0
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>

 

[Joe Was]

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

 

[Norie]

Walt

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

If it is then something like this, untested, code might work.

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

 

[walt1122]

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

 

[Norie]

Walt

All the code posted so far, including the original code, is going through each line of the file.

 

[walt1122]

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

 

[Norie]

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.

 

[walt1122]

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

 

[Joe Was]

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.

 

[walt1122]

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