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]

My fault, I reset the data stream to the next column, but did not set it back to Row 1, so it failed. This code corrects this error:


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
ActiveSheet.Cells(1, myCol).Select
Else
ActiveCell.Offset(1, myCol).Select
End If

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

 

[walt1122]

Hi Joe Was, hate causing you all these problems but it is still giving me the 1004 defined object error?? It gets to the same spot, maybe up one line? 65535?? but the error stops it all the same.

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

any thoughts??

thanks

Walt

 

[Joe Was]

It ran on my file correctly?

Do you have it in a standard Module, like: Module1?

 

[Norie]

Walt

What do you actually want the code to do?

As far as I can see, though of course I may be wrong, Joe's code is reading
in 65535 rows to a column, then reading the next 65536 rows to the next column and so on.

 

[walt1122]

Hi Joe Was, yes it is in the Module 13 of the file.

I sorry, I don't know what I might be doing wrong. All I can tell you is I start it and it runs and it ends abruptly saying 1004 error. I look at the sheet it created from the data and it looks fine all the way down to the bottom at line 65535 it has this
\par SILVEIRA, LOIS M 6/01/2004 ACH D123456 Exactly what we would suspect. then a blank line and the status bar says
Importing rows 65791!!!!! ( appears to be from the right file). So where are the rows that make up the difference between 65535 and 65791 I don't know. Why don't I ever see the next sheet of data I don't know.
Problem is if and when I do get it to run will it do what I need. In the end I'm trying to find something that will not stop in between a persons data. I have code that does that now. I can tell that code what line to stop on and then start adding new data to a new Tab until the end of the file is reached. I would like to able to have a complete list of the persons payment history all 12 months of check numbers and amounts on one page not across two. I'm sorry for driving us all crazy over this. I just thought it would be easy to have a conditional statement built into the counter. So it would go to the counter total and if the second condition was met like counter total > 65000 AND row blank then start new sheet.

guess I was wrong.

thanks for all the help and if you can figure out what is going wrong that would be great but I don't want to waste your time. You've worked hard enough on this already!!

Walt

 

[walt1122]

Hi Joe Was, don't know for sure but I just re-ran the macro and took a close look at the output after I got the error 1004. Yes the file does go down to 65535 but it also goes ACROSS to IV256 meaning it isn't going to a new sheet but going across one column at a time trying to put in the data. Guess we know where the the extra data is going!


Norie, all along I have been trying to get a solution, some direction, some lines of code so I can get the data to not stop between payment details of a person. If you look at the original request I have the sample data on that spreadsheet.
http://www.mrexcel.com/board2/viewtopic.php?t=193885&start=0

I can not have the code put, for example, the name on one sheet and their address on another?? I need to have it all together so i can eventually clean it up and do a sub total on it. As I have mentioned I do have code from others that already can break up a .TXT file into smaller more manageable pieces that Excel can handle <65536 lines but that code just does what I don't wat it cuts the lines based on the counter number and not on some IF statement that takes into account the counter AND that there is still a block of data that should not be seperated onto two sheets.

thanks again for the effort.

Walt

 

[Norie]

Walt

I'm pretty sure I know what you want but the example data you've posted
hasn't been very useful.

Also I think it's probably going to be pretty hard to go through each line of data
and check for both conditions.

For example what do you do when you find that the counter for a particular set of
records pushes over the 65535 limit?

How do you go back to the beginning for that set of records?

The code I posted previously, though untested, should theoretically create a seperate text
file for each set of records using NORTHERN... as a delimiter/sentinel.

 

[Joe Was]

OK, Changed the code to NOT do it all on one sheet, so it now Adds a New sheet every 65000 rows. I tested it and it works. But then again so did my other code, so I don't know?

Also on my last code the row number is the counter so if you change that row number it changes which row number trips the shift!

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



In any case, try this:


Sub largeFileImport()
'Standard module code, like: Module1!
Dim ResultStr$, FileName$
Dim Counter As Double
Dim FileNum%

FileName = InputBox("Please enter the Text File's name, e.g. test.txt")

If FileName = "" Then End
FileNum = FreeFile()

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

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 = 65000 Then
ActiveWorkbook.Sheets.Add
Else
ActiveCell.Offset(1, 0).Select
End If

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

 

[walt1122]

sorry guys, maybe it's me!
I will try to explain one more time and then thank you for your time and effort but will just shut up.
I need code that will continue to import in data from a .TXT file until at some predetermined spot such as line Row 65000 it starts to look for some special value or something that will trigger it to stop adding data to this particular sheet, will add in a new sheet and continue the process again starting from where it left off and continues to again take in the data from the .TXT file until the same set of contitions are met again. It continues to do this until data has once again filled up the sheet to Row 65000 and then the trigger what ever that is is found. I have suggested that the words "Northern Trust" could be that trigger or even a "Blank" Row on the .TXT file would mean there is a break in the personal record for the individual and this is a logical place to stop and go to a new sheet and continue with a new persons record there.

I had thought that it would be an easy task for people like yourselves with such a high degree of understanding of Excel and VB to be able to put together an IF statement? that combined a counter with a search to come up with a way to do what I'm suggesting. Actually you both have done a piece of it already. Joe Was I think yours will probably do the half that says stop here and go to a new sheet and start up again and Norie yours says every time you see "Northern Trust " it start a new sheet. So if they were combined together so that Joe Was's got us down to some line before 65000 and then the search found Northern Trust and the programming now paused added a new sheet and continued inporting in the data from the .TXT it should work. Maybe a LOOP? counter > 65000 if yes + active row search = Northern Trust? then new sheet, I don't know. This is way beyond my grasp! That is why I'm here.

Joe Was, thank you for all the help and I will try the last piece of code. For some reason the other ones didn't build a new sheet they just moved over one column down one row put in the one line of code saw the counter was past the > counter value moved one column down one row put in the one line of code saw the counter was past the > counter value moved one column down one row put in the one line of code saw the counter was past the > counter value moved etc., etc. till it hit the wall at column IV coudn't go anymore so it returned the 1004 error.


Norie, my thanks to you also, yes yours did work fine, it saw the words "Northern Trust" and made a new sheet. Problem is "Norhtern Trust" is on ever page of the report It is par of the "Header" and as I mentioned on the first question I posted that means on the report it will show up ever 40 or 50 lines and maybe 18,000 times. So by itself the code just picks up the 40 or 50 lines and then makes a new sheet. My system blow up somewhere around 193 new sheets on any Excel file.

So if there is some way to combine the two thoughts or some other way that would be great

otherwise

thank you very much and I will just have to do it manually. not a big job it just limits what I wanted to do with it after this step was automated.

Walt

 

[Norie]

Walt

This is how I would do it I think.

1 Import each 40/50 rows for each set of records to a new sheet.

2 In another sheet check how many records already exist - easily done.

3 If the no of existing records plus the no of records from stage 1< 65535 then append the records.

4 Delete/clear sheet from stage 1.

5 Import next set of records and repeat 3,4.

6 If at some point the no of records is going to exceed 65535 then add another sheet and continue, adding the records to that sheet

7 And so on.

Does that make sense to you?

I'm just off out but I'll try and post some code later, or maybe Joe will beat me to it.