Query to get data from CSV - very slow

[ferocious12]

Hi All,

I am using this code to get the data from csv file through query. It is taking around 5 min to run this macro. Is there a way I can expedite it as the data is not too heavy?

Many thanks !

Sub Copy_CSV_Data()

On Error Resume Next
ActiveWorkbook.Queries("PairsRates5").Delete
On Error GoTo 0

Application.EnableEvents = False
Application.DisplayAlerts = False



ActiveWorkbook.Worksheets("Rates").Delete
Application.EnableEvents = True
Application.DisplayAlerts = True


   ActiveWorkbook.Queries.Add Name:="PairsRates5", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""C:\Users\Zeb\Desktop\Personal\Satori\PairsRates5.csv""),[Delimiter="","", Columns=29, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Time"", type datetime}, {""" & _
        "EURUSD-Close"", type number}, {""EURCAD-Close"", type number}, {""EURCHF-Close"", type number}, {""EURGBP-Close"", type number}, {""EURAUD-Close"", type number}, {""EURNZD-Close"", type number}, {""EURJPY-Close"", type number}, {""USDCAD-Close"", type number}, {""USDCHF-Close"", type number}, {""GBPUSD-Close"", type number}, {""AUDUSD-Close"", type number}, {""NZDUS" & _
        "D-Close"", type number}, {""USDJPY-Close"", type number}, {""CADCHF-Close"", type number}, {""GBPCAD-Close"", type number}, {""AUDCAD-Close"", type number}, {""NZDCAD-Close"", type number}, {""CADJPY-Close"", type number}, {""GBPCHF-Close"", type number}, {""AUDCHF-Close"", type number}, {""NZDCHF-Close"", type number}, {""CHFJPY-Close"", type number}, {""GBPAUD-Clo" & _
        "se"", type number}, {""GBPNZD-Close"", type number}, {""GBPJPY-Close"", type number}, {""AUDNZD-Close"", type number}, {""AUDJPY-Close"", type number}, {""NZDJPY-Close"", type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    
    Dim myConnection As WorkbookConnection
Dim mFormula As String

On Error Resume Next
ActiveWorkbook.Queries("query1").Delete
On Error GoTo 0


mFormula = _
"let Source = Csv.Document(File.Contents(""C:\data.txt""),null,""#(tab)"",null,1252) in Source"
query1 = ActiveWorkbook.Queries.Add("query1", mFormula)
    
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""PairsRates5 (2)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [PairsRates5 (2)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "PairsRates5"
        .Refresh BackgroundQuery:=False
    End With
  
    
End Sub

 

[Marc L]

Hi,​

​

as you forgot again the necessary attachment (workbook like text files) so this is just a guessing challenge and as guessing can't be coding …​

​

But you can easily activate the Macro Recorder then open manually the csv file and just well answer to the Import Assistant​

in order to get the proper ListObjects.Add codeline.​

​

As this is a common subject you can easily find some examples within this forum.​

​

 

[AlanSidman]

Use Power Query/Get and Transform Data