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>

 

[Norie]

Right, I created a sample data file with 500K+ records with this code.

Sub createFile()
Dim FF As Integer
Dim I As Long
Dim J As Long
Dim NoRecords As Long
    
    FF = FreeFile()
    Open "C:\NorthernTrust.txt" For Output As #FF
    
    For I = 1 To 10000
        Print #FF, "NORTHERN TRUST"
        NoRecords = Int(Rnd * 20) + 40
        
        For J = 1 To NoRecords
            Print #FF, "Record " & J
        Next J
    Next I
    
    Close #FF
        
        
End Sub

I then used this code to import the records.

Sub LargeFileImport()
Dim Datafile As Variant
Dim FileNum
Dim ResultStr As String
Dim wsRecords As Worksheet
Dim wsDest As Worksheet
Dim LastRowDst As Long
Dim I As Long
Dim NoSheets As Long
    
    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
    
    Set wsRecords = Worksheets("Records")
    
    NoSheets = 1
    LastRowDst = 1
    Set wsDest = Worksheets.Add
    wsDest.Name = "Data" & NoSheets
    FileNum = FreeFile()
    'Open Text File For Input
    Open Datafile For Input As #FileNum
    
        
        Do While Seek(FileNum) <= LOF(FileNum)
            Line Input #FileNum, ResultStr
            
            If Left(ResultStr, 14) <> "NORTHERN TRUST" Then
                I = I + 1
                wsRecords.Range("A" & I) = ResultStr
            Else
                If LastRowDst + I > 65536 Then
                    NoSheets = NoSheets + 1
                    LastRowDst = 1
                    Set wsDest = Worksheets.Add
                    wsDest.Name = "Data" & NoSheets
                End If
                If I <> 0 Then
                    wsRecords.Range("A1").Resize(I).Copy wsDest.Range("A" & LastRowDst)
                    wsRecords.Range("A1").Resize(I).ClearContents
                    LastRowDst = LastRowDst + I
                End If
                
                I = 0
            End If
        Loop
        'Close The Open Text File
    Close #FileNum

End Sub

It appears to work, though I think there could be a problem with the last set of records.

Also it took at least 15 mins to run.

I was able to import the same text file into Access in under a minute.

 

[walt1122]

Hi Norie, thanks for not giving up on me.

I'm sorry but I think you solution will talke more work than if I do just keep the code I have and then just have the system sort on and delete all the blanks and pieces of the headers and then merge the sheets together before I do the sub totals that I'm trying to develop.

Can you just answer this question.

Is there any way to combine an "IF statement", or use the "case", or loop or whatever so that

there could be some thing like this:

If Counter > 65000 And Left(ResultStr, 19) = "\par NORTHERN TRUST" Then
'Output One Line Of Text From Variable To File
Print #g, ResultStr

or
If Counter > 65000 And Left(ResultStr, 19) = "\par NORTHERN TRUST" Then
'Output One Line Of Text From Variable To File
Print #g, ResultStr
Else
If Counter >65001 And Left(ResultStr, 19) = "\par NORTHERN TRUST" Then
'Output One Line Of Text From Variable To File
Print #g, ResultStr
Else
If Counter > 65002 And Left(ResultStr, 19) = "\par NORTHERN TRUST" Then
'Output One Line Of Text From Variable To File
Print #g, ResultStr
Else
If Counter > 65003 And Left(ResultStr, 19) = "\par NORTHERN TRUST" Then
'Output One Line Of Text From Variable To File
Print #g, ResultStr
Else
If Counter > 65004 And Left(ResultStr, 19) = "\par NORTHERN TRUST" Then
'Output One Line Of Text From Variable To File
Print #g, ResultStr
Else
If Counter > 65005 And Left(ResultStr, 19) = "\par NORTHERN TRUST" Then
'Output One Line Of Text From Variable To File
Print #g, ResultStr

or case1. something = 65000
case2.something = "Northern Trust"
or LOOP UNTIL SOMETHING


I don't know what I'm talking about but I can't believe Excel doesn't have a way to do this.

thanks

Walt

 

[Norie]

Walt

Did you even try the code I posted?

 

[walt1122]

yes I'm trying but I can't get past the

Set wsRecords = Worksheets("Records")

not sure what the problem is.

thanks

Walt

 

[walt1122]

sorry would help if i said what the error code was.

Set wsRecords = Worksheets("Records")

run time error '9' subscript out of range

thanks

Walt

 

[Norie]

Walt

I named a sheet Records.

 

[walt1122]

OH, sorry just goes to show how little I know! I'm home now and don't have the spreadsheets with me so I will give you a break and not bother you over the weekend. I wish I could take advantage of the Access. Problem is I'm trying to do this for the auditors who will want to check the data from the .TXT files and use it in their review of our accounts, I'm not allowed to be involved in any way with the data or the selection. But as you have seen the format of the data stinks and does not lend itself to being easily manipulated. So I'm trying to set up the code so they can just hit a few buttons and have the data mean something and we can use it. Cause in the end I will be the one having to do the mail merge to send outletters to these people and to confirm from them and from other systems everything was handled properly

thanks and have a good weekend

Walt

 

[Norie]

Walt

I'm afraid that your last post makes no sense.

You talk about 'auditors'.

In my experience such people would be a bit wary of any code, no matter what it did.

The last major project I was involved with was to do with misselling of financial products.

It was regularly (monthly) checked by auditors eg the FSA(Financial Services Authority, a government body), Ernst & Young etc.

The data I was working with was downloads from ancient legacy databases.

I would import that into Access, manipulate it as needed, then export it to Excel.

Every step of what I did was looked at by them, especially the downloads/imports/exports.

 

[walt1122]

Exactly! That is why I can not work on the actual files without them present. Considering we are looking at 150,000 people times 12 payments each (one for each month) the auditors certainly aren't going to sit here and watch me play with this much data. This can take weeks! Therefore I want to have the code do the manipulation and not me. I can give them the code and the files directly from the CD from the "record keepers" and they can do it themselves. From the sample data you can easily see that it is not in a user friendly format. Our "records keeper" says it would take many programming hours and at a significant expense to us to do what I'm trying to do here. The totals I develop must match the master file the data is drawn from. It is the names, addresses and payment amounts are what we are interested in for this exercise. Since it is so difficult to get this data easily from the reports we are working on I want to have the system do the work. Right now everything is in place to have them do this except for the step we have been working on. They can use code to find the file, convert it into 65500 Row blocks onto separate .TXT files and then have other code import the rows and reconfigure the data ( very simple VLOOKUPs and OFFSETs) to get it the way we need it all without me touching any of it. Just the issue of it breaking between a persons data is the problem part. Because the rows go across and down vital information is not captured properly. I have already got the go ahead from the auditors otherwise I would not have started. They too hate the file in it's current form.

thanks

Walt

 

[Norie]

Walt

Have you considered using Access?

On the project I mentioned there was a guy who was doing data manipulation using Excel.

He had code that took a whole day to run.

He left for some reason and I was asked to look at what he was doing.

After deciphering his code I imported the data into Access, set up a simple query and got the same result in about 5 seconds.

By the way he was getting paid about £300 a day.