Importing last 10 entries of a .csv file
 

Does anyone have a good way to import only the last 10 lines of a .csv file?
I was using 'Open Filename For Input As #1' etc, but I couldn't figure out
how to only include a set number of lines to import. Thanks in advance.

Importing last 10 entries of a .csv file
 

As far as I know you must open them and copy the last ten rows

This example will do it but there is no error check on this moment if there are less then 10 rows in the file
It will loop through all csv files in C:\Data and copy the data in the first sheet of the workbook with the code
This file must be outside the folder with the csv files

Sub Example2()
Dim MyPath As String
Dim FilesInPath As String
Dim MyFiles() As String
Dim SourceRcount As Long
Dim Fnum As Long
Dim mybook As Workbook
Dim basebook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim lrow As Long

'Fill in the path\folder where the files are
MyPath = "C:\Data"


'Add a slash at the end if the user forget it
If Right(MyPath, 1) < "\" Then
MyPath = MyPath & "\"
End If


'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.csv")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If


On Error GoTo CleanUp
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
'clear all cells on the first sheet
basebook.Worksheets(1).Cells.Clear
rnum = 1


'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath < ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop


'Loop through all files in the array(myFiles)
If Fnum 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
lrow = LastRow(mybook.Sheets(1))

Set sourceRange = mybook.Worksheets(1).Range("A" & lrow - 10 & ":IV" & lrow).EntireRow
SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Range("A" & rnum)

sourceRange.Copy destrange

rnum = rnum + SourceRcount
mybook.Close savechanges:=False
Next Fnum
End If
CleanUp:
Application.ScreenUpdating = True
End Sub


Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


--
Regards Ron de Bruin
http://www.rondebruin.nl


"TAndrew" wrote in message ...
Does anyone have a good way to import only the last 10 lines of a .csv file?
I was using 'Open Filename For Input As #1' etc, but I couldn't figure out
how to only include a set number of lines to import. Thanks in advance.

Importing last 10 entries of a .csv file
 

Andrew,
Could you issue a query ?
You can use 'TOP x' to get the first x records, so if you ORDER BY some
column to reverse your recordset, you can it all in a single query:
SELECT TOP 10 * FROM YourCSVFile.csv ORDER BY SequenceNumber DESC
(Unless there something like "BOTTOM" ?

NickHK

"TAndrew" wrote in message
...
Does anyone have a good way to import only the last 10 lines of a .csv
file?
I was using 'Open Filename For Input As #1' etc, but I couldn't figure out
how to only include a set number of lines to import. Thanks in advance.

Importing last 10 entries of a .csv file
 

I am not sure this will work in your .csv file. but try it.

Sub readlast10()
Dim filename
Dim FileNum As Integer
Dim Counter As Long, maxrow As Long
Dim WorkResult() As String
Dim endnum As Long
Dim co As Long, l As Long
Dim i As Long
On Error GoTo ErrorCheck
endnum = 9
ReDim WorkResult(endnum)
co = 0
maxrow = Cells.Rows.Count
filename = Application.GetOpenFilename(FileFilter:="All File
(*.*),*")
If VarType(filename) = vbBoolean Then
Exit Sub
End If

Application.ScreenUpdating = False
Application.EnableEvents = False
Counter = Cells(Cells.Rows.Count, "a").End(xlUp).Row
If Counter < 1 Then
Counter = Counter + 1
End If
FileNum = FreeFile()
Open filename For Input As #FileNum

Do While Not EOF(FileNum)
Line Input #FileNum, WorkResult(co)
co = co + 1
l = l + 1
If co endnum Then
co = 0
End If
Loop
Close FileNum

If l endnum Then
For i = co To endnum
If WorkResult(i) < "" Then
Cells(Counter, 1) = """" & WorkResult(i)
Cells(Counter, 1).TextToColumns _
Destination:=Cells(Counter, 1), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False, _
Comma:=True, Space:=False, Other:=False
End If
Counter = Counter + 1
If Counter maxrow Then
MsgBox "data have over max rows"
Exit For
End If
Next
End If
For i = 0 To co - 1
If WorkResult(i) < "" Then
Cells(Counter, 1) = """" & WorkResult(i)
Cells(Counter, 1).TextToColumns _
Destination:=Cells(Counter, 1), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False
End If
Counter = Counter + 1
If Counter maxrow Then
MsgBox "data have over max rows"
Exit For
End If
Next
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
ErrorCheck:
Application.EnableEvents = True
Application.ScreenUpdating = True
MsgBox "An error occured in the code."
End Sub

keizi

"TAndrew" wrote in message
...
Does anyone have a good way to import only the last 10 lines of a .csv
file?
I was using 'Open Filename For Input As #1' etc, but I couldn't figure
out
how to only include a set number of lines to import. Thanks in
advance.