elmama

​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking h​ere

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