Loop Through Every CSV Files in Folder and Import Using Power Query

[BarisEMIND]

Hello,

I've got tons of CSV files I need to process. I want to load them inside one Workbook seperate worksheets and tables using Power Query. I could do with separate files as well. The reason I want to use Power Query is because the files contain language specific letters and they need to be corrected. Using Power Query to retrieve CSV files easily achieves these. However, since I've got many files to process, I need some kind of looping option. I've tried macro recording but it constructs the query for the specific file. I'm open to other solutions.

Below query works for a single specific file, I can iterate through every csv file using VBA, however, I'm not sure how to modify Table.TransformColumnTypes function so that it can be dynamic for every file since they all have different structures.

let
    Source = Csv.Document(File.Contents("C:\Raw\Data_001\Data\Accounts_RecordAccess_001.csv"),[Delimiter=",", Columns=8, Encoding=1254, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Record.id", type text}, {"Shared To.id", type text}, {"Permission Type", Int64.Type}, {"Sharing Source", Int64.Type}, {"Shared By.id", type text}, {"Shared Time", type datetime}, {"Shared From.id", type text}, {"Share Related Records", type logical}})
in
    #"Changed Type"

Below is my incomplete VBA Code.

Global Const folderPath As String = "C:/Raw/Data_001/Data/"

Sub ImportCSVFilesWithPowerQuery()
    Application.ScreenUpdating = False
    Dim fileName As String
    Dim fullFilePath As String
    Dim ws As Worksheet
    Dim location As String

    'Loop through each file in the folder
    fileName = Dir(folderPath & "*.csv")
    Do While fileName <> ""
        fullFilePath = folderPath & fileName
        location = Replace(fileName, ".csv", "")
        Set ws = ThisWorkbook.Sheets.Add
        
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & location & ";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & location & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = location
        .Refresh BackgroundQuery:=False
    End With
        fileName = Dir
    Loop


    Application.ScreenUpdating = True
    Application.CommandBars("Queries and Connections").Visible = False
End Sub

[#2]

@BarisEMIND 

I'm not sure why do you need VBA for that, From Folder connector could work.

Change Type step is optional, if you skip it most probably that doesn't affect the result. If we speak about Excel.

If every file have different structure, when we need some logic defined how to handle it for the concrete file and how to combine into resulting file (if required). 

[#3]

Hello, @SergeiBaklan 

All the files have a different stucture. I either need to load them inside a separate sheet and table, or a new workbook. I'm not sure how to approach this and tried to accomplish it with VBA as reading the folder through Power Query combines all the files inside one sheet and table which doesn't fullfil the purpose because every file has a different structure.

[#4]

What do you want by "modify Table.TransformColumnTypes function so that it can be dynamic for every file"?



https://techcommunity.microsoft.com/t5/excel/split-to-values/m-p/4073531/highlight/false#M222583

Do you intent to parse .csv like above?

[#5]

Yes, my goal is to parse all the csv files. But the importang thing is language specific characters. All the unicode characters need to be converted into the language letter.