Error in large file import macro

[Scott Calkins via OfficeKB.com]

I am trying to use the macro that many people linked to here for importing
data longer than the 65536 rows permited in excel. Wen I run it, it loads
in the first line ok then errors out. It gives me a "Input past end of
file" error at >Line Input #FileNum, ResultStr<. The full macro is as
follows:

Sub LargeFileImport()

Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
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 = 65536 Then
ActiveWorkbook.Sheets.Add
Else
ActiveCell.Offset(1, 0).Select
End If
Counter = Counter + 1
Loop
Close
Application.StatusBar = False

End Sub


Due to I am loading in data that 1s 16 columns by 200000+ rows I would like
to get this working.

 

[Jim Thomlinson]

I am not trying to rain on your parade but an excel file with 200,000+
records can be very problematic to deal with. Is it possible to load the text
file to a database such as Access instead. Access is much better suited to
deal with files of that size. If you want to use Excel as the front end that
is still easy to do via "Get External Data" queries or pivot tables linked
directly to the Access source. I have worked with files of that size and they
are nothing but trouble in Excel. Just my 2 cents...

Jim Thomlinson

 

[Scott Calkins via OfficeKB.com]

I tried the Access route. It took 20 minutes to load the comma delimites
file, and it keeps trying to load it on all one row. My only other
alternitive besides this macro it splitting the file manualy in notepad
each day.

 

[David Sisson]

that many people linked to here for importing

What does this mean?

data longer than the 65536 rows permited in excel. Wen I run it, it loads
in the first line ok then errors out. It gives me a "Input past end of
file" error at >Line Input #FileNum, ResultStr<.

This usually means that Line Input read past the end of the file
before encountering a chr$(13), the delimiter Line Input uses to
determine lines. (or it might be a line feed, I can't remember)

When you look at it in Notepad, is each item on it's own line? If
not, then the file doesn't have the delimiter it needs to use Line
Input.

 

[David]

that many people linked to here for importing

What does this mean?

data longer than the 65536 rows permited in excel. Wen I run it, it loads
in the first line ok then errors out. It gives me a "Input past end of
file" error at >Line Input #FileNum, ResultStr<.

This usually means that Line Input read past the end of the file
before encountering a chr$(13) or LF Chr$(10), the delimiter Line Input
uses to determine lines.
When you look at it in Notepad, is each item on it's own line? If
not, then the file doesn't have the delimiter it needs to use Line
Input.

 

[spkr29]

HI,

Perhaps, you could create two .csv files. Then, you could import each. Try
the following code.

Sub Capture_Append_API_Files()
Dim ColumnsArray
Dim Pathfile
Dim TestLine
Close:

Variable = 1
Pathfile = Cells(5, 3) & Cells(5, 4)
Open Pathfile For Input As #3 ' Open file.
Line Input #3, HeadingI
Contar = 1
Do While Not EOF(3) ' Loop until end of file.
Contar = Contar + 1
Line Input #3, Inform
'Debug.Print Inform
Loop
Close:

Limitinf = 0
LimitSup = 60000
For Archivo = 1 To Round(Contar / 60000, 0) + 1
PathSaveFile = Range("SavePath") & "Part" & Archivo & ".csv"
On Error Resume Next
Open PathSaveFile For Append As #1
Open Pathfile For Input As #3 ' Open file.
If Archivo > 1 Then
Print #1, HeadingI
End If
counter = 0
Do While Not EOF(3) ' Loop until end of file.
counter = counter + 1

If counter = LimitSup Then GoTo NextArchivo

If counter > Limitinf And counter < LimitSup Then

Line Input #3, Linex ' Read line into variable.
' Debug.Print , Linex
Print #1, Linex ' Print to the new file
End If

Loop
NextArchivo:
Limitinf = Limitinf + 60001
LimitSup = LimitSup + 60000
NuevoArchivo:
Close #1
Next

OtroArchivo:


End Sub

 

[spkr29]

HI,

Perhaps, you could create two .csv files. Then, you could import each. Try
the following code.

Sub Capture_Append_API_Files()
Dim ColumnsArray
Dim Pathfile
Dim TestLine
Close:

Variable = 1
Pathfile = Cells(5, 3) & Cells(5, 4)
Open Pathfile For Input As #3 ' Open file.
Line Input #3, HeadingI
Contar = 1
Do While Not EOF(3) ' Loop until end of file.
Contar = Contar + 1
Line Input #3, Inform
'Debug.Print Inform
Loop
Close:

Limitinf = 0
LimitSup = 60000
For Archivo = 1 To Round(Contar / 60000, 0) + 1
PathSaveFile = Range("SavePath") & "Part" & Archivo & ".csv"
On Error Resume Next
Open PathSaveFile For Append As #1
Open Pathfile For Input As #3 ' Open file.
If Archivo > 1 Then
Print #1, HeadingI
End If
counter = 0
Do While Not EOF(3) ' Loop until end of file.
counter = counter + 1

If counter = LimitSup Then GoTo NextArchivo

If counter > Limitinf And counter < LimitSup Then

Line Input #3, Linex ' Read line into variable.
' Debug.Print , Linex
Print #1, Linex ' Print to the new file
End If

Loop
NextArchivo:
Limitinf = Limitinf + 60001
LimitSup = LimitSup + 60000
NuevoArchivo:
Close #1
Next

OtroArchivo:


End Sub

 

[spkr29]

HI,

Perhaps, you could create two .csv files. Then, you could import each. Try
the following code.

Sub Capture_Append_API_Files()
Dim ColumnsArray
Dim Pathfile
Dim TestLine
Close:

Variable = 1
Pathfile = Cells(5, 3) & Cells(5, 4)
Open Pathfile For Input As #3 ' Open file.
Line Input #3, HeadingI
Contar = 1
Do While Not EOF(3) ' Loop until end of file.
Contar = Contar + 1
Line Input #3, Inform
'Debug.Print Inform
Loop
Close:

Limitinf = 0
LimitSup = 60000
For Archivo = 1 To Round(Contar / 60000, 0) + 1
PathSaveFile = Range("SavePath") & "Part" & Archivo & ".csv"
On Error Resume Next
Open PathSaveFile For Append As #1
Open Pathfile For Input As #3 ' Open file.
If Archivo > 1 Then
Print #1, HeadingI
End If
counter = 0
Do While Not EOF(3) ' Loop until end of file.
counter = counter + 1

If counter = LimitSup Then GoTo NextArchivo

If counter > Limitinf And counter < LimitSup Then

Line Input #3, Linex ' Read line into variable.
' Debug.Print , Linex
Print #1, Linex ' Print to the new file
End If

Loop
NextArchivo:
Limitinf = Limitinf + 60001
LimitSup = LimitSup + 60000
NuevoArchivo:
Close #1
Next

OtroArchivo:


End Sub

 

[BeagleWillie]

This program appears to work OK on a sample file of text. Perhaps there is
something in the file you are trying to import which causes the error. What
is the content and format of the file you are importing?