How to import big txt file into excel fast!!!

Hello I'm trying to import big txt file (800 000 lines and 10 columns) into excel sheet as fast as possible. I'm not sure if the code below is doing it efficiently but there is another problem with this code. After importing data into sheet1 all other
data in sheet1 are cleared. Do you have any suggestion how to change the code so all data from column K on are saved and not cleared? Also do you think there is more efficient way how to import big txt file into excel? Thank you for help.


Option Explicit

Public Sub test()
Columns("A:M").Select
Selection.ClearContents
Range("A1").Select
copyDataFromCsvFileToSheet "E:\Robota\quotes.txt", ",", "Sheet1"
Columns("A:A").EntireColumn.AutoFit
End Sub

Private Sub copyDataFromCsvFileToSheet(parFileName As String, parDelimiter As String, parSheetName As String)

Dim data As Variant

data = getDataFromFile(parFileName, parDelimiter)
If Not isArrayEmpty(data) Then
With Sheets(parSheetName)
.Cells.ClearContents
.Cells(1, 1).Resize(UBound(data, 1), UBound(data, 2)) = data
End With
End If

End Sub

Public Function isArrayEmpty(parArray As Variant) As Boolean
'Returns false if not an array or dynamic array that has not been initialised (ReDim) or has been erased (Erase)

If IsArray(parArray) = False Then isArrayEmpty = True
On Error Resume Next
If UBound(parArray) < LBound(parArray) Then isArrayEmpty = True: Exit Function Else: isArrayEmpty = False

End Function

Private Function getDataFromFile(parFileName As String, parDelimiter As String, Optional parExcludeCharacter As String = "") As Variant
'parFileName is supposed to be a delimited file (csv...)
'parDelimiter is the delimiter, "," for example in a comma delimited file
'Returns an empty array if file is empty or can't be opened
'number of columns based on the line with the largest number of columns, not on the first line
'parExcludeCharacter: sometimes csv files have quotes around strings: "XXX" - if parExcludeCharacter = """" then removes the quotes


Dim locLinesList() As Variant
Dim locData As Variant
Dim i As Long
Dim j As Long
Dim locNumRows As Long
Dim locNumCols As Long
Dim fso As Variant
Dim ts As Variant
Const REDIM_STEP = 10000

Set fso = CreateObject("Scripting.FileSystemObject")

On Error GoTo error_open_file
Set ts = fso.OpenTextFile(parFileName)
On Error GoTo unhandled_error

'Counts the number of lines and the largest number of columns
ReDim locLinesList(1 To 1) As Variant
i = 0
Do While Not ts.AtEndOfStream
If i Mod REDIM_STEP = 0 Then
ReDim Preserve locLinesList(1 To UBound(locLinesList, 1) + REDIM_STEP) As Variant
End If
locLinesList(i + 1) = Split(ts.ReadLine, parDelimiter)
j = 10 'number of columns
If locNumCols < j Then locNumCols = j
i = i + 1
Loop

ts.Close

locNumRows = i

If locNumRows = 0 Then Exit Function 'Empty file

ReDim locData(1 To locNumRows, 1 To locNumCols + 1) As Variant

'Copies the file into an array
If parExcludeCharacter <> "" Then

For i = 1 To locNumRows
For j = 0 To UBound(locLinesList(i), 1)
If Left(locLinesList(i)(j), 1) = parExcludeCharacter Then
If Right(locLinesList(i)(j), 1) = parExcludeCharacter Then
locLinesList(i)(j) = Mid(locLinesList(i)(j), 2, Len(locLinesList(i)(j)) - 2) 'If locTempArray = "", Mid returns ""
Else
locLinesList(i)(j) = Right(locLinesList(i)(j), Len(locLinesList(i)(j)) - 1)
End If
ElseIf Right(locLinesList(i)(j), 1) = parExcludeCharacter Then
locLinesList(i)(j) = Left(locLinesList(i)(j), Len(locLinesList(i)(j)) - 1)
End If
locData(i, j + 1) = locLinesList(i)(j)
Next j
Next i

Else

For i = 1 To locNumRows
For j = 0 To UBound(locLinesList(i), 1)
locData(i, j + 1) = locLinesList(i)(j)
Next j
Next i

End If

getDataFromFile = locData

Exit Function

error_open_file: 'returns empty variant
unhandled_error: 'returns empty variant

End Function

hi,
perhaps this can help you, download this add in for excel and please try.

http://www.rondebruin.nl/win/addins/rdbmerge.htm

i'm alrady try it and it works...