Import CSV data

[Assamita]

Hello everyone. Here's one for the advanced users.
I need to import data from a csv file, but I can't just open the csv because it populates some records in different columns (not only A). I think it has something to do with Excel automatically doing some text to columns to some of the records when opening the file.
It needs to be done by opening a blank file and via tab Data, From text/csv, import the file. This automatically detects the columns, delimiter, etc., and leaves the data in perfect shape.
I've recorded the process to see what the code was doing and I ended up with this:

    ActiveWorkbook.Queries.Add Name:= _
        "ExportData_ChannelFuturePricelistExternal_201912111105333", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""C:\Users\MPavilla\OneDrive - TechData\Documents\SWS Projects\Data Improvement\Vendor sources\Online\Trend Micro\ExportData_ChannelFuturePricelistExternal_201912111105333.csv""),[Delimiter=""|"", Columns=21, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAll" & _
        "Scalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""SKU"", type text}, {""Standard Discount Type"", type text}, {""Item Code"", type text}, {""Order Duration"", Int64.Type}, {""Users from"", Int64.Type}, {""Users to"", Int64.Type}, {""User_Band_ID"", Int64.Type}, {""SRP Price_EUR"", type number}, {""Product_Group"", type tex" & _
        "t}, {""Application"", type text}, {""OS"", type text}, {""Language"", type text}, {""Version"", type text}, {""Shipment_Code"", type text}, {""Price_Type"", type text}, {""Sales_Type"", type text}, {""Order_Type"", type text}, {""Description"", type text}, {""Status"", type text}, {""Up/Grade Paths"", type text}, {""Product Segment"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Change" & _
        "d Type"""
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=ExportData_ChannelFuturePricelistExternal_201912111105333;Exte" _
        , "nded Properties="""""), Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "SELECT * FROM [ExportData_ChannelFuturePricelistExternal_201912111105333]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = _
        "ExportData_ChannelFuturePricelistExternal_201912111105333"
        .Refresh BackgroundQuery:=False
    End With
End Sub

My question would be: how should I modify the code to process any csv file, with different number of columns, delimiters, etc.? I would like to have a generic function/procedure that I would provide it the file location, and it would automatically process the data.

I hope I made myself clear, it's kind of confusing.

Thank you

 

[pjmorris]

Unless there's a good reason for using VBA I would absolutely use PowerQuery, it might take a little learning (and only a little), but will allow you to manipulate the incoming data very precisely. Hope that helps a bit.

Regards

 

[Assamita]

Thank you pjmorris. I'm using VBA because this is a tool meant to run automatically. It autoruns a process at startup that checks within a folder for files that are regularly uploaded there. Is that something that could be automated with power query?

 

[pjmorris]

put simply: yes. Go to Data ribbon and on the Get & Transform tab select 'New Query' - 'From File' - 'From Folder' and then navigate to the relevant folder.

I would search for 'Powerquery, from file from folder' on the internet though I tend to use Mynda Tracy's website for much of my guidance.

You can put the 'Refresh All' command in VBA code if necessary, but it only requires a click on 'Refresh All' to import all data within the folder (it is possible to filter our files that are inappropriate).

 

[sandy666]

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference

and if really necessary but I don't think so

Private Sub Worksheet_Change(ByVal Target As Range)
Dim con As WorkbookConnection
Dim Cname As String

For Each con In ActiveWorkbook.Connections
    If Left(con.Name, 8) = "Query - " Then
    Cname = con.Name
        With ActiveWorkbook.Connections(Cname).OLEDBConnection
            .BackgroundQuery = False  'or true, up to you
            .Refresh
        End With
    End If
Next
End Sub