Combine many .csv files into one excel sheet

Try this from All Stations book

PHP Code: 

Sub combineStations()
Application.ScreenUpdating = False
Dim k As Integer
Dim strPath As String
Dim myCSV As String
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
strPath = ThisWorkbook.Path
Set ObjShell = CreateObject("Shell.Application")
Set objfolder = ObjShell.Namespace(CStr(strPath))
k = 0
For Each strfilename In objfolder.Items
    If strfilename Like "St*" Then
        On Error Resume Next
        Workbooks.Open strPath & "\" & strfilename
            ActiveWindow.Visible = False
            myCSV = strfilename
            k = ThisWorkbook.Sheets(1).Cells(1, 10000).End(xlToLeft).Column + 1
            ThisWorkbook.Sheets(1).Columns(k).Value = Workbooks(myCSV).Sheets(1).Columns(1).Value
            ThisWorkbook.Sheets(1).Columns(k + 1).Value = Workbooks(myCSV).Sheets(1).Columns(2).Value
            Workbooks(myCSV).Close True
    End If
Next strfilename
Set objfolder = Nothing
Application.ScreenUpdating = True
End Sub 


Assuming all your files including the "All stations" are in the same directory.

Just load via Get & Transform. From Folder menu.

It will combine as append rather then merge. But you can use the "Removed Other Columns1" step to iterate over table and merge one another.

ImranBhatti Thanks a lot this is working as I want

If it helps you and fully answers your question , please do the following:

1: Mark your thread as "Solved" (On top right of your first post in the current thread click on "Thread Tools>Mark as Solved"
2: Give some rep to the post that helped you. (At bottom left side of the post that helped click on * button)

Thanks for the rep+