import exceeds 65,535 rows

[jdttek]

I am opening a .rtf or txt file in Excel, with code to delimit the file during the conversion process. Excel only allows 65,535 rows. Many of the import rows are blank. Is there any way to exclude the blank rows during the import process? That would get me below 65,000 rows.

 

[scking]

You could write an excel macro to count the rows prior to opening the text file for import. If the rows were > 65535 then build a new file by reading each line and adding it to the temporary file only if the line were not blank. Then the macro would know whether to automatically import the original file (if < 65,535) or the temporary file (if the original file count were > 65,535. Caution, the temporary file may also be greater that the maximum so you would want to test it before import. -------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[jdttek]

scking
Thanks for the suggestion and it sound as if it would work. THe macro I have written (that works perfectly on text / rtf files <65,535 is
Workbooks.OpenText FileName:=&quot;C:\WINDOWS\TEMP\AR_Report_pg288-578.rtf&quot;, _
Origin:=xlWindows, StartRow:=32767, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 9), Array(21, 1), Array(44, 1), Array(53, 1), Array(99, 1), Array(105, 1), _
Array(225, 1))

I have tried to force the start row at 65,536 but it always defaults back to line 32,767.

I succeded in manually splitting a text file in two, importing each into seperate excel files, deleting the blank lines and extra headers and then combining the two smaller excel files which got me down to 35,000 rows (since the blank lines and headers were now gone.)

Could you pls give me some pointers on how to structure the macro, as you suggested, to:
1. count the lines in the text document before importing
2. add only the non-blank lines to the import file PRIOR TO the parsing code shown above.

Thanks. Your help much appreciated.

jdttek

 

[scking]

I don't know of any quick and easy way with text files. Try setting up a counter variable for each row, read to 65,535 + 1 to determine if it is too big. I'm sure someone else may have a better way of counting the line because I don't do a lot of text file IO.
-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[lambuhere1]

Hi

I use the following code to import data of more than 65536 rows. Hope this helps.

Sub ImportText_file()
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
FileName = InputBox(&quot;Please enter the Text File's name with its path, e.g. c:\test.txt&quot
'If Error Then End
If FileName = &quot;&quot; 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 = &quot;Importing Row &quot; & _
Counter & &quot; of text file &quot; & FileName
Line Input #FileNum, ResultStr
If Left(ResultStr, 1) = &quot;=&quot; Then
ActiveCell.Value = &quot;'&quot; & ResultStr

Else
ActiveCell.Value = ResultStr
End If

If ActiveCell.Row = 65536 Then
ActiveWorkbook.Sheets.Add
Else
ActiveCell.Offset(1, 0).Select
End If
Counter = Counter + 1
Loop

Close
Application.StatusBar = False

End
End Sub


Thanks

Ram P

Caution: This takes a lot of time and system resources. Hence I would recommened you to run it in the mights when u r not working