VBA: How to read very very big csv file into excel quickly without excel's auto formatting?

Copper Contributor

When I use `Workbooks.open` method to open the csv file I want to use, it takes me 30 seconds.

However, Excel foramtted the data automatically during file opening. It formats some values like date, currency, etc.

 

To avoid this, now I use QueryTable to load a csv file instead.

Nevertheless, it's horribly slow. It took me over 90 seconds to open the same file.
I believe that I've already optimize the codes when I am using. (Or maybe not? any suggestion is appreciated)

Is there any other good idea to have excel open a csv file as quick as possible as Workbook.open without formatting it?

 

By the way, the data in my csv file will certainly contains comma in the value.

I need a safe way (without parsing comma by myself) to open the csv file.

 

 

 

Public Function ImportCsv(path As Variant, TargetWb As Workbook) As Worksheet
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    TargetWb.Activate
    Dim QueryTableName As String
    QueryTableName = "tmp_" & Format(Now, "hhmmss") & Right(Format(Timer, "0000"), 4)  'Use time stamp since delete might fail due to unexpected reason.
 
    ActiveWorkbook.Queries.Add Name:=QueryTableName, Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "      Source= Csv.Document(File.Contents(""" & path & """),[Delimiter="","", Columns=null, Encoding=932, QuoteStyle=QuoteStyle.Csv])," & Chr(13) & "" & Chr(10) & "    Transformation = Table.TransformColumnTypes( Source, Table.ToRows(Table.FromColumns({Table.ColumnNames(Source), List.Repeat({type text}, List.Count(Table.ColumnNames(Source))  )})))" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Transformation"

    ActiveWorkbook.Worksheets.Add  'Only ActiveSheet works for some properties of QueryTable.
    With ActiveSheet.ListObjects.Add(LinkSource:=False, SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & QueryTableName & ";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & QueryTableName & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = False
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .ListObject.DisplayName = QueryTableName
        .ListObject.ShowHeaders = False
        .ListObject.ShowTableStyleRowStripes = False
        .Refresh BackgroundQuery:=False
        .MaintainConnection = False
    End With
    ActiveSheet.Rows("1:1").Delete Shift:=xlUp  'Remove empty row which was once header.
    'ActiveSheet.QueryTables(QueryTableName).Delete  'Delete unneeded TempQueryTable.
    Set ImportCsv = ActiveSheet
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Function

 

 

              

5 Replies

Hi @cindy_lu 

 

my proposal would be to use Power Query for any sort if import, so also for CSV files. I can't tell if it is faster than your solution (maybe not), but there you have highest flexibility to adjust the import and transform it to a structure that fits your needs, without coding.

 

If VBA is needed anyway, you could just trigger the Power Query from there. 

Use power query connection only option by way of creating connecting to csv file.
https://excel.officetuts.net/examples/open-large-csv/
Thanks for the possible suggestion.
However, I need to load all the data at once since I will later use other macro to edit them.
The link looks like load the data dynamically, which is not what i want to do.
Thanks for the suggestion.
However, this function is only one small part of the automation process I wrote in VBA.
Also, I don't need any other flexibility this time :D

Or do you mean that I can start the Power Query then load all the data by VBA automatically?

Hi @cindy_lu 

 

yes, that's what I meant. Create a new query/connection with Power Query and load the data once. And after that, you can trigger the next import from VBA:

Either with

ActiveWorkbook.Connections("Name of your query/connection").Refresh

or

ActiveWorkbook.RefreshAll