Error in large file import macro

Scott Calkins via OfficeKB.com · 05-19-2005, 05:06 PM

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.

--
Message posted via http://www.officekb.com

Jim Thomlinson · 05-19-2005, 05:06 PM

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" wrote:

> 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.
>
> --
> Message posted via http://www.officekb.com
>

Scott Calkins via OfficeKB.com · 05-20-2005, 12:06 AM

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.

--
Message posted via http://www.officekb.com

David Sisson · 05-20-2005, 04:06 AM

On Thu, 19 May 2005 20:31:03 GMT, "Scott Calkins via OfficeKB.com"
<[email protected]> wrote:

> 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 · 05-20-2005, 07:06 PM

On Thu, 19 May 2005 20:31:03 GMT, "Scott Calkins via OfficeKB.com"
<[email protected]> wrote:

> 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 · 08-18-2005, 12:05 PM

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

"Scott Calkins via OfficeKB.com" wrote:

> 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.
>
> --
> Message posted via http://www.officekb.com
>

spkr29 · 08-18-2005, 12:05 PM

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

"Scott Calkins via OfficeKB.com" wrote:

> 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.
>
> --
> Message posted via http://www.officekb.com
>

spkr29 · 08-18-2005, 12:05 PM

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

"Scott Calkins via OfficeKB.com" wrote:

> 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.
>
> --
> Message posted via http://www.officekb.com
>

BeagleWillie · 09-30-2005, 08:05 AM

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?

"Scott Calkins via OfficeKB.com" wrote:

> 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.
>
> --
> Message posted via http://www.officekb.com
>