Fastest way to read large CSV files as text.

[shadow12345]

So, I already have something set up that loops through all csv files in a given folder and applies some code to them. The bit i'd like to try and speed up is the import as text that im using. Below is the code im using to import the CSV file as text (must be text as one file is a 20 digit number and that doesn't open correctly without it being text)


My question is, can i do this faster? rather than using a query table im wondering if i could use scripting dictionary somehow?

Sub importdata

folderpath = "C:\text\"
filename = "1.csv"


    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & folderpath & filename _
        , Destination:=Range("$A$1"))
        .Name = ""
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 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

 

[Rick Rothstein]

I am not sure this offers any additional benefit over the method VBA Geek gave you, but you may want to try it anyway...

Sub GetFileCVS()
  Dim FileNum As Long, Rw As Long, Rec As Variant
  Dim TotalFile As String, Records() As String, Fields() As String
  FileNum = FreeFile
  Open "C:\text\1.csv" For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  Records = Split(TotalFile, vbNewLine)
  On Error Resume Next
  For Each Rec In Records
    Fields = Split(Rec, ",")
    Rw = Rw + 1
    Cells(Rw, "A").Resize(, UBound(Fields) + 1) = Fields
  Next
  On Error GoTo 0
End Sub

 

[shadow12345]

Hmmm, thats seems to have run a little quicker, ill toy with that one.

ill report back once i run it overnight against the massive files.

 

[shadow12345]

also... how do i tell if it ends in a carriage return?

 

[Rick Rothstein]

also... how do i tell if it ends in a carriage return?

All lines in a text files on a PC end with a Line Feed followed by a Carriage Return whereas on a Mac they end with just a Carriage Return. If you look in my code, you will see I split the individual line using vbNewLine... that constant is defined as a Line Feed followed by a Carriage Return on PCs and as just a Carriage Return on Macs. Also note the On Error Resume Next statement... that insures empty lines are ignored (including the one normally/sometimes found at the end of the file).