elmama
A selection of topics on IT and its application to finance.
Send me your comments, questions or suggestions by clicking here
Two different ways to quickly read a delimited text file (e.g CSV) into Excel
Below are the two fastest ways I know of to read a delimited text file into Excel without any user input to the process. I’m assuming the file is a CSV but it will work with any field delimited text file e.g pipe separated etc … For example if it was a # delimited file, for method 1 you would change this line:
.TextFileCommaDelimiter = True to
.TextFileCommaDelimiter = False and this line
.TextFileOtherDelimiter = "" & Chr(10) & "" to
.TextFileOtherDelimiter = “#”.
For method2 you would change the line
l = Replace(quickread(0), ",", vbTab) to
l = Replace(quickread(0), "#", vbTab)
Method 1
Sub ImportTextFile1()
Dim fName As String
fName = "d:\test.csv"
‘ Uncomment the GetOpenFilename line below line if you want to display a dialog box
‘ allowing the user to manually select a file to import
‘ fName = Application.GetOpenFilename("Text Files (*.csv), *.csv")
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fName, _
Destination:=Range("$A$1"))
.Name = "sample"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "" & Chr(10) & ""
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
Method 2
Sub ImportTextFile2()
Dim intFileNum%, bytTemp As Byte, intCellRow%
Dim mydata As DataObject
Set mydata = New DataObject
Application.StatusBar = "Reading in data file"
thesheet = “Data”
WorksheetExists = Evaluate("ISREF('" & thesheet & "'!A1)")
If (Not (WorksheetExists)) Then
' create it otherwise select it and clear its contents
Sheets.Add.Name = thesheet
Else
‘ it exists so clear down any existing data thats on it
Sheets(thesheet).Select
Cells.Select
Selection.Delete Shift:=xlUp
End If
thefile = “d:\test.csv”
intFileNum = FreeFile
Open thefile For Binary Access Read As intFileNum
Dim strBuff As String: strBuff = Space(LOF(intFileNum))
Dim l As String
Get intFileNum, , strBuff
Close intFileNum
'split the file on record separator vbcrlf
quickread = Split(strBuff, vbCrLf)
‘ replace the field delimiter with TAB
l = Replace(quickread(0), ",", vbTab)
‘ put it into the paste buffer
mydata.SetText l
mydata.PutInClipboard
Cells(1, 1).Select
ActiveSheet.Paste
So, which version is faster ?
I tested with a 166,000 record CSV file located on my PC so no network issues to take account of. Each record was approx 130 bytes wide consisting of 11 comma separated fields.
On my PC method 1 took 4 seconds and method 2 took 12 seconds