Forum Discussion
VBA: How to read very very big csv file into excel quickly without excel's auto formatting?
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
- 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.
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.
- 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 😄
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
