VBA code to open CSV file and extract the data in a Excel sheet

Raychin

New Member
Joined
Apr 7, 2022
Messages
25
Office Version
  1. 2013
Platform
  1. Windows
Hello! I have a issue with making a code that will extract the data/table from the csv file to a designated Excel(.xlxs) file and sheet. The thing is that the full table is only visible when .csv file is open with Excel Power Query option (Data--> From Text/CSV). So i tried to make some code that will do that for me, instead i to have to open the file, process it and save the content. But i have an issue....
Can you please help me again? I'm stuck and don't know how to complete the code, if it possible at first place.
Here is my code so far :

VBA Code:
Sub PowerQueryTransform()

    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=TSO Metered Data;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "SELECT * FROM [TSO Metered Data]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "TSO Metered Data"
        .Refresh BackgroundQuery:=False
    End With
    Range("C21").Select
    Windows("Meterd Monthly Data Colector.xlsm").Activate
  
End Sub
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
try this on a copy of your file. I use it a lot, its not the fastest method but it works every time.

creat a sheet and name it "Import" you can hide it if you want to

VBA Code:
Sub Get_data_from_CSV_File()

Dim rs As Worksheet
Dim fName As String

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = False

Set rs = Worksheets("Import")

fName = Application.GetOpenFilename("Text Files (*.csv), *.csv")
If fName = "False" Then Exit Sub

rs.Cells.ClearContents 'clear data on import sheet

    Open fName For Input As #1
    r = 1
   
    Do Until EOF(1)
    Line Input #1, LineFromFile
   
    lineitems = Split(LineFromFile, ",")
   
    For c = 0 To UBound(lineitems)
    rs.Cells(r, c + 1) = lineitems(c)
    Next c
   
    r = r + 1
    Loop
   
    Close #1
   
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayStatusBar = True
MsgBox r & " Rows Imported"

End Sub
 
Upvote 0
Solution
@rpaulson Thank you! Now the data is in the "Import" sheet. Well now i have to complete the process - this data have to be processed with Power Query so the real table can show off and to be useful for the process. can it be done by VBA?
 
Upvote 0
I just past the 10min Edit period - it doesn't need PQ just a simple code for convert Text-to-Column is enough. Again - thank you very much rpaulson !
 
Upvote 0
Well, unfortunately i need Power Query to kicks in after the .csv data is imported in sheet "Import". Can it be done with VBA?
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top