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

 

[VBA Geek]

is it comma delimited?

 

[shadow12345]

yes it is

 

[VBA Geek]

You wanna try this:

Sub impTxt()
Const folderPath As String = "C:\text\"
Const fileName   As String = "1.csv"
Dim varAllData As Variant


varAllData = Split(CreateObject("Scripting.FileSystemObject").GetFile(folderPath & fileName).OpenAsTextStream.ReadAll, vbCrLf)
With Range("A1").Resize(UBound(varAllData) + 1)
    .Value = Application.Transpose(varAllData)
    .TextToColumns , xlDelimited, xlTextQualifierNone, False, False, False, True, False, False
End With
End Sub

 

[shadow12345]

I get a run time error '13: type mismatch on the below ... any ideas?

    .Value = Application.Transpose(varAllData)

 

[VBA Geek]

varAllData should be an array which is storing each line of your text file

add a "Watch" to it and see What it is after


varAllData = Split(CreateObject("Scripting.FileSystemObject").GetFile(folderPath & fileName).OpenAsTextStream.ReadAll, vbCrLf)

 

[shadow12345]

ha, now i get a out of memory error. I chopped most of the file data away (it was 100 mb) down to 4kb just to test it and it runs ok now. With the watch on I got an out of memory at line 7 error, so im goign to try running it on a more powerful machine and see if that helps?

 

[VBA Geek]

a 100MB txt file has quite a lot of lines. if they're more than 1,048,576 then you cannot import them in just 1 excel sheet

 

[shadow12345]

its a maxium of 300,000 rows because that was set at an SQL level for the export data run over night... but its col a to col AK so its pretty wide.

 

[VBA Geek]

a normal pc should be able to handle an array of size 300,000
is each line delimited by a carriage return?